| 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 Mike Burden <michael.burden_at_capgemini.co.uk>
(if that email address didn't require changing)
On Fri, 29 Jan 1999 16:26:39 +0000, you wrote:
>So .... we can re-org indexes on the fly in 8i eh. Can we do tables too?
>
there are no truely automated table re-orgs for organization HEAP tables (organization INDEX yes, you can rebuild them online) as yet. re-org'ing a table is a big big deal. To truely re-org a table, you would do things like:
so, rowids would change. that would imply that indexes (all of them) must be rebuilt, some replication stuff would break (the remaining pieces that use rowids - in support of legacy replication), and so on...
>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.
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 - 11:06:57 CST
 |
 |