Le hint CARDINALITY permet d’aider l’optimiseur Oracle en lui « soufflant » le nombre de lignes retournées pour une étape d’un plan. Vous pensez qu’avec les différents mécanismes de dynamic sampling, cardinality feedback, statistiques multi-colonnes, statistiques sur les colonnes virtuelles et SQL Profile, ce hint est inutile ?

Voici une requête où typiquement, la seule solution, sauf de fixer le plan ou de réécrire le SQL, consiste à utiliser ce hint :

Schéma exemple

Pour commencer, créez une table SALES qui contient des éléments de vente :

create table sales 
  as select to_date('31/12/2010','DD/MM/YYYY')+mod(rownum,365) sdate,
            mod(rownum,99) product_id,
            mod(rownum,177) qty,
            rpad('X',1000) description
       from dual 
      connect by level <=36401;

exec dbms_stats.gather_table_stats( -
               ownname=>user, -
               tabname=>'SALES', -
               method_opt=>'for all columns size 254');

create index sales_date on sales(sdate);

Requête originale

Pour les besoins d’un rapport, affichez tous les jours entre 2 dates avec les ventes associées ; il est possible que la table SALES ne contienne pas de ligne pour une journée donnée ; vous utiliserez une jointure externe comme ci-dessous :

select xdate,
       count(distinct product_id),
       sum(qty) qty
  from sales,
       (select to_date('01/01/2011','DD/MM/YYYY')+rownum -1 xdate 
          from dual
         connect by level <=   to_date('31/12/2013','DD/MM/YYYY') -
                               to_date('01/01/2011','DD/MM/YYYY') + 1) cal
  where cal.xdate=sales.sdate(+)
group by xdate
order by xdate;
  
select * 
  from table(
      dbms_xplan.display_cursor(
                   null,
                   null,
                  'last basic +note +rows +predicate'));

EXPLAINED SQL STATEMENT:
------------------------
Plan hash value: 2708806741
------------------------------------------------------------------
| Id  | Operation                           | Name       | Rows  |
------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |            |       |
|   1 |  SORT ORDER BY                      |            |    99 |
|   2 |   HASH GROUP BY                     |            |    99 |
|   3 |    VIEW                             | VW_DAG_0   |    99 |
|   4 |     HASH GROUP BY                   |            |    99 |
|   5 |      NESTED LOOPS OUTER             |            |   100 |
|   6 |       VIEW                          |            |     1 |
|   7 |        COUNT                        |            |       |
|   8 |         CONNECT BY WITHOUT FILTERING|            |       |
|   9 |          FAST DUAL                  |            |     1 |
|  10 |       TABLE ACCESS BY INDEX ROWID   | SALES      |   100 |
|* 11 |        INDEX RANGE SCAN             | SALES_DATE |   100 |
------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
  11 - access("SALES"."SDATE"=INTERNAL_FUNCTION("CAL"."XDATE"))

Dans cet exemple l’estimation du nombre de lignes retournées sur le bloc interrogeant DUAL est de 1 ligne. Le nombre de lignes retournées dans l’étape qui accède à la table SALES est très loin de ce qui sera constaté lors de l’exécution…

Notes:
Pour des raisons qu’illustre typiquement cet exemple, il est préférable de matérialiser la dimension du temps dans une table qui contient les jours et les relations hiérarchiques avec les semaines, mois et années. Même si, ça nécessite de maintenir cette table.

Hint CARDINALITY

L’utilisation du hint CARDINALITY donne une meilleure estimation des cardinalités des différentes étapes et change le plan de la requête. Vous pourrez regarder le temps d’exécution et le nombre d’I/O logique pour vous persuader de l’amélioration apportée par le changement :

select xdate,
       count(distinct product_id),
       sum(qty) qty
  from sales,
       (select /*+ cardinality(1096) */ to_date('01/01/2011','DD/MM/YYYY')+rownum -1 xdate 
          from dual
         connect by level <=   to_date('31/12/2013','DD/MM/YYYY') -
                               to_date('01/01/2011','DD/MM/YYYY') + 1) cal
  where cal.xdate=sales.sdate(+)
group by xdate
order by xdate;
  
select * 
  from table(
      dbms_xplan.display_cursor(
                   null,
                   null,
                  'last basic +note +rows +predicate'));

EXPLAINED SQL STATEMENT:
------------------------
Plan hash value: 1411371914
----------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  |
----------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |       |
|   1 |  SORT ORDER BY                      |          |    99 |
|   2 |   HASH GROUP BY                     |          |    99 |
|   3 |    VIEW                             | VW_DAG_0 |    99 |
|   4 |     HASH GROUP BY                   |          |    99 |
|*  5 |      HASH JOIN OUTER                |          |   109K|
|   6 |       VIEW                          |          |  1096 |
|   7 |        COUNT                        |          |       |
|   8 |         CONNECT BY WITHOUT FILTERING|          |       |
|   9 |          FAST DUAL                  |          |     1 |
|  10 |       TABLE ACCESS FULL             | SALES    | 36401 |
----------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   5 - access("SALES"."SDATE"=INTERNAL_FUNCTION("CAL"."XDATE"))

Le plan choisi est plus efficace que le plan original. Dans ce cas, la nuance est légère mais au bout du compte, la différence peut être importante.

Les autres outils

Persuadez-vous que les autres outils dont vous disposez ne permettent pas de régler ce problème sans ré-écrire la requête ou fixer le plan. Ci-dessous, voilà ce que donne une session SQL Tuning Advisor. J’ai pensé un instant qu’un profile SQL aurait pu être détecté :

variable gg varchar2(100)
exec :gg := DBMS_SQLTUNE.CREATE_TUNING_TASK( -
        sql_text => 'select xdate,'|| -
'       count(distinct product_id),'|| -
'       sum(qty) qty'|| -
'  from sales,'|| -
'       (select to_date(''01/01/2011'',''DD/MM/YYYY'')+rownum -1 xdate'|| - 
'          from dual'|| -
'         connect by level <=   to_date(''31/12/2013'',''DD/MM/YYYY'') -'|| -
'                               to_date(''01/01/2011'',''DD/MM/YYYY'') + 1) cal'|| -
'  where cal.xdate=sales.sdate(+)'|| -
'  group by xdate'|| -
'  order by xdate',-
         user_name => user, -
         scope => 'COMPREHENSIVE', -
         time_limit => 30, -
         task_name => 'MYDEMO', -
         description => 'SQL Profile vs Cardinality');

exec dbms_sqltune.execute_tuning_task( task_name => 'MYDEMO' );

set long 10000
set longchunksize 10000
set lines 100
select dbms_sqltune.report_tuning_task('MYDEMO')
from dual;

DBMS_SQLTUNE
.REPORT_TUNING_TASK('MYDEMO')
-------------------------------------------------------------------------------
GENERAL INFORMATION SECTION
-------------------------------------------------------------------------------
Tuning Task Name   : MYDEMO
Tuning Task Owner  : DEMO
Workload Type      : Single SQL Statement
Scope              : COMPREHENSIVE
Time Limit(seconds): 30
Completion Status  : COMPLETED
Started at         : 01/22/2012 12:47:32
Completed at       : 01/22/2012 12:47:35

-------------------------------------------------------------------------------
Schema Name: DEMO
SQL ID     : 4m8q874b86uwm
SQL Text   : select xdate,       count(distinct product_id),       sum(qty)
             qty  from sales,       (select to_date('01/01/2011','DD/MM/YYYY')
             +rownum -1 xdate          from dual         connect by level <=
              to_date('31/12/2013','DD/MM/YYYY') -
                to_date('01/01/2011','DD/MM/YYYY') + 1) cal  where
             cal.xdate=sales.sdate(+)  group by xdate  order by xdate

-------------------------------------------------------------------------------
There are no recommendations to improve the statement.
-------------------------------------------------------------------------------

exec dbms_sqltune.drop_tuning_task( task_name => 'MYDEMO' );