1 Reply Latest reply: Mar 2, 2013 2:21 AM by Srinath Menon-Oracle RSS

    $I rows: 24,973,528 takes 8 hours to optimize against 0% fragmentation

    subbaiah
      We are using UCM 10g for our Web Content Management environment. We use Oracle Text Search (DB version 11.1.0.7) 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=============


      Script:*

      select '=============== FT_IDCTEXT2 Start Date and Time: ' ||sysdate||'=============== ' from dual;
      begin
      ctx_ddl.optimize_index('FT_IDCTEXT2','FULL', parallel_degree =>'10');
      end;
      /
      select '=============== FT_IDCTEXT2 End Date and Time: ' ||sysdate||'=============== ' from dual;