Re: How to find out if my index is optimal or scattered
- From: Robert Freeman <robertgfreeman@xxxxxxxxx>
- To: Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 15 Sep 2009 11:02:24 -0700 (PDT)
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
Other related posts:
