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

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.
>> --
>> 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


Other related posts: