merge 和 push_pred
转自 回帖 http://www.itpub.net/thread-1306866-1-1.html
首先，我们看看 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是否能够带来性能的提升~
push_pred 对应的执行计划operation是 VIEW PUSHED PREDICATE
只有在执行计划里出现了这个operation的时候，oracle才进行了谓词推入,而不是说view内部用到了Join字段的值.
下面这个例子里，网友认为 no_push_pred 没有起作用，其实这里起作用了，因为没有出现VIEW PUSHED PREDICATE.
而计划用来外部的值，进而走了索引，是因为闭包传递的原因.
SELECT /*+ no_push_pred(v) */
v.*
FROM zt$test z,
(SELECT owner,
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
TIMESTAMP,
status,
temporary,
generated,
secondary
FROM big_table1
UNION ALL
SELECT owner,
object_name,
subobject_name,
object_id,
data_object_id,
object_type,
created,
last_ddl_time,
TIMESTAMP,
status,
temporary,
generated,
secondary
FROM big_table2) v
WHERE z.fid = v.object_id(+)
AND z.fid = 39;
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------
Plan hash value: 2324578912
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 190 7 (15)| 00:00:01 |
|* 1 | HASH JOIN OUTER | | 1 | 190 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | ZT$TEST | 1 | 13 2 (0)| 00:00:01 |
| 3 | VIEW | | 2 | 354 4 (0)| 00:00:01 |
| 4 | UNION-ALL | | | | |
| 5 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE1 | 1 | 177 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IX_1 | 1 | 1 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID| BIG_TABLE2 | 1 | 177 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IX_2 | 1 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("Z"."FID"="V"."OBJECT_ID"(+))
2 - filter("Z"."FID"=39)
6 - access("OBJECT_ID"=39)
8 - access("OBJECT_ID"=39)
闭包传递就是为了尽量多的更具等值推算出一些不用join就能拿到的明文信息，进而走出更多的索引路径.
WHERE z.fid = v.object_id(+)
AND z.fid = 39;
等价于
WHERE 39 = v.object_id(+)
AND z.fid = 39;
所以 select * from v where 39 = v.object_id(+) 走了索引是很正常的。
浙公网安备 33010602011771号