Oracle里的查询转换

共计 15911 个字符，预计需要花费 40 分钟才能阅读完成。

Oracle 里的查询转换，有称为查询改写，指 oracle 在执行目标 sql 时可能会做等价改写，目的是为了更高效的执行目标 sql 在10g 及其以后的版本中，oracle 会对某些类型的查询转换（比如子查询展开、复杂视图合并等）计算成本，oracle 会分别计算查询转换后的等价改写的 sql 的成本和原始 sql 的成本，如果改写后的 sql 的成本低于原始 sql 的成本，oracle 才会对目标 sql 执行查询转换。

指优化器不在将子查询当做一个独立的单元来处理，而是将该子查询转为它自身和外部查询之间的等价的表连接，将子查询拆开，

将子查询中的表、视图从子查询中拿出来，然后后外部查询的表、视图做连接，或者会把该子查询转换为一个 内嵌视图 （inline view）然后再和外部查询的表、视图做连接（ 子查询展开）

Oracle 子查询前的 where 条件是以下

single-row(=,>,<,<=,>=,<>)

exists

not exists

not in

any all

SQL> select t1.col1,t1.col2 from t1 where t1.col2 in(select col2 from t2 where t2.col3='B1');
Elapsed: 00:00:00.36
Execution Plan
----------------------------------------------------------
Plan hash value: 2894408441
---------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    10 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |        |     1 |    10 |     4   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | T1     |     3 |    15 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |     5 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T2 |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   3 - filter("T2"."COL3"='B1')
   4 - access("T1"."COL2"="COL2")
转换为对应的半连接，找到满足的记录，马上停止扫描

SQL> select t1.col1,t1.col2 from t1 where t1.col2 in(select /*+ no_unnest */ col2 from t2 where t2.c
ol3='B1');
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3615181094
---------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |     5 |     6   (0)| 00:00:01 |
|*  1 |  FILTER                      |        |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | T1     |     3 |    15 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| T2     |     1 |     5 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | IDX_T2 |     1 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "T2" "T2" WHERE
              "COL2"=:B1 AND "T2"."COL3"='B1'))
   3 - filter("T2"."COL3"='B1')
   4 - access("COL2"=:B1)
不能展开的子查询通常是目标 sql 的执行计划的最后一步才会被执行，并且会走 filter
SQL> select tab1.col1,tab1.col2 from tab1 where tab1.col2  not in(select col2 from tab2 where tab2.col3='B1' and tab1.col2=tab2.col2);
7 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3181642956
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |    64 |    15   (0)| 00:00:01 |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| TAB1 |    10 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TAB2 |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NOT EXISTS (SELECT /*+ */ 0 FROM "TAB2" "TAB2" WHERE
              "TAB2"."COL3"='B1' AND "TAB2"."COL2"=:B1 AND LNNVL("COL2"<>:B2)))
   3 - filter("TAB2"."COL3"='B1' AND "TAB2"."COL2"=:B1 AND
              LNNVL("COL2"<>:B2))

SQL> select tab1.col1,tab1.col2 from tab1 where not exists(select 1 from tab2 where tab2.col3='B1' and tab1.col2=tab2.col2);
Execution Plan
----------------------------------------------------------
Plan hash value: 949155623
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     8 |   112 |     7  (15)| 00:00:01 |
|*  1 |  HASH JOIN ANTI    |      |     8 |   112 |     7  (15)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| TAB1 |    10 |    80 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| TAB2 |     1 |     6 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("TAB1"."COL2"="TAB2"."COL2")
   3 - filter("TAB2"."COL3"='B1')

SQL>  select area_id,area_name,country_code,iso_code
  2        from provider_area where area_id in (3          select distinct area_id from PROVIDER_PREFIX p
  4           where exists(select 1 from IDD_Channel i where 
  5               p.area_code = SUBSTR (I.Virtual_No, 1, LENGTH (p.area_code))
  6               AND I.Status = 'IDLE'
  7                 AND I.DDI_Owner = 'GLOBALROAM'
  8             AND I.Reserved_Provider_ID = 'GLOBALROAM'
  9          AND I.Provider_ID = 'GLOBALROAM'))
 10           and provider_id= 'GLOBALROAM';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 3848479730
---------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |     1 |    46 |    12   (9)| 00:00:01 |
|   1 |  NESTED LOOPS                |                  |     1 |    46 |    12   (9)| 00:00:01 |
|   2 |   VIEW                       | VW_NSO_1         |     1 |    13 |    10   (0)| 00:00:01 |
|   3 |    HASH UNIQUE               |                  |     1 |    47 |            |          |
|   4 |     NESTED LOOPS             |                  |     1 |    47 |    10   (0)| 00:00:01 |
|*  5 |      TABLE ACCESS FULL       | IDD_CHANNEL      |     1 |    37 |     5   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL       | PROVIDER_PREFIX  |     1 |    10 |     5   (0)| 00:00:01 |
|   7 |   TABLE ACCESS BY INDEX ROWID| PROVIDER_AREA    |     1 |    33 |     1   (0)| 00:00:01 |
|*  8 |    INDEX UNIQUE SCAN         | PROVIDER_AREA_UK |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter("I"."DDI_OWNER"='GLOBALROAM' AND "I"."RESERVED_PROVIDER_ID"='GLOBALROAM'
              AND "I"."PROVIDER_ID"='GLOBALROAM' AND "I"."STATUS"='IDLE')
   6 - filter("P"."AREA_CODE"=SUBSTR("I"."VIRTUAL_NO",1,LENGTH("P"."AREA_CODE")))
   8 - access("PROVIDER_ID"='GLOBALROAM' AND "AREA_ID"="$nso_col_1")

能否做子查询展开取决如下 2 个条件：

1 子查询展开所对应的等价改写 sql 和原 sql 在 语义上一定要是完全等价 的。

2 对��不拆开的子查询会把它转换为一个 内嵌视图的子查询展开。

对于第一种情况，Oracle 10g 及以后的版本中，Oracle 也不会考虑子查询展开的成本。及 Oracle 都会做子查询展开

SQL> select t1.ename,t1.deptno from emp t1 where t1.deptno in (select t2.deptno from dept t2 where t2.loc='hongquan');
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 351108634
---------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     5 |   105 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     5 |   105 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL          | EMP     |    14 |   140 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    11 |     1   (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("T1"."DEPTNO" IS NOT NULL)
   3 - filter("T2"."LOC"='hongquan')
   4 - access("T1"."DEPTNO"="T2"."DEPTNO")
将表 dept，emp 做 nl 方式连接

不将视图作为单独的处理单元，将视图的基表拿出来与外部查询的表合并，不会再有视图的出现

SQL> select emp.ename,emp.empno from
  2           emp, EMP_MGR_VIEW e
  3           where emp.empno=e.empno;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1225002481
---------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     3 |    69 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    12 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |           |     3 |    69 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS FULL        | EMP       |    14 |   154 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN         | INDEX_EMP |     3 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------

   1 - filter("EMP"."EMPNO"="EMPNO")
   4 - access("JOB"='MANAGER')
 2 不进行视图合并/*+ no_merge(e)*/
SQL> select /*+ no_merge(e)*/emp.ename,emp.empno from
  2           emp, EMP_MGR_VIEW e
  3           where emp.empno=e.empno;
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 3750149094
---------------------------------------------------------------------
| Id  | Operation           | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT    |              |     3 |    72 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN          |              |     3 |    72 |     5  (20)| 00:00:01 |
|   2 |   VIEW              | EMP_MGR_VIEW |     3 |    39 |     2   (0)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP          |     3 |    36 |     2   (0)| 00:00:01 |
|   4 |   TABLE ACCESS FULL | EMP          |    14 |   154 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("EMP"."EMPNO"="E"."EMPNO")
   3 - filter("JOB"='MANAGER')
/*+ MERGE(VIEW) */ CARDINALITY(T1 100)
进行简单的视图合并，该视图的视图定义不能包含以下
  集合运算符（union，union all，minus，intersect）connect by 字句
rownum

Outer join view merging，针对那些使用了外连接，以及所带视图的视图定义 sql 语句中 不含 distinct，group by 等聚合函数 的目标sql 的视图合并

外连接视图合并的通用限制，该视图被作为 外连接的驱动表 ，或者该视图定义的sql 语句 中只包含一个表

例

SQL>  select emp.ename,emp.empno from
  2           emp, EMP_MGR_VIEW e
  3           where emp.empno(+)=e.empno;

Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2341341676
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     3 |    69 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN OUTER   |      |     3 |    69 |     5  (20)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |     3 |    36 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   154 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("EMP"."EMPNO"(+)="EMPNO")
   2 - filter("JOB"='MANAGER')
SQL>  select emp.ename,emp.empno from
  2           emp, EMP_MGR_VIEW e
  3           where emp.empno=e.empno(+);

14 rows selected.

Elapsed: 00:00:00.03

Execution Plan
----------------------------------------------------------
Plan hash value: 1264059372

------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |    14 |   322 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER          |           |    14 |   322 |     3   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL          | EMP       |    14 |   154 |     2   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| EMP       |     1 |    12 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | INDEX_EMP |     3 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("EMP"."EMPNO"="EMPNO"(+))
   4 - access("JOB"(+)='MANAGER')

3 复杂视图的合并

复杂视图的合并会推迟group by，distinct 操作，所以不一定能带来效率的提升

Join predicate pushdown，优化处理带视图的另一个优化手段，会把视图当做一个独立的单元，但优化器会把处于该视图外部查询中和该视图的连接条件推入到该 视图的定义 的sql 语句内部，为了能使用上基表的 index

Oracle 仅仅支持如下类型的视图做谓词推入

视图定义sql 语句中包含 union all、union

视图定义sql 包含 distinct

视图定义sql 包含 group by 视图

和外部查询之间的连接类型是外连接

和外部查询之间的连接类型是反连接

和外部查询之间的连接类型是半连接

例

create table emp1 as select * from emp

create table emp2 as select * from emp

create index emp1_idx on emp1(empno)

create index emp2_idx on emp2(empno)

create view emp_view as
select emp1.empno as empno1 from emp1;

create view emp_view_union as
select emp1.empno as empno1 from emp1
union all
select emp2.empno as empno1 from emp2;

SQL> select /*+ no_merge(emp_view)*/ emp.empno
  2  from emp,emp_view
  3  where emp.empno=emp_view.empno1(+)
  4  and emp.ename='FORD';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 1890544608
---------------------------------------------------------------------
| Id  | Operation               | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |     1 |    19 |     3   (0)| 00:00:01 |
|   1 |  NESTED LOOPS OUTER     |          |     1 |    19 |     3   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL     | EMP      |     1 |    15 |     2   (0)| 00:00:01 |
|   3 |   VIEW PUSHED PREDICATE | EMP_VIEW |     1 |     4 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN     | EMP1_IDX |     1 |    26 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("EMP"."ENAME"='FORD')
   4 - access("EMP1"."EMPNO"="EMP"."EMPNO")
Note
-----
   - dynamic sampling used for this statement

2 没有做谓词推入
SQL> select emp.empno
  2  from emp,emp_view_union
  3  where emp.empno=emp_view_union.empno1
  4  and emp.ename='FORD';
Elapsed: 00:00:00.01
Execution Plan
----------------------------------------------------------
Plan hash value: 2081391209
---------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    24 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     1   (0)| 00:00:01 |
|   2 |   NESTED LOOPS              |                |     1 |    24 |     3   (0)| 00:00:01 |
|   3 |    VIEW                     | EMP_VIEW_UNION |    28 |   364 |     2   (0)| 00:00:01 |
|   4 |     UNION-ALL               |                |       |       |            |          |
|   5 |      INDEX FULL SCAN        | EMP1_IDX       |    14 |   182 |     1   (0)| 00:00:01 |
|   6 |      INDEX FULL SCAN        | EMP2_IDX       |    14 |   182 |     1   (0)| 00:00:01 |
|*  7 |    INDEX RANGE SCAN         | EMP_IDX        |     1 |       |     0   (0)| 00:00:01 |
---------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("EMP"."EMPNO"="EMP_VIEW_UNION"."EMPNO1")
   7 - access("EMP"."ENAME"='FORD')
3 做了谓词推入
SQL> select /*+ push_pred(emp_view_union) */emp.empno
  2  from emp,emp_view_union
  3  where emp.empno=emp_view_union.empno1
  4  and emp.ename='FORD';
Elapsed: 00:00:00.03
Execution Plan
----------------------------------------------------------
Plan hash value: 765440681
---------------------------------------------------------------------
| Id  | Operation                     | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |                |     1 |    31 |     4   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                 |                |     1 |    31 |     4   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL           | EMP            |     1 |    23 |     2   (0)| 00:00:01 |
|   3 |   VIEW                        | EMP_VIEW_UNION |     1 |     8 |     2   (0)| 00:00:01 |
|   4 |    UNION ALL PUSHED PREDICATE |                |       |       |            |          |
|*  5 |     INDEX RANGE SCAN          | EMP1_IDX       |     1 |    26 |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN          | EMP2_IDX       |     1 |    26 |     1   (0)| 00:00:01 |
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("EMP"."ENAME"='FORD')
   5 - access("EMP1"."EMPNO"="EMP"."EMPNO")
   6 - access("EMP2"."EMPNO"="EMP"."EMPNO")

11gr2 被引入，优化器处理带 union-all 的目标的 sql 的一种优化手段，把多个 union 的相同结果提取出来为一个结果集，然后再和原 union all 中剩下的部分表做连接

优化器针对分区表的目标sql 的一种优化手段，当指定目标 sql 中的分区表的某个局部分区索引由于某种原因在某些分区上变得不可用（unusable），

oracle 将目标 sql 等级的改写成 按分区 union all 的形式，不可用index union all 可用 index

11g r2引入

对同一个目标sql 而言，oracle 可能会采用不止一种的查询转换手段

是优化器处理带多表连接的目标的sql 的一种优化手段，使用表与表之间通过外键相连的情形，还使用于表与表之间外连接的情况

SQL> select ename from emp,dept
  2  where emp.deptno=dept.deptno;
------ 执行计划没有选择 dept 表，列 deptno 的属性 is not null
该列有一个 fk
14 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |    98 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |    98 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("EMP"."DEPTNO" IS NOT NULL)

优化器在处理带in 字句的 sql 时，会将其转换为 or，2 者等价

优化器在处理带in 的 sql 时，通常会采用以下 4 中方法

Inlist lterator

Inlist expansion

inlist filter

对in 做子查询展开，或者既展开又做视图合并

—– 针对 in 后面是常量集合的一种处理方法

需要注意的地方：1 inlist lterator 是 oracle 针对 in 的首选处理方法

2 来处理 in 的前提条件是 in 所在的 列一定要有index

3 不能让 oracle 强制走 inlist lterator，没有相关的 hint

SQL> select ename,empno from emp where empno in (7521,7566);
Execution Plan
----------------------------------------------------------
Plan hash value: 1899965127
---------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     2 |    16 |     2   (0)| 00:00:01 |
|   1 |  INLIST ITERATOR             |        |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP    |     2 |    16 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | PK_EMP |     2 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access("EMPNO"=7521 OR "EMPNO"=7566)
2 inlist expansion| or expansion
  针对 in 后面是常量集合的另一种处理方法
  把 in 后面的每一个常量都用 union all 来展开，有多少个常量，就会产生 union all 多少次
  改为 union all 后，各个分支就可以走自己的 index，分区裁剪，表连接等相关的执行计划而不互相干扰
create index idx_emp_depto on emp(deptno)

select /*+ use_concat*/* from emp where deptno in (10,20,30);
也还是走的 INLIST ITERATOR