--CTXRULE Index

CREATE TABLE transaction_rules
(
    rule_id      NUMBER,
    rule_name    VARCHAR2(100),
    rule_text    VARCHAR2(4000)
);

--Insert rules:

INSERT INTO transaction_rules VALUES
(1,'ATM transaction',
 'ATM OR "cash withdrawal"');

INSERT INTO transaction_rules VALUES
(2,'Loan payment',
 'loan AND payment');

INSERT INTO transaction_rules VALUES
(3,'Salary',
 'salary OR payroll');

INSERT INTO transaction_rules VALUES
(4,'Card transfer',
 '(transfer AND card) OR "card payment"');

COMMIT;

CREATE INDEX idx_transaction_rules
ON transaction_rules(rule_text)
INDEXTYPE IS CTXSYS.CTXRULE;

SELECT rule_id,
       rule_name
FROM transaction_rules
WHERE MATCHES(rule_text,
              'Online transfer to card 4587') > 0;
              
SELECT rule_id, rule_name
FROM transaction_rules
WHERE MATCHES(rule_text,
              'ATM cash withdrawal Yerevan branch') > 0;