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.