关于merge 和 push_pred的

首先，我们看看 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是否能够带来性能的提升~

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

转载于:http://blog.itpub.net/8102208/viewspace-667564/