Join Predicate Pushdown-连接谓词推入

客户系统发现有一条SQL执行非常慢，但是只查询其中一条记录，查看SQL语句发现开发人员使用了push_pred的Hint，强制推入连接谓词，如下是执行计划：
[img]http://www.orasql.com/bbs/download/file.php?id=146[/img]
连接条件是一个type字段，且where条件中有where a.type=50，显然这个字段的选择性非常差，在推入后表上走了索引跳扫，扫出来的大量结果集再和视图的结果集进行nested loops连接，可以看到本身索引跳扫就消耗了大量资源，且扫出来的9w结果集要做86998*11次嵌套循环，所以该SQL的执行效率极差。
不推入的执行计划如下：
[img]http://www.orasql.com/bbs/download/file.php?id=145[/img]
开发人员刚了解了连接谓词推入的特性，可以提升SQL效率，所以在SQL中加了push_pred的Hint，且在部分案例中得到证实，但是不一定对所以SQL都提升，像这个例子就说明谓词推入大大降低了SQL的性能，更何况如果不推入谓词SQL本身有非常好的筛选条件。

一般情况下连接谓词推入是将原本视图外部的查询中和该视图之间的连接条件推入到该视图SQL的语句内部，这样就能走视图相关基表的索引，最后再基于索引的嵌套循环关联，也就是外部的每条记录都会根据连接条件去扫一遍推入谓词之后的视图，显然当外部结果集过大时即使视图能走索引也不能提升SQL性能，相反可能大大较低SQL的执行效率，所以oracle在解析SQL时会通过计算推入后的COST值是否比不推入谓词的COST低，否则不会推入，不过也可以通过push_pred和no_push_pred的HINT人为控制，也就是一般在连接条件选择性非常好的情况下是能提高SQL性能的。
另外连接谓词推入的前提如下：
UNION ALL/UNION view
Outer-joined view
Anti-joined view
Semi-joined view
DISTINCT view
GROUP-BY view
简单针对是否推入连接谓词做了测试，并对是否推入的执行计划和COST进行对比。
如下是连接谓词推入会提升SQL执行效率的DEMO：
[code]select /*+ push_pred(v) */t.object_name from t_jppd t,V_JPPD v where
t.object_id=v.object_id and t.object_name='EMP'

Plan hash value: 445158766

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 238 (100)| |
| 1 | NESTED LOOPS | | 1 | 81 | 238 (8)| 00:00:03 |
|* 2 | TABLE ACCESS FULL | T_JPPD | 8 | 632 | 158 (2)| 00:00:02 |
| 3 | VIEW | V_JPPD | 1 | 2 | 10 (20)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | 214 | 10 (40)| 00:00:01 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | TABLE ACCESS BY INDEX ROWID | T_JPPD_T1 | 1 | 107 | 6 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | IND_TJPPD_OBJ1 | 208 | | 1 (0)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 | TABLE ACCESS BY INDEX ROWID| T_JPPD_T2 | 1 | 107 | 2 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | IND_TJPPD_OBJ2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T"."OBJECT_NAME"='EMP')
6 - filter("OBJECT_NAME"='EMP')
7 - access("OBJECT_ID"="T"."OBJECT_ID")
8 - filter(51575="T"."OBJECT_ID")
10 - access("OBJECT_ID"=51575)

select /*+ no_push_pred(v) */t.object_name from t_jppd t,V_JPPD v where
t.object_id=v.object_id and t.object_name='EMP'

Plan hash value: 3509995342

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 320 (100)| |
|* 1 | HASH JOIN | | 1 | 92 | 320 (2)| 00:00:04 |
|* 2 | TABLE ACCESS FULL | T_JPPD | 8 | 632 | 158 (2)| 00:00:02 |
| 3 | VIEW | V_JPPD | 9 | 117 | 162 (3)| 00:00:02 |
| 4 | SORT UNIQUE | | 9 | 963 | 162 (4)| 00:00:02 |
| 5 | UNION-ALL | | | | | |
|* 6 | TABLE ACCESS FULL | T_JPPD_T1 | 8 | 856 | 158 (2)| 00:00:02 |
| 7 | TABLE ACCESS BY INDEX ROWID| T_JPPD_T2 | 1 | 107 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IND_TJPPD_OBJ2 | 1 | | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."OBJECT_ID"="V"."OBJECT_ID")
2 - filter("T"."OBJECT_NAME"='EMP')
6 - filter("OBJECT_NAME"='EMP')
8 - access("OBJECT_ID"=51575)[/code]
如下是连接谓词推入会大大降低SQL执行效率的DEMO：
[code]select /*+ push_pred(v) */t.object_name from t_jppd t,V_JPPD v where
t.object_type=v.object_type and t.object_type='TABLE' and v.object_id=51575

Plan hash value: 3599935278

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 9404 (100)| |
| 1 | NESTED LOOPS | | 3080 | 108K| 9404 (33)| 00:01:53 |
|* 2 | TABLE ACCESS FULL | T_JPPD | 1540 | 52360 | 158 (2)| 00:00:02 |
| 3 | VIEW | V_JPPD | 2 | 4 | 6 (34)| 00:00:01 |
| 4 | SORT UNIQUE | | 2 | 90 | 6 (67)| 00:00:01 |
| 5 | UNION ALL PUSHED PREDICATE | | | | | |
|* 6 | FILTER | | | | | |
|* 7 | TABLE ACCESS BY INDEX ROWID| T_JPPD_T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IND_TJPPD_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 9 | FILTER | | | | | |
|* 10 | TABLE ACCESS BY INDEX ROWID| T_JPPD_T2 | 1 | 45 | 2 (0)| 00:00:01 |
|* 11 | INDEX RANGE SCAN | IND_TJPPD_OBJ2 | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

2 - filter("T"."OBJECT_TYPE"='TABLE')
6 - filter('TABLE'="T"."OBJECT_TYPE")
7 - filter(("OBJECT_NAME"='EMP' AND "OBJECT_TYPE"='TABLE'))
8 - access("OBJECT_ID"=51575)
9 - filter('TABLE'="T"."OBJECT_TYPE")
10 - filter("OBJECT_TYPE"='TABLE')
11 - access("OBJECT_ID"=51575)

select /*+ no_push_pred(v) */t.object_name from t_jppd t,V_JPPD v where
t.object_type=v.object_type and t.object_type='TABLE' and v.object_id=51575

Plan hash value: 3916224529

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 165 (100)| |
|* 1 | HASH JOIN | | 3080 | 174K| 165 (4)| 00:00:02 |
| 2 | VIEW | V_JPPD | 2 | 48 | 6 (34)| 00:00:01 |
| 3 | SORT UNIQUE | | 2 | 90 | 6 (67)| 00:00:01 |
| 4 | UNION-ALL | | | | | |
|* 5 | TABLE ACCESS BY INDEX ROWID| T_JPPD_T1 | 1 | 45 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | IND_TJPPD_OBJ1 | 1 | | 1 (0)| 00:00:01 |
|* 7 | TABLE ACCESS BY INDEX ROWID| T_JPPD_T2 | 1 | 45 | 2 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | IND_TJPPD_OBJ2 | 1 | | 1 (0)| 00:00:01 |
|* 9 | TABLE ACCESS FULL | T_JPPD | 1540 | 52360 | 158 (2)| 00:00:02 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - access("T"."OBJECT_TYPE"="V"."OBJECT_TYPE")
5 - filter(("OBJECT_NAME"='EMP' AND "OBJECT_TYPE"='TABLE'))
6 - access("OBJECT_ID"=51575)
7 - filter("OBJECT_TYPE"='TABLE')
8 - access("OBJECT_ID"=51575)
9 - filter("T"."OBJECT_TYPE"='TABLE')[/code]
从上面可以看出连接谓词推入在执行计划中有PUSHED PREDICATE关键字，OUTLINE信息中也会显示，同时从谓词信息可以查看详细的推入情况。
除了特殊情况需要人工调整外不要盲目使用HINT，在统计信息准确的情况下CBO的算法大多数情况下还是准确的，而且这些算法还在不断的优化。
参考文档：
Optimizer Transformation: Join Predicate Pushdown
https://blogs.oracle.com/optimizer/entry/basics_of_join_predicate_pushdown_in_oracle