Jointure d'index : le INDEX FAST FULL SCAN n'est pas optimisé par Oracle - Index join: INDEX FAST FULL SCAN is not optimized
Introduction
Le but de cet article est de discuter du mode d'accès aux index appelé INDEX FAST FULL SCAN, et plus précisément de montrer qu'il n'est pas optimisé (oui, c'est prétentieux de ma part) lors d'une jointure d'index. Pour cela nous allons utiliser un hint et montrer que Oracle lit TOUS les blocs de l'index puis filtre les données au lieu de traiter N blocs d'un coup et de s'arrêter une fois tous les enregistrements trouvés, ce qui serait plus pertinent.
Bien sur, INDEX FAST FULL SCAN signifie "lecture rapide de TOUT l'index" mais, selon la sélectivité de la requête, on pourrait le faire de façon plus optimisée.
Points d'attention
NA.
Base de tests
N'importe quelle base Oracle.
Exemples
============================================================================================
Création de l'environnement de test
============================================================================================
On commence par créer une table de test.
SQL> CREATE TABLE clients(id NUMBER PRIMARY KEY, nom VARCHAR2(30 CHAR), prenom VARCHAR2(30 CHAR));
Table CLIENTS created.
Ensuite, ajout des contraintes NOT NULL pour que Oracle décide d'utiliser les index plutôt que la table. Sans ces contraintes, Oracle ne sait pas si dans les index se trouve l'intégralité des données des tables.
SQL> ALTER TABLE clients MODIFY (nom NOT NULL);
Table altered.
SQL> ALTER TABLE clients MODIFY (prenom NOT NULL);
Table altered.
Et maintenant la création du jeu de test. ATTENTION, on met le même nombre de caractères dans le champ NOM pour les deux jeux d'essai pour que le nombre de blocs à traiter soit le même. En outre les prénoms sont identiques, à une séquence prête. Je crée 10 000 enregistrements : 1% de nom MARTIN, 99% de nom PICHOT. Les 100 MARTIN sont dans les premiers blocs de l'index sur le nom puisque MARTIN < PICHOT et dans les premiers blocs de l'index sur le prénom car Marc < Pierre. Il n'y a donc pas de mélange dans les deux index entre les personnes des deux noms de famille : dans les deux index, les MARTIN et PICHOT sont bien séparés, que ce soit par le nom ou par le prénom.
On me dira que cela fausse les résultats observés plus tard, je rétorquerai que cela peut ressembler à un cas de la vraie vie mais sur des colonnes pas aussi porteuses de sens que Nom et Prénom. Et puis il faut que le test soit parlant donc on force le trait sur la valeur des données.
Création de 100 enregistrements de nom MARTIN.
SQL> begin
for i in 1..100 loop
INSERT INTO clients VALUES (i, 'MARTIN', 'Marc' || TO_CHAR(i));
end loop;
end;
/
PL/SQL procedure successfully completed.
Création de 9 900 enregistrements de nom PICHOT.
SQL> begin
for i in 101..10000 loop
INSERT INTO clients VALUES (i, 'PICHOT', 'Pierre' || TO_CHAR(i));
end loop;
end;
/
PL/SQL procedure successfully completed.
SQL> COMMIT;
Commit complete.
Extrait de la table : on a bien 1% de MARTIN, 99% de PICHOT.
SQL> select nom, count(*) from clients group by nom order by nom;
NOM COUNT(*)
------------------------------ ----------
MARTIN 100
PICHOT 9900
select * from clients where id < 11 order by id;
ID NOM PRENOM
---------- ------------------------
1 MARTIN Marc1
2 MARTIN Marc2
3 MARTIN Marc3
4 MARTIN Marc4
5 MARTIN Marc5
6 MARTIN Marc6
7 MARTIN Marc7
8 MARTIN Marc8
9 MARTIN Marc9
10 MARTIN Marc10
10 rows selected.
On crée maintenant les index sur le nom et un autre sur le prénom : deux index séparés, pas un index composé.
SQL> CREATE INDEX idx_clients_nom ON clients(nom);
Index IDX_CLIENTS_NOM created.
SQL> CREATE INDEX idx_clients_prenom ON clients(prenom);
Index IDX_CLIENTS_PRENOM created.
Bien penser à calculer les stats sur le schéma :-)
SQL> exec dbms_stats.gather_schema_stats('HR');
PL/SQL procedure successfully completed.
Maintenant regardons combien Oracle a alloué de blocs pour la table et les index.
La table représente 35 blocs de 8 Ko.
SQL> select blocks from dba_tables where table_name = 'CLIENTS' and OWNER = 'HR';
BLOCKS
----------
43
Pour les index, le calcul est plus difficile, on a dans DBA_INDEXES que la statistique LEAF_BLOCKS (nombre de feuilles) mais pas le nombre total de blocs de l'index (racine, branches et feuilles). Vu le nombre de données dans la table, on peut ajouter 1 à 3 blocs à chaque index : 1 bloc racine puisque on a plusieurs leaf blocks à gérer et 2 blocs de branche sur le même niveau max au cas où il y aurait entre la racine et les leaf blocks un niveau de branche, mais bon, on est au max à 34 blocs par index (31 +3 = 31).
SQL> select INDEX_NAME, LEAF_BLOCKS from dba_indexes where TABLE_NAME = 'CLIENTS' and OWNER = 'HR' AND INDEX_NAME LIKE 'IDX_CLIENTS%';
INDEX_NAME LEAF_BLOCKS
-------------------- ----------------
IDX_CLIENTS_NOM 26
IDX_CLIENTS_PRENOM 31
============================================================================================
INDEX FAST FULL SCAN sur un index
============================================================================================
Pour voir si le INDEX FAST FULL SCAN est optimisé, nous allons nous focaliser sur la statistique "Physical reads" dans l'autotrace car c'est elle qui donne le nb de blocs lus sur le disque dur.
Premier test : faire un INDEX FAST FULL SCAN sur un seul index via un hint dédié. Attention à bien vider les données en mémoire pour forcer la lecture sur disque dur et voir à chaque fois le nombre de blocs lus.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> set autotrace traceonly
Le SELECT sur MARTIN ne va récupérer que 1% des enregistrements mais Oracle va lire 44 blocs au lieu du strict minimum dans l'index (3 blocs je pense : la racine, une branche, un seul leaf bloc contenant les ROWID pour MARTIN) car il fait un INDEX FAST FULL SCAN. A noter que comme on ne sélectionne que le nom, Oracle n'accède pas à la table et donc la stats "Physical reads" permet bien de voir le nombre de blocs lus pour l'index sans être pollué avec des blocs de la table.
Ce nombre 44 se décompose a peu près en 30 blocs de l'index sur le nom plus les blocs du dictionnaire de données.
SQL> select /*+ index_ffs(clients,IDX_CLIENTS_NOM) */ nom from clients where nom = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2868924643
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 700 | 9 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_CLIENTS_NOM | 100 | 700 | 9 (0)| 00:00:01|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='MARTIN')
Statistics
----------------------------------------------------------
89 recursive calls
0 db block gets
232 consistent gets
44 physical reads
0 redo size
2235 bytes sent via SQL*Net to client
673 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
100 rows processed
Autre test, cette fois on récupère 99% des données de la même table : même nombre de physical reads! C'est la preuve que le INDEX FAST FULL SCAN lit TOUS les blocs, indépendemment de la sélectivité. Ensuite il effectue un filtre sur les données lues, comme le prouve la ligne "filter("NOM"='PICHOT')".
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+ index_ffs(clients,IDX_CLIENTS_NOM) */ nom from clients where nom = 'PICHOT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2868924643
----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 69300 | 9 (0)| 00:00:01 |
|* 1 | INDEX FAST FULL SCAN| IDX_CLIENTS_NOM | 9900 | 69300 | 9 (0)| 00:00:01|
----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='PICHOT')
Statistics
----------------------------------------------------------
85 recursive calls
0 db block gets
879 consistent gets
44 physical reads
0 redo size
175958 bytes sent via SQL*Net to client
7856 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
9900 rows processed
============================================================================================
Jointure d'index : INDEX RANGE SCAN et INDEX FAST FULL SCAN
============================================================================================
Utilisation du hint INDEX_JOIN
Nous améliorons maintenant notre test et passons de un à deux index : on choisit dans le SELECT les deux colonnes indexées, en forçant une jointure d'index. L'objectif est de lire les données uniquement via les index pour voir comment Oracle gère cela.
Notre premier SELECT n'est pas bon, il y a un accès à la table; Oracle lit 66 blocs sur le disque dur.
SQL> select nom, prenom from clients where nom = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4036073249
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5000 | 90000 | 13 (0)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| CLIENTS | 5000 | 90000 | 13 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='MARTIN')
Statistics
----------------------------------------------------------
87 recursive calls
0 db block gets
248 consistent gets
66 physical reads
0 redo size
2995 bytes sent via SQL*Net to client
673 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
10 sorts (memory)
0 sorts (disk)
100 rows processed
En utilisant le hint INDEX_JOIN, on a bien cette fois l'utilisation des deux index, sans accès à la table. Et, plus que tout, Oracle décide de faire un INDEX FAST FULL SCAN sur le deuxième index, exactement ce que je voulais.
SQL> select /*+INDEX_JOIN(clients IDX_CLIENTS_NOM IDX_CLIENTS_PRENOM)*/ nom, prenom from clients where nom = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3094148457
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1600 | 30 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 100 | 1600 | 30 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_CLIENTS_NOM | 100 | 1600 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_CLIENTS_PRENOM | 100 | 1600 | 36 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='MARTIN')
2 - access(ROWID=ROWID)
3 - access("NOM"='MARTIN')
Que voit-on dans le plan d'exécution?
- Id 3 exécuté en premier : RANGE SCAN sur l'index IDX_CLIENTS_NOM pour ramener les données Nom, ROWID des 100 personnes de nom MARTIN (access("NOM"='MARTIN'))
- Id 4 exécuté en second : INDEX FAST FULL SCAN sur l'index IDX_CLIENTS_PRENOM pour ramener TOUTES les données de cet index (pas de prédicat associéà cette opération)
- Id 2 exécuté en troisième : Oracle utilise les rowid pour joindre les deux index (access(ROWID=ROWID)) et ainsi associer les 100 MARTIN au dataset des 10 000 prénoms
- Id 1 exécuté en quatrième : Oracle filtre sur le nom MARTIN les 10 000 rows pour ne garder que les rows qui nous intéressent
Le prédicat d'accès est "ROWID=ROWID" lors de la comparaison entre les dataset des deux index; normal puisque c'est la seule donnée présente dans les deux index!
Colonnes indexées NOT NULL obligatoire
ATTENTION, avec ce hint, il faut que les colonnes ayant les index soient NOT NULL sinon Oracle change son plan d'exécution. En effet, si on ne spécifie pas NOT NULL alors Oracle ne sait pas si dans l'index il y a tous les enregistrements de la table puisqu'il n'indexe pas les valeurs NULL.
SQL> ALTER TABLE clients MODIFY (nom NULL);
Table altered.
SQL> ALTER TABLE clients MODIFY (prenom NULL);
Table altered.
SQL> select /*+INDEX_JOIN(clients IDX_CLIENTS_NOM IDX_CLIENTS_PRENOM)*/ nom, prenom from clients where nom = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1358180766
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1600 | 2 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID BATCHED| CLIENTS | 100 | 1600 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_CLIENTS_NOM | 100 | | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("NOM"='MARTIN')
Si on remet les colonnes en NOT NULL : tout est OK.
SQL> ALTER TABLE clients MODIFY (nom NOT NULL);
Table altered.
SQL> ALTER TABLE clients MODIFY (prenom NOT NULL);
Table altered.
SQL> select /*+INDEX_JOIN(clients IDX_CLIENTS_NOM IDX_CLIENTS_PRENOM)*/ nom, prenom from clients where nom = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3094148457
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1600 | 30 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 100 | 1600 | 30 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_CLIENTS_NOM | 100 | 1600 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_CLIENTS_PRENOM | 100 | 1600 | 36 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='MARTIN')
2 - access(ROWID=ROWID)
3 - access("NOM"='MARTIN')
"Physical Read" avec SELECT de sélectivité 1%
Maintenant nous exécutons N fois le SELECT pour valider la valeur de la stat "Physical Read". Attention à bien vider la mémoire à chaque fois sinon cette stat vaudra 0 après la première exécution étant donné que les blocs seront déjà en mémoire.
Première exécution : 57 blocs lus sur disque dur. C'est moins que le premier plan d'exécution choisit par Oracle où il faisait un FTS sur la table Clients. On le décompose de la façon suivante : autour de 30 blocs pour l'index sur le prénom, près de 3 pour l'index sur le nom (1 bloc racine, 1 bloc branche, 1 bloc de données : 1% de données à lire en Range Scan alors que les 100% sont sur 26 blocs, les données de MARTIN sont dans un seul bloc). On arrive à 33, 35 blocs. A quoi correspondent les 22 restant? Les blocs du dictionnaire de données pour la plupart.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+INDEX_JOIN(clients IDX_CLIENTS_NOM IDX_CLIENTS_PRENOM)*/ nom, prenom from clients where nom = 'MARTIN';
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3094148457
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 100 | 1600 | 30 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 100 | 1600 | 30 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_CLIENTS_NOM | 100 | 1600 | 1 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_CLIENTS_PRENOM | 100 | 1600 | 36 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='MARTIN')
2 - access(ROWID=ROWID)
3 - access("NOM"='MARTIN')
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
241 consistent gets
57 physical reads
0 redo size
3195 bytes sent via SQL*Net to client
673 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
100 rows processed
Si je réexecute la requête sans vider le database buffer cache, physical reads tombe à 0 comme attendu puisque les blocs sont déjà en mémoire. En revanche Consistent Gets ne change plus.
SQL> /
100 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3094148457
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
43 consistent gets
0 physical reads
0 redo size
3195 bytes sent via SQL*Net to client
673 bytes received via SQL*Net from client
8 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
100 rows processed
Je ne copie pas ici tous mes tests mais j'ai flushé N fois la mémoire et on obtient toujours 57 Physicals Reads.
"Physical Read" avec SELECT de sélectivité 99%
Passons maintenant aux tests sur le nom le plus présent, PICHOT soit 9 900 rows sur 10 000 donc 99%.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
Le Plan hash value est le même que pour la recherche sur les MARTIN. En revanche Oracle lit 82 blocs sur le disque dur au lieu de 57 précédemment. Bizarre? Non, puisque le RANGE SCAN sur l'index du NOM lit cet index en totalité, soit 26 feuilles au lieu de 1, donc on a bien une différence de 25.
Précédemment on avait lu 57 blocs, ici on en lit 25 de plus donc 57 + 25 = 82!!!!! BINGO, on retombe sur nos pieds!
SQL> select /*+INDEX_JOIN(clients IDX_CLIENTS_NOM IDX_CLIENTS_PRENOM)*/ nom, prenom from clients where nom = 'PICHOT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3094148457
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 154K| 55 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 9900 | 154K| 55 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_CLIENTS_NOM | 9900 | 154K| 26 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_CLIENTS_PRENOM | 9900 | 154K| 36 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='PICHOT')
2 - access(ROWID=ROWID)
3 - access("NOM"='PICHOT')
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
919 consistent gets
82 physical reads
0 redo size
264228 bytes sent via SQL*Net to client
7856 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
9900 rows processed
On continue pour voir si le nb de physicals reads va changer, en vidant la mémoire : non, il ne change pas.
SQL> alter system flush buffer_cache;
System altered.
SQL> alter system flush shared_pool;
System altered.
SQL> select /*+INDEX_JOIN(clients IDX_CLIENTS_NOM IDX_CLIENTS_PRENOM)*/ nom, prenom from clients where nom = 'PICHOT';
9900 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3094148457
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 9900 | 154K| 55 (0)| 00:00:01 |
|* 1 | VIEW | index$_join$_001 | 9900 | 154K| 55 (0)| 00:00:01 |
|* 2 | HASH JOIN | | | | | |
|* 3 | INDEX RANGE SCAN | IDX_CLIENTS_NOM | 9900 | 154K| 26 (0)| 00:00:01 |
| 4 | INDEX FAST FULL SCAN| IDX_CLIENTS_PRENOM | 9900 | 154K| 36 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("NOM"='PICHOT')
2 - access(ROWID=ROWID)
3 - access("NOM"='PICHOT')
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
919 consistent gets
82 physical reads
0 redo size
264228 bytes sent via SQL*Net to client
7856 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
9900 rows processed
Encore une fois.
SQL> /
9900 rows selected.
Statistics
----------------------------------------------------------
88 recursive calls
0 db block gets
919 consistent gets
82 physical reads
0 redo size
264228 bytes sent via SQL*Net to client
7856 bytes received via SQL*Net from client
661 SQL*Net roundtrips to/from client
11 sorts (memory)
0 sorts (disk)
9900 rows processed
============================================================================================
Conclusion
============================================================================================
Qu'est ce que tout cela veut dire? Que même si dans un RANGE SCAN on récupère 1% des ROWID ou presque 100%, Oracle va, en faisant un INDEX FAST FULL SCAN pour fusionner deux index, lire quand même 100% des blocs du deuxième index, même si les 1% se trouvent dans les premiers blocs.
Ma conclusion est que le INDEX FAST FULL SCAN n'est pas optimisé! OK, c'est prétentieux de ma part de critiquer Oracle mais les faits sont là, ou bien je les interprète mal, ce qui est fort possible : règle d'or en informatique, être HUMBLE et accepter qu'on peut se tromper!
Idéalement j'aurai vu Oracle lire les blocs du deuxième index un par un ou paquet de blocs par paquet de blocs (grâce au paramètre Multi Blocks Read Count) mais tester à chaque fois si les ROWID du premier index sont présents dans ces blocs. Une fois tous les ROWID trouvés, Oracle arrête de lire les blocs du deuxième index. N'oubliez pas que les ROWID sont des identifiants! Si on en a retenu 100 dans l'index 1, on sait qu'il faut juste retrouver les mêmes 100 ROWID dans l'index 2. A quoi ça sert de lire 10 000 enregistrements si ceux cherchés sont dans les 1 000 premiers?
On me rétorquera qu'en faisant une jointure d'index, via un hint ou non, Oracle déclenche un INDEX FAST FULL SCAN sur un des deux index et qu'on a pas la main dessus. Bon, bien sur, en pinaillant, on pourrait proposer à Oracle de développer la possibilité de créer un index sur index en utilisant le ROWID pour que le parcours du deuxième index se fasse via le ROWID et pas en INDEX FAST FULL SCAN. De la sorte quand on utilise le hint INDEX_JOIN ou que Oracle décide de lui même de faire une jointure d'index, il pourrait se baser sur deux techniques selon la sélectivité de la requête : soit faire un INDEX FAST FULL SCAN soit utiliser un index secondaire, exactement comme ce qui existe pour les tables. Pour celles-ci, Oracle a le choix entre faire un FULL TABLE SCAN ou utiliser un index; ici on n'a pas le choix, Oracle lit tout systématiquement, peu importe la sélectivité de la requête.
Les index secondaires sur table IOT
Vous trouvez ma proposition stupide? Et les index secondaires ou index sur index des tables IOT, ça vous parle?
Voici un extrait de la doc Oracle https://docs.oracle.com/database/121/CNCPT/indexiot.htm#CNCPT721 : "Secondary Indexes on Index-Organized Tables
A secondary index is an index on an index-organized table. In a sense, it is an index on an index. The secondary index is an independent schema object and is stored separately from the index-organized table."
Quand on crée une table IOT, Oracle crée celle-ci sous forme d'index en se basant sur la PK. Si ensuite on a besoin de faire des recherches sur une colonne non PK, on peut créer un nouvel index sur cette table. Comme cette table est en réalité un index (object_type = TABLE dans DBA_OBJECTS mais le segment est de type INDEX dans DBA_SEGMENTS), ce nouvel index est ce qu'on appelle un index secondaire, soit un index sur index.
SQL> CREATE TABLE zztest(id NUMBER CONSTRAINT PK_ZZTEST PRIMARY KEY) ORGANIZATION INDEX;
Table created.
SQL> insert into zztest values (1);
1 row created.
SQL> commit;
Commit complete.
SQL> select object_type from dba_objects where object_name = 'ZZTEST';
OBJECT_TYPE
-----------------------
TABLE
Attention, le nom du segment associé à cette table est celui de l'index PK, pas celui de la table. On voit bien que pour Oracle la table ZZTEST est en réalité un index.
SQL> select SEGMENT_TYPE from dba_segments where SEGMENT_NAME = 'ZZTEST';
no rows selected
SQL> select SEGMENT_TYPE from dba_segments where SEGMENT_NAME = 'PK_ZZTEST';
SEGMENT_TYPE
------------------
INDEX
Création d'un index sur index; pour cela il faut au minimum une autre colonne dans la table.
SQL> ALTER TABLE zztest ADD NOM VARCHAR2(30);
Table altered.
SQL> CREATE UNIQUE INDEX IDX_ZZTEST_NOM ON zztest(NOM);
Index created.
A noter que les ROWID de cette table sont des logicals ROWID, pas des physical ROWID : leur tête n'a rien à voir avec les ROWID classiques.
SQL> select rowid from zztest;
ROWID
-----------------------------------------
*BAMAJPsCwQL+
Exemple de physical ROWID.
SQL> select rowid from emp where rownum = 1;
ROWID
------------------
AAATTBAAMAAACD9ABD
Aïe, impossible de créer un index sur la pseudo colonne ROWID.
SQL> CREATE UNIQUE INDEX IDX_ZZTEST_rowid ON zztest(rowid);
CREATE UNIQUE INDEX IDX_ZZTEST_rowid ON zztest(rowid)
*
ERROR at line 1:
ORA-00904: : invalid identifier
IL est néanmoins possible avec Oracle de déclarer une colonne de type ROWID et de créer un index dessus... on se rapproche de ma solution :-)
SQL> ALTER TABLE zztest ADD ROWID_TEST ROWID;
Table altered.
Ah non, échec... est-ce parce qu'on est sur une colonne avec un logical rowid et pas un physical rowid? D'après Internet c'est ça le problème, on ne peut écrire dans une colone de type ROWID que des physical ROWID.
SQL> update zztest set rowid_test = rowid;
update zztest set rowid_test = rowid
*
ERROR at line 1:
ORA-01410: invalid ROWID
Bon, cela nous aura entraîné loin, j'espère ne pas vous avoir fait perdre votre temps mais en ce qui me concerne j'ai trouvé ce voyage très intéressant :-)
Et puis, comme certains voyages, c'est le voyage en lui même qui est plus excitant et enrichissant que la destination d'arrivée.
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)