Blog

le 17/09/2011
Thomas Bordet
Archives blog-arkzoyd @fr

Référencer les tables d'une requêtes dans vos hints

Lorsqu’une requête inclut des sous-requêtes, il n’est pas toujours simple de référencer les tables associées dans des hints ; cet article illustre, grâce à quelques exemples simples, comment faciliter ces références :

grâce à des hints au niveau de chaque bloc
à l’aide du hint QB_NAME
en s’appuyant sur le nommage automatique des blocs d’une requête

Evidemment chaque méthode à ses avantages et ses limites…

Remarque :
Cet article n’est en aucun cas un encouragement à mettre des hints dans vos requêtes…

Schéma exemple

Pour commencer, créez une table et un index, ici la clé primaire ; collectez les statistiques :

drop table T1 purge;

create table T1 (id number,
                 lib varchar2(100),
                 constraint T1_PK primary key (id));

insert into T1
  (select rownum, rpad('X',100,'X')
    from dual connect by level <=1000);

commit;

exec dbms_stats.gather_table_stats(user, 'T1');

La requête que nous allons utiliser est celle-ci :

explain plan for select count(lib) 
  from T1
 where id in (select max(id) 
                from T1
               where id in (1,2,3));

set tab off
set lines 120
set pages 1000
select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
--------------------------------------------------
Plan hash value: 3471599218

--------------------------------------------------
| Id  | Operation                        | Name  |
--------------------------------------------------
|   0 | SELECT STATEMENT                 |       |
|   1 |  SORT AGGREGATE                  |       |
|   2 |   TABLE ACCESS BY INDEX ROWID    | T1    |
|   3 |    INDEX UNIQUE SCAN             | T1_PK |
|   4 |     SORT AGGREGATE               |       |
|   5 |      INLIST ITERATOR             |       |
|   6 |       FIRST ROW                  |       |
|   7 |        INDEX RANGE SCAN (MIN/MAX)| T1_PK |
--------------------------------------------------

Imaginons que pour une raison X ou Y, vous vouliez que le plan utilise un accès « FULL » à la table T1 dans la sous-requête

Hint dans la sous-requête

La première solution et la plus simple sans doute consiste à mettre le hint dans la sous requête comme ci-dessous :

explain plan for select count(lib) 
  from T1
 where id in (select /*+ full(T1) */ max(id) 
                from T1
               where id in (1,2,3));

set tab off
set lines 120
set pages 1000
select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 1185119255

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  SORT AGGREGATE              |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |
|   3 |    INDEX UNIQUE SCAN         | T1_PK |
|   4 |     SORT AGGREGATE           |       |
|   5 |      TABLE ACCESS FULL       | T1    |
----------------------------------------------

Un hint placé au niveau de la requête englobante n’agit que sur la requête englobante :

explain plan for select /*+ full(T1) */ count(lib) 
  from T1
 where id in (select max(id) 
                from T1
               where id in (1,2,3));

set tab off
set lines 120
set pages 1000
select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
-------------------------------------------------
Plan hash value: 249990877

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  SORT AGGREGATE                 |       |
|   2 |   TABLE ACCESS FULL             | T1    |
|   3 |    SORT AGGREGATE               |       |
|   4 |     INLIST ITERATOR             |       |
|   5 |      FIRST ROW                  |       |
|   6 |       INDEX RANGE SCAN (MIN/MAX)| T1_PK |
-------------------------------------------------

A l’aide de `QB_NAME`

Le hint QB_NAME (Query Block NAME) permet de nommer les blocs de votre requête. Vous pourrez alors mettre tous vos hints au niveau le plus élevé comme dans l’exemple ci-dessous :

explain plan for select /*+ full(@NSQL T1) */ count(lib) 
  from T1
 where id in (select /*+ QB_NAME(NSQL) */ max(id) 
                from T1
               where id in (1,2,3));

set tab off
set lines 120
set pages 1000
select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 1185119255

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  SORT AGGREGATE              |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |
|   3 |    INDEX UNIQUE SCAN         | T1_PK |
|   4 |     SORT AGGREGATE           |       |
|   5 |      TABLE ACCESS FULL       | T1    |
----------------------------------------------

Sans `QB_NAME`

Il n’est pas toujours possible de nommer les blocs de vos requêtes. Ainsi si, par exemple, la requête imbriqué s’appuie sur une vue. Vous choisirez alors d’utiliser le nom canonique du bloc généré par l’optimiseur.

Pour connaitre nom, utilisez la directive +alias du paramètre format de dbms_xplan.displayXXX comme ci-dessous :

explain plan for select /*+ no_query_transformation */ count(lib) 
  from T1
 where id in (select max(id) 
                from T1
               where id in (1,2,3));

set tab off
set lines 120
set pages 1000
select * from table(dbms_xplan.display(format=>'basic +alias'));

PLAN_TABLE_OUTPUT
-------------------------------------------------------------
Plan hash value: 1030027961

-------------------------------------------------
| Id  | Operation                       | Name  |
-------------------------------------------------
|   0 | SELECT STATEMENT                |       |
|   1 |  SORT AGGREGATE                 |       |
|   2 |   FILTER                        |       |
|   3 |    TABLE ACCESS FULL            | T1    |
|   4 |    SORT AGGREGATE               |       |
|   5 |     INLIST ITERATOR             |       |
|   6 |      FIRST ROW                  |       |
|   7 |       INDEX RANGE SCAN (MIN/MAX)| T1_PK |
-------------------------------------------------

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

   1 - SEL$1
   3 - SEL$1 / T1@SEL$1
   4 - SEL$2
   7 - SEL$2 / T1@SEL$2

Le nom du bloc SQL imbriqué est donc SEL$2 dans ce cas.

Note:
Le hint no_query_transformation évite que des blocs SQL disparaissent dans l’explain plan du fait d’optimisation du SQL. Utiliser ce hint dans l’explain plan permet donc de retrouver votre bloc avec certitude. Dans ce cas particulier, le hint est inutile…

Vous pouvez alors influer sur le plan sans nommer vous-même la requête imbriquée :

explain plan fo
r select /*+ full(@SEL$2 T1) */ count(lib) 
  from T1
 where id in (select max(id) 
                from T1
               where id in (1,2,3));

set tab off
set lines 120
set pages 1000
select * from table(dbms_xplan.display(format=>'basic'));

PLAN_TABLE_OUTPUT
----------------------------------------------
Plan hash value: 1185119255

----------------------------------------------
| Id  | Operation                    | Name  |
----------------------------------------------
|   0 | SELECT STATEMENT             |       |
|   1 |  SORT AGGREGATE              |       |
|   2 |   TABLE ACCESS BY INDEX ROWID| T1    |
|   3 |    INDEX UNIQUE SCAN         | T1_PK |
|   4 |     SORT AGGREGATE           |       |
|   5 |      TABLE ACCESS FULL       | T1    |
----------------------------------------------

Conclusion

Pour en savoir plus sur la manière de référencer les blocs de vos requêtes SQL, utilisez cette section de la documentation comme point d’entrée.

Et si vous pensez tous savoir d’Oracle, listez tous les hints disponibles par version à l’aide de la requête ci-dessous :

col version format a9
select version, name
  from v$sql_hint
 order by 
       to_number(regexp_substr(version,'[0-9]*',1,1)),
       to_number(regexp_substr(version,'[0-9]*',regexp_instr(version,'(.)')+1)),
       to_number(regexp_substr(version,'[0-9]*',regexp_instr(version,'(.)',1,2)+1)),
       to_number(regexp_substr(version,'[0-9]*',regexp_instr(version,'(.)',1,3)+1)),
       name;

Thomas Bordet

Voir tous ses articles

1 réflexion sur “Référencer les tables d'une requêtes dans vos hints”

Ahmed AANGOUR
22/09/2011 à 09:47

J’avais en tête d’écrire un article sur ce sujet mais tu m’as devancé. Je n’aurai plus qu’à créer un post avec un lien sur le tien puisqu’il est comme d’habitude excellent.
Je rajouterai juste comme info que les QB_NAME ne sont disponibles qu’à partir de la 10g. Je dis ça pour ceux qui tenteraient de reproduire ton test case sur une base 9i.
A+

Les commentaires sont fermés.