Un index B*Tree étant organisé selon les valeurs des données, on peut imaginer que si un plan exécute une étape de type INDEX FULL SCAN sur un index, les données retournées par l’étape du plan en question sont triées. Si donc vous voulez ramener les données dans l’ordre des colonnes de l’index, une étape supplémentaire de tri est-elle bien utile ? Oui ? Non ? C’est la réponse à cette question haletante que vous découvrirez ci-dessous ! Plus sérieusement, ne vous êtes vous jamais demandé à quoi sert une étape particulière dans un plan ? Voici un exemple que vous avez déjà probablement rencontré 1000 fois; mais vous êtes vous posé la question ?
Les tests ci-dessous ont été réalisés sur une base 11.2.0.1 sur Linux 32 bits. Le comportenment peut varier sur d’autres versions
Pourquoi trier ce qui l’est déjà ?
Prenons un exemple simple d’une table avec 5 lignes et un index sur une colonne non nulle; pas de surprise, regardez le plan, si vous effectuer un ORDER BY dans le sens de l’index, les données étant déjà triées dans le plan, Oracle n’effectue le tri qu’une fois :
create table x (col1 number not null,
col2 varchar2(4000));
insert into x values (1, rpad('X', 4000, 'X'));
insert into x values (2, rpad('X', 4000, 'X'));
insert into x values (3, rpad('X', 4000, 'X'));
insert into x values (4, rpad('X', 4000, 'X'));
insert into x values (5, rpad('X', 4000, 'X'));
commit;
exec dbms_stats.gather_table_stats(user, 'X')
create index xidx on x(col1);
select col1 from x order by 1;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID c4zy2hvxux5v3, child number 0
-------------------------------------
select col1 from x order by 1
Plan hash value: 1230327558
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | INDEX FULL SCAN | XIDX | 10 | 30 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Si par contre, vous interrogez les données dans un ordre différent, surprise… Oracle ne fait toujours pas de tri après avoir fait un INDEX FULL SCAN mais change simplement son algorithme de parcours de l’index en INDEX FULL SCAN DESCENDING :
select col1 from x order by 1 desc;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------
SQL_ID bqwqbk6nta063, child number 0
-------------------------------------
select col1 from x order by 1 desc
Plan hash value: 91904716
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | INDEX FULL SCAN DESCENDING| XIDX | 10 | 30 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Il n’y a toujours pas de tri.
Alors pourquoi Oracle fait-il un tri des données caractères?
Si vous changez le type des données de votre colonne indexée, le comportement est peut-être très différent (?); Oracle effectue peut-être un tri après avoir parcouru l’index comme c’est le cas sur mon système :
create table y (col1 varchar2(1 char) not null,
col2 varchar2(4000));
insert into y values ('A', rpad('X', 4000, 'X'));
insert into y values ('B', rpad('X', 4000, 'X'));
insert into y values ('C', rpad('X', 4000, 'X'));
insert into y values ('D', rpad('X', 4000, 'X'));
insert into y values ('E', rpad('X', 4000, 'X'));
exec dbms_stats.gather_table_stats(user, 'Y')
create index yidx on y(col1);
select col1 from y order by 1;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6thd7cv3ha5jv, child number 1
-------------------------------------
select col1 from y order by 1
Plan hash value: 1664128530
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 2 (100)| |
| 1 | SORT ORDER BY | | 5 | 10 | 2 (50)| 00:00:01 |
| 2 | INDEX FULL SCAN| YIDX | 5 | 10 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Bon, l’explication est simple, et si vous obtenez un résultat différent, pas de panique, vous avez surement un client en anglais ou en américain. Ce plan est obtenu parce que mon client est en français et que NLS_SORT qui découle de la langue est également en français. Faite vos tris dans l’ordre binaire des caractères et vous verrez que l’étape de tri n’est plus exécutée :
alter session set nls_sort=binary;
select col1 from y order by 1;
select * from table(dbms_xplan.display_cursor);
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------
SQL_ID 6thd7cv3ha5jv, child number 2
-------------------------------------
select col1 from y order by 1
Plan hash value: 707317413
-------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | INDEX FULL SCAN | YIDX | 5 | 10 | 1 (0)| 00:00:01 |
-------------------------------------------------------------------------
Conclusion
Etre ordonné et parler français à un prix comme vous pouvez vous en rendre compte ci-dessus. Le résultat est que l’ordre des données ramenées dans une langue plutôt qu’une autre ou que dans l’ordre binaire est différent de l’ordre dans lequel les données sont indexées par Oracle dans un index classique :
truncate table y;
insert into y values ('e', rpad('X', 4000, 'X'));
insert into y values ('E', rpad('X', 4000, 'X'));
insert into y values ('é', rpad('X', 4000, 'X'));
insert into y values ('f', rpad('X', 4000, 'X'));
insert into y values ('F', rpad('X', 4000, 'X'));
commit;
exec dbms_stats.gather_table_stats(user, 'Y')
alter session set nls_sort=binary;
select col1 from y order by 1;
COL1
----
E
F
e
f
é
alter session set nls_sort=binary_ci;
select col1 from y order by 1;
COL1
----
e
E
F
f
é
alter session set nls_sort=french;
select col1 from y order by 1;
COL1
----
E
e
é
F
f
Bien sur, comme souvent, supprimez vous tables pour en terminer avec ce petit exemple:
drop table x purge;
drop table y purge;
