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
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
Posts
