首先,我们看看 Jonathan Lewis 的解释:
• Merge / no_merge: if you use a complex view (e.g. aggregate view, or join view) in your query, should you rewrite the query to merge the tables in the view into a single from clause with all the other tables (merge), or should you evaluate the view to produce a “standalone” result set and then join the result set to the remaining tables (no_merge).
-- 只要查询中出现view,就可以使用merge/no_merge;
merge 的含义是把veiw展开,把sql从(view join view/table) 变成多个table 相互jion的写法, 执行计划中也不会出现view的acces path~
no_merge 则正好相反,不改写view,不把view展开~ 执行计划中必定出现view的access path~
• Push_pred / no_push_pred: If you have a non-mergeable view (possible because of a no_merge hint) in your query, how should you operate the join from other tables; should you create one large view result and join it once (no_push_pred) or should you push the join predicate down into the view definition and recreate the view result set for every driving row from another table (push_pred).
--同样查询中必须至少有view的存在,并且这个view是不可以被merge的情况下才可以使用push_pred/no_push_pred
no_push_pred的含义是view只作为一个view先得出结果,然后根据连接条件和其他的table/view根据连接条件,进行连接~
Push_pred的含义是我将“连接条件”插入到view中, 使用其他结果集根据连接条件去驱动这个view进行连接(也就是被push_pred的view只能作为nested-loop的外循环)
下面是我的一个测试
create table tx (x1 int,x2 int);
create table ty (y1 int,y2 int);
create index ind1_tx on tx (x1);
create index ind2_tx on tx (x2);
create index ind1_ty on ty (y1);
create index ind2_ty on ty (y2);
insert into tx select rownum+1,rownum+3 from dba_tables;
insert into ty select rownum+2,rownum+4 from dba_tables;
commit;
exec dbms_stats.gather_table_stats(user,'TX',cascade=>true);
exec dbms_stats.gather_table_stats(user,'TY',cascade=>true);
查询使用的是:
select * from tx, (select ty.y2 from ty where ty.y1 <10) y
where tx.x1 <10 and tx.x2 =y.y2(+)
--也就是table tx 和 view y通过连接条件tx.x2=y.y2(+) 进行外连接 (因为我发现似乎push_pred只能发生在外连接上~)
先看看merge/no_merge
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID asavnptg692v7, child number 1
-------------------------------------
select /*+ merge(y) */ * from tx, (select ty.y2 from ty where ty.y1 <10) y where tx.x1
<10 and tx.x2 =y.y2(+)
Plan hash value: 161655348
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 8 | 8 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 8 | 8 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| TY | 1 | 7 | 7 |00:00:00.01 | 5 |
|* 5 | INDEX RANGE SCAN | IND1_TY | 1 | 7 | 7 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TX"."X2"="TY"."Y2"
3 - access("TX"."X1"<10)
5 - access("TY"."Y1"<10)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 2jybp392j7vab, child number 1
-------------------------------------
select /*+ no_merge(y) */ * from tx, (select ty.y2 from ty where ty.y1 <10) y where tx.x1
<10 and tx.x2 =y.y2(+)
Plan hash value: 453677432
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 8 | 8 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID | TX | 1 | 8 | 8 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 2 |
| 4 | VIEW | | 1 | 7 | 7 |00:00:00.01 | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID| TY | 1 | 7 | 7 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | IND1_TY | 1 | 7 | 7 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TX"."X2"="Y"."Y2"
3 - access("TX"."X1"<10)
6 - access("TY"."Y1"<10)
--可见虽然执行计划没有什么改变,但是当我们使用no_merge的时候 view y出现在了access path中,也就是说第二个执行计划(no_merge)中是table tx和view y做的
hash join outer ,而不是像第一个执行计划中ty可以直接和tx发生hash join outer~ 也就是no_merge的条件下必须先计算view的结果,然后才能和其它的
table或者view或者结果集,进行连接~
然后我们再看看push_pred/no_push_pred (请注意这两个hints生效的前提是sql必须有view而且这个view不能被merge,所以我使用的是no_merge来保证这点)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID c9j3pyyhrhcr6, child number 0
-------------------------------------
select /*+ no_merge(y) no_push_pred(y) */ * from tx, (select ty.y2 from ty where ty.y1
<10) y where tx.x1 <10 and tx.x2 =y.y2(+)
Plan hash value: 453677432
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 8 | 8 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID | TX | 1 | 8 | 8 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 2 |
| 4 | VIEW | | 1 | 7 | 7 |00:00:00.01 | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID| TY | 1 | 7 | 7 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | IND1_TY | 1 | 7 | 7 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TX"."X2"="Y"."Y2"
3 - access("TX"."X1"<10)
6 - access("TY"."Y1"<10)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6hxjk8xpzn3fm, child number 1
-------------------------------------
select /*+ no_merge(y) push_pred(y) */ * from tx, (select ty.y2 from ty where ty.y1 <10) y
where tx.x1 <10 and tx.x2 =y.y2(+)
Plan hash value: 1364836571
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 1 | 8 | 8 |00:00:00.01 | 22 |
| 2 | TABLE ACCESS BY INDEX ROWID | TX | 1 | 8 | 8 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 3 |
| 4 | VIEW PUSHED PREDICATE | | 8 | 1 | 7 |00:00:00.01 | 17 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TY | 8 | 1 | 7 |00:00:00.01 | 17 |
|* 6 | INDEX RANGE SCAN | IND2_TY | 8 | 1 | 7 |00:00:00.01 | 10 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TX"."X1"<10)
5 - filter("TY"."Y1"<10)
6 - access("TY"."Y2"="TX"."X2"
-- 可见当我使用no_push_pred的时候view可以先得出结果集,然后和其它的table或者view或者结果集进行hash join outer连接~ 也就是连接条件并不发生在view的计算中~
但是当我使用push_pred的时候,tx的结果集开始使用连接条件驱动view y,进行nested loop outer操作~ 来完成连接~ 注意出现了 VIEW PUSHED PREDICATE,请注意ty的索引使用和access 谓词~
no_push_pred的时候view y 的计算只是用了6 - access("TY"."Y1"<10)来访问ind1_ty,
而使用push_pred的时候view y的计算使用的是6 - access("TY"."Y2"="TX"."X2"来访问 IND2_TY 并且通过5 - filter("TY"."Y1"<10)来过滤ty.y1<10这个过滤条件,
也就是说push_pred是把连接条件tx.x2=y.y2推入到了view中,变为tx.x2=ty.y2来先进行计算~
那么我们可以看出merge和push_pred的区别了~
merge是把view中的table提取出来,重写sql,将view的连接变成table的连接
而push_pred必须保证有view不能merge~ 也就是说view还是一个整体,只不过连接条件要在view中先进性计算~
这也就决定了merge可以使用table之间的hash jion或者nest loop来完成
但是push_pred只能使用nested loop的方式来被驱动完成sql地执行~
我想这就是他们的区别吧~
所以其实我觉得/no_merge no_push_pred/和/no_merge push_pred/的选择,关键在于使用nestloop方式连接这个view是否能够带来性能的提升~
• Merge / no_merge: if you use a complex view (e.g. aggregate view, or join view) in your query, should you rewrite the query to merge the tables in the view into a single from clause with all the other tables (merge), or should you evaluate the view to produce a “standalone” result set and then join the result set to the remaining tables (no_merge).
-- 只要查询中出现view,就可以使用merge/no_merge;
merge 的含义是把veiw展开,把sql从(view join view/table) 变成多个table 相互jion的写法, 执行计划中也不会出现view的acces path~
no_merge 则正好相反,不改写view,不把view展开~ 执行计划中必定出现view的access path~
• Push_pred / no_push_pred: If you have a non-mergeable view (possible because of a no_merge hint) in your query, how should you operate the join from other tables; should you create one large view result and join it once (no_push_pred) or should you push the join predicate down into the view definition and recreate the view result set for every driving row from another table (push_pred).
--同样查询中必须至少有view的存在,并且这个view是不可以被merge的情况下才可以使用push_pred/no_push_pred
no_push_pred的含义是view只作为一个view先得出结果,然后根据连接条件和其他的table/view根据连接条件,进行连接~
Push_pred的含义是我将“连接条件”插入到view中, 使用其他结果集根据连接条件去驱动这个view进行连接(也就是被push_pred的view只能作为nested-loop的外循环)
下面是我的一个测试
create table tx (x1 int,x2 int);
create table ty (y1 int,y2 int);
create index ind1_tx on tx (x1);
create index ind2_tx on tx (x2);
create index ind1_ty on ty (y1);
create index ind2_ty on ty (y2);
insert into tx select rownum+1,rownum+3 from dba_tables;
insert into ty select rownum+2,rownum+4 from dba_tables;
commit;
exec dbms_stats.gather_table_stats(user,'TX',cascade=>true);
exec dbms_stats.gather_table_stats(user,'TY',cascade=>true);
查询使用的是:
select * from tx, (select ty.y2 from ty where ty.y1 <10) y
where tx.x1 <10 and tx.x2 =y.y2(+)
--也就是table tx 和 view y通过连接条件tx.x2=y.y2(+) 进行外连接 (因为我发现似乎push_pred只能发生在外连接上~)
先看看merge/no_merge
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID asavnptg692v7, child number 1
-------------------------------------
select /*+ merge(y) */ * from tx, (select ty.y2 from ty where ty.y1 <10) y where tx.x1
<10 and tx.x2 =y.y2(+)
Plan hash value: 161655348
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
--------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 8 | 8 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | 8 | 8 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 2 |
| 4 | TABLE ACCESS BY INDEX ROWID| TY | 1 | 7 | 7 |00:00:00.01 | 5 |
|* 5 | INDEX RANGE SCAN | IND1_TY | 1 | 7 | 7 |00:00:00.01 | 3 |
--------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TX"."X2"="TY"."Y2"
3 - access("TX"."X1"<10)
5 - access("TY"."Y1"<10)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 2jybp392j7vab, child number 1
-------------------------------------
select /*+ no_merge(y) */ * from tx, (select ty.y2 from ty where ty.y1 <10) y where tx.x1
<10 and tx.x2 =y.y2(+)
Plan hash value: 453677432
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 8 | 8 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID | TX | 1 | 8 | 8 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 2 |
| 4 | VIEW | | 1 | 7 | 7 |00:00:00.01 | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID| TY | 1 | 7 | 7 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | IND1_TY | 1 | 7 | 7 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TX"."X2"="Y"."Y2"
3 - access("TX"."X1"<10)
6 - access("TY"."Y1"<10)
--可见虽然执行计划没有什么改变,但是当我们使用no_merge的时候 view y出现在了access path中,也就是说第二个执行计划(no_merge)中是table tx和view y做的
hash join outer ,而不是像第一个执行计划中ty可以直接和tx发生hash join outer~ 也就是no_merge的条件下必须先计算view的结果,然后才能和其它的
table或者view或者结果集,进行连接~
然后我们再看看push_pred/no_push_pred (请注意这两个hints生效的前提是sql必须有view而且这个view不能被merge,所以我使用的是no_merge来保证这点)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID c9j3pyyhrhcr6, child number 0
-------------------------------------
select /*+ no_merge(y) no_push_pred(y) */ * from tx, (select ty.y2 from ty where ty.y1
<10) y where tx.x1 <10 and tx.x2 =y.y2(+)
Plan hash value: 453677432
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
|* 1 | HASH JOIN OUTER | | 1 | 8 | 8 |00:00:00.01 | 8 |
| 2 | TABLE ACCESS BY INDEX ROWID | TX | 1 | 8 | 8 |00:00:00.01 | 3 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 2 |
| 4 | VIEW | | 1 | 7 | 7 |00:00:00.01 | 5 |
| 5 | TABLE ACCESS BY INDEX ROWID| TY | 1 | 7 | 7 |00:00:00.01 | 5 |
|* 6 | INDEX RANGE SCAN | IND1_TY | 1 | 7 | 7 |00:00:00.01 | 3 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("TX"."X2"="Y"."Y2"
3 - access("TX"."X1"<10)
6 - access("TY"."Y1"<10)
PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID 6hxjk8xpzn3fm, child number 1
-------------------------------------
select /*+ no_merge(y) push_pred(y) */ * from tx, (select ty.y2 from ty where ty.y1 <10) y
where tx.x1 <10 and tx.x2 =y.y2(+)
Plan hash value: 1364836571
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
---------------------------------------------------------------------------------------------------
| 1 | NESTED LOOPS OUTER | | 1 | 8 | 8 |00:00:00.01 | 22 |
| 2 | TABLE ACCESS BY INDEX ROWID | TX | 1 | 8 | 8 |00:00:00.01 | 5 |
|* 3 | INDEX RANGE SCAN | IND1_TX | 1 | 8 | 8 |00:00:00.01 | 3 |
| 4 | VIEW PUSHED PREDICATE | | 8 | 1 | 7 |00:00:00.01 | 17 |
|* 5 | TABLE ACCESS BY INDEX ROWID| TY | 8 | 1 | 7 |00:00:00.01 | 17 |
|* 6 | INDEX RANGE SCAN | IND2_TY | 8 | 1 | 7 |00:00:00.01 | 10 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("TX"."X1"<10)
5 - filter("TY"."Y1"<10)
6 - access("TY"."Y2"="TX"."X2"
-- 可见当我使用no_push_pred的时候view可以先得出结果集,然后和其它的table或者view或者结果集进行hash join outer连接~ 也就是连接条件并不发生在view的计算中~
但是当我使用push_pred的时候,tx的结果集开始使用连接条件驱动view y,进行nested loop outer操作~ 来完成连接~ 注意出现了 VIEW PUSHED PREDICATE,请注意ty的索引使用和access 谓词~
no_push_pred的时候view y 的计算只是用了6 - access("TY"."Y1"<10)来访问ind1_ty,
而使用push_pred的时候view y的计算使用的是6 - access("TY"."Y2"="TX"."X2"来访问 IND2_TY 并且通过5 - filter("TY"."Y1"<10)来过滤ty.y1<10这个过滤条件,
也就是说push_pred是把连接条件tx.x2=y.y2推入到了view中,变为tx.x2=ty.y2来先进行计算~
那么我们可以看出merge和push_pred的区别了~
merge是把view中的table提取出来,重写sql,将view的连接变成table的连接
而push_pred必须保证有view不能merge~ 也就是说view还是一个整体,只不过连接条件要在view中先进性计算~
这也就决定了merge可以使用table之间的hash jion或者nest loop来完成
但是push_pred只能使用nested loop的方式来被驱动完成sql地执行~
我想这就是他们的区别吧~
所以其实我觉得/no_merge no_push_pred/和/no_merge push_pred/的选择,关键在于使用nestloop方式连接这个view是否能够带来性能的提升~
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/8102208/viewspace-667564/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/8102208/viewspace-667564/