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ł

I've been tagged 3 times; 8 things about me

Few busy days without any blog reading and after I've gone through my feeds it occurred that I've been tagged 3 times:

1. By Eddie Awad
2. By H.Tonguç YILMAZ
   
3. By Jorrit Nijssen (Jornica)
   

Thanks You for tagging.
Now I feel that I really need to write something about me:

1. I was born and grow up in Krosno in south-east Poland (see satellite picture),
2. I 15th when Communism fall down in Poland. At this moment Poland is member of Schengen Agreement, and I can travel around Europe without passport. Lot of changes in last 19 years,
3. I'm Thawte Web of Trust Notary,
   
4. In 1993 I took 2nd place in Nation Contest in Programming for College Students. Unfortunately I was 6 month too old to take part in international final :(
5. In 1990 I've sold my first program; small DB written in Clipper. I did not think that this program will be used for so many years. In 1999 I had to do some changes to support year 2000,
6. My first program was written in Basic and was run on PSP-80 (page in Polish)
   
7. I'm married and we have 2 daughters (6 and 2 years old),
8. My favourite TV show is Top Gear with Jeremy Clarkson , Richard Hammond and James May.
   

I will not invite anyone directly as I do not want to accused of spam. All my readers that have blogs are welcome to post 8 things about themselves.

Cheers,
Paweł