Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: index blocks reuse

Re: index blocks reuse

From: <kennethkoenraadt_at_no-spam.hotmail.com>
Date: Thu, 19 Sep 2002 19:47:59 GMT
Message-ID: <3d8a255a.1919239@news.mobilixnet.dk>


On 19 Sep 2002 07:43:28 -0700, lve3162_at_yahoo.com (lve) wrote:

>We have the following composite BTree index on a table :
>
>Index Columns:
>
>OrderDate date
>OrderNum Number(30).
>
>The table has more than 200 million rows. Thousands of rows in the
>table are inserted every day. Order Date is the current date and
>OrderNum starts from 1 every day. The index has grown to more than 10
>GBs in size. I have started deleting rows older than 100 days from
>the table,starting with the oldest record. The deletions are being
>done in date order starting with the oldest day in the tabl. Rows
>should get deleted from the index also.
>
>Question is : Will the space in the index be reused after deletion?
>Since the leaf pages in the index will have ordered data, deleted the
>data from the table in date order should free up 100 per cent space in
>such index blocks. Would this space be used automatically for new
>index data (since there are new insertions to the table daily) or do
>I have to coalesce/rebuild the index to reuse thus freed space?
>
>Percent free is 10% for the index, and percentage increase for the
>index extents is zero.

The space will be reused, yes.

You probably don't need to rebuild the index. Since you delete a coherent range of old values from the table, you will thereby free a range of whole blocks in the index, which are then free to be used for the new values.

It would be more problematic if you deleted, say, 10% of the rows distributed evenly over the OrderDate range. Then you would only delete 10% of the entries in each index blocks, and none of the blocks would be free for the new values of OrderDate (when entries are deleted from an index block, those entries can only store the same values again. Unless ALL the entries are deleted, then the block becomes ready for reuse).

Received on Thu Sep 19 2002 - 14:47:59 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US