Oracle SQL谓词推进、视图合并

来源：互联网发布：图形旋转矩阵编辑：程序博客网时间：2024/05/16 01:42

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 ProductionWith the Partitioning, OLAP, Data Mining and Real Application Testing optionsHR@sean> desc employees Name                                      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 DATE JOB_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                         124        199 Douglas              Grant                            124        200 Jennifer             Whalen                           101        201 Michael              Hartstein                        100        202 Pat                  Fay                              201        203 Susan                Mavris                           101        204 Hermann              Baer                             101        205 Shelley              Higgins                          101        206 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_IDEMP_NAME_IX          EMPLOYEES                      LAST_NAMEEMP_NAME_IX          EMPLOYEES                      FIRST_NAMEEMP_MANAGER_IX       EMPLOYEES                      MANAGER_IDEMP_EMP_ID_PK        EMPLOYEES                      EMPLOYEE_IDEMP_EMAIL_UK         EMPLOYEES                      EMAILEMP_JOB_IX           EMPLOYEES                      JOB_ID7 rows selected.

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

HR@sean> set autotrace traceonly expHR@sean> select /*+ no_merge(v) no_push_pred(v) */      *       from employees e, (select manager_id from employees) v      where 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) v      where 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) v  4        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) v      where 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

阅读全文

1 0