Opération INDEX JOIN (jointure entre indexes), par Franck Pachot

Publié le par

En général lorsqu’il y a plusieurs indexes qui peuvent répondre aux prédicats d’une requête, Oracle va choisir l’index le plus selectif, et les autres conditions seront vérifiées au moment d’aller lire l’enregistrement complet dans la table.
Ceci peut nous amener à créer un index concaténé, en combinant toutes les colonnes sur lesquelles il y a des prédicats, afin que l’index soit plus sélectif.

Mais ajouter un index a un coût et va pénaliser les insert/deletes ainsi que les updates qui touchent aux colonnes indexées.

Il y a cependant des cas où Oracle peut combiner deux indexes.
C’est le cas par exemple des indexes bitmap. On peut avoir un index bitmap sur chaque colonne, et Oracle va combiner les bitmaps avant d’aller voir la table (en utilisant des opérateurs binaire AND et OR sur les bitmaps). Mais ce n’est pas le sujet de cet article.

Il y a aussi un cas particulier avec les indexes ‘normaux': c’est le chemin d’accès ‘INDEX JOIN‘: les 2 indexes sont lus, et sont réunis, comme si l’on faisait une jointure sur le rowid, et la sortie de cette opération est équivalente à un index qui contiendrait toutes les colonnes.

Voici un exemple de cette opération ‘index join’, le but de cet exemple était de répondre à la question suivante, sur le forum dba-village: ‘Que signifient les indexes index$_join$_9 et index$_join$_8′ dans un plan d’exécution, et ‘comment les remplacer par mes propres indexes ?‘

Je commence par créer une table avec 2 colonnes (n1 et n2):

SQL> create table t as  
 select mod(rownum,10) n1 ,mod(rownum,8) n2 , lpad('x',1000,'x') x from dual connect by level < 1000;
Table created.

Et je crée un index sur chaque colonne:

SQL> create index ti1 on t(n1);
Index created.
SQL> create index ti2 on t(n2);
Index created.

Je met autotrace à ‘trace explain’ pour afficher le plan d’exécution, mais pas le résultat:

SQL> set autotrace trace explain

et je lance un select avec des prédicats sur n2 et n2:

SQL> select n1,n2 from t where n2 is not null and n1=3;



------------------------------------------------------------------------------------------- 

| Id  | Operation              | Name             | Rows  | Bytes | Cost (%CPU)| Time     | 

------------------------------------------------------------------------------------------- 

|   0 | SELECT STATEMENT       |                  |    97 |  2522 |     3  (34)| 00:00:01 | 

|*  1 |  VIEW                  | index$_join$_001 |    97 |  2522 |     3  (34)| 00:00:01 | 

|*  2 |   HASH JOIN            |                  |       |       |            |          | 

|*  3 |    INDEX RANGE SCAN    | TI1              |    97 |  2522 |     1   (0)| 00:00:01 | 

|*  4 |    INDEX FAST FULL SCAN| TI2              |    97 |  2522 |     1   (0)| 00:00:01 | 

-------------------------------------------------------------------------------------------> 

Predicate Information (identified by operation id): 

--------------------------------------------------- 

   1 - filter("N2" IS NOT NULL AND "N1"=3) 

   2 - access(ROWID=ROWID) 

   3 - access("N1"=3) 

   4 - filter("N2" IS NOT NULL)

On peut voir la jointure sur les indexes:

  • (3) un RANGE SCAN de l’index TI1 pour avoir les ROWID qui vérifient le prédicat n1=3.
    Le résultat est mis en table de hachage pour le HASH JOIN
  • (4) un FULL SCAN de l’index TI2 pour avoir les ROWID qui vérifient le prédicat n2 is not null
  • (2) un HASH join de ce résulat avec le premier. On voit la condition de jointure ROWID=ROWID
  • (1) Le résultat de cette opération, appelé index$_join$_001, a les colonnes ROWID, N1 et N2, exactement comme si on avait un index concaténé sur (N1,N2)

Mainenant, je crée un index similaire:

SQL> create index ti12 on t(n1,n2);
Index created.

Et j’exécute la même requête:

SQL> select n1,n2 from t where n2 is not null and n1=3;



------------------------------------------------------------------------- 

| Id  | Operation        | Name | Rows  | Bytes | Cost (%CPU)| Time     | 

------------------------------------------------------------------------- 

|   0 | SELECT STATEMENT |      |    97 |  2522 |     2   (0)| 00:00:01 | 

|*  1 |  INDEX RANGE SCAN| TI12 |    97 |  2522 |     2   (0)| 00:00:01 | 

------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 

--------------------------------------------------- 

   1 - access("N1"=3) 

       filter("N2" IS NOT NULL)

On voit alors que l’index a été utilisé, et que le coût est un peu meilleur.
Reste à voir si ce gain est justifié par rapport au coût d’avoir une index supplémentaire à maintenir lors des inser/delete/update.

Laisser un commentaire