Re: Growth of Index

Chandra Meyyappan <chandra_at_mink.mt.att.com> wrote:
>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
>

So far as I understood your problem, the table only really grows at the end, because of the timestamp. In a normal case you would start of by creating the index with a large value for PCTFREE (20 percent in your case). Space for dropped records is reclaimed only if the same index value reappears again, if records are added only at one side of the index, the index will continue to grow, and space is not reclaimed. By using the statements
analyze index ... compute statistics
and
select * from user_indexes where index_name = ... you will be able to see a PCT_USED value which is more or less a measure for the efficiency of the index. My own guideline is : start thinking about recreating the index if this value is below 80 percent and certainly do something about it if it drop below 70 percent. You stated you run a 24 x 7 system. In Oracle7 however you will be able to index a table that is in use.

Hope this helps

Sybrand Bakker
Senior IS Analyst Received on Mon Jul 24 1995 - 00:00:00 CEST