以下是在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失效。
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/