Database Software

Hi,

I am trying to create a text index on a range partitioned and sub partitioned table which is of size 160gb.

Am using the following option.

exec Ctx_Ddl.drop_Preference('SC1');
exec Ctx_Ddl.Create_Preference('SC1', 'BASIC_WORDLIST');
exec ctx_ddl.set_attribute('SC1', 'wildcard_maxterms',15000) ;
exec ctx_ddl.set_attribute('SC1', 'substring_index', 'TRUE') ;

execute CTXSYS.CTX_ADM.SET_PARAMETER ('LOG_DIRECTORY','/oat_lg/otmwdb/archive/ctx/');
execute CTXSYS.CTX_OUTPUT.START_LOG('g_SCB_IDX_gmis.LOG');
exec ctx_output.add_event(CTX_OUTPUT.EVENT_INDEX_PRINT_ROWID);

CREATE INDEX NORKOM56.scb_trx_mith ON NORKOM56.transactions_mith
(CONCAT_SEARCH_INFO)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('Sync (on commit) MEMORY 1073741823 wordlist SC1');


If i dont set exec ctx_ddl.set_attribute('SC1', 'wildcard_maxterms',15000) ; then the default wildcard_maxterms is taken as 5000. With the value as 5000 my index creation takes around 20 hours.


But with wildcartd_maxterms as 15000 it doesnot come out even after 10 days.

So we thought well create the index in parallel and used the following to create the index.

CREATE INDEX NORKOM56.scb_trx_mith ON NORKOM56.transactions_mith
(CONCAT_SEARCH_INFO)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS('Sync (on commit) MEMORY 1073741823 wordlist SC1') parallel 8;

It creates 8 parallel processes but it starts creating locks and the index creation gets stuck. It does not even imporve even after using online option.

I would like to know how to increase the performance of text index. If using parallel is the only option then how to get rid of the locks.

Thanks,
mYth