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

67次阅读
没有评论

共计 9045 个字符,预计需要花费 23 分钟才能阅读完成。

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

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

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

正文完
 
丸趣
版权声明:本站原创文章,由 丸趣 2023-07-17发表,共计9045字。
转载说明:除特殊说明外本站除技术相关以外文章皆由网络搜集发布,转载请注明出处。
评论(没有评论)