Re: Index extents keep increasing but data remains the same

In a previous article, szander_at_tyndall2.tyndall.com.au (Stephen Zander) says:

>
>Partially filled pages in a b-tree index do not get reused until
>they are completely empty. If you are not deleting _all_ the
>rows from the table, when the new rows are added they will use
>new index pages and so more space. Before someone flames me for
>that, I'm assuming the your new key values are all greater than
>the old keys. If they fall inside the current ket range then
>obviously that's not necessarily true.
>
>If the table ends up about the same size each day, why not create
>the index with a initial allocation large enough to hold all the
>pages it's likely to use? Then you won't suffer from a gradual
>fragementation of the tablespace as you create and delete the
>index.
>

The key value of my indexes are all greater than the old keys.

I have just recreated the indexes of a different table in which the keys also increment. Strangely enough nothing get deleted from this table or updated(We plan to archive in a few months). The index originally had many extents but after the reindex it has reduced in size. Received on Thu Sep 29 1994 - 02:10:38 CET