Skip to main content

Posts

Showing posts from October, 2009

Oracle Text Index -- Real time implementation

Problem:

Create a context index on a table which has around 100 million records adds upto a size of 120GB. Index creation takes more than 20 days to complete. Along with 100 million records there are around 50 million more records which will be added to this table which makes the table grow to a size of 180GB and with this data index creation is expected to take even more time.

Following steps were being used to create the index.
set timing on time onexec Ctx_Ddl.Create_Preference('SCB', 'BASIC_WORDLIST'); exec ctx_ddl.set_attribute('SCB', 'wildcard_maxterms',1 5000) ;exec ctx_ddl.set_attribute('SCB', 'substring_index', 'TRUE') ;execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/oat_lg/rrotmedb/archive/ctx/'); execute CTXSYS.CTX_OUTPUT.START_LOG('g_SCB_IDX_gmis.LOG'); exec ctx_output.add_event(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID); drop index NORKOM56.SCB_TRANS_IDX force;CREATE INDEX NORKOM56.s…