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
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