_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/，如需轉載，請註明出處，否則將追究法律責任。