par Guillaume Lelarge.

Émuler des index partiels dans la base de données Oracle


La façon étrange avec laquelle la base de données Oracle gère le NULL dans les index peut être utilisée pour émuler les index partiels. Pour cela, nous devons juste utiliser NULL pour les lignes que nous ne devons pas indexer.

Pour le démontrer, nous émulons l'index partiel suivant :

CREATE INDEX messages_atraiter
          ON messages (destinataire)
       WHERE traite = 'N'

Tout d'abord, nous avons besoin d'une fonction qui renvoie la valeur seulement si colonne TRAITE vaut 'N'.

CREATE OR REPLACE
FUNCTION pi_traites(traite CHAR, destinataire NUMBER)
RETURN NUMBER
DETERMINISTIC
AS BEGIN
   IF traite IN ('N') THEN
      RETURN destinataire;
   ELSE
      RETURN NULL;
   END IF;
END;
/

La fonction doit être déterministe pour qu'elle soit utilisable dans une définition d'index.

Maintenant, nous pouvons traiter un index qui contient seulement des lignes ayant TRAITE='N'.

CREATE INDEX messages_atraiter
          ON messages (pi_traites(traite, destinataire));

Pour utiliser l'index, vous devez utiliser l'expression indexée dans la requête :

SELECT message
  FROM messages
 WHERE pi_traites(traite, destinataire) = ?
--------------------------------------------------------------
|Id | Operation                   | Name              | Cost |
--------------------------------------------------------------
| 0 | SELECT STATEMENT            |                   | 5330 |
| 1 |  TABLE ACCESS BY INDEX ROWID| MESSAGES          | 5330 |
|*2 |   INDEX RANGE SCAN          | MESSAGES_ATRAITER | 5303 |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("PI_TRAITES"("TRAITE","DESTINATAIRE")=:X)

Index partiels, partie II

À partir de la version 11g, il existe une seconde approche, toute aussi effrayante, pour émuler les index partiels dans la base de données Oracle, en utilisant une partition d'index cassé intentionnellement et le paramètre SKIP_UNUSABLE_INDEX.

À propos de l'auteur

Photo de Markus Winand

Markus Winand teaches efficient SQL—inhouse and online. He minimizes the development time using modern SQL and optimizes the runtime with smart indexing—for that he also published the book SQL Performance Explained.

“Use The Index, Luke!” by Markus Winand and translated by Guillaume Lelarge is licensed under a Creative Commons Attribution-Noncommercial-No Derivative Works 3.0 Unported License.
Mentions légales | Contact | NO WARRANTY | Marque déposée | Privacy | CC-BY-NC-ND 3.0 license