RE: Antwort: Re: How to find out if my index is optimal or scattered
- From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
- To: "Martin.Klier@xxxxxxxxxx" <Martin.Klier@xxxxxxxxxx>, "robertgfreeman@xxxxxxxxx" <robertgfreeman@xxxxxxxxx>
- Date: Tue, 29 Sep 2009 09:01:55 -0400
Martin,
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,
-Mark
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Martin Klier
Sent: Tuesday, September 29, 2009 3:14 AM
To: robertgfreeman@xxxxxxxxx
Cc: Oracle-L@xxxxxxxxxxxxx; oracle-l-bounce@xxxxxxxxxxxxx
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
mailto: martin.klier@xxxxxxxxxx
www.klug-is.de
------------------------------------------------------------------------------
Geschäftsführer: Johann Klug, Roman Sorgenfrei
Sitz der Gesellschaft: Teunz, USt-ID-Nr. DE175481608,
HRB Nr. 2037, Amtsgericht Amberg
|------------>
| Von: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Robert Freeman <robertgfreeman@xxxxxxxxx>
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| An: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Oracle-L@xxxxxxxxxxxxx
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Datum: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|15.09.2009 20:06
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Betreff: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|Re: How to find out if my index is optimal or scattered
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|------------>
| Gesendet |
| von: |
|------------>
>--------------------------------------------------------------------------------------------------------------------------------------------------|
|oracle-l-bounce@xxxxxxxxxxxxx
|
>--------------------------------------------------------------------------------------------------------------------------------------------------|
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@xxxxxxxxx>
To: bdbafh@xxxxxxxxx; sundarmahadevan82@xxxxxxxxx; Oracle-L@xxxxxxxxxxxxx
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@xxxxxxxxx> 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@xxxxxxxxx> 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.
>> --
>> //www.freelists.org/webpage/oracle-l
>>
>>
>>
>
>
>
> --
> http://www.completestreets.org/faq.html
> http://safety.fhwa.dot.gov/ped_bike/docs/pamanual.pdf
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
--
Sent from Google Mail for mobile | mobile.google.com
Niall Litchfield
Oracle DBA
http://www.orawin.info
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts: