5 Replies Latest reply: Jun 27, 2012 9:22 AM by ReinerKuehl RSS

    Optimize index for domain index with composite domain index (CDI) very slow

      I am on Enterprise Edition.
      We are using the new feature "Composite Domain Index" for a Domain index on a very large table (>250.000.000 rows). It really helps us with mixed queries. We added two number columns using FILTER BY.
      We have lots of DML on this table. Therefore, we are executing synchronize and optimize once the week. The synch behaves pretty normal. But "optimize_index" takes a very very long time to complete. I have switsched on 'logging' for the optimize process. The $I table takes some time but is finished normally. But the optimization of the $S table (that is the table created for the CDI feature) is running over 12 hours now - and far from being finished. From the logfile, I can see that it optimizes 1000 rows every 20 minutes. Here is the output of the logfile:
      Oracle Text,
      14:33:05 06/26/12 begin logging
      14:33:05 06/26/12 event
      14:33:05 06/26/12 process $N for optimize: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
      14:33:16 06/26/12
      14:33:16 06/26/12
      15:24:06 06/26/12 optimize sdata: SEQDEV.GEN_GES_DESCRIPTION_CTX_I
      15:24:29 06/26/12 19656965 invalid docids read from $N table
      15:24:29 06/26/12 $S table optimize initialization complete
      15:24:29 06/26/12 starting $S row optimization
      15:43:04 06/26/12 Old $S: 1000 rows read
      15:43:04 06/26/12 New $S: 998 rows written
      16:02:39 06/26/12 Old $S: 2000 rows read
      16:02:39 06/26/12 New $S: 1998 rows written
      16:21:44 06/26/12 Old $S: 3000 rows read
      16:21:44 06/26/12 New $S: 2998 rows written

      I haven't found a recommendation from Oracle not to use "optimize_index" for Domain Indexes with CDI. But in my case, it would be much faster just to drop and recreate the Domain Index in question.

      Has anybody made similar experiences with the CDI feature?