Oracle : trouver les colonnes candidates à un index
Utiliser sys.col_usage$ pour lister les colonnes fréquemment utilisées dans des prédicats et non indexées.
Oracle enregistre la fréquence d'utilisation des colonnes dans les prédicats dans sys.col_usage$. Cette requête liste les colonnes les plus utilisées dans des prédicats d'égalité ou d'équijointure qui ne font partie d'aucun index : ce sont des candidates à un nouvel index.
Requête pour un schéma
Elle renvoie les 10 principales colonnes d'un schéma utilisées plus de 100 fois dans un prédicat d'égalité ou d'équijointure et absentes des index de ce schéma (&SCHEMA_NAME est une variable de substitution SQL*Plus).
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;
Variante pour tous les schémas
Supprimer les filtres sur le schéma pour analyser l'ensemble des schémas :
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;
Remarques
- Le contrôle compare uniquement les noms de colonnes, pas les tables : un nom de colonne indexé dans une autre table est aussi exclu.
- Les seuils (100) et le nombre de lignes (10) sont ajustables.