Translate

Search This Blog

query to find cardinality of columns to consider for indexing

You ran sqal tuning advisory on sql tuning sets conatining vast queries and got advisory. Now you have to decide whether to create indexes based on sql tuning advisory or not. One autoamted way again is to run sql access advisor and decide. Alternate is decide after looking at cardinality of potentials columns for indexing.  Following query helps in later part.This query lists the size of table, total number of rows in table, number of distinct rows on column and total number of rows. Further it lists if the given column is foreign key  which is often considered as candidate for indexing ,potentially good for join query with parent table. This query also lists the columns names present in indexes along with their order in case of composite index. Further this list primary keys and unique keys also. Prerequisite of accuracy of this query is table should be analyzed.

SELECT  t2.table_name,  t3.last_analyzed,  t3.tsize "Table Size(MB)",  t2.column_name,  t2.column_position "Col position in Indx",  t2.index_name,  t2.isize "Index Size(MB)" ,  DECODE(t1.constraint_type,  'P',  'Pkey',  'U',  'U key',  'R',  'Fkey') TYPE ,  DECODE(t2.uniqueness,  'UNIQUE',  'Yes',  'No') "UNIQUE",  t2.index_type ,  t3.num_distinct,  t3.num_rows
 FROM
 ( SELECT uc.TABLE_NAME,  ucc.COLUMN_NAME,  ucc.POSITION,  uc.constraint_type FROM user_constraints uc ,  user_cons_columns ucc  WHERE  uc.TABLE_NAME=ucc.TABLE_NAME AND  uc.CONSTRAINT_NAME=ucc.CONSTRAINT_NAME AND  constraint_type IN ('P','U','R' ) )
t1 ,
 ( SELECT ui.table_name,ui.index_name,ROUND(ui.leaf_blocks*16384/1024/1024/1024,3) isize,  ui.uniqueness,uic.COLUMN_NAME,uic.column_position, ui.index_type  FROM user_indexes ui ,user_ind_columns uic
WHERE ui.INDEX_NAME=uic.INDEX_NAME ) t2,
( SELECT uc.last_analyzed,uc.table_name,ROUND(uc.blocks*16384/1024/1024/1024,3)   tsize,utc.column_name,uc.num_rows,utc.num_distinct  FROM user_tables uc, user_tab_columns utc WHERE uc.table_name=utc.table_name ) t3
WHERE t1.table_name(+)=t2.table_name
AND  t1.column_name(+)=t2.column_name
AND  t3.table_name(+)=t2.table_name
AND  t3.column_name(+)=t2.column_name
ORDER BY 1,6,5 desc

Follow by Email