有关如何理解SQL优化中连接谓词推入

来源：恒创科技编辑：恒创科技编辑部

2023-12-24 12:32:59

这篇文章将为大家详细讲解有关如何理解SQL优化中连接谓词推入，文章内容质量较高，因此小编分享给大家做个参考，希望大家阅读完这篇文章后对相关知识有一定的了解。

SQL优化之连接谓词推入：

环境准备：
create table emp1 as select * from emp;
create table emp2 as select * from emp;
create index idx_emp1 on emp1(empno);
create index idx_emp2 on emp2(empno);
create or replace view emp_view as select emp1.empno as empno1 from emp1;
create or replace view emp_view_union as select emp1.empno as empno1 from emp1 union all select emp2.empno as empno1 from emp2;

赋权，scott用户可以开启set autot
grant select on v_$sesstat to scott;
grant select on v_$statname to scott;
grant select on v_$mystat to scott;

sql范例1:
select /*+ no_merge(emp_view) */emp.empno from emp,emp_view where emp.empno=emp_view.empno1(+) and emp.ename='FROD';
可以看到emp表和emp_view视图左外连接，视图是补充表。

查看执行计划：
SQL> set autot traceonly
SQL> set line 250
SQL> select /*+ no_merge(emp_view) */emp.empno from emp,emp_view where emp.empno=emp_view.empno1(+) and emp.ename='FROD';

no rows selected

Execution Plan
----------------------------------------------------------
Plan hash value: 101695337

------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 12 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 1 | 12 | 4 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | EMP_VIEW | 1 | 2 | 1 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | IDX_EMP1 | 1 | 13 | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

2 - filter("EMP"."ENAME"='FROD')
4 - access("EMP1"."EMPNO"="EMP"."EMPNO")

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
7 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

该执行计划比较好理解：步骤2与步骤3同级，但是步骤2没有子ID，所以最先执行步骤2.
步骤2：该步骤有一个filter条件filter("EMP"."ENAME"='FROD')，全表扫描emp表，找出ename=frod的所有数据
步骤4：索引范围扫描，目标条件满足access("EMP1"."EMPNO"="EMP"."EMPNO")，这里把视图和表左外连接的条件推入到了视图中。
步骤3：VIEW PUSHED PREDICATE说明没有做视图合并，把视图当做一个独立单元来执行，但是把外部条件推入到了视图内部
。如果没有做这次连接谓词推入，那么就不会在抓取视图内部数据的时候用到emp1表上的索引，那样的话就会全表扫描了。
步骤1：然后两个结果集做循环嵌套外连接，得到结果。

下面验证一下，连接谓词未推入，抓取视图数据集的时候不会走emp1的索引，而是全表扫描emp1了。
select /*+ no_merge(emp_view) no_push_pred(emp_view) */emp.empno from emp,emp_view where emp.empno=emp_view.empno1(+) and emp.ename='FROD';

Execution Plan
----------------------------------------------------------
Plan hash value: 3053348535

-----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00:00:01 |
| 1 | MERGE JOIN OUTER | | 1 | 23 | 6 (17)| 00:00:01 |
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)| 00:00:01 |
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)| 00:00:01 |
|* 4 | SORT JOIN | | 14 | 182 | 4 (25)| 00:00:01 |
| 5 | VIEW | EMP_VIEW | 14 | 182 | 3 (0)| 00:00:01 |
| 6 | TABLE ACCESS FULL | EMP1 | 14 | 182 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

2 - filter("EMP"."ENAME"='FROD')
4 - access("EMP"."EMPNO"="EMP_VIEW"."EMPNO1"(+))
filter("EMP"."EMPNO"="EMP_VIEW"."EMPNO1"(+))

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
9 consistent gets
1 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

这时可以看到对emp表进行索引全扫描，利用条件"EMP"."ENAME"='FROD'回表，得到数据集；视图并没有走emp1的索引，而是全表扫描，并将结果进行排序，然后与第一个结果集进行排序合并外连接。

范例sql：
select emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD';
Execution Plan
----------------------------------------------------------
Plan hash value: 2223410919

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 24 | 5 (0)|
| 1 | NESTED LOOPS | | 2 | 24 | 5 (0)|
|* 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0)|
| 3 | VIEW | EMP_VIEW_UNION | 1 | 2 | 2 (0)|
| 4 | UNION ALL PUSHED PREDICATE | | | | |
|* 5 | INDEX RANGE SCAN | IDX_EMP1 | 1 | 13 | 1 (0)|
|* 6 | INDEX RANGE SCAN | IDX_EMP2 | 1 | 13 | 1 (0)|
-------------------------------------------------------------------------------------

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

2 - filter("EMP"."ENAME"='FROD')
5 - access("EMP1"."EMPNO"="EMP"."EMPNO")
6 - access("EMP2"."EMPNO"="EMP"."EMPNO")

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
28 recursive calls
0 db block gets
39 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
因为视图定义中有union all，所以EMP_VIEW_UNION不能做视图合并，但是可以做连接谓词推入，所以看到步骤5和步骤6将连接条件推入到了视图内部，从而走了emp1和emp2表的索引。然后将结果集与全表扫描emp表得到的ename=frod的结果集做循环嵌套连接，得到最终结果。
同样地，如果阻止了连接谓词推入，那么视图内部结果集会按照全表扫描。

select /*+ no_push_pred(emp_view_union)*/emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD';

Execution Plan
----------------------------------------------------------
Plan hash value: 894575737
------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 46 | 9 (12)|
| 1 | MERGE JOIN | | 2 | 46 | 9 (12)|
|* 2 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 2 (0)|
| 3 | INDEX FULL SCAN | PK_EMP | 14 | | 1 (0)|
|* 4 | SORT JOIN | | 28 | 364 | 7 (15)|
| 5 | VIEW | EMP_VIEW_UNION | 28 | 364 | 6 (0)|
| 6 | UNION-ALL | | | | |
| 7 | TABLE ACCESS FULL | EMP1 | 14 | 182 | 3 (0)|
| 8 | TABLE ACCESS FULL | EMP2 | 14 | 182 | 3 (0)|
------------------------------------------------------------------------------------

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

2 - filter("EMP"."ENAME"='FROD')
4 - access("EMP"."EMPNO"="EMP_VIEW_UNION"."EMPNO1")
filter("EMP"."EMPNO"="EMP_VIEW_UNION"."EMPNO1")

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
14 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
注意：能否做谓词推入，与视图能否合并，是否是内嵌视图没有关系，与目标视图的类型，与外部查询之间的连接类型以及连接方法有关。
如下是一个无法谓词推入的sql：
原因：视图在外链接的右侧。
select /*+ no_merge(emp_view) use_nl(emp_view) push_pred(emp_view) */emp.empno from emp,emp_view where emp.empno=emp_view.empno1 and ename='FROD';

Execution Plan
----------------------------------------------------------
Plan hash value: 3774177413

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 23 | 6 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0)| 00:00:01 |
|* 3 | VIEW | EMP_VIEW | 1 | 13 | 3 (0)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP1 | 14 | 182 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

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

2 - filter("ENAME"='FROD')
3 - filter("EMP"."EMPNO"="EMP_VIEW"."EMPNO1")

Note
-----
- dynamic sampling used for this statement (level=2)

Statistics
----------------------------------------------------------
11 recursive calls
0 db block gets
14 consistent gets
0 physical reads
0 redo size
333 bytes sent via SQL*Net to client
508 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed

连接谓词推入条件：
视图定义语句中存在union all/union/group by/distinct
视图与外部查询之间是外连接，半连接，反连接
以上只要满足一种条件就可以谓词推入，比如内连接，但是视图定义语句中有union all。
如上面的范例sql：select emp.empno from emp,emp_view_union where emp.empno=emp_view_union.empno1 and emp.ename='FROD';