Cet article illustre sur une base Oracle 11.2, avec une seule table, les différents algorithmes qu’Oracle peut utiliser avec le même index B*Tree, à savoir :
- Index Unique Scan
- Index Range Scan
- Index Skip Scan
- Index Fast Full Scan
- Index Full Scan
- Index Range Scan Descending
- Index Full Scan Descending
- Index Range Scan (Min/Max)
- Index Full Scan (Min/Max)
- Index Sample Fast Full Scan
Et un petit exemple (no comment) en bonus pour les plus courageux…
Table d’exemple
Pour commencer, on crée une table exemple avec un seul index :
connect demo/demo
drop table t purge;
create table t(col1 number not null,
col2 number not null,
text varchar2(1000));
insert into t
(select rownum,
mod(rownum,10000),
rpad('X',1000)
from dual
connect by level <=100000);
commit;
create unique index t_col12 on t(col1, col2);
exec dbms_stats.gather_table_stats(user, -
'T', -
method_opt =>'for all columns size 254');
Index Unique Scan
Le premier cas est le plus simple ; il met en oeuvre une égalité sur un index unique :
set lines 120
set pages 1000
set tab off
select count(text)
from t
where col1=1 and col2=1;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(text) from t where col1=1 and col2=1
Plan hash value: 3696465660
-------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 2 (0)|
| 3 | INDEX UNIQUE SCAN | T_COL12 | 1 (0)|
-------------------------------------------------------------
Index Range Scan
Pour ce 2nd cas, il s’agit de montrer qu’on peut rechercher une plage de valeur (Range), même avec une égalité :
select count(text)
from t
where col1=1;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(text) from t where col1=1
Plan hash value: 225312217
-------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 3 (0)|
| 3 | INDEX RANGE SCAN | T_COL12 | 2 (0)|
-------------------------------------------------------------
Index Skip Scan
Ce 3e cas, ne fonctionne pas aussi bien avec une 10g; le calcul du coût est très différent et il faut changer la distribution des données (où les statistiques) pour obtenir le même plan :
select count(text)
from t
where col2=1;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(text) from t where col2=1
Plan hash value: 3248117931
-------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 267 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 267 (0)|
| 3 | INDEX SKIP SCAN | T_COL12 | 265 (0)|
-------------------------------------------------------------
Index Fast Full Scan
Ce 4e cas s’appuie sur le fait que le segment d’index est beaucoup plus petit que la table ; peu importe qu’il soit trié :
select count(col2)
from t;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(col2) from t
Plan hash value: 1087210772
------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
------------------------------------------------------
| 0 | SELECT STATEMENT | | 74 (100)|
| 1 | SORT AGGREGATE | | |
------------------------------------------------------
Index Full Scan
Pour ce 5e cas, au contraire, Oracle s’appuie sur le fait que les données de l’index sont triées mais on ne fait pas de sélection :
select col1
from t
order by col1;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select col1 from t order by col1
Plan hash value: 4144353643
-------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------
| 0 | SELECT STATEMENT | | 266 (100)|
| 1 | INDEX FULL SCAN | T_COL12 | 266 (1)|
-------------------------------------------------
Index Range Scan Descending
ce cas comme les suivants, sont de petites variations des cas précédents :
select col1
from t
where col1 between 1 and 2
order by col1 desc;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
----------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select col1 from t where col1 between 1 and 2 order by col1 desc
Plan hash value: 3847511302
------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 (100)|
| 1 | INDEX RANGE SCAN DESCENDING| T_COL12 | 2 (0)|
------------------------------------------------------------
Index Full Scan Descending
select col1
from t
order by col1 desc;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select col1 from t order by col1 desc
Plan hash value: 1059343953
-----------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------- ---------------------------------------------
| 0 | SELECT STATEMENT | | 266 (100)|
| 1 | INDEX FULL SCAN DESCENDING| T_COL12 | 266 (1)|
-----------------------------------------------------------
Index Range Scan (Min/Max)
Pour ce 2nd cas, il s’agit de montrer qu’on peut rechercher une plage de valeur (Range), même avec une égalité :
select max(col1)
from t
where col1 between 1 and 2;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
--------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select max(col1) from t where col1 between 1 and 2
Plan hash value: 4202339534
-------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | FIRST ROW | | 2 (0)|
| 3 | INDEX RANGE SCAN (MIN/MAX)| T_COL12 | 2 (0)|
-------------------------------------------------------------
Index Full Scan (Min/Max)
select min(col1)
from t;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select min(col1) from t
Plan hash value: 428793588
-----------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-----------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | INDEX FULL SCAN (MIN/MAX)| T_COL12 | 2 (0)|
-----------------------------------------------------------
Index Sample Fast Full Scan
select count(col2)
from t sample(1);
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(col2) from t sample(1)
Plan hash value: 817362606
-------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 74 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | INDEX SAMPLE FAST FULL SCAN| T_COL12 | 74 (2)|
-------------------------------------------------------------
Le bonus…
Pour terminer, voici un cas pour lequel Oracle pourrait très bien utiliser un index avec une efficacité certes relative :
select /*+ index (t) */ count(text)
from t
where col2+1=1;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select /*+ index (t) */ count(text) from t where col2+1=1
Plan hash value: 409290077
-------------------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
-------------------------------------------------------------
| 0 | SELECT STATEMENT | | 409 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | TABLE ACCESS BY INDEX ROWID| T | 409 (1)|
| 3 | INDEX FULL SCAN | T_COL12 | 266 (1)|
-------------------------------------------------------------
et, pour lequel, il préfère, malgré un coût plus élevé utiliser un accès à la table
select count(text)
from t
where col2+1=1;
select * from table(
dbms_xplan.display_cursor(
null,null,format=>'basic +cost +note'));
PLAN_TABLE_OUTPUT
------------------------------------------------
EXPLAINED SQL STATEMENT:
------------------------
select count(text) from t where col2+1=1
Plan hash value: 2966233522
------------------------------------------------
| Id | Operation | Name | Cost (%CPU)|
------------------------------------------------
| 0 | SELECT STATEMENT | | 4124 (100)|
| 1 | SORT AGGREGATE | | |
| 2 | TABLE ACCESS FULL| T | 4124 (1)|
------------------------------------------------
Ce dernier exemple illustre plusieurs points qui vont de (1) il ne faut pas écrire du SQL n’importe comment à (2) c’est largement un abus de langage que de dire qu’Oracle prend le plan dont le coût est le moins élevé, en passant par (3) il est possible de filtrer les valeurs de la première colonne d’un index. Et hop :
drop table t purge;
1 réflexion sur “10 algorithmes qui utilisent le même index”
c’est une excellente idée d’article que d’illustrer simplement les différents types d’index scan.
Il me semble important de préciser que parmi tous ces algo, seul le Fast Full Scan ne parcours pas l’index selon sa structure. Comme pour un Full Table Scan, la lecture va se faire depuis le data file avec des accès multi-blocks (DB FILE SCATTERED READ) ce qui constitue son avantage premier car pour tous les autres accès indexés les blocks sont lus en mode single-block (DB FILE SEQUENTIAL READ).
Les commentaires sont fermés.