Tips of Handling Oracle Text Optimization Issues

Example of Sql to run optimization

begin
ctx_output.start_log('opt_rebuild_TEST_IDCTEXT1');
ctx_output.add_event(ctx_output.event_opt_print_token);
ctx_ddl.optimize_index(idx_name=>'TEST_IDCTEXT1',optlevel=>'REBUILD');
ctx_output.end_log;
end;
/

If proc error out  straight away, logs show it started and ended without rows in between, it indicates it may have shadow index, need to drop it
To drop shadown index:    exec ctx_ddl.drop_shadow_index('TEST_IDCTEXT2');

Check idx_options
select idx_name, idx_status, idx_option from ctxsys.dr$index where idx_name='TEST_IDCTEXT1';
 to check idx_option   (this is assuming TEST_IDCTEXT1 is current index)
if idx_option=C....then
conn ctxsys
update dr$index set idx_option='CY' where idx_name='TEST_IDCTEXT1';



How to add debug information during optimziation
begin
ctx_output.start_log('opt_reb_TEST_IDCTEXT1_'||to_char(sysdate,'DD-MON-YY_HH24MISS')||'.log');
ctx_output.add_event(ctx_output.event_opt_print_token);
ctx_output.add_event(ctx_output.EVENT_DRG_DUMP_ERRORSTACK,ctx_output.DRG_DUMP_ALL_ERRORS);
ctx_ddl.optimize_index(idx_name=>'TEST_IDCTEXT1',optlevel=>'FULL');
ctx_output.end_log;
end;

How to use sql to rebuild index online
ctx_output.start_log('TEST_IDCTEXT1_Rebuild14july2012.log');
alter index UCM_SEARCH.TEST_IDCTEXT1 rebuild online parameters('replace memory 200M');
ctx_output.end_log;