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