_complex_view_merging对执行计划的影响

以下是在10.2.0.1版本下测试，以下实验来着于Jonathan Lewis 《CBO》。

1、创建测试表

SQL>create table t1 (
              id_par        number(6)    not null,
               vc1        varchar2(32)    not null,
                vc2        varchar2(32)    not null,
               padding        varchar2(100)
           );
SQL>alter table t1 add constraint t1_pk primary key (id_par);

SQL>create table t2 (
                 id_ch        number(6)    not null,
                 id_par        number(6)    not null,
                 val        number(6,2),
                 padding        varchar2(100)
                  );
SQL>alter table t2 add constraint t2_pk primary key (id_ch);
SQL>alter table t2 add constraint t2_fk_t1 foreign key (id_par) references t1;

2、插入测试数据
SQL>insert into t1
        select
               rownum,
                vc1,
                 vc2,
                 rpad('x',100)
        from
            (
                  select
                 lpad(trunc(sqrt(rownum)),32)    vc1,
                 lpad(rownum,32)            vc2
          from all_objects
        where rownum <= 32
        ) ;
SQL>commit;

SQL>insert into t2
           select
                  rownum,
                  d1.id_par,
                   rownum,
                    rpad('x',100)
              from
                      t1    d1,
                      t1    d2
;
SQL>commit;

3、统计分析（略）
4、创建视图
SQL>create or replace view avg_val_view AS
          select
                id_par, avg(val) avg_val_t1
           from    t2
            group by   id_par;

5、设置_complex_view_merging=true （缺省）
SQL>set autotrace traceonly explain
SQL>select
                 t1.vc1, avg_val_t1
             from
                t1, avg_val_view
           where   
                t1.vc2 = lpad(18,32)
           and    avg_val_view.id_par = t1.id_par;
执行计划1:
-----------------------------------------------------------------------
| Id  | Operation                                 | Name  | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT              |       |    23 |  2024 |     8 |
|   1 |  HASH GROUP BY                    |       |    23 |  2024 |     8 |
|   2 |   NESTED LOOPS                     |       |    32 |  2816 |     5 |
|   3 |    TABLE ACCESS FULL          | T2    |  1024 |  7168 |     4 |
|*  4 |    TABLE ACCESS BY INDEX ROWID| T1    |     1 |    81 |     1 |
|*  5 |     INDEX UNIQUE SCAN         | T1_PK |     1 |       |       |
-----------------------------------------------------------------------
T1表与视图avg_val_view中的T2表进行关联，然后在hash group by。在9i版本是sort (group by)。该执行计划进行复杂视图合并。

如果不进行合并，希望执行计划将按部就班，我们需要加no_merge hint
SQL>select
              /*+ no_merge (avg_val_view) */
               t1.vc1, avg_val_t1
           from
               t1, avg_val_view
          where   
                t1.vc2 = lpad(18,32)
           and    avg_val_view.id_par = t1.id_par
执行计划2：
-----------------------------------------------------------------------------
| Id  | Operation                                     | Name         | Rows  | Bytes | Cost  |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |              |     1 |    95 |     9 |
|   1 |  NESTED LOOPS                      |              |     1 |    95 |     9 |
|   2 |   VIEW                                         | AVG_VAL_VIEW |    32 |   832 |     8 |
|   3 |    HASH GROUP BY                  |              |    32 |   224 |     8 |
|   4 |     TABLE ACCESS FULL        | T2           |  1024 |  7168 |     4 |
|*  5 |   TABLE ACCESS BY INDEX ROWID| T1           |     1 |    69 |     1 |
|*  6 |    INDEX UNIQUE SCAN         | T1_PK        |     1 |       |       |
-----------------------------------------------------------------------------

5、设置_complex_view_merging=false
       
        无论是否加hint，执行计划都与 执行计划2一致。我们加merge(avg_val_view)  hint想使执行计划变成 执行计划1是不可能的，因为该merge hint在参数_complex_view_merging=false失效。

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/354732/viewspace-622055/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/354732/viewspace-622055/