Oracle: find columns that may need an index
Use sys.col_usage$ to list columns often used in predicates that are not indexed.
Oracle records how often columns are used in predicates in sys.col_usage$. This query lists the columns most often used in equality or equijoin predicates that are not part of any index, which makes them candidates for a new index.
Query for one schema
It returns the top 10 columns of a schema used more than 100 times in an equality or equijoin predicate and absent from the indexes of that schema (&SCHEMA_NAME is a SQL*Plus substitution variable).
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;
Variant for all schemas
Remove the schema filters to analyse every schema:
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 c.name not in (select column_name from dba_ind_columns)
and (u.equality_preds > 100 OR u.equijoin_preds > 100)
order by u.equality_preds+u.equijoin_preds desc)
WHERE rownum <11;
Notes
- The check compares column names only, not tables: a column name indexed in another table is excluded too.
- The thresholds (100) and the number of rows (10) can be adjusted.