CREATE TABLE customers_text_demo
(
    id    NUMBER PRIMARY KEY,
    name  VARCHAR2(200)
);

INSERT INTO customers_text_demo VALUES (1, 'SARGSYAN ARTYOM');
INSERT INTO customers_text_demo VALUES (2, 'SARGSIAN ARTUR');
INSERT INTO customers_text_demo VALUES (3, 'SARUKHAN MARTIN');
INSERT INTO customers_text_demo VALUES (4, 'BANK OF ARMENIA');
INSERT INTO customers_text_demo VALUES (5, 'BANKING SERVICES');
INSERT INTO customers_text_demo VALUES (6, 'ARTASHES GEVORG');
INSERT INTO customers_text_demo VALUES (7, 'ARTYOM HAKOBYAN');
INSERT INTO customers_text_demo VALUES (8, 'ARTAK PETROSYAN');
COMMIT;

--Create WORDLIST preference

--This preference enables prefix indexing so searches like SARG* or ART* can work efficiently.

BEGIN
  CTX_DDL.CREATE_PREFERENCE('CUST_WORDLIST', 'BASIC_WORDLIST');
END;
/
--Set preference attributes
BEGIN
  CTX_DDL.SET_ATTRIBUTE('CUST_WORDLIST', 'PREFIX_INDEX', 'TRUE');
  CTX_DDL.SET_ATTRIBUTE('CUST_WORDLIST', 'PREFIX_MIN_LENGTH', '2');
  CTX_DDL.SET_ATTRIBUTE('CUST_WORDLIST', 'PREFIX_MAX_LENGTH', '6');
  CTX_DDL.SET_ATTRIBUTE('CUST_WORDLIST', 'SUBSTRING_INDEX', 'TRUE');
END;

/*
Meaning

PREFIX_INDEX = TRUE
tells Oracle Text to store prefix tokens

PREFIX_MIN_LENGTH = 2
prefixes shorter than 2 characters are not indexed

PREFIX_MAX_LENGTH = 6
prefixes longer than 6 characters are not stored as separate prefix entries
*/

CREATE INDEX idx_customers_text
ON customers_text_demo(name)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS ('WORDLIST CUST_WORDLIST');

BEGIN
  CTX_DDL.SYNC_INDEX('IDX_CUSTOMERS_TEXT');
END;

SELECT id, name
FROM customers_text_demo
WHERE CONTAINS(name, 'SARG%') > 0
ORDER BY id;

SELECT id, name
FROM customers_text_demo
WHERE CONTAINS(name, 'BAN_') > 0
ORDER BY id;