L’indexation est un mécanisme d’optimisation extrêmement efficace et parfois négligé…ou méconnu, cet article devrait remettre les pendules a l’heure.
LE PRINCIPE  est de stocker dans une structure  annexe de la table, des entrées d’index (les clés) et les adresses des blocs de table correspondants.  Cette structure doit être organisée de manière efficace pour se  parcourir très  rapidement.
L’index étant maintenu à jour (et équilibré !) lors des mises à jour de la table.

rem : un index peut être ajoputé et supprimé à tout moment. Un index peut porter sur des colonnes multiples ( concatenated index)
Plus d’infos dans DBA_INDEXES et DBA_TAB_COLUMNS.

Oracle propose un certain nombre de type d’index différents adaptés aux différnetes qualités de données et de requêtes.

Index standard ou B*tree

Comme leur nom l’indique , organisés en B*tree ou arbres équilibrés (B alanced …et pas B inaire !)
Les entrées d’index sont ordonnées dans un arbre hiérarchique équilibré (toutes les feuilles sont a la même distance / profondeur de la racine de l’arbre.

Les adresses des lignes de la tables associées sont des ROWID (identifiant physique unique de chaque ligne).

Voici un exemple de clés numériques rangées dans un B*tree (122 est la racine, les + petits a gauche, les + grands à droite)

EFFICAce : pour stocker des clé primaires ou des clés très sélectives (très peu redondantes). Pour faire des tris (puisque les données … sont déjà triées ! )

Index bitmap

UN index bitmap est organisé comme un tableau a 2 dimensions :

• autant de lignes que de valeurs distinctes dans la colonne
• autant de colonnes que lignes dans la tables

Ce tableau peut être compressé si tant est que les lignes soient relativement ordonnées ou regroupées (clustering factor) ==> peu de place utilisée, décompression en RAM. Produit une liste de Rowids.

EFFICACE : pour les données très redondantes donc avec peu de valeurs distinctes :  nb valeurs < 1% nb lignes ou nb valeurs répétées > 100 ?
Pour les requêtes complexes avec des AND et des OR, qui utilisent PLUSIEURS index bitmap.

rem : il existe également des BITMAP JOIN INDEX qui stocke le ROWID des lignes correspondantes de la table jointe en plus  du ROWID de la table primaire…

Index organized tables ( oracle  IOT )

les  IOT sont des tables dont les données sont triées suivant la clef primaire. Toutes les colonnes d’une ligne de la table sont stockées dans la feuille de l’index de clef primaire, en plus de la PK elle même. Les acces à la table APRES l’index deviennent inutiles…

Syntaxe : CREATE TABLE <nom_table> (def_colonnes…,
CONSTRAINT <constraint_name>
PRIMARY KEY (<colonne pk>))
ORGANIZATION INDEX;

EFFICACE : pour gagner de la place et diviser les IO par 2 ! …mais sur des tables assez ’statiques’

Index partitionnés

Les index sont regroupés suivant certains critères dans différentes parttions. Les règles de partitionnement peuvent etre identiques à celles de l atable indexée ou spécifiques à l’index