Indexes

Fiche express
Type	Objet Oracle
Rôle	Index B-tree : création, maintenance, optimiseur
Voir aussi	Oracle · Alter · Essentiel oracle

Index B-tree (Balanced Tree) — structure d'index par défaut sous Oracle : création, dimensionnement, maintenance et lecture des statistiques.

Quand un index B-tree est-il utilisé ?

Les index ne sont pas utilisés avec ces formes de clause :

• IS NULL
• NOT IN
• <> 'chaine'
• LIKE '%TEL' (le début de la chaîne recherchée n'est pas connu)
• SUBSTR(nom,1,1)='1' (fonction appliquée à la colonne indexée — nécessiterait un index fonction-based)

Les index sont utilisés avec :

• nom > 'chaine'
• LIKE 'H%' (préfixe connu)

Stockage des index

CREATE INDEX schema_name$nom#prenom ON schema_name(nom,prenom)
TABLESPACE indx
PCTFREE 20
STORAGE (INITIAL 20M);

ALTER TABLE schema_name ADD CONSTRAINT schema_name$pk PRIMARY KEY (numero)
USING INDEX
TABLESPACE indx
PCTFREE 0
STORAGE (INITIAL 2M);

Rattacher une contrainte à un index existant :

ALTER TABLE schema_name ADD CONSTRAINT schema_name$uk01
UNIQUE (nom, prenom, telephone)
USING INDEX schema_name$ix01;

Créer la contrainte et son index en une seule commande :

ALTER TABLE schema_name ADD CONSTRAINT schema_name$uk01
UNIQUE (nom, prenom, telephone)
USING INDEX
(
 CREATE INDEX schema_name$ix01
  ON schema_name(nom, prenom, telephone)
 TABLESPACE indx
 PCTFREE 25
 STORAGE (INITIAL 10M)
);

DBA_INDEXES

Recommandations

• stocker les index dans un tablespace dédié (séparé des données) ;
• régler le PCTFREE avec soin selon la volatilité de la colonne indexée ;
• allouer un espace initial (INITIAL) adéquat pour limiter le nombre d'extensions ultérieures.

Estimer la taille d'un index

ANALYZE INDEX schema_name$ix01 VALIDATE STRUCTURE;

SELECT lf_blks+br_blks FROM index_stats WHERE name='schema_name$ix';

Formule d'estimation par extrapolation (exemple illustratif — remplacer les valeurs par celles mesurées) :

-- (nb_blocs_mesurés / nb_lignes_mesurées) * nb_lignes_estimé = estimation de blocs
SELECT (59/10000)*250000 AS estimation FROM DUAL;

Estimation du PCTFREE

Le PCTFREE ne se dimensionne que si la colonne indexée n'est pas figée.

• PCTFREE = 0 si la colonne n'est jamais modifiée, ou si les nouvelles valeurs n'entrent pas dans la plage actuelle des valeurs existantes.
• Sinon :

PCTFREE = 100 x (1 - Ni / Nf)
Ni = nombre de lignes initiales
Nf = nombre de lignes finales attendu

Supervision

DBA_SEGMENTS
DBA_EXTENTS
DBMS_SPACE
DBMS_STATS

ANALYZE INDEX nom_index VALIDATE STRUCTURE;

Lecture des statistiques :

SELECT height, lf_blks, br_blks, blocks, pct_used, lf_rows, del_lf_rows
FROM index_stats WHERE name='schema_name$ix01';

Espace alloué mais inutilisé :

SELECT lf_blks+br_blks "occupés", blocks "alloués"
FROM index_stats WHERE name='schema_name$ix01';

Signes d'un index à réorganiser : faible taux d'occupation des blocs et/ou profondeur importante :

• DEL_LF_ROWS/LF_ROWS > 10-20 %
• PCT_USED < 70 %
• HEIGHT > 5

SELECT height, pct_used, ROUND(del_lf_rows/lf_rows*100) pct_del
FROM index_stats WHERE name='schema_name$ix01';

Réorganiser un index

Objectifs possibles :

• libérer l'espace situé au-dessus de la HWM (High Water Mark) ;
• réparer une structure dégradée ;
• réorganiser le stockage (changement de tablespace, réduction du nombre d'extensions, changement de PCTFREE...).

Comparatif des méthodes :

	DEALLOCATE	COALESCE	SHRINK	REBUILD
Libère l'espace au-dessus de la HWM	X		X	X
Améliore le taux de remplissage des blocs		X	X	X
Réorganise plus globalement (tablespace, extents, PCTFREE)				X

• DEALLOCATE :

ALTER INDEX nom_index DEALLOCATE UNUSED [KEEP valeur[K|M]];

• COALESCE :

ALTER INDEX nom_index COALESCE;

SELECT height, lf_blks, br_blks, blocks, pct_used, lf_rows, del_lf_rows
FROM index_stats WHERE name='schema_name$ix01';

• SHRINK :

ALTER INDEX schema_name.schema_name$ix01 SHRINK SPACE [COMPACT];

SELECT height, lf_blks, br_blks, blocks, pct_used, lf_rows, del_lf_rows
FROM index_stats WHERE name='schema_name$ix01';

• REBUILD (reconstruit un index complet à côté de l'ancien : volumétrie x2 pendant l'opération, mais résultat optimal) :

ALTER INDEX schema_name$pk REBUILD PCTFREE 40 STORAGE (INITIAL 10M);
ALTER INDEX schema_name.schema_name$ix01 REBUILD;
ALTER INDEX schema_name.schema_name$ix01 REBUILD COMPRESS 2;

Monitoring d'utilisation

ALTER INDEX nom_index MONITORING USAGE | NOMONITORING USAGE;
V$OBJECT_USAGE

Informations

DBA_INDEXES
DBA_IND_COLUMNS
INDEX_STATS   -- résultat du dernier ANALYZE INDEX ... VALIDATE STRUCTURE
DBA_SEGMENTS
DBA_EXTENTS

Optimiseur de requêtes

Il est recommandé de laisser le plan d'exécution par défaut, c'est-à-dire le CBO (Cost Based Optimizer).

Le mode RBO (Rules Based Optimizer) n'est plus supporté depuis longtemps.

Le CBO se base sur les statistiques générées par le package DBMS_STATS :

• Oracle 9i : collecte des statistiques à la charge du DBA ;
• Oracle 10g : intégration automatique ;
• Oracle 11g : génération via une tâche planifiée automatique.

Procédures DBMS_STATS courantes :

• GATHER_TABLE_STATS(owname, tabname) : statistiques d'une table (colonnes et index de la table par défaut).
• GATHER_INDEX_STATS(owname, indname) : statistiques d'un index.
• GATHER_SCHEMA_STATS(owname) : statistiques de toutes les tables et index d'un schéma.

EXEC dbms_stats.gather_schema_stats('schema_name');

Voir aussi

• Oracle
• Alter
• Essentiel oracle

Notes

• Corrections apportées par rapport à la source : coquilles KAY→KEY, REBIULD→REBUILD, heuight→height, francisation DESALLOCATE→DEALLOCATE (mot-clé SQL réel), tableau comparatif reconstruit en wikitable natif (le modèle array_5col utilisé dans la source n'existe pas sur ce wiki).