How to shrink Oracle Context Index

On of databases I've found that one of Context Indexes is getting bigger and bigger everyday. It was around 5-10% grow per day for table DR$MY_TEXT_I$I which contains tokens(word). No of records indexed was almost the same, but index grown was unexpectedly big. It was due fact, that quite many rows were modified every day, and it caused re-indexing those rows during index synchronization (CTX_DDL.SYNC_INDEX). So I've decided to reclaim this space.

Method 1.

This was my first though:

• Drop Index ... /Create Index ...
• or Alter Index ... Rebuild

But those methods have some disadvantages:

• Users cannot perform text searches during this operation,
• It does not solve problem for longer time, as index will still grow ...

So I had to find root cause and eliminate it.

Method 2.

While looking for root cause I've find out, that Index has never been optimized, so it kept old (obsolete) data. My solution was:
SQL> exec ctx_ddl.optimize_index('MY_TEXT_I', 'FULL', 120);
SQL> alter table DR$MY_TEXT_I$I enable row movement;
SQL> alter table DR$MY_TEXT_I$I shrink space cascade;

With this method table DR$MY_TEXT_I$I took 30% of its original size.
So let me explain why this worked. With ctx_ddl.optimize_index context index was internally optimized. It means information about old documents were completely deleted and index was internally minimized. Remember - third parameter limits time (in minutes) allowed for optimization. If it is really big index it can take hours. But you can run this optimization many times, until your index will be fully optimized.
Then I've just shrunk table (shrink space cascade). But this operation requires to enable row movement on table first.

But this was one time operation. To avoid this problem in future I've scheduled job to run ctx_ddl.optimize_index on regular basis. Now this table has grown a little, but is no longer growing so fast.

Foot note:

• It was tested on Oracle EE 10gR2 (10.2.0.3),
• I'm not sure if row movement is supported by Oracle for Context Index table DR$<index>$I. It worked for me and I did not experience any problems since turning it on.
  

Cheers, Paweł