| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Rebuild Indexes
I'm curious, how does coding of an application effect index block splits. Does Oracle know because the index is based on a monotonic key that the splits should be 100/0. I wouldn't call that application coding. Do the 100/0 splits occur unless numbers are lost due to the sequence being kicked out of cache or something else causes values to be skipped?
Ian MacGregor
Stanford Linear Accelerator Center
ian_at_SLAC.Stanford.edu
-----Original Message-----
From: Jonathan Lewis [mailto:jonathan_at_jlcomp.demon.co.uk]
Sent: Thursday, February 19, 2004 1:43 AM
To: oracle-l_at_freelists.org
Subject: Re: Rebuild Indexes
John,
Yes, the "biggest issue" requires three simultaneous events:
column is monotonic increasing (or monotonic decreasing, though that's a rare beast) with time. That includes indexing on date/time stamps.
MOST, but not all data from the past is deleted, so index leaf blocks can be left holding just one or two entries.
Queries have to scan for current data by starting at the beginning of the index.
In this case, the scans will find that they are scanning through a large number of old, nearly empty, leaf blocks to find a few current rows.
For such indexes your remedy is good: a one-off rebuild when you realise what's been going on, followed by a regular coalesce to repack as many of the near-empty leafs as possible.
Even so, this may not make much different to performance as you still have to scan the index - and if the scan ALSO requires you to visit the table for every index entry, the cost of the redundant visits to the table is probably going to be serious.
Under Oracle 8 and 10, an index on a sequence-based (monotonic increasing) column would pack to 100% if the application was coded properly - many weren't, of course. But there is a bug in Oracle 9.2 (I haven't checked 9.0) which results in 50/50 block splits, and 50% packing on single row inserts.
Regards
Jonathan Lewis
http://www.jlcomp.demon.co.uk
The educated person is not the person
who can answer the questions, but the
person who can question the answers -- T. Schick Jr
Next public appearances:
March 2004 Hotsos Symposium - The Burden of Proof March 2004 Charlotte NC OUG - CBO Tutorial April 2004 Iceland
One-day tutorials:
http://www.jlcomp.demon.co.uk/tutorial.html
Three-day seminar:
see http://www.jlcomp.demon.co.uk/seminar.html
____UK___February
____UK___June
The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html
Naveen,
I believe the problem is not with a monotonically increasing index (which should NOT be rebuilt as it is of no use) - rather it is with a monotonically increasing index that is also being _deleted. This is typical of indexes on FIFO-type tables such as the FND_CONCURRENT_REQUESTS in Apps (any version). In the latter case, you should perform a 'one-time' index rebuild of such monotonically-increasing, followed by regular COALESCE of the indexes to 'reclaim' 'lost' space. Jonathan/Richard Foote might have something to add on this....
John Kanagaraj <><
DB Soft Inc
Phone: 408-970-7002 (W)
Listen to great, commercial-free christian music 24x7x365 at http://www.klove.com
>-----Original Message-----
>From: oracle-l-bounce_at_freelists.org
>[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Naveen,
>Nahata (IE10)
>Sent: Tuesday, February 17, 2004 11:24 PM
>To: oracle-l_at_freelists.org
>Subject: RE: Rebuild Indexes
>
>
>John,
>
>Would it be advisable to go for Index rebuilds in case of
>index based on
>monotonically increasing columns? As much as I understand (and
>I must admit,
>i don't know much about the internals of indexes), in case of
>such indexes
>the problem of 'leaf block split' should not happen.
>
>Regards
>Naveen
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- Please see the official ORACLE-L FAQ: http://www.orafaq.com ---------------------------------------------------------------- To unsubscribe send email to: oracle-l-request_at_freelists.org put 'unsubscribe' in the subject line. -- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html -----------------------------------------------------------------Received on Tue Feb 24 2004 - 11:12:29 CST
 |
 |