Latest posts by Cyrille Modiano (see all)
- Renaming a RAC cluster - 27/09/2018
- Stop/Start all RAC databases at once - 26/09/2018
- RMAN Backup script - 08/11/2017
This script list the top 10 columns most used in equality predicates or equijoin predicates and which are not indexed.
If you want to be more precise in your analyze you can limit the list to table containing more than a defined number of rows by adding the dba_tables table and filter on the num_rows column.
Missing Index script
ACCEPT SCHEMA_NAME PROMPT 'Choose the schema to analyze:' select * from ( select 'the column ' || c.name || ' of the table ' || us.name || '.' || o.name || ' was used ' || u.equality_preds || ' times in an equality predicate and ' || u.equijoin_preds || ' times in an equijoin predicate and is not indexed' as colum_to_index from sys.col_usage$ u, sys.obj$ o, sys.col$ c, sys.user$ us where u.obj# = o.obj# and u.obj# = c.obj# and us.user# = o.owner# and u.intcol# = c.col# and us.name='&SCHEMA_NAME' and c.name not in (select column_name from dba_ind_columns where index_owner ='&SCHEMA_NAME') and (u.equality_preds > 100 OR u.equijoin_preds > 100) order by u.equality_preds+u.equijoin_preds desc) WHERE rownum <11;
3 thoughts on “Find missing index”
thank you very much for your share
FYI, I had to lose the VARCHAR2 on the first line, then all is good!
Thanks for the post.
Thanks for your feedback, the script has been corrected