jeudi 24 novembre 2011

"View merging" et "Predicate pushing"

Le principe du “view merging” correspond à la capacité pour l’optimiseur de pouvoir transformer une requête SQL qui appelle une vue afin de fusionner cette vue au sein de la requête. Lorsqu’une vue est mergée, le code de la vue n’est plus exécutée à part mais fait partie intégrante de la requête principale.

Le fait de merger une vue peut être choisi par l’optimiseur afin par exemple d’appliquer à la vue une clause WHERE se trouvant à l’extérieur de la vue et ainsi limiter le nombre de lignes retournées par une table de la vue.

Exemple :
SQL> create table t1 as select * from dba_objects;

Table created.

SQL> insert into t1 select * from t1;

72706 rows created.

SQL> insert into t1 select * from t1;

145412 rows created.

SQL> create index idx_obj_id on t1(object_id);

Index created.

SQL> create index idx_obj_type on T1(object_type);

Index created.

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

PL/SQL procedure successfully completed.

SQL> create view v1 as select * from t1;

View created.

J’ai une table qui contient environ 290 000 lignes, un index B-TREE sur la colonne OBJECT_TYPE, un index B-TREE sur la colonne OBJECT_ID et une vue V1 qui sélectionne les lignes de la table T1.

La requête suivante effectue une jointure entre T1 et V1 en filtrant les objets de type EDITION :
select t1.object_name 
  from V1, T1 
 where t1.object_type = 'EDITION' 
   and t1.object_id = v1.object_id; 

Voyons ce que donne le plan lorsque la vue n’est pas mergée :
---------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
---------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |     16 |00:00:00.50 |    4147 |   4136 | 
|*  1 |  HASH JOIN                   |              |      1 |    106 |     16 |00:00:00.50 |    4147 |   4136 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       7 |      0 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       3 |      0 | 
|   4 |   VIEW                       | V1           |      1 |    290K|    290K|00:00:00.31 |    4140 |   4136 | 
|   5 |    TABLE ACCESS FULL         | T1           |      1 |    290K|    290K|00:00:00.18 |    4140 |   4136 | 
---------------------------------------------------------------------------------------------------------------- 
 Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - access("T1"."OBJECT_ID"="V1"."OBJECT_ID") 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 

L’opération 4 « VIEW » indique que le SELECT de la vue est exécuté à part et puisque dans le code de la vue il n’y a pas de clause WHERE, un Full Scan de la table T1 est effectué car toutes les lignes doivent être ramenées.

En revanche, la table T1 de la requête principale (outer query) est bien accédée via l’index IDX_OBJ_TYPE car le prédicat sur la colonne OBEJCT_TYPE est bien pris en compte.

Voyons maintenant le plan lorsque la vue est mergée :
---------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
---------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |     16 |00:00:00.04 |      21 |     10 | 
|   1 |  NESTED LOOPS                |              |      1 |    106 |     16 |00:00:00.04 |      21 |     10 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.02 |       9 |      7 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       5 |      3 | 
|*  4 |   INDEX RANGE SCAN           | IDX_OBJ_ID   |      4 |      4 |     16 |00:00:00.01 |      12 |      3 | 
---------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 
   4 - access("T1"."OBJECT_ID"="OBJECT_ID") 

Il n’y a plus d’opération VIEW dans le plan, c’est donc que la vue a été mergée.

La colonne Buffers du plan montre que le nombre de logical reads effectués est de 21. Avec la vue non mergée le coût était de 4147 logical reads.

Ce gain s’explique par le fait que le CBO a transformé la requête pour intégrer le code de la vue dans la requête principale. La requête devient en fait la suivante :
select t1.object_name 
from T1, T1 v1 
where t1.object_type = 'EDITION' 
and t1.object_id = v1.object_id; 

Cette transformation permet un accès indéxé pour les 2 tables beaucoup plus efficace que lorsque la vue doit être exécutée à part.

Pour obtenir le plan avec la vue non mergée dans mon exemple précédent, j’ai ajouté le hint NO_MERGE dans le code de la vue :
create or replace view v1 as select /*+ no_merge */ * from t1; 

Ce hint indique à l’optimiseur que la vue ne doit pas être mergée pendant la phase de transformation de la requête. Si vous êtes dans un cas où le CBO a décidé de ne pas merger la vue, vous pouvez forcer le merge en utilisant le hint MERGE.

Toutefois, il existe des cas où le merge peut être considéré comme non applicable par le CBO. C’est le cas parfois lorsque la vue contient par exemple:
  • une fonction analytique (ex : LEAD, LAG, RANK, ROW_NUMBER etc.)
  • une fonction d’agrégation (ex : SUM, AVG, MAX, MIN etc.)
  • la colonne ROWNUM
  • la clause ORDER BY

En effet, le fait de merger la vue dans ces cas là peut avoir une influence sur le résultat de la requête. Si le fait de merger peut influencer le résultat alors le CBO s’interdit de merger pendant la phase de transformation de la requête.

Exemple :
Je rajoute la colonne ROWNUM à ma vue V1 :
create or replace view v1 as select  t1.*,rownum RN from t1; 

J’exécute une requête simple avec un prédicat sur la colonne OBJECT_TYPE :
select * from v1 where object_type='EDITION'; 

  ----------------------------------------------------------------------------------------------- 
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
----------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT    |      |      1 |        |      4 |00:00:00.37 |    4139 |   4135 | 
|*  1 |  VIEW               | V1   |      1 |    290K|      4 |00:00:00.37 |    4139 |   4135 | 
|   2 |   COUNT             |      |      1 |        |    290K|00:00:00.27 |    4139 |   4135 | 
|   3 |    TABLE ACCESS FULL| T1   |      1 |    290K|    290K|00:00:00.14 |    4139 |   4135 | 
----------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter("OBJECT_TYPE"='EDITION') 

On s’aperçoit que la vue n’est pas mergée. Oracle doit attaquer la table T1 en Full Table Scan pour ensuite filtrer les lignes sur la colonne OBJECT_TYPE. Ce plan s’explique par la présence de la colonne ROWNUM qui serait différent si on appliquait très tôt la clause WHERE sur la colonne OBJECT_TYPE.

Il existe aussi des cas où même quand la vue ne peut être mergée le CBO peut tout de même se permettre d’appliquer les prédicats ,qui se trouvent au niveau de la requête principale, aux tables de la vue. Le but est de pouvoir filtrer les données au plus tôt au niveau du plan et/ou de pouvoir utiliser un index plus efficace.

Exemple :

La requête suivante donne un plan avec une vue complètement mergée et un accès indexé au niveau de la table T1 :
select * from ( select  t1.* from t1) v1 where object_type='EDITION'; 

------------------------------------------------------------------------------------------------------ 
| Id  | Operation                   | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------------ 
|   0 | SELECT STATEMENT            |              |      1 |        |      4 |00:00:00.01 |       8 | 
|   1 |  TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       8 | 
|*  2 |   INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       4 | 
------------------------------------------------------------------------------------------------------ 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
2 - access("T1"."OBJECT_TYPE"='EDITION') 

Voyons ce qui se passe si j’empêche le merge de la vue grâce au hint NO_MERGE :
select * from ( select /*+ no_merge */  t1.* from t1) v1 
where object_type='EDITION'; 
  
------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |      4 |00:00:00.01 |       8 | 
|   1 |  VIEW                        |              |      1 |     27 |      4 |00:00:00.01 |       8 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       8 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       4 | 
------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 

L’opération VIEW à la ligne 1 indique que la vue n’est pas mergée. Par contre on s’aperçoit qu’au lieu d’avoir un Full Table Scan on a un accès indexé. La clause WHERE sur la colonne OBJECT_TYPE a été pushée à l’intérieur de la vue afin de bénéficier de l’utilisation de l’index. Cette capacité à pusher les prédicats se nomme « Predicate Pushing ».

Normalement, le hint NO_PUSH_PRED devrait empêcher l’optimiseur de faire un « push down » des predicats, mais je ne sais pas pourquoi il ne fonctionne pas dans mon cas :
select /*+ NO_PUSH_PRED(v1) */ * from ( select /*+ no_merge */  t1.* 
from t1) v1 where object_type='EDITION'; 
------------------------------------------------------------------------------------------------------- 
| Id  | Operation                    | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers | 
------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT             |              |      1 |        |      4 |00:00:00.01 |       8 | 
|   1 |  VIEW                        |              |      1 |     27 |      4 |00:00:00.01 |       8 | 
|   2 |   TABLE ACCESS BY INDEX ROWID| T1           |      1 |     27 |      4 |00:00:00.01 |       8 | 
|*  3 |    INDEX RANGE SCAN          | IDX_OBJ_TYPE |      1 |     27 |      4 |00:00:00.01 |       4 | 
------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("T1"."OBJECT_TYPE"='EDITION') 

Du coup pour avoir une idée du plan sans le predicate pushing, j’ai ajouté la colonne ROWNUM dans la vue :
select * from ( select /*+ no_merge */  t1.* from t1 WHERE rownum > 1) v1  
where object_type='EDITION'; 
----------------------------------------------------------------------------- 
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     | 
----------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT     |      |   290K|    57M|  1127   (1)| 00:00:14 | 
|*  1 |  VIEW                |      |   290K|    57M|  1127   (1)| 00:00:14 | 
|   2 |   COUNT              |      |       |       |            |          | 
|*  3 |    FILTER            |      |       |       |            |          | 
|   4 |     TABLE ACCESS FULL| T1   |   290K|    26M|  1127   (1)| 00:00:14 | 
----------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   1 - filter("OBJECT_TYPE"='EDITION') 
   3 - filter(ROWNUM>1) 

On voit bien que quand le prédicat sur la colonne OBJECT_TYPE n’est pas pushé dans la vue, l’index de T1 ne peut être utilisé et on obtient donc un TABLE ACCESS FULL.

J’ai pensé à écrire cet article suite à un problème de perf rencontrée sur une requête faisant référence à une vue contenant notamment une fonction analytique.

La requête était la suivante :
SELECT task_instance_id, task_class_id, status, class_rank, instance_number 
  FROM (select 
         ti.task_instance_id as task_instance_id, 
         tc.task_class_id as task_class_id, 
         ti.status, 
         Rank() over(PARTITION BY tc.task_class_id ORDER BY ti.task_instance_id) AS class_rank, 
tc.instance_number, 
         tc.poll_max_grab 
          from mdw_task_instance      ti, 
               mdw_task_type          tt, 
               mdw_task_class         tc, 
               mdw_batch_engine_group eg, 
               mdw_batch_engine       be 
         where ti.status = 0 
           and ti.app_code = tt.app_code 
           and ti.task_label = tt.task_label 
           and tt.task_class_id = tc.task_class_id 
           and tc.batch_group_id = eg.batch_group_id 
and eg.batch_engine_id = be.batch_engine_id 
           and be.label = 'RLX000') poll_req 
 WHERE (instance_number <= 0 OR 
       (class_rank <= poll_req.poll_max_grab AND 
       0 <= instance_number - class_rank - 
       (select count(*) 
                 from mdw_task_instance ti, mdw_task_type tt 
                where ti.status in (1) 
                  and ti.app_code = tt.app_code 
                  and ti.task_label = tt.task_label 
                  and tt.task_class_id = poll_req.task_class_id))) 
 order by task_instance_id; 

----------------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | Writes | 
----------------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                             |      1 |        |      0 |00:00:02.28 |    5102 |   6781 |   6778 | 
|   1 |  SORT ORDER BY                      |                             |      1 |  64486 |      0 |00:00:02.28 |    5102 |   6781 |   6778 | 
|*  2 |   FILTER                            |                             |      1 |        |      0 |00:00:02.28 |    5102 |   6781 |   6778 | 
|   3 |    VIEW                             |                             |      1 |  64486 |    311K|00:00:02.23 |    5102 |   6781 |   6778 | 
|   4 |     WINDOW SORT                     |                             |      1 |  64486 |    311K|00:00:02.15 |    5102 |   6781 |   6778 | 
|   5 |      NESTED LOOPS                   |                             |      1 |  64486 |    311K|00:00:00.29 |    5100 |      3 |      0 | 
|   6 |       NESTED LOOPS                  |                             |      1 |     29 |     93 |00:00:00.07 |       8 |      2 |      0 | 
|*  7 |        HASH JOIN                    |                             |      1 |    175 |    108 |00:00:00.07 |       5 |      2 |      0 | 
|   8 |         MERGE JOIN                  |                             |      1 |     97 |     98 |00:00:00.07 |       4 |      2 |      0 | 
|   9 |          TABLE ACCESS BY INDEX ROWID| MDW_TASK_CLASS              |      1 |     94 |     94 |00:00:00.05 |       3 |      1 |      0 | 
|  10 |           INDEX FULL SCAN           | MDW_TASK_CLASS_PK           |      1 |     94 |     94 |00:00:00.05 |       1 |      1 |      0 | 
|* 11 |          SORT JOIN                  |                             |     94 |     97 |     98 |00:00:00.02 |       1 |      1 |      0 | 
|* 12 |           INDEX FULL SCAN           | MDW_TASK_TYPE_IDX1          |      1 |     97 |     98 |00:00:00.02 |       1 |      1 |      0 | 
|  13 |         INDEX FULL SCAN             | MDW_BATCH_ENGINE_GROUP_IDX1 |      1 |     18 |     18 |00:00:00.01 |       1 |      0 |      0 | 
|* 14 |        INDEX RANGE SCAN             | MDW_BATCH_ENGINE_IDX1       |    108 |      1 |     93 |00:00:00.01 |       3 |      0 |      0 | 
|* 15 |       INDEX RANGE SCAN              | MDW_TASK_INSTANCE_IDX4      |     93 |   2216 |    311K|00:00:00.16 |    5092 |      1 |      0 | 
|  16 |    SORT AGGREGATE                   |                             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 | 
|  17 |     NESTED LOOPS                    |                             |      0 |     40 |      0 |00:00:00.01 |       0 |      0 |      0 | 
|* 18 |      INDEX RANGE SCAN               | MDW_TASK_TYPE_IDX2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |      0 | 
|* 19 |      INDEX RANGE SCAN               | MDW_TASK_INSTANCE_IDX4      |      0 |     36 |      0 |00:00:00.01 |       0 |      0 |      0 | 
----------------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter(("INSTANCE_NUMBER"<=0 OR ("CLASS_RANK"<="POLL_REQ"."POLL_MAX_GRAB" AND "INSTANCE_NUMBER"-"CLASS_RANK"->=0))) 
   7 - access("TC"."BATCH_GROUP_ID"="EG"."BATCH_GROUP_ID") 
  11 - access("TT"."TASK_CLASS_ID"="TC"."TASK_CLASS_ID") 
       filter("TT"."TASK_CLASS_ID"="TC"."TASK_CLASS_ID") 
  12 - filter("TT"."TASK_CLASS_ID" IS NOT NULL) 
  14 - access("BE"."LABEL"='RLX000' AND "EG"."BATCH_ENGINE_ID"="BE"."BATCH_ENGINE_ID") 
  15 - access("TI"."STATUS"=0 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 
  18 - access("TT"."TASK_CLASS_ID"=:B1) 
  19 - access("TI"."STATUS"=1 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 

Le plan génère 5102 logical reads pour retourner 0 ligne. C’est beaucoup trop.

Le problème vient du fait que la clause la plus filtrante est la suivante : « instance_number <= 0 »

Cette clause se situe en dehors de la vue POLL_REQ mais s’applique à une colonne retournée par la vue. Comme la vue contient une fonction analytique le CBO ne peut ni merger la vue ni effectuer un push down des prédicats. Le filtrage des lignes ne s’effectue alors qu’après exécution de la vue. L’opération 2 du plan nommé VIEW indique que la vue n’a pas été mergée et la section « Predicate Information » du plan pour l’opération 2 indique que le filtrage des lignes sur notamment la colonne INSTANCE_NUMBER n’est effectué qu’après que la vue ait été exécutée.

En ajoutant le filtre sur la colonne INSTANCE_NUMBER à l’intérieur de la vue, on obtient un plan différent et beaucoup moins couteux :
SELECT  task_instance_id, task_class_id, status, class_rank, instance_number 
  FROM (select 
         ti.task_instance_id as task_instance_id, 
         tc.task_class_id as task_class_id, 
         ti.status, 
         Rank() over(PARTITION BY tc.task_class_id ORDER BY ti.task_instance_id) AS class_rank, 
         tc.instance_number, 
         tc.poll_max_grab 
          from mdw_task_instance      ti, 
               mdw_task_type          tt, 
               mdw_task_class         tc, 
               mdw_batch_engine_group eg, 
               mdw_batch_engine       be 
         where ti.status = 0 
           and ti.app_code = tt.app_code 
           and ti.task_label = tt.task_label 
           and tt.task_class_id = tc.task_class_id 
           and tc.batch_group_id = eg.batch_group_id 
           and eg.batch_engine_id = be.batch_engine_id 
           and be.label = 'RLX000' 
                                   and tc.instance_number <= 0 -- ajout 
                                   ) poll_req 
 WHERE (instance_number <= 0 OR 
       (class_rank <= poll_req.poll_max_grab AND 
       0 <= instance_number - class_rank - 
       (select count(*) 
                 from mdw_task_instance ti, mdw_task_type tt 
                where ti.status in (1) 
                  and ti.app_code = tt.app_code 
                  and ti.task_label = tt.task_label 
                  and tt.task_class_id = poll_req.task_class_id))) 
 order by task_instance_id; 
-------------------------------------------------------------------------------------------------------------------------------------- 
| Id  | Operation                           | Name                        | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  | 
-------------------------------------------------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                    |                             |      1 |        |      0 |00:00:00.05 |       1 |      1 | 
|   1 |  SORT ORDER BY                      |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|*  2 |   FILTER                            |                             |      1 |        |      0 |00:00:00.05 |       1 |      1 | 
|   3 |    VIEW                             |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|   4 |     WINDOW SORT                     |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|   5 |      NESTED LOOPS                   |                             |      1 |    347 |      0 |00:00:00.05 |       1 |      1 | 
|   6 |       NESTED LOOPS                  |                             |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|   7 |        NESTED LOOPS                 |                             |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|   8 |         MERGE JOIN CARTESIAN        |                             |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|   9 |          TABLE ACCESS BY INDEX ROWID| MDW_TASK_CLASS              |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|* 10 |           INDEX SKIP SCAN           | MDW_TASK_CLASS_IDX1         |      1 |      1 |      0 |00:00:00.05 |       1 |      1 | 
|  11 |          BUFFER SORT                |                             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 12 |           INDEX RANGE SCAN          | MDW_BATCH_ENGINE_IDX1       |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 13 |         INDEX RANGE SCAN            | MDW_BATCH_ENGINE_GROUP_IDX1 |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 14 |        INDEX RANGE SCAN             | MDW_TASK_TYPE_IDX2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 15 |       INDEX RANGE SCAN              | MDW_TASK_INSTANCE_IDX4      |      0 |   2216 |      0 |00:00:00.01 |       0 |      0 | 
|  16 |    SORT AGGREGATE                   |                             |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|  17 |     NESTED LOOPS                    |                             |      0 |     40 |      0 |00:00:00.01 |       0 |      0 | 
|* 18 |      INDEX RANGE SCAN               | MDW_TASK_TYPE_IDX2          |      0 |      1 |      0 |00:00:00.01 |       0 |      0 | 
|* 19 |      INDEX RANGE SCAN               | MDW_TASK_INSTANCE_IDX4      |      0 |     36 |      0 |00:00:00.01 |       0 |      0 | 
-------------------------------------------------------------------------------------------------------------------------------------- 

Predicate Information (identified by operation id): 
--------------------------------------------------- 
   2 - filter(("INSTANCE_NUMBER"<=0 OR ("CLASS_RANK"<="POLL_REQ"."POLL_MAX_GRAB" AND "INSTANCE_NUMBER"-"CLASS_RANK"->=0))) 
  10 - access("TC"."INSTANCE_NUMBER"<=0) 
       filter("TC"."INSTANCE_NUMBER"<=0) 
  12 - access("BE"."LABEL"='RLX000') 
  13 - access("EG"."BATCH_ENGINE_ID"="BE"."BATCH_ENGINE_ID" AND "TC"."BATCH_GROUP_ID"="EG"."BATCH_GROUP_ID") 

  14 - access("TT"."TASK_CLASS_ID"="TC"."TASK_CLASS_ID") 
       filter("TT"."TASK_CLASS_ID" IS NOT NULL) 
  15 - access("TI"."STATUS"=0 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 
  18 - access("TT"."TASK_CLASS_ID"=:B1) 
  19 - access("TI"."STATUS"=1 AND "TI"."APP_CODE"="TT"."APP_CODE" AND "TI"."TASK_LABEL"="TT"."TASK_LABEL") 

On passe de 5102 logical reads à 1 logical read. Bien sûr le fait d’ajouter la clause à l’intérieur de la vue a un impact sur la fonction analytique. C’est pour cela que le CBO s’interdit de merger la vue. Il revient au développeur d’estimer l’impact de cette solution.


Conclusion:

Depuis la version 10g, le CBO peut choisir de merger ou pas une vue en se basant sur le coût estimé de chaque plan. Il existe des cas où le CBO décide pour éviter d’impacter le résultat de la requête de ne pas merger la vue bien que le coût du plan aurait été bien plus intéressant (ex : ROWNUM ou fonction analytique impliqués dans la vue). Il existe aussi des cas où la vue n’est pas mergée mais où le CBO décide quand même d’effectuer un push down de certains prédicats à l’intérieur de la vue.

jeudi 10 novembre 2011

Exclure une table lors d'un import

Un petit post juste pour partager une astuce que j'ai utilisé aujourd'hui lors d'un import à l'ancienne (c'est à dire sans DataPump).

Je devais importer un dump d'un schéma (dump généré par un export classique) dans un schéma d'une autre base. Toutefois une énorme table ne m'intéressait pas dans ce dump. Avec Datapump, il est facile d'exclure une table de l'import en utilisant le paramètre EXCLUDE. Avec l'import classique c'est plus compliqué car la seule chose qu'on puisse faire dans les règles de l'art c'est lister les tables à importer. Or il est plus facile d'exclure une table sur 1000 en la nommant explicitement que de lister les 999 tables qu'on souhaite importer.

L'astuce que j'ai utilisée consiste à créer dans le schéma cible une table du même nom que celle qu'on veut exclure mais avec une structure différente. Ainsi, une erreur sera générée lors de l'import de cette table mais il ira au bout. Ensuite il ne nous reste plus qu'à dropper la "fausse" table.

Exemple:
Je souhaite importer le schéma ADMRTI contenu dans le dump export_admrti_s11.dump vers le schéma RTI$OWNER de la base OPARLISD12 tout en excluant la table TLS_AUDIT.

Pour ce faire, je me connecte d'abord à la base cible pour créer une table portant le même nom que celle que je veux exclure:
SQL> create table tls_audit as select * from user_objects where 1=2;
 
 Table created.

Ensuite je peux effectuer mon import:
C:\HOMEWARE>imp userid=rti$owner/xxxxx@oparlisd12 file=export_admrti_s11.dump log=import_rti$owner_LISD12.log Fromuser=ADMRTI Touser=RTI$OWNER ignore=y
 

Voici le log d'import obtenu:
Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
 With the Partitioning, OLAP, Data Mining and Real Application Testing options
 
 Export file created by EXPORT:V10.02.01 via direct path
 
 Warning: the objects were exported by ADMRTI, not by you
 
 import done in WE8MSWIN1252 character set and AL16UTF16 NCHAR character set
 import server uses AL32UTF8 character set (possible charset conversion)
 . . importing table                   "ADM_IMPORT"          2 rows imported
 . . importing table                      "CONTACT"          5 rows imported
 . . importing table                    "CO_SG_BDR"      11569 rows imported
 . . importing table                    "CO_SG_RTI"         62 rows imported
 . . importing table            "DATABASECHANGELOG"         28 rows imported
 . . importing table        "DATABASECHANGELOGLOCK"          1 rows imported
 . . importing table           "LNK_CONTACT_3PTYBR"          0 rows imported
 . . importing table               "LNK_CO_EXTCODE"         58 rows imported
 . . importing table       "LST_CONTACT_DEPARTMENT"         43 rows imported
 . . importing table         "LST_CONTACT_FUNCTION"         42 rows imported
 . . importing table            "LST_CONTACT_LEVEL"          4 rows imported
 . . importing table        "LST_INVESTOR_CATEGORY"         13 rows imported
 . . importing table                    "LST_TITLE"          3 rows imported
 . . importing table            "RTI_3PTY_EXT_CODE"       5239 rows imported
 . . importing table                 "RTI_BDR_3PTY"         92 rows imported
 . . importing table   "RTI_LNK_3PTY_BUSINESS_ROLE"       1007 rows imported
 . . importing table                 "RTI_LYX_3PTY"       2474 rows imported
 . . importing table           "RTI_REF_A_CLI_TYPE"          5 rows imported
 . . importing table        "RTI_REF_BUSINESS_ROLE"         18 rows imported
 . . importing table             "RTI_REF_CATEGORY"          3 rows imported
 . . importing table             "RTI_REF_CLI_TYPE"         68 rows imported
 . . importing table             "RTI_REF_EXT_CODE"         11 rows imported
 . . importing table         "RTI_REF_PRIMARY_ROLE"          9 rows imported
 . . importing table          "RTI_REF_SENSIBILITY"          6 rows imported
 . . importing table                 "RTI_SDD_3PTY"     333605 rows imported
 . . importing table         "RTI_TMP_SDD_3PTY_ADR"         17 rows imported
 . . importing table                    "TLS_AUDIT"
 IMP-00058: ORACLE error 904 encountered
 ORA-00904: "LATEST": invalid identifier
 IMP-00017: following statement failed with ORACLE error 904:
  "CREATE INDEX "IDX_TLS_AUDIT_CL_LA_PK" ON "TLS_AUDIT" ("ENTITY_CLASS_NAME" ,"
  " "LATEST" , "PK" )  PCTFREE 10 INITRANS 2 MAXTRANS 255 STORAGE(INITIAL 5200"
  "93696 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPACE "INDXRT"
  "I_L01" LOGGING"
 IMP-00003: ORACLE error 904 encountered
 ORA-00904: "PK": invalid identifier
 IMP-00017: following statement failed with ORACLE error 20000:
  "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_CL_LA_PK"',NULL,NULL"
  ",NULL,5679533,59956,429434,1,2,956931,3,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_CL_LA_PK" does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 4455
 ORA-06512: at "SYS.DBMS_STATS", line 9464
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 904:
  "CREATE INDEX "IDX_TLS_AUDIT_CL_LA_OT_PK" ON "TLS_AUDIT" ("ENTITY_CLASS_NAME"
  "" , "LATEST" , "OTHER_PK" )  PCTFREE 10 INITRANS 2 MAXTRANS 155 STORAGE(INI"
  "TIAL 562036736 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT) TABLESPAC"
  "E "INDXRTI_L01" LOGGING"
 IMP-00003: ORACLE error 904 encountered
 ORA-00904: "OTHER_PK": invalid identifier
 IMP-00017: following statement failed with ORACLE error 20000:
  "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_CL_LA_OT_PK"',NULL,N"
  "ULL,NULL,5558841,61214,5481,3825,16419,262719,3,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_CL_LA_OT_PK" does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 4455
 ORA-06512: at "SYS.DBMS_STATS", line 9464
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 904:
  "CREATE INDEX "IDX_TLS_AUDIT_ID" ON "TLS_AUDIT" ("AUDIT_ID" )  PCTFREE 10 IN"
  "ITRANS 2 MAXTRANS 255 STORAGE(INITIAL 173015040 FREELISTS 1 FREELIST GROUPS"
  " 1 BUFFER_POOL DEFAULT) TABLESPACE "INDXRTI_L01" LOGGING"
 IMP-00003: ORACLE error 904 encountered
 ORA-00904: "AUDIT_ID": invalid identifier
 IMP-00017: following statement failed with ORACLE error 20000:
  "BEGIN  DBMS_STATS.SET_INDEX_STATS(NULL,'"IDX_TLS_AUDIT_ID"',NULL,NULL,NULL,"
  "5505116,20231,5486471,1,1,394696,2,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: INDEX "RTI$OWNER"."IDX_TLS_AUDIT_ID" does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 4455
 ORA-06512: at "SYS.DBMS_STATS", line 9464
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C31D374E'; SREC.MAX"
  "VAL := 'C40A0C5D11'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DB"
  "MS_STATS.NUMARRAY(285477,9119216); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1);"
  " SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"AUDIT_ID"'"
  ", NULL ,NULL,NULL,5486471,.000000182266524328662,0,srec,6,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column AUDIT_ID: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '786C0B14131C0F'; SR"
  "EC.MAXVAL := '786F0B0412301B'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NO"
  "VALS := DBMS_STATS.NUMARRAY(2454791.76891204,2455870.74127315); SREC.BKVALS"
  " := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NU"
  "LL,'"TLS_AUDIT"','"DATETIME"', NULL ,NULL,NULL,40852,.0000244786056986194,0"
  ",srec,8,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column DATETIME: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '494E53455254'; SREC"
  ".MAXVAL := '555044415445'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS"
  " := DBMS_STATS.NUMARRAY(380626296013610000000000000000000000,44297323347720"
  "5000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC "
  ":= 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"TRIGGER_EVENT"', NUL"
  "L ,NULL,NULL,2,.5,0,srec,7,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column TRIGGER_EVENT: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '424541312D303030363"
  "436354641303143453531444138423828343938313229'; SREC.MAXVAL := '424541312D3"
  "74646383230443939383342343336414133304328323138343430'; SREC.EAVS := 0; SRE"
  "C.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(3440962439759090000000"
  "00000000000000,344096243975942000000000000000000000); SREC.BKVALS := DBMS_S"
  "TATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_A"
  "UDIT"','"TX_ID"', NULL ,NULL,NULL,355868,.00000281003068553509,8537,srec,36"
  ",6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column TX_ID: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '3C616E6F6E796D6F757"
  "33E'; SREC.MAXVAL := '6E617468616E61656C2E697469632D73696D6F6E69'; SREC.EAV"
  "S := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(31351395482"
  "7912000000000000000000000,573129270941465000000000000000000000); SREC.BKVAL"
  "S := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(N"
  "ULL,'"TLS_AUDIT"','"USER_ID"', NULL ,NULL,NULL,12,.0833333333333333,0,srec,"
  "10,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column USER_ID: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '636F6D2E6C79786F722"
  "E7274692E64616F2E656E746974792E436F5367426472'; SREC.MAXVAL := '636F6D2E6C7"
  "9786F722E7274692E64616F2E656E746974792E52746953646433'; SREC.EAVS := 0; SRE"
  "C.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(5162973866981150000000"
  "00000000000000,516297386698115000000000000000000000); SREC.BKVALS := DBMS_S"
  "TATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_A"
  "UDIT"','"ENTITY_CLASS_NAME"', NULL ,NULL,NULL,4,.25,0,srec,37,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column ENTITY_CLASS_NAME: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '80'; SREC.MAXVAL :="
  " 'C102'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUM"
  "ARRAY(0,1); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_ST"
  "ATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"IS_LONG_PK"', NULL ,NULL,NULL,2,."
  "5,0,srec,3,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column IS_LONG_PK: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := 'C20431'; SREC.MAXVA"
  "L := 'C422054140'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVALS := DBMS"
  "_STATS.NUMARRAY(348,33046463); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SRE"
  "C.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"PK"', NULL ,NU"
  "LL,NULL,274069,.00000364871619920531,6214,srec,6,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column PK: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '286C797849643A312C6"
  "272436F64653A3129'; SREC.MAXVAL := '636F6D2E6C79786F722E7274692E64616F2E656"
  "E746974792E4C6E6B436F4578'; SREC.EAVS := 0; SREC.CHVALS := NULL; SREC.NOVAL"
  "S := DBMS_STATS.NUMARRAY(209891998413178000000000000000000000,5162973866981"
  "15000000000000000000000); SREC.BKVALS := DBMS_STATS.NUMARRAY(0,1); SREC.EPC"
  " := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDIT"','"OTHER_PK"', NULL ,N"
  "ULL,NULL,5481,.000182448458310527,5490846,srec,2,6); END;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column OTHER_PK: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '626472416374697665'"
  "; SREC.MAXVAL := '727469427573696E657373526F6C6573'; SREC.EAVS := 0; SREC.C"
  "HVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(5108823853440640000000000"
  "00000000000,594282940912058000000000000000000000); SREC.BKVALS := DBMS_STAT"
  "S.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_AUDI"
  "T"','"PROPERTY_NAME"', NULL ,NULL,NULL,27,.037037037037037,0,srec,17,6); EN"
  "D;"
 IMP-00003: ORACLE error 20000 encountered
 ORA-20000: Unable to set values for column PROPERTY_NAME: does not exist or insufficient privileges
 ORA-06512: at "SYS.DBMS_STATS", line 8837
 ORA-06512: at "SYS.DBMS_STATS", line 9103
 ORA-06512: at line 1
 IMP-00017: following statement failed with ORACLE error 20000:
  "DECLARE  SREC DBMS_STATS.STATREC; BEGIN SREC.MINVAL := '6A6176612E6C616E672"
  "E4C6F6E67'; SREC.MAXVAL := '6A6176612E7574696C2E536574'; SREC.EAVS := 0; SR"
  "EC.CHVALS := NULL; SREC.NOVALS := DBMS_STATS.NUMARRAY(552360239735591000000"
  "000000000000000,552360239735634000000000000000000000); SREC.BKVALS := DBMS_"
  "STATS.NUMARRAY(0,1); SREC.EPC := 2; DBMS_STATS.SET_COLUMN_STATS(NULL,'"TLS_"
  "AUDIT"','"PROPERTY_CLASS_NAME"', NULL ,NULL,NULL,4,.25,12066,srec,17,6); EN"
  "D;"
 [...]
 . . importing table                      "WLSTORE"         36 rows imported
 About to enable constraints...
 Import terminated successfully with warnings.
 

On constate que toutes les tables ont été importées sauf la table TLS_AUDIT et ses objets associés (index, contraintes etc.)