| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: How to interpret INDEX_STATS
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
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 - 09:45:30 CST
 |
 |