当前位置: 代码迷 >> 综合 >> Oracle SQL谓词推进、视图合并
  详细解决方案

Oracle SQL谓词推进、视图合并

热度:92   发布时间:2024-01-16 02:21:48.0
Oracle的查询优化器在评估sql的执行计划成本之前,会对有些sql做等价改写,这个等价指的是语义上是等价的,但是sql结构发生变化。常见的改写包括:
  谓词推进(predicate push)

  视图合并(view merge)

  子查询展开(subquery unnest)

  星型转换(star transformation)
  查询重写(query rewrite)

这次我们来看下其中最常见的两种:谓词推进和视图合并。

分别对应的hint主要有如下这些:
    谓词推进
        PUSH_PRED
        NO_PUSH_PRED
    视图合并
       MERGE

       NO_MERGE

    子查询展开

       UNNEST

       NO_UNNEST

   星型转换

       STAR_TRANSFORMATION
       NO_STAR_TRANSFORMATION
    查询重写
      REWRITE

      NO_REWRITE


基础数据准备

[oracle@sean ~]$ sqlplus hr/hrSQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 7 14:48:47 2017Copyright (c) 1982, 2013, Oracle.  All rights reserved.Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing optionsHR@sean> desc employeesName                                      Null?    Type----------------------------------------- -------- ----------------------------EMPLOYEE_ID                               NOT NULL NUMBER(6)FIRST_NAME                                         VARCHAR2(20)LAST_NAME                                 NOT NULL VARCHAR2(25)EMAIL                                     NOT NULL VARCHAR2(25)PHONE_NUMBER                                       VARCHAR2(20)HIRE_DATE                                 NOT NULL DATEJOB_ID                                    NOT NULL VARCHAR2(10)SALARY                                             NUMBER(8,2)COMMISSION_PCT                                     NUMBER(2,2)MANAGER_ID                                         NUMBER(6)DEPARTMENT_ID                                      NUMBER(4)HR@sean> select EMPLOYEE_ID,FIRST_NAME,LAST_NAME,MANAGER_ID from employees where rownum<10;EMPLOYEE_ID FIRST_NAME           LAST_NAME                 MANAGER_ID
----------- -------------------- ------------------------- ----------198 Donald               OConnell                         124199 Douglas              Grant                            124200 Jennifer             Whalen                           101201 Michael              Hartstein                        100202 Pat                  Fay                              201203 Susan                Mavris                           101204 Hermann              Baer                             101205 Shelley              Higgins                          101206 William              Gietz                            2059 rows selected.HR@sean> select INDEX_NAME,TABLE_NAME,COLUMN_NAME from user_ind_columns where table_name='EMPLOYEES';INDEX_NAME           TABLE_NAME                     COLUMN_NAME
-------------------- ------------------------------ --------------------
EMP_DEPARTMENT_IX    EMPLOYEES                      DEPARTMENT_ID
EMP_NAME_IX          EMPLOYEES                      LAST_NAME
EMP_NAME_IX          EMPLOYEES                      FIRST_NAME
EMP_MANAGER_IX       EMPLOYEES                      MANAGER_ID
EMP_EMP_ID_PK        EMPLOYEES                      EMPLOYEE_ID
EMP_EMAIL_UK         EMPLOYEES                      EMAIL
EMP_JOB_IX           EMPLOYEES                      JOB_ID7 rows selected.



1. 无视图合并 + 无谓词推进,成本cost=3

HR@sean> set autotrace traceonly exp
HR@sean> select /*+ no_merge(v) no_push_pred(v) */*from employees e, (select manager_id from employees) vwhere e.manager_id = v.manager_id(+)and e.employee_id = 100;Execution Plan
----------------------------------------------------------
Plan hash value: 1940769814----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |               |     6 |   492 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |               |     6 |   492 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |     1 |    69 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   VIEW                       |               |     6 |    78 |     2   (0)| 00:00:01 |
|   5 |    TABLE ACCESS FULL         | EMPLOYEES     |   107 |   428 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("E"."EMPLOYEE_ID"=100)4 - filter("E"."MANAGER_ID"="V"."MANAGER_ID"(+))

这是我们最直观能理解的执行计划,
v是一个内联视图,e是一个表。
第1步:
e表的列employee_id列上有索引,所以从执行计划的行2-3可以看出是通过索引定位到数据。得到 结果集1
第2步:
v这个内联视图的数据通过对表EMPLOYEES进行一次全表扫描获取。执行计划行4-5。得到 结果集2
第3步:
结果集1 和 结果集2 进行循环嵌套连接,得到最终结果



2. 视图合并 + 无谓词推进,成本cost=3

HR@sean> select /*+ no_push_pred(v) use_merge(v) */  2        *  3         from employees e, (select manager_id from employees) v  4        where e.manager_id = v.manager_id(+)  5          and e.employee_id = 100; Execution Plan
----------------------------------------------------------
Plan hash value: 3306627101-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     6 |   438 |     3  (34)| 00:00:01 |
|   1 |  MERGE JOIN OUTER            |                |     6 |   438 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    69 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   FILTER                     |                |       |       |            |          |
|   5 |    INDEX FULL SCAN           | EMP_MANAGER_IX |   107 |   428 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("E"."EMPLOYEE_ID"=100)4 - filter("E"."MANAGER_ID"="MANAGER_ID"(+))

视图合并指的是,在与视图做join的时候,不是先将视图计算出结果,再与其他表或者视图join,而是将视图展开,外部的表等可以直接和视图内部的表发生join的情况。 我们看下这次的执行计划:
第1步:
e表的列employee_id列上有索引,所以从执行计划的行2-3可以看出是通过索引定位到数据。得到 结果集1
第2步:
这次直接对EMPLOYEES表上的索引EMP_MANAGER_IX做了个范围扫描RANGE SCAN,可见优化器发现了视图v的底层表就是EMPLOYEES,而关联字段为manager_id,当然,只需要访问索引就能获取这些信息,得到 结果集2,不需要对表做全表扫描了,可见优化器不再将视图v看做一个黑盒子,而是深入视图内部去寻找更加高效的执行计划,将外部的表和视图进行了合并,融合到了一起。
第3步:
结果集1 和 结果集2 进行循环嵌套连接,得到最终结果



3. 无视图合并 + 谓词推进,成本cost=2

HR@sean> select /*+ no_merge(v) push_pred(v) */*from employees e, (select manager_id from employees) vwhere e.manager_id = v.manager_id(+)and e.employee_id = 100;Execution Plan
----------------------------------------------------------
Plan hash value: 4259233738-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     6 |   492 |     2   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |                |     6 |   492 |     2   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    69 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|   4 |   VIEW PUSHED PREDICATE      |                |     1 |    13 |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | EMP_MANAGER_IX |     6 |    24 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("E"."EMPLOYEE_ID"=100)5 - access("MANAGER_ID"="E"."MANAGER_ID")

谓词推进指的是视图在与表等做join时,比如有1个关联字段,表在这个关联字段上有谓词过滤,那么这个谓词会被传递、推进到视图里面。
我们看下这次的执行计划:
第1步:
e表的列employee_id列上有索引,所以从执行计划的行2-3可以看出是通过索引定位到数据。得到 结果集1
第2步:
我们用no_merge(v)这个hint禁用了视图合并,所以优化器会将视图v当做一个黑盒子,但是会发现即使这样,还是和第1个执行计划不一样,并没有对表EMPLOYEES做全表扫描,而是直接访问EMP_MANAGER_IX索引,而且在索引上了做了谓词过滤,执行计划第5行,这个就是谓词推进的结果,最终得到 结果集2。最明显的区别是,第1次的执行计划,视图v这个黑盒子返回的结果107行数据,可以看执行计划的rows列,而这次是6行。
第3步:
结果集1 和 结果集2 进行循环嵌套连接,得到最终结果


4. 视图合并 + 谓词推进,成本cost=1

HR@sean> select /*+ merge(v) push_pred(v) */2        *3         from employees e, (select manager_id from employees) v4        where e.manager_id = v.manager_id(+)5          and e.employee_id = 100;Execution Plan
----------------------------------------------------------
Plan hash value: 3100588360-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     6 |   438 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |                |     6 |   438 |     1   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    69 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | EMP_MANAGER_IX |     6 |    24 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("E"."EMPLOYEE_ID"=100)4 - access("E"."MANAGER_ID"="MANAGER_ID"(+))



5. 默认情况,成本cost=1

HR@sean> select *from employees e, (select manager_id from employees) vwhere e.manager_id = v.manager_id(+)and e.employee_id = 100;Execution Plan
----------------------------------------------------------
Plan hash value: 3100588360-----------------------------------------------------------------------------------------------
| Id  | Operation                    | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                |     6 |   438 |     1   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |                |     6 |   438 |     1   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    69 |     1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK  |     1 |       |     0   (0)| 00:00:01 |
|*  4 |   INDEX RANGE SCAN           | EMP_MANAGER_IX |     6 |    24 |     0   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------3 - access("E"."EMPLOYEE_ID"=100)4 - access("E"."MANAGER_ID"="MANAGER_ID"(+))

从第2、3两种情况我们得知,不管是谓词推进或是视图合并,都会让视图v不走全表扫描,
视图合并会发生EMP_MANAGER_IX索引的 FULL SCAN
谓词推进会发生EMP_MANAGER_IX索引的 RANGE SCAN