Useful shortcuts for vi editor

Wednesday 25 January 2017

How to define/use Oracle domain index?

What is domain index?:
Oracle says that domain indexes reformed beyond to the basic types of hash, bitmapped, and B-tree indexes that developers define their own specified indexes to gain more speed on applications such as video based data. 

Define domain index:

BEGIN
CTXSYS.CTX_DDL.DROP_PREFERENCE('EMP_DATASTORE');
CTXSYS.CTX_DDL.DROP_PREFERENCE('EMP_LEXER');
CTXSYS.CTX_DDL.DROP_PREFERENCE('EMP_WORLDLIST');
END;
/
DROP INDEX SYS.IX_EMP_FULL
/
ALTER TABLE HR.EMPLOYEES DROP COLUMN SEARCH_TEXT
/
BEGIN 
CTX_DDL.CREATE_PREFERENCE('EMP_DATASTORE','MULTI_COLUMN_DATASTORE');
CTX_DDL.SET_ATTRIBUTE('EMP_DATASTORE','COLUMNS','EMPLOYEE_ID,FIRST_NAME,LAST_NAME,EMAIL,PHONE_NUMBER,HIRE_DATE');
CTX_DDL.CREATE_PREFERENCE('EMP_LEXER','BASIC_LEXER');
CTX_DDL.SET_ATTRIBUTE('EMP_LEXER','BASE_LETTER','YES');
CTX_DDL.CREATE_PREFERENCE('EMP_WORLDLIST','BASIC_WORDLIST');
CTX_DDL.SET_ATTRIBUTE('EMP_WORLDLIST','SUBSTRING_INDEX','TRUE');
END;
/
ALTER TABLE HR.EMPLOYEES ADD SEARCH_TEXT VARCHAR(1)
/
CREATE INDEX SYS.IX_EMP_FULL ON HR.EMPLOYEES (SEARCH_TEXT) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('datastore EMP_DATASTORE lexer EMP_LEXER wordlist EMP_WORLDLIST sync(on commit)')
/

Optional:

Remove a word from stopwords:
SQL>
BEGIN
CTXSYS.CTX_DDL.REMOVE_STOPWORD('CTXSYS.DEFAULT_STOPLIST', 'can', NULL);
END;
/

Add a word to stopwords:

SQL>
BEGIN
CTXSYS.CTX_DDL.ADD_STOPWORD('CTXSYS.DEFAULT_STOPLIST', 'alper', NULL);
END;
/

Check it;

SQL>
SELECT * FROM CTX_STOPWORDS WHERE spw_word like '%alper%';

Note that after this kind of operation, related domain index should be dropped and redefined again.


Usage:

Prepare test data:
Insert into HR.EMPLOYEES
   (EMPLOYEE_ID, FIRST_NAME, LAST_NAME, EMAIL, PHONE_NUMBER, 
    HIRE_DATE, JOB_ID, SALARY, MANAGER_ID, DEPARTMENT_ID)
 Values
   (1000, 'Alper', 'Ozsoyler', 'ozsoyler.blogspot.com', '1111', 
    TO_DATE('10/10/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'IT_PROG', 10000, 100, 90);
COMMIT;

Test it:

SQL>
SELECT *
  FROM EMPLOYEES
 WHERE CONTAINS (SEARCH_TEXT, 'ALPER') > 0
       AND CONTAINS (SEARCH_TEXT, '10/10/2010') > 0

Extra:

If you deal an update on employees.first_name column, the domain index does not synchronize because when only SEARCH_TEXT column updates, EMP_DATASTORE will be updated with new data. There will not be any problem when you issue insert or delete operations.

e.g.

SQL>
UPDATE HR.EMPLOYEES
   SET last_name = 'OZ'
 WHERE employee_id = 1000;
COMMIT;

SQL>

SELECT *
  FROM EMPLOYEES
 WHERE     CONTAINS (SEARCH_TEXT, 'OZ') > 0
       AND CONTAINS (SEARCH_TEXT, '10/10/2010') > 0;
-- Result will be empty

-- To solve it;
SQL>
UPDATE HR.EMPLOYEES
   SET search_text = '1'
 WHERE employee_id = 1000;
COMMIT;

-- or

-- as SYSDBA
SQL> 
SELECT IDX_ID FROM CTXSYS.CTX_INDEXES WHERE IDX_NAME='IX_EMP_FULL'; 
--e.g. 1063

SQL> 

INSERT INTO CTXSYS.DR$PENDING (PND_CID,PND_PID,PND_ROWID,PND_TIMESTAMP)
SELECT 1063, 0, ROWID, CURRENT_DATE
FROM hr.employees
WHERE employee_id = 1000;
COMMIT;

SQL>

CALL CTX_DDL.SYNC_INDEX('IX_EMP_FULL');

-- Check index;

SQL>
SELECT *
  FROM HR.EMPLOYEES
 WHERE     CONTAINS (SEARCH_TEXT, 'OZ') > 0
       AND CONTAINS (SEARCH_TEXT, '10/10/2010') > 0;

Attention: Lastly, do not forget recompile all objects with UTLRP.
SQL> EXEC UTL_RECOMP.recomp_serial();

Ref: https://stackoverflow.com/questions/1916244/how-to-sync-and-optimize-an-oracle-text-index (check BernardMarx comment)

http://www.dba-oracle.com/t_domain_index.htm