We are using UCM 10g for our Web Content Management environment. We use Oracle Text Search (DB version 184.108.40.206) as our search engine. To maintain performance of the indexes, we have a daily job scheduled in database to optimize the indexes. But it takes around 8 hours to optimize a 0% fragmented index. Any idea why it takes long time? Any suggestions to reduce the time it takes to run?
Before running optimization script - Fragmentation details:*
total size of $I data: 838,699,490 (799.85 MB)
$I rows: 24,973,528
estimated $I rows if optimal: 24,973,528
estimated row fragmentation: 0 %
Time taken to optimize the above index:_
=============== FT_IDCTEXT2 Start Date and Time: 28=FEB-2013 02:37:22===========
PL/SQL procedure successfully completed.
=============== FT_IDCTEXT2 End Date and Time: 28=FEB-2013 10:55:55=============
select '=============== FT_IDCTEXT2 Start Date and Time: ' ||sysdate||'=============== ' from dual;
ctx_ddl.optimize_index('FT_IDCTEXT2','FULL', parallel_degree =>'10');
select '=============== FT_IDCTEXT2 End Date and Time: ' ||sysdate||'=============== ' from dual;
I believe it would be a question for DB to tell why the fragmentation is taking such a long time for un-fragmented db schema . For UCM to improve it's search , indexing processes this action plan is done . But , since the DB itself is taking such an inordinate long time to do optimization , I think it would be a DBA question :-) .