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

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 15 Sep 2009 18:11:15 +0100
Message-ID: <7765c8970909151011o3b95f3bbx49fa6fde4eebf836_at_mail.gmail.com>

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

Received on Tue Sep 15 2009 - 12:11:15 CDT