RE: Antwort: Re: How to find out if my index is optimal or scattered

From: Bobak, Mark <>
Date: Tue, 29 Sep 2009 09:01:55 -0400
Message-ID: <>


You're correct. If the nature of the table and application design is that you have a monotonically increasing, sequence generated PK, and you're always deleting the lowest PK value first, then you've got leaf blocks being emptied and falling off the bottom "left side" of the tree, and new blocks being added to the bottom "right side". When that block on the left side has the last index entry deleted from it, it's placed on the free list. When it's time for a new block on the right side, Oracle will unlink the block from the left side, and link it in on the right side, so, blocks are very efficiently reused.

For a well-written paper on the subject, with well thought out and proven test cases, *including* some thoughts on when rebuilds *are* appropriate, do a google search for "Richard Foote Rebuilding the Truth".

Hope that helps,


-----Original Message-----
From: [] On Behalf Of Martin Klier Sent: Tuesday, September 29, 2009 3:14 AM To:
Cc:; Subject: Antwort: Re: How to find out if my index is optimal or scattered

Dear list, Robert,

the OP question and the index rebuild topic touches a nerve, since I am asked often if we have to rebuild our indexes today, tomorrow, ever, and the "old farts" "proof" all the time, that it helps.

My concrete question out of my daily work, let's talk about 10gR2 and above.

We have lots of tables with ascending IDs as primary keys. We usually delete the oldest (lowest) IDs first, in order to keep the table size constant. Does the RDBMS keep the index clean enough, so that it's not necesary to rebuild it? I always claimed "yes". My justification: Deleting all low IDs (leaving back none beyond ID XY) mean, that the oldest, leftmost leaf nodes of the index become empty, deleted, and the corresponding entries in the branch block(s) vanish.

Am I right? Can someone backlight the mechanism a bit better than I did?

Thanks in advance and best regards

Mit freundlichem Gruß

Martin Klier
Senior Oracle Database Administrator

Klug GmbH integrierte Systeme
Lindenweg 13, D-92552 Teunz
Tel.:  +49 9671/9216-245
Fax.: +49 9671/9216-112

Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg

| Von: |
>--------------------------------------------------------------------------------------------------------------------------------------------------| |Robert Freeman <> | >--------------------------------------------------------------------------------------------------------------------------------------------------|
| An: |
>--------------------------------------------------------------------------------------------------------------------------------------------------| | | >--------------------------------------------------------------------------------------------------------------------------------------------------|
| Datum: |
>--------------------------------------------------------------------------------------------------------------------------------------------------| |15.09.2009 20:06 | >--------------------------------------------------------------------------------------------------------------------------------------------------|
| Betreff: |
>--------------------------------------------------------------------------------------------------------------------------------------------------| |Re: How to find out if my index is optimal or scattered | >--------------------------------------------------------------------------------------------------------------------------------------------------|
| Gesendet | | von: |
>--------------------------------------------------------------------------------------------------------------------------------------------------| | | >--------------------------------------------------------------------------------------------------------------------------------------------------| Hmmmm... I know we are all just itching to jump on someone should they mention index rebuilds... but, it sounds to me like the OP might well be talking about a case where there is a PK index with sparse deletions. If you look in the AskTom thread listed by Paul, this is actually a case where index rebuilds MIGHT (MIGHT) be worth considering. Of course, I'd be asking lots of questions and making sure that there are not some application related things we could be doing differently, etc...etc... It makes me wonder though, are we becomming like those in the past, when we discount every rebuild index question (and similiar questions) without a second thought because we have our standard answers. Are we starting to respond with a scoff and a get away from me kid your bothering me attitude? How can we always be so sure that our answers are the right ones, if we don't know and have not asked, all the questions? When things change (and things always change) if we ignore the questions we think we have the answers too how do we find out that we are actually wrong becuase of change? Just a few thoughts..... Robert G. Freeman Oracle ACE Author: Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON IT'S WAY SOON! OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex) Oracle Database 11g New Features (Oracle Press) Portable DBA: Oracle (Oracle Press) Oracle Database 10g New Features (Oracle Press) Oracle9i RMAN Backup and Recovery (Oracle Press) Oracle9i New Features (Oracle Press) Other various titles out of print now... Blog: The LDS Church is looking for DBA's. You do have to be a Church member in good standing. A lot of kind people write me, concerned I may be breaking the law by saying you have to be a Church member. It's legal I promise! :-) ----- Original Message ---- From: Niall Litchfield <> To:;; Sent: Tuesday, September 15, 2009 11:11:15 AM Subject: Re: How to find out if my index is optimal or scattered Hey paul I know where you are coming from, but I can't let the clustering factor comment stand without mentioning that it *doesn't* tell you about the efficient use of space in an index but does tell you how scattered your *table* data is. A tree dump will tell you for sure about the index and you can also review the number of indexed keys against leaf blocks and key size. To the OP you *probably* don't need to worry unduly. On 9/15/09, Paul Drake <> wrote: > Sundar, > > How many times have you rebuilt it? > > > C'mon. Everyone deserves a laugh now and then. > > The column [dba user all] _indexes.clustering_factor has some info for you. > Check that with blevel, num_rows, leaf_blocks ... > > or read this thread: > > > > or spend some time on Ricard Foote's site: > > > > Download Radiohead "In Rainbows" and read a few white papers. > > hth. > > Paul > > > On Tue, Sep 15, 2009 at 11:45 AM, sundar mahadevan > <> wrote: >> Hi All, >> I would like to know if there is a way to find out if my index is >> scattered. Lets say I have a table and the table's primary key(or the >> indexed key) is inserted/updated and deleted regularly. Do I find >> these details from user_indexes or are there any other tables that >> would give me the required information to find if the index is >> scattered? Any help is appreciated. Thanks in advance. >> -- >> >> >> >> > > > > -- > > > -- > > > > -- Sent from Google Mail for mobile | Niall Litchfield Oracle DBA -- -- -- --
Received on Tue Sep 29 2009 - 08:01:55 CDT

Original text of this message