Oracle的查询优化器在评估sql的执行计划成本之前,会对有些sql做等价改写,这个等价指的是语义上是等价的,但是sql结构发生变化。常见的改写包括:
谓词推进(predicate push)
查询重写(query rewrite)
谓词推进
PUSH_PRED
NO_PUSH_PRED
视图合并
MERGE
NO_STAR_TRANSFORMATION
查询重写
REWRITE
谓词推进(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/hr
SQL*Plus: Release 11.2.0.4.0 Production on Fri Jul 7 14:48:47 2017
Copyright (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 options
HR@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