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

网友投稿 382 2023-12-14

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

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

如何理解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

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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

-----------------------------------------------------------------------------------------

-----------------------------------------------------------------------------------------

| 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

-------------------------------------------------------------------------------------

-------------------------------------------------------------------------------------

| 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

------------------------------------------------------------------------------------

------------------------------------------------------------------------------------

| 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

--------------------------------------------------------------------------------

--------------------------------------------------------------------------------

| 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;

关于如何理解SQL优化中连接谓词推入就分享到这里了，希望以上内容可以对大家有一定的帮助，可以学到更多知识。如果觉得文章不错，可以把它分享出去让更多的人看到。

标签：优化链接数据方法

暂时没有评论，来抢沙发吧~

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

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

企业如何通过vue小程序开发满足高效运营与合规性需求

react 前端框架如何驱动企业数字化转型与创新发展

微前端架构如何改变企业的开发模式与效率提升

最近发表

更多内容

小程序SDK

Finclip技术文档

小程序开发

小程序容器

小程序框架

Finclip小程序平台

Finclip用户投稿

车联网

推荐文章

小程序SDK是什么意思？小程序sdk和插件有什么区别？

小程序支付功能怎么实现？

企业app开发流程是什么？

app运营模式有哪些？

小程序多端引流怎么做？

小程序生态分析的机会和威胁

Flutter入门这一篇效率文章就够了

原生与跨平台解决方案分析,跨平台软件开发技术方案

热更新技术：让软件更新变得更加轻松快速

解决方案

银行解决方案

证券解决方案

互联网解决方案

政企OA解决方案

科技解决方案

loT解决方案

信任解决方案

热评文章

AppCan:基于混合模式的移动应用开发,移动混合模

Hybrid App混合模式开发的了解

小程序容器技术助力券商数字营销突围，小程序容器化的意

用mpvue开发微信小程序基础知识（vue.js开发

小程序多端框架全面测评对比，强烈推荐！

券商app架构 - 解析券商应用程序的构建与设计