Antwort: Re: How to find out if my index is optimal or scattered
From: Martin Klier <Martin.Klier_at_klug-is.de>
Date: Tue, 29 Sep 2009 09:13:39 +0200
Message-ID: <OFBA580A22.8A30B3D3-ONC1257640.00263860-C1257640.0027B3C8_at_klug-is.de>
Dear list, Robert,
Date: Tue, 29 Sep 2009 09:13:39 +0200
Message-ID: <OFBA580A22.8A30B3D3-ONC1257640.00263860-C1257640.0027B3C8_at_klug-is.de>
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 mailto: martin.klier_at_klug-is.de www.klug-is.de ------------------------------------------------------------------------------ Geschäftsführer: Johann Klug, Roman Sorgenfrei Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608, HRB Nr. 2037, Amtsgericht AmbergReceived on Tue Sep 29 2009 - 02:13:39 CDT
|------------>
| Von: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |Robert Freeman <robertgfreeman_at_yahoo.com> | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| An: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |Oracle-L_at_freelists.org | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Datum: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |15.09.2009 20:06 | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Betreff: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |Re: How to find out if my index is optimal or scattered | >--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Gesendet | | von: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------| |oracle-l-bounce_at_freelists.org | >--------------------------------------------------------------------------------------------------------------------------------------------------| 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: http://robertgfreeman.blogspot.com 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! :-) http://pages.sssnet.com/messndal/church/parachurch.pdf ----- Original Message ---- From: Niall Litchfield <niall.litchfield_at_gmail.com> To: bdbafh_at_gmail.com; sundarmahadevan82_at_gmail.com; Oracle-L_at_freelists.org 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 <bdbafh_at_gmail.com> 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: > > http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2290062993260 > > or spend some time on Ricard Foote's site: > http://www.orafaq.com/aggregator/sources/83 > http://richardfoote.wordpress.com/category/index-rebuild/ > > Download Radiohead "In Rainbows" and read a few white papers. > > hth. > > Paul > > > On Tue, Sep 15, 2009 at 11:45 AM, sundar mahadevan > <sundarmahadevan82_at_gmail.com> 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. >> -- >> http://www.freelists.org/webpage/oracle-l >> >> >> > > > > -- > http://www.completestreets.org/faq.html > http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf > -- > http://www.freelists.org/webpage/oracle-l > > > -- Sent from Google Mail for mobile | mobile.google.com Niall Litchfield Oracle DBA http://www.orawin.info -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-l