Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

how to find the worst cluf having indexes? on 10gR2

598210Apr 7 2008 — edited Apr 8 2008
If the clustering factor(dba_index.clustering_factor) value is close to number of rows of the related table(dba_table.num_rows) this shows bad clustering_factor(CLUF) where as if CLUF is close to the number of blocks of that related table than this shows good CLUF.

So how may I find the indexes having the worst CLUF valuse in my database. Looking for an SQL operator which produces this "close to" output;
create table tdba_indexes nologging parallel 4 pctused 99 pctfree 1 as 
SELECT t2.owner, t2.table_name, index_name, clustering_factor, t2.num_rows, t2.blocks, t2.sample_size
  FROM dba_indexes t1, dba_tables t2
 WHERE clustering_factor > 0
   AND t1.owner NOT IN ('SYS', 'SYSTEM')
   AND t2.owner NOT IN ('SYS', 'SYSTEM')
   and t1.owner = t2.owner 
   and t1.table_name = t2.table_name
   AND t2.blocks > 0
 ORDER BY clustering_factor DESC ;

create table tcluf_bad nologging parallel 4 pctused 99 pctfree 1 as  
SELECT *
  FROM (SELECT CASE
                  WHEN a_sql_operator_needed_here_for_close_to_determination THEN
                   'BAD'
                  ELSE
                   'NOT SO BAD'
               END AS "CLUF",
               abs(clustering_factor / num_rows) ratio1,
               abs(clustering_factor / blocks) ratio2,
               t.*
          FROM tdba_indexes t)
 WHERE cluf = 'BAD' ;
Thank you.
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on May 6 2008
Added on Apr 7 2008
6 comments
592 views