Re: Growth of Index
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
- drop the index(es)
- drop your rows
- add your rows
- 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