Oracle的查询优化器在评估sql的执行计划成本之前,会对有些sql做等价改写,这个等价指的是语义上是等价的,但是sql结构发生变化。常见的改写包括:
谓词推进(predicate push)
查询重写(query rewrite)
谓词推进
PUSH_PRED
NO_PUSH_PRED
视图合并
MERGE
NO_STAR_TRANSFORMATION
查询重写
REWRITE
这是我们最直观能理解的执行计划,
v是一个内联视图,e是一个表。
第1步:
e表的列employee_id列上有索引,所以从执行计划的行2-3可以看出是通过索引定位到数据。得到 结果集1
第2步:
v这个内联视图的数据通过对表EMPLOYEES进行一次全表扫描获取。执行计划行4-5。得到 结果集2
第3步:
结果集1 和 结果集2 进行循环嵌套连接,得到最终结果
视图合并指的是,在与视图做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 进行循环嵌套连接,得到最终结果
谓词推进指的是视图在与表等做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 进行循环嵌套连接,得到最终结果
从第2、3两种情况我们得知,不管是谓词推进或是视图合并,都会让视图v不走全表扫描,
视图合并会发生EMP_MANAGER_IX索引的 FULL SCAN
谓词推进会发生EMP_MANAGER_IX索引的 RANGE SCAN
谓词推进(predicate push)
视图合并(view merge)
子查询展开(subquery unnest)
查询重写(query rewrite)
这次我们来看下其中最常见的两种:谓词推进和视图合并。
谓词推进
PUSH_PRED
NO_PUSH_PRED
视图合并
MERGE
NO_MERGE
子查询展开
UNNEST
NO_UNNEST
星型转换
STAR_TRANSFORMATIONNO_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