Re: Growth of Index

From: Bob Johnson <bobj_at_magna.com.au>
Date: 1995/07/29
Message-ID: <3vca57$ivk_at_kettle.magna.com.au>#1/1


In article <DC33FB.5Mr_at_nntpa.cb.att.com>, chandra_at_mink.mt.att.com says...
>
>Oracle gurus:
>
>I have a problem. I've a table with 5 million records, which grows at
 the rate
>of 1 million records everyday. The is a cleanup process that deletes the
 oldest
>1 million records. The index is on a timestamp field. My questions is,
 how will
>my index grow. Because if I delete 1 million records, will the blocks be
>released for new index rows within the index tablespace or will the be
 adding
>new leaf nodes and not release the space. Oracle says that if there is a
>performace degradation due to sparce index, the only way to correct the
 problem
>is drop and recreate the index. This would not be feasible for my
 situation
>because we operate as 24 by 7.
>
>Could someone shed some information on this regard.
>
>Thank you in advance,
>
>Chandra
>chandra_at_mink.mt.att.com
>

Don't count as a guru but I can give an answer anyway.

When doing so much change to the index (deleting 20% or rows and inserting another 20%), it will become exceedingly fragmented, unbalanced and all these other yucky things. what you should do is

  1. drop the index(es)
  2. drop your rows
  3. add your rows
  4. build your index(es)

You will also find that this is much quicker, uses less rollback space, uses index tablespace much more efficiently, and blah, blah....

The fragmentation in your data tablespace however is likely to be a problem, if you have operations/reports the read say 10% of your data, they will also quite likely work quicker by not using the index(es). If you want to know how to avoid using indexes, drop me an email.

Bob Johnson
Computervision
Sydney Australia Received on Sat Jul 29 1995 - 00:00:00 CEST

Original text of this message