Optimiser des requêtes SQL contenant des vues

Malgré l’amélioration de l’optimiseur au fur et à mesure des versions, on rencontre encore souvent des requêtes mal optimisées. C’est plus ou moins compliqué d’identifier le problème mais une fois qu’on le tient, une des techniques consiste à ajouter un conseil pour aider l’optimiseur. En général cela se passe bien sauf si votre requête fait appel à des vues.

Mais il existe des moyens pour s’en sortir.

Tout d’abord créons notre jeu de test :

-- les tables
create table o1 as select * from dba_objects ;
create table o2 as select * from dba_objects ;

-- les indexes
create index ix1 on o1(object_id) ;

-- vue simple
create view v1 as select * from o1 ;

-- vue complexe
create view v2 as select t1.* from o2 t1 join (select * from o1 t2 where object_id > 10000) t3 on t1.object_id=t3.object_id ;

• Cas simple

Commençons par le plus facile, l’utilisation d’un index dans v1. Avec une requete du type :

select * from v1 where object_id > 1;

vous allez immanquablement vous retrouver en full scan sur la table grâce/à cause de l’optimiseur.  Si pour une quelconque raison vous décidiez que ce n’est pas la bonne méthode, le conseil suivant serait parfaitement ignoré :

select /*+ INDEX(v1, ix1 ) */ from v1 where object_id > 1 ;
 --------------------------------------------------------------------------
 | Id | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 --------------------------------------------------------------------------
 | 0  | SELECT STATEMENT   |      | 86453 | 17M   | 204 (2)    | 00:00:02 |
 |* 1 |   TABLE ACCESS FULL| O1   | 86453 | 17M   | 204 (2)    | 00:00:02 |
 --------------------------------------------------------------------------

Pour que cet index soit utilisé il faut définir la table sous-jacente dans la vue :

select /*+ INDEX(v1 o1, ix1 ) */ from v1 where object_id > 1;
 ----------------------------------------------------------------------------------------
 | Id | Operation                       | Name | Rows | Bytes | Cost (%CPU)| Time     |
 ----------------------------------------------------------------------------------------
 | 0  | SELECT STATEMENT                |      | 86453 | 17M  | 1759 (1)   | 00:00:14 |
 | 1  |   TABLE ACCESS BY INDEX ROWID   | O1   | 86453 | 17M  | 1759 (1)   | 00:00:14 |
 |* 2 |     INDEX RANGE SCAN            | IX1  | 86453 |      | 189 (1)    | 00:00:02 |
 ----------------------------------------------------------------------------------------

• Cas complexe

On souhaite requêter la vue v2 mais le temps d’exécution n’est pas satisfaisant.

select * from v2 ;
 ------------------------------------------------------------------------------------------
 | Id | Operation                | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------------
 | 0  | SELECT STATEMENT         |      | 61313 | 12M  |       | 560 (2)    | 00:00:05 |
 |* 1 |   HASH JOIN              |      | 61313 | 12M  | 1840K | 560 (2)    | 00:00:05 |
 |* 2 |     INDEX FAST FULL SCAN | IX1  | 75272 | 955K |       | 35 (3)     | 00:00:01 |
 |* 3 |     TABLE ACCESS FULL    | O2   | 61314 | 12M  |       | 203 (2)    | 00:00:02 |
 ------------------------------------------------------------------------------------------

On peut voir que la jointure utilisée est la hash join. Si on estime qu’une nested loop serait plus appropriée, on aimerait bien modifier la requete comme suit. Toutefois, on peut voir que l’optimiseur ne prend pas en compte notre conseil :

select /*+ USE_NL(O1, O2) */ * from v2 ;
 ------------------------------------------------------------------------------------------
 | Id | Operation                | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------------
 | 0  | SELECT STATEMENT         |      | 61313 | 12M  |       | 560 (2)    | 00:00:05 |
 |* 1 |   HASH JOIN              |      | 61313 | 12M  | 1840K | 560 (2)    | 00:00:05 |
 |* 2 |     INDEX FAST FULL SCAN | IX1  | 75272 | 955K |       | 35 (3)     | 00:00:01 |
 |* 3 |     TABLE ACCESS FULL    | O2   | 61314 | 12M  |       | 203 (2)    | 00:00:02 |
 ------------------------------------------------------------------------------------------

Ou même en reprenant la syntaxe de l’index :

select /*+ USE_NL(v2 o1,  v2 o2) */ * from v2 ;
 ------------------------------------------------------------------------------------------
 | Id | Operation                | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time     |
 ------------------------------------------------------------------------------------------
 | 0  | SELECT STATEMENT         |      | 61313 | 12M  |       | 560 (2)    | 00:00:05 |
 |* 1 |   HASH JOIN              |      | 61313 | 12M  | 1840K | 560 (2)    | 00:00:05 |
 |* 2 |     INDEX FAST FULL SCAN | IX1  | 75272 | 955K |       | 35 (3)     | 00:00:01 |
 |* 3 |     TABLE ACCESS FULL    | O2   | 61314 | 12M  |       | 203 (2)    | 00:00:02 |
 ------------------------------------------------------------------------------------------

Pour s’en sortir il faut utiliser les blocks de requete. Il faut ainsi tout d’abord les identifier :

explain plan for select * from v2 ;
select * from table(dbms_xplan.display(null,null,'typical alias -rows -bytes -cost'));
 -----------------------------------------------------
 | Id | Operation               | Name | Time     |
 -----------------------------------------------------
 | 0  | SELECT STATEMENT        |      | 00:00:05 |
 |* 1 |   HASH JOIN             |      | 00:00:05 |
 |* 2 |     INDEX FAST FULL SCAN| IX1  | 00:00:01 |
 |* 3 |     TABLE ACCESS FULL   | O2   | 00:00:02 |
 -----------------------------------------------------

 Query Block Name / Object Alias (identified by operation id):
 -------------------------------------------------------------

 1 - SEL$5CB8D2B8
 2 - SEL$5CB8D2B8 / T2@SEL$3
 3 - SEL$5CB8D2B8 / T1@SEL$2

Il ne nous reste plus qu’à glisser les conseils avec les blocks et les alias :

select /*+ USE_NL(@SEL$5CB8D2B8 T1@SEL$2 T2@SEL$3) */ * from v2 ;
 -------------------------------------------------------------------------------
 | Id | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
 -------------------------------------------------------------------------------
 | 0  | SELECT STATEMENT     |      | 61313 | 12M   | 61554 (1)  | 00:07:36 |
 | 1  |   NESTED LOOPS       |      | 61313 | 12M   | 61554 (1)  | 00:07:36 |
 |* 2 |     TABLE ACCESS FULL| O2   | 61314 | 12M   | 203 (2)    | 00:00:02 |
 |* 3 |     INDEX RANGE SCAN | IX1  | 1     | 13    | 1 (0)      | 00:00:01 |
 -------------------------------------------------------------------------------

Références :

http://jonathanlewis.wordpress.com/2007/06/25/qb_name/