LLI Wiki — My Digital Knowledge Base

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

Mastodon LLIMastodon SocialFosstodon