Re: How to interpret INDEX_STATS

So .... we can re-org indexes on the fly in 8i eh. Can we do tables too?

Thomas Kyte wrote:

> A copy of this was sent to chuckh_at_safeaccess.net (Chuck Hamilton)
> (if that email address didn't require changing)
> On Fri, 29 Jan 1999 15:44:13 GMT, you wrote:
>
> >How do you interpret the data in INDEX_STATS to see if an index needs
> >to be reorganized? I have an index that has 13 million distinct keys,
> >0 deleted leaf rows, and pct_used of only 68. The index isn't
> >oversized as it extends every 3 to 4 days. Is this index a reorg
> >candidate? Why would it only be using 68% of the space if there are no
> >deleted rows?
>
> one reason could be splits. block gets full, insert another record, index block
> splits and you have 2 blocks each 1/2 half (approx)...
>
> If you insert 'scattered' values (values that fit between other values) this
> will happen due to the splits. You don't see it as often with indexes on
> columns that are made from sequences (monotomically increasing values) but they
> have their own set of problems.
>
> You can temporarily reclaim some of the space by rebuilding the index (hey, in
> 8i you can rebuild the index without stopping other insert/update/delete
> activity as well, an online index rebuild) but it will quickly go back to the
> state its in (due to the splits again).
>
>
> Thomas Kyte
> tkyte_at_us.oracle.com
> Oracle Service Industries
> Reston, VA USA
>
> --
> http://govt.us.oracle.com/ -- downloadable utilities
>
> ----------------------------------------------------------------------------
> Opinions are mine and do not necessarily reflect those of Oracle Corporation
>
> Anti-Anti Spam Msg: if you want an answer emailed to you,
> you have to make it easy to get email to you. Any bounced
> email will be treated the same way i treat SPAM-- I delete it.
Received on Fri Jan 29 1999 - 10:26:39 CST