Friday, 27 July 2012

Domain Index creation script

Here is an example of the Domain index creation script for a table column -


alter session set "_sort_multiblock_read_count"=16;

EXEC Ctx_Ddl.drop_preference('USE');
EXEC Ctx_Ddl.Create_Preference('USE', 'BASIC_WORDLIST');
EXEC ctx_ddl.set_attribute('USE', 'wildcard_maxterms',15000) ;

EXEC ctx_ddl.drop_preference('ABC_LEXER');
EXEC ctx_ddl.create_preference('ABC_LEXER','basic_lexer');
EXEC ctx_ddl.set_attribute('ABC_LEXER','printjoins','-.,&/');

EXEC Ctx_Ddl.drop_preference('dimp_USE');
EXEC ctx_ddl.create_preference('dimp_USE', 'BASIC_STORAGE');
EXEC ctx_ddl.set_attribute('dimp_USE', 'I_TABLE_CLAUSE','tablespace ABC_CM_DATA01 STORAGE (INITIAL 10M NEXT 10M)');
EXEC ctx_ddl.set_attribute('dimp_USE', 'K_TABLE_CLAUSE','tablespace ABC_CM_DATA01 STORAGE (INITIAL 10M NEXT 10M)');
EXEC ctx_ddl.set_attribute('dimp_USE', 'N_TABLE_CLAUSE','tablespace ABC_CM_DATA01 STORAGE (INITIAL 10M NEXT 10M)');
EXEC ctx_ddl.set_attribute('dimp_USE', 'I_INDEX_CLAUSE','tablespace ABC_CM_DATA01 STORAGE (INITIAL 10M NEXT 10M) COMPRESS 2');


EXEC CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/abc_lg/abcdb/archive/ctx/');
EXEC CTXSYS.CTX_OUTPUT.START_LOG('g_ABC_IDX_mYth1.LOG');
EXEC CTX_OUTPUT.ADD_EVENT(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID);

drop index ABC_TRANS_IDX force;

CREATE INDEX ABC_TRANS_IDX ON <SCHEMA_NAME>.<TABLE_NAME> (CONCAT_SEARCH_INFO) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('Sync (on commit) WORDLIST USE STORAGE dimp_USE STOPLIST CTXSYS.EMPTY_STOPLIST LEXER ABC_LEXER');

or index can also be created as below -


CREATE INDEX ABC_TRANS_IDX ON <SCHEAMA_NAME>.<TABLE_NAME> (CONCAT_SEARCH_INFO) INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('Sync (on commit) WORDLIST USE STORAGE dimp_USE STOPLIST CTXSYS.EMPTY_STOPLIST LEXER ABC_LEXER') parallel 4 local;
exit;


-------------------------------------------------------------------------------------------------------------


TO LOOK AT THE DESCRIPTION OF THE SCRIPT AND CREATION SCRIPTS -
---------------------------------------------------------------

SELECT CTX_REPORT.CREATE_INDEX_SCRIPT ('INDEXNAME') FROM DUAL;
SELECT CTX_REPORT.DESCRIBE_INDEX ('INDEXNAME') FROM DUAL;

No comments:

Post a Comment