Oracle查询转换

最新推荐文章于 2021-08-28 23:42:02 发布
置顶 最新推荐文章于 2021-08-28 23:42:02 发布
阅读量470 收藏
点赞数 1
分类专栏： SQL Tuning
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
本文链接：https://blog.csdn.net/a4221722/article/details/50688556
版权

概述

Oracle查询转换器的作用是把原始sql重写为语义相同的语句，目的是为了获得更高效的sql。
查询转换主要有四种技术：子查询展开，视图合并，谓词推入，星型转换。
了解查询转换是掌握SQL优化的基础，本文将对这四种技术做一些简单的介绍。

子查询展开

子查询展开是指优化器不再把子查询作为独立的单元处理，而是转换成等价的join方式。转换有两种方式：一是将子查询的结果集作为视图，与外层表或视图做join；二是将子查询中的表或视图拆出来，与外层表或视图做join。子查询前包含以下条件可以被展开：

  • any(= any和in等价)
  • all(<> all和not in等价)
  • exists
  • not exists
  • single row条件（where后面接=,<,>,<=,>=等条件）
    子查询展开的例子：
select e.empno,e.deptno from emp e where e.deptno in (select d.deptno from dept d where d.loc='CHICAGO');
---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    5 |   235 |    6   (0)| 00:00:01 |
|*  1 |  HASH JOIN       |      |    5 |   235 |    6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| DEPT |    1 |    21 |    3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |   364 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   2 - filter("D"."LOC"='CHICAGO')

最终转换的语句：

SELECT "E"."EMPNO" "EMPNO","E"."DEPTNO" "DEPTNO" FROM "SCOTT"."DEPT" "D","SCOTT"."EMP" "E" WHERE "E"."DEPTNO"="D"."DEPTNO" AND "D"."LOC"='CHICAGO'

可以看到子查询中的dept表被拆出来，与外部查询块的emp表做inner join。可以这样转换的前提是dept表的deptno列是唯一键。如果deptno列不是唯一键，将做semi join（即所谓的半连接）：

SQL> alter table dept drop constraint PK_DEPT CASCADE;

Table altered.

SQL> select e.empno,e.deptno from emp e where e.deptno in (select d.deptno from dept d where d.loc='CHICAGO');

6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 230627304

---------------------------------------------------------------------------
| Id  | Operation       | Name | Rows  | Bytes | Cost (%CPU)| Time      |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    5 |   235 |    6   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI    |      |    5 |   235 |    6   (0)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   364 |    3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS FULL| DEPT |    1 |    21 |    3   (0)| 00:00:01 |
---------------------------------------------------------------------------

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

   1 - access("E"."DEPTNO"="D"."DEPTNO")
   3 - filter("D"."LOC"='CHICAGO')

如果不做子查询展开，就会走filter类型的执行计划，并且子查询放在最后一步执行，作用是对emp全表扫描之后的结果集进行过滤：

select e.empno,e.deptno from emp e where e.deptno in (select  /*+ no_unnest*/ d.deptno from dept d where d.loc='CHICAGO');
----------------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |           |    14 |   364 |     5     (0)| 00:00:01 |
|*  1 |  FILTER              |           |       |       |        |           |
|   2 |   TABLE ACCESS FULL         | EMP     |    14 |   364 |     3     (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPT    |     1 |    21 |     2     (0)| 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PK_DEPT |     1 |       |     1     (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "D" WHERE
          "D"."DEPTNO"=:B1 AND "D"."LOC"='CHICAGO'))
   3 - filter("D"."LOC"='CHICAGO')
   4 - access("D"."DEPTNO"=:B1)

看一个子查询结果集作为内联视图与外层查询块做join的例子：

select e.EMPLOYEE_ID from employees e where e.DEPARTMENT_ID in(select d.department_id from departments d,locations l where d.location_id=l.location_id and l.city='Venice');
-----------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |    39 |   780 |     5   (0)| 00:00:01 |
|*  1 |  HASH JOIN SEMI                 |                   |    39 |   780 |     5   (0)| 00:00:01 |
|   2 |   VIEW                          | index$_join$_001  |   107 |   749 |     2   (0)| 00:00:01 |
|*  3 |    HASH JOIN                    |                   |       |       |            |          |
|   4 |     INDEX FAST FULL SCAN        | EMP_DEPARTMENT_IX |   107 |   749 |     1   (0)| 00:00:01 |
|   5 |     INDEX FAST FULL SCAN        | EMP_EMP_ID_PK     |   107 |   749 |     1   (0)| 00:00:01 |
|   6 |   VIEW                          | VW_NSO_1          |     4 |    52 |     3   (0)| 00:00:01 |
|   7 |    NESTED LOOPS                 |                   |     4 |    76 |     3   (0)| 00:00:01 |
|   8 |     NESTED LOOPS                |                   |     4 |    76 |     3   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID| LOCATIONS         |     1 |    12 |     2   (0)| 00:00:01 |
|* 10 |       INDEX RANGE SCAN          | LOC_CITY_IX       |     1 |       |     1   (0)| 00:00:01 |
|* 11 |      INDEX RANGE SCAN           | DEPT_LOCATION_IX  |     4 |       |     0   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID | DEPARTMENTS       |     4 |    28 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

   1 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")
   3 - access(ROWID=ROWID)
  10 - access("L"."CITY"='Venice')
  11 - access("D"."LOCATION_ID"="L"."LOCATION_ID")

执行计划仍然走了hash join semi，要使得转换是等价的，必须先完成子查询中departments和locations的join，结果集作为内联视图VM_NSO_1，与外层查询块的结果集做join。

下面的执行计划中，子查询的结果被作为nest loop的驱动表，为保证结果集正确，需要对子查询做hash unique去重。

select e.department_id from employees e where e.DEPARTMENT_ID in(select d.department_id from departments d,locations l where d.location_id=l.location_id and l.city='Venice');
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |    39 |   624 |     4  (25)| 00:00:01 |
|   1 |  NESTED LOOPS                    |                   |    39 |   624 |     4  (25)| 00:00:01 |
|   2 |   VIEW                           | VW_NSO_1          |     4 |    52 |     3   (0)| 00:00:01 |
|   3 |    HASH UNIQUE                   |                   |     4 |    76 |            |          |
|   4 |     NESTED LOOPS                 |                   |     4 |    76 |     3   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                |                   |     4 |    76 |     3   (0)| 00:00:01 |
|   6 |       TABLE ACCESS BY INDEX ROWID| LOCATIONS         |     1 |    12 |     2   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | LOC_CITY_IX       |     1 |       |     1   (0)| 00:00:01 |
|*  8 |       INDEX RANGE SCAN           | DEPT_LOCATION_IX  |     4 |       |     0   (0)| 00:00:01 |
|   9 |      TABLE ACCESS BY INDEX ROWID | DEPARTMENTS       |     4 |    28 |     1   (0)| 00:00:01 |
|* 10 |   INDEX RANGE SCAN               | EMP_DEPARTMENT_IX |    10 |    30 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   7 - access("L"."CITY"='Venice')
   8 - access("D"."LOCATION_ID"="L"."LOCATION_ID")
  10 - access("E"."DEPARTMENT_ID"="DEPARTMENT_ID")

如果满足（不限于）下面的条件，子查询展开可能导致转换不等价，因此将不作展开：

  • where后面的连接符为=all或者<> any
  • exists后面的子查询中带有rownum
  • exists后面的子查询中带有having子句，cube子句或者rollup子句

例如下面的例子：

select e.department_id from employees e where e.department_id <>any (select department_id from departments d where d.location_id=1700);
-------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                  |    10 |    30 |    25   (0)| 00:00:01 |
|*  1 |  FILTER                      |                  |       |       |            |          |
|   2 |   TABLE ACCESS FULL          | EMPLOYEES        |   107 |   321 |     3   (0)| 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS      |     2 |    14 |     2   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN          | DEPT_LOCATION_IX |    21 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT 0 FROM "DEPARTMENTS" "D" WHERE "D"."LOCATION_ID"=1700 AND
              "DEPARTMENT_ID"<>:B1))
   3 - filter("DEPARTMENT_ID"<>:B1)
   4 - access("D"."LOCATION_ID"=1700)

视图合并

视图合并是指对包含视图的查询做出转换，使查询只包含基表。视图合并提供了更多的访问路径和join的可能性。也就是说，不做视图合并的执行计划包含在做了视图合并的执行计划中。下面的例子可以帮助理解这句话：

create view emp100 as select * from employees where MANAGER_ID=100;

select employee_id from emp100 where employee_id=120;
---------------------------------------------------------------------------------------------
| Id  | Operation            | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |            |      1 |      8 |      1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |      1 |      8 |      1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN        | EMP_EMP_ID_PK |      1 |        |      0   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - filter("MANAGER_ID"=100)
   2 - access("EMPLOYEE_ID"=120)

如果不做视图合并，执行计划如下：

select /*+ no_merge(a)*/ employee_id from emp100 a where employee_id=120;
----------------------------------------------------------------------------------------------
| Id  | Operation             | Name         | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT         |             |       1 |      13 |       1   (0)| 00:00:01 |
|   1 |  VIEW                 | EMP100         |       1 |      13 |       1   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| EMPLOYEES     |       1 |       8 |       1   (0)| 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | EMP_EMP_ID_PK |       1 |         |       0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

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

   2 - filter("MANAGER_ID"=100)
   3 - access("EMPLOYEE_ID"=120)

可以看到不对emp100做视图合并，执行计划中出现view字样,name列对应的就是视图名emp100。
第二个执行计划除了多了view的一行，访问路径和成本是和第一个相同的。
如果视图定义中包含下列内容，将不能做视图合并：

  • 集合操作符(UNION,UNION ALL,INTERSECT,MINUS)
  • connect by子句
  • rownum伪列
    做这些限制是为了防止视图合并之后得到错误的结果集。
    不能视图合并的例子：
create view emp_r1 as select * from employees where rownum<=10;

select employee_id from emp_r1 where employee_id=102;
-----------------------------------------------------------------------------------
| Id  | Operation      | Name      | Rows  | Bytes | Cost (%CPU)| Time      |
-----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |          |    10 |   130 |    1   (0)| 00:00:01 |
|*  1 |  VIEW          | EMP_R1      |    10 |   130 |    1   (0)| 00:00:01 |
|*  2 |   COUNT STOPKEY   |          |      |      |           |      |
|   3 |    INDEX FULL SCAN| EMP_EMP_ID_PK |    10 |    40 |    1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

   1 - filter("EMPLOYEE_ID"=102)
   2 - filter(ROWNUM<=10)

复杂视图合并

复杂视图合并技术允许对包含gourp by或者distinct的视图做展开。

create or replace view avg_salary_view as select department_id,avg(salary) avg_sal_dept from employees group by department_id;

select d.location_id,v.avg_sal_dept from departments d, avg_salary_view v where d.department_id=v.department_id and d.location_id=2400;
----------------------------------------------------------------------------------------------------
| Id  | Operation               | Name           | Rows  | Bytes | Cost (%CPU)| Time       |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |           |     8 |   208 |     4  (25)| 00:00:01 |
|   1 |  HASH GROUP BY               |           |     8 |   208 |     4  (25)| 00:00:01 |
|   2 |   NESTED LOOPS               |           |    10 |   260 |     3   (0)| 00:00:01 |
|   3 |    NESTED LOOPS            |           |    10 |   260 |     3   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |     1 |    19 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN           | DEPT_LOCATION_IX  |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     INDEX RANGE SCAN           | EMP_DEPARTMENT_IX |    10 |       |     0   (0)| 00:00:01 |
|   7 |    TABLE ACCESS BY INDEX ROWID | EMPLOYEES       |    10 |    70 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   5 - access("D"."LOCATION_ID"=2400)
   6 - access("D"."DEPARTMENT_ID"="DEPARTMENT_ID")

转换之后的sql文本为：
SELECT "D"."LOCATION_ID" "LOCATION_ID",AVG("EMPLOYEES"."SALARY") "AVG_SAL_DEPT" FROM "HR"."DEPARTMENTS" "D",HR."EMPLOYEES" "EMPLOYEES" WHERE "D"."DEPARTMENT_ID"="EMPLOYEES"."DEPARTMENT_ID" AND "D"."LOCATION_ID"=2400 GROUP BY "EMPLOYEES"."DEPARTMENT_ID","D".ROWID,"D"."LOCATION_ID"

_COMPLEX_VIEW_MERGING参数控制是否激活复杂视图合并，在9i之后默认为true，同时受OPTIMIZER_FEATURES_ENABLE参数控制：

SQL> @param
Enter value for nam: _complex_view_merging
old   8:    and a.ksppinm = '&nam'
new   8:    and a.ksppinm = '_complex_view_merging'

Parameter                       KSPPDESC                                                Session_Va Instance_V
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ----------
_complex_view_merging                   enable complex view merging                                        TRUE       TRUE

SQL> alter session set optimizer_features_enable='8.0.0';

Session altered.

SQL> @param
Enter value for nam: _complex_view_merging
old   8:    and a.ksppinm = '&nam'
new   8:    and a.ksppinm = '_complex_view_merging'

Parameter                       KSPPDESC                                                Session_Va Instance_V
-------------------------------------------------- ---------------------------------------------------------------------------------------------------- ---------- ----------
_complex_view_merging                   enable complex view merging                                        FALSE       TRUE

外连接视图合并

使用了外连接的sql中，视图合并需要满足下列条件之一：

  • 视图为外连接的驱动表
  • 视图的定义只包含单表
    下例中视图v1包含两张基表，在做外连接的驱动表时发生视图合并，做被驱动表则没有。
create view v1 as select a.EMPLOYEE_ID,a.JOB_ID,b.JOB_TITLE from employees a,jobs b where a.job_id=b.job_id;

select * from v1,departments d where v1.department_id(+)=d.department_id;
-----------------------------------------------------------------------------------------------
| Id  | Operation            | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |          |   106 |  7738 |     9  (23)| 00:00:01 |
|   1 |  MERGE JOIN OUTER        |          |   106 |  7738 |     9  (23)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID    | DEPARTMENTS |    27 |   567 |     2    (0)| 00:00:01 |
|   3 |    INDEX FULL SCAN        | DEPT_ID_PK  |    27 |       |     1    (0)| 00:00:01 |
|*  4 |   SORT JOIN            |          |   107 |  5564 |     7  (29)| 00:00:01 |
|   5 |    VIEW             | V1          |   107 |  5564 |     6  (17)| 00:00:01 |
|   6 |     MERGE JOIN            |          |   107 |  4601 |     6  (17)| 00:00:01 |
|   7 |      TABLE ACCESS BY INDEX ROWID| JOBS          |    19 |   513 |     2    (0)| 00:00:01 |
|   8 |       INDEX FULL SCAN        | JOB_ID_PK   |    19 |       |     1    (0)| 00:00:01 |
|*  9 |      SORT JOIN            |          |   107 |  1712 |     4  (25)| 00:00:01 |
|  10 |       TABLE ACCESS FULL     | EMPLOYEES   |   107 |  1712 |     3    (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------

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

   4 - access("V1"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")
       filter("V1"."DEPARTMENT_ID"(+)="D"."DEPARTMENT_ID")
   9 - access("A"."JOB_ID"="B"."JOB_ID")
       filter("A"."JOB_ID"="B"."JOB_ID")

select * from v1,departments d where v1.department_id=d.department_id(+);
---------------------------------------------------------------------------------------------
| Id  | Operation              | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |         |    107 |  6848 |      9  (12)| 00:00:01 |
|*  1 |  HASH JOIN OUTER          |         |    107 |  6848 |      9  (12)| 00:00:01 |
|   2 |   MERGE JOIN              |         |    107 |  4601 |      6  (17)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID| JOBS        |     19 |    513 |      2   (0)| 00:00:01 |
|   4 |     INDEX FULL SCAN          | JOB_ID_PK   |     19 |        |      1   (0)| 00:00:01 |
|*  5 |    SORT JOIN              |         |    107 |  1712 |      4  (25)| 00:00:01 |
|   6 |     TABLE ACCESS FULL          | EMPLOYEES   |    107 |  1712 |      3   (0)| 00:00:01 |
|   7 |   TABLE ACCESS FULL          | DEPARTMENTS |     27 |    567 |      3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------

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

   1 - access("A"."DEPARTMENT_ID"="D"."DEPARTMENT_ID"(+))
   5 - access("A"."JOB_ID"="B"."JOB_ID")
       filter("A"."JOB_ID"="B"."JOB_ID")

谓词推入

优化器在处理不能合并的视图时，可以选择将外部查询的谓词推入该视图的查询块，或者将视图中的谓词拉出到主查询。这样更早的处理视图的结果集，有可能会减小后续步骤操作所需的成本。
谓词推入到视图内部的例子：

create or replace view emp13 as select employee_id,department_id from employees where department_id=10 union  select employee_id,department_id from employees where department_id=110;

select employee_id from emp13 where employee_id<205;
----------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                   |     2 |    26 |     6  (34)| 00:00:01 |
|   1 |  VIEW                          | EMP13             |     2 |    26 |     6  (34)| 00:00:01 |
|   2 |   SORT UNIQUE                  |                   |     2 |    14 |     6  (34)| 00:00:01 |
|   3 |    UNION-ALL                   |                   |       |       |            |          |
|*  4 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |     7 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     1   (0)| 00:00:01 |
|*  6 |     TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |     7 |     2   (0)| 00:00:01 |
|*  7 |      INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------

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

   4 - filter("EMPLOYEE_ID"<205)
   5 - access("DEPARTMENT_ID"=10)
   6 - filter("EMPLOYEE_ID"<205)
   7 - access("DEPARTMENT_ID"=110)

注意到执行计划中条件EMPLOYEE_ID<205被推入到视图内部，将两张基表各过滤一次，然后对结果集做union。
两表关联时，连接条件也可以做推入，先来看不做谓词推入的执行计划：

select e.employee_id from emp13 e,departments d where e.department_id=d.department_id and d.manager_id=205;
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |    33 |     9  (23)| 00:00:01 |
|   1 |  NESTED LOOPS                    |                   |     1 |    33 |     9  (23)| 00:00:01 |
|   2 |   NESTED LOOPS                   |                   |     3 |    33 |     9  (23)| 00:00:01 |
|   3 |    VIEW                          | EMP13             |     3 |    78 |     6  (34)| 00:00:01 |
|   4 |     SORT UNIQUE                  |                   |     3 |    21 |     6  (34)| 00:00:01 |
|   5 |      UNION-ALL                   |                   |       |       |            |          |
|   6 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |     7 |     2   (0)| 00:00:01 |
|*  7 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     1   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     2 |    14 |     2   (0)| 00:00:01 |
|*  9 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
|* 10 |    INDEX UNIQUE SCAN             | DEPT_ID_PK        |     1 |       |     0   (0)| 00:00:01 |
|* 11 |   TABLE ACCESS BY INDEX ROWID    | DEPARTMENTS       |     1 |     7 |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   7 - access("DEPARTMENT_ID"=10)
   9 - access("DEPARTMENT_ID"=110)
  10 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")
  11 - filter("D"."MANAGER_ID"=205)

执行计划中emp13作为驱动表与departments表做nest loop，我们使用hint强制发生谓词推入：

select /*+ push_pred(e)*/ e.employee_id from emp13 e,departments d where e.department_id=d.department_id and d.manager_id=205;
------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name              | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                   |     1 |    22 |     9  (23)| 00:00:01 |
|   1 |  NESTED LOOPS                    |                   |     1 |    22 |     9  (23)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL              | DEPARTMENTS       |     1 |     7 |     3   (0)| 00:00:01 |
|   3 |   VIEW                           | EMP13             |     1 |    15 |     6  (34)| 00:00:01 |
|   4 |    SORT UNIQUE                   |                   |     3 |    21 |     6  (34)| 00:00:01 |
|   5 |     UNION ALL PUSHED PREDICATE   |                   |       |       |            |          |
|*  6 |      FILTER                      |                   |       |       |            |          |
|   7 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     1 |     7 |     2   (0)| 00:00:01 |
|*  8 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     1 |       |     1   (0)| 00:00:01 |
|*  9 |      FILTER                      |                   |       |       |            |          |
|  10 |       TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |     2 |    14 |     2   (0)| 00:00:01 |
|* 11 |        INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |     2 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   2 - filter("D"."MANAGER_ID"=205)
   6 - filter("D"."DEPARTMENT_ID"=10)
   8 - access("DEPARTMENT_ID"=10)
   9 - filter("D"."DEPARTMENT_ID"=110)
  11 - access("DEPARTMENT_ID"=110)

可以看到执行计划中出现PUSHED PREDICATE字样，条件e.department_id=d.department_id被转换成等值条件对employees表做过滤。join的谓词推入往往产生nest loop的执行计划（驱动表的每一行驱动被驱动表，来做谓词的过滤）。如果是大数据集的sql，可以使用hint no_push_pred或者设置参数_push_join_predicate为false禁止谓词推入。

星型转换

星型转换为提高星型查询的效率发生，在原有条件基础上会产生新的子查询对事实表做过滤，然后通过对事实表相应连接列的位图索引做位图操作，达到过滤事实表结果集的目的。
是否开启星型转换受参数star_transformation_enabled控制，可以设置为：
true：优化器将考虑基于成本的星型查询转换；
false：禁止星型转换；
temp_disable：优化器将考虑基于成本的星型查询转换，但是转换中不会使用临时表。
首先看一下星型转换的例子：

alter session set star_transformation_enabled=temp_disable;

select s.*,cu.cust_id from sales s,products p,customers cu, channels ch where s.prod_id=p.prod_id and s.cust_id=cu.cust_id and s.channel_id=ch.channel_id and cu.country_id =52789 and p.PROD_NAME='Bounce' and ch.CHANNEL_CLASS='Direct';
---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                       | Name              | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                |                   |  6379 |   604K|  2512   (1)| 00:00:31 |       |       |
|*  1 |  HASH JOIN                      |                   |  6379 |   604K|  2512   (1)| 00:00:31 |       |       |
|*  2 |   TABLE ACCESS FULL             | CUSTOMERS         |  7557 | 75570 |   405   (1)| 00:00:05 |       |       |
|   3 |   VIEW                          | VW_ST_9C0C7742    |  6380 |   542K|  2107   (1)| 00:00:26 |       |       |
|   4 |    NESTED LOOPS                 |                   |  6380 |   330K|  1696   (1)| 00:00:21 |       |       |
|   5 |     PARTITION RANGE ALL         |                   |  6380 |   149K|  1578   (1)| 00:00:19 |     1 |    28 |
|   6 |      BITMAP CONVERSION TO ROWIDS|                   |  6380 |   149K|  1578   (1)| 00:00:19 |       |       |
|   7 |       BITMAP AND                |                   |       |       |            |          |       |       |
|   8 |        BITMAP MERGE             |                   |       |       |            |          |       |       |
|   9 |         BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
|  10 |          BUFFER SORT            |                   |       |       |            |          |       |       |
|* 11 |           TABLE ACCESS FULL     | PRODUCTS          |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|* 12 |          BITMAP INDEX RANGE SCAN| SALES_PROD_BIX    |       |       |            |          |     1 |    28 |
|  13 |        BITMAP MERGE             |                   |       |       |            |          |       |       |
|  14 |         BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
|  15 |          BUFFER SORT            |                   |       |       |            |          |       |       |
|* 16 |           TABLE ACCESS FULL     | CHANNELS          |     2 |    22 |     3   (0)| 00:00:01 |       |       |
|* 17 |          BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX |       |       |            |          |     1 |    28 |
|  18 |        BITMAP MERGE             |                   |       |       |            |          |       |       |
|  19 |         BITMAP KEY ITERATION    |                   |       |       |            |          |       |       |
|  20 |          BUFFER SORT            |                   |       |       |            |          |       |       |
|* 21 |           TABLE ACCESS FULL     | CUSTOMERS         |  7557 | 75570 |   405   (1)| 00:00:05 |       |       |
|* 22 |          BITMAP INDEX RANGE SCAN| SALES_CUST_BIX    |       |       |            |          |     1 |    28 |
|  23 |     TABLE ACCESS BY USER ROWID  | SALES             |     1 |    29 |   530   (1)| 00:00:07 | ROWID | ROWID |
---------------------------------------------------------------------------------------------------------------------

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

   1 - access("ITEM_1"="CU"."CUST_ID")
   2 - filter("CU"."COUNTRY_ID"=52789)
  11 - filter("P"."PROD_NAME"='Bounce')
  12 - access("S"."PROD_ID"="P"."PROD_ID")
  16 - filter("CH"."CHANNEL_CLASS"='Direct')
  17 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  21 - filter("CU"."COUNTRY_ID"=52789)
  22 - access("S"."CUST_ID"="CU"."CUST_ID")

注意到执行计划首先对各个维度表过滤出结果集，然后访问sales表连接列上的索引，做bitmap and操作之后，回表访问数据。事实上整个过程类似于将查询转换为如下等价sql：

select s.*,cu.cust_id from sales s,customers cu where s.cust_id=cu.cust_id and s.prod_id in (select p.prod_id from products p where p.PROD_NAME='Bounce') and s.channel_id in (select ch.channel_id from channels ch where ch.CHANNEL_CLASS='Direct') and cu.country_id =52789;

下面看一下star_transformation_enabled参数设置为true的结果：

alter session set star_transformation_enabled=true;

select s.*,cu.cust_id from sales s,products p,customers cu, channels ch where s.prod_id=p.prod_id and s.cust_id=cu.cust_id and s.channel_id=ch.channel_id and cu.country_id =52789 and p.PROD_NAME='Bounce' and ch.CHANNEL_CLASS='Direct';
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name                      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |                           |  6380 |   573K|  1717   (1)| 00:00:21 |       |       |
|   1 |  TEMP TABLE TRANSFORMATION       |                           |       |       |            |          |       |       |
|   2 |   LOAD AS SELECT                 | SYS_TEMP_0FD9D6601_11F1D1 |       |       |            |          |       |       |
|*  3 |    TABLE ACCESS FULL             | CUSTOMERS                 |  7557 | 75570 |   405   (1)| 00:00:05 |       |       |
|*  4 |   HASH JOIN                      |                           |  6380 |   573K|  1312   (0)| 00:00:16 |       |       |
|   5 |    TABLE ACCESS FULL             | SYS_TEMP_0FD9D6601_11F1D1 |  7557 | 37785 |     5   (0)| 00:00:01 |       |       |
|   6 |    VIEW                          | VW_ST_51173BA3            |  6380 |   542K|  1307   (0)| 00:00:16 |       |       |
|   7 |     NESTED LOOPS                 |                           |  6380 |   330K|  1296   (0)| 00:00:16 |       |       |
|   8 |      PARTITION RANGE ALL         |                           |  6380 |   149K|  1178   (1)| 00:00:15 |     1 |    28 |
|   9 |       BITMAP CONVERSION TO ROWIDS|                           |  6380 |   149K|  1178   (1)| 00:00:15 |       |       |
|  10 |        BITMAP AND                |                           |       |       |            |          |       |       |
|  11 |         BITMAP MERGE             |                           |       |       |            |          |       |       |
|  12 |          BITMAP KEY ITERATION    |                           |       |       |            |          |       |       |
|  13 |           BUFFER SORT            |                           |       |       |            |          |       |       |
|* 14 |            TABLE ACCESS FULL     | PRODUCTS                  |     1 |    30 |     3   (0)| 00:00:01 |       |       |
|* 15 |           BITMAP INDEX RANGE SCAN| SALES_PROD_BIX            |       |       |            |          |     1 |    28 |
|  16 |         BITMAP MERGE             |                           |       |       |            |          |       |       |
|  17 |          BITMAP KEY ITERATION    |                           |       |       |            |          |       |       |
|  18 |           BUFFER SORT            |                           |       |       |            |          |       |       |
|* 19 |            TABLE ACCESS FULL     | CHANNELS                  |     2 |    22 |     3   (0)| 00:00:01 |       |       |
|* 20 |           BITMAP INDEX RANGE SCAN| SALES_CHANNEL_BIX         |       |       |            |          |     1 |    28 |
|  21 |         BITMAP MERGE             |                           |       |       |            |          |       |       |
|  22 |          BITMAP KEY ITERATION    |                           |       |       |            |          |       |       |
|  23 |           BUFFER SORT            |                           |       |       |            |          |       |       |
|  24 |            TABLE ACCESS FULL     | SYS_TEMP_0FD9D6601_11F1D1 |  7557 | 37785 |     5   (0)| 00:00:01 |       |       |
|* 25 |           BITMAP INDEX RANGE SCAN| SALES_CUST_BIX            |       |       |            |          |     1 |    28 |
|  26 |      TABLE ACCESS BY USER ROWID  | SALES                     |     1 |    29 |   130   (0)| 00:00:02 | ROWID | ROWID |
------------------------------------------------------------------------------------------------------------------------------

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

   3 - filter("CU"."COUNTRY_ID"=52789)
   4 - access("ITEM_1"="C0")
  14 - filter("P"."PROD_NAME"='Bounce')
  15 - access("S"."PROD_ID"="P"."PROD_ID")
  19 - filter("CH"."CHANNEL_CLASS"='Direct')
  20 - access("S"."CHANNEL_ID"="CH"."CHANNEL_ID")
  25 - access("S"."CUST_ID"="C0")

注意到执行计划中先对customers根据过滤条件cu.country_id =52789得到临时表SYS_TEMP_0FD9D6601_11F1D1，后续步骤中每次需要访问customers表时则由临时表来替换，这也是为了总共减少所访问的数据量所考虑。
星型转换同样有一些限制条件，本文暂不讨论。

以上是对四类查询转换概念性的描述，对于具体的应用场景中的SQL要具体分析如何利用这些技术。查询转换还有诸如子查询合并，连接因式分解，表扩展，表裁剪，物化视图重写等技术。有机会将再写文章介绍，或者有兴趣的同学自行研究。

DeronQ
关注
  • 1
    点赞
  • 踩
  • 0
    收藏
    觉得还不错? 一键收藏
  • 0
    评论
专栏目录
oracle 到mysql转换工具
01-17
一个可以从oralce转换为mysql语句的客户端，操作简单，按照向导只需要几步即可完成，各位小伙伴记得给个好评哦！
Oracle CBO几种基本的查询转换详解
09-10
在本篇内容中，我们将深入探讨Oracle CBO的几种基本查询转换，这些转换有助于提高查询性能，包括视图合并、子查询解嵌套、谓语前推和物化视图查询重写。 1. 视图合并 视图合并是CBO优化过程中的一个常见操作，它将...
oracle 查询转换
异道的专栏
08-09 1070
1、子查询展开     SINGLE-ROW（=，,=,     不能做子查询展开的通常会在SQL执行计划最后一步才执行，一般是FILTER类型计划，效率很差     IN ，EXISTS， =ANY 可转换为半连接（SEMI JOIN）     NOT IN， NOT EXISTS，     展开2条件：展开后语义完全等价，内嵌视图的子查询必须COST低于原SQL才展开 2
oracle里的查询转换
weixin_30415113的博客
03-22 364
oracle里的查询转换的作用 Oracle里的查询转换，有称为查询改写，指oracle在执行目标sql时可能会做等价改写，目的是为了更高效的执行目标sql 在10g及其以后的版本中，oracle会对某些类型的查询转换（比如子查询展开、复杂视图合并等）计算成本，oracle会分别计算查询转换后的 等价改写的sql的成本和原始sql的成本，如果改写后的sql的成本低于原始sql的成...
Oracle 查询转换 （下）
chao_19的博客
06-07 631
星型转换 连接谓词推入：表扩展：表移除 使用in-List Iterator 使用 in-List Expansion 使用in-list filter 对In做子查询展开，或者即做子查询展开，又做视图合并。
Oracle版本转换工具
11-22
为了解决这个问题，我们有专门的“Oracle版本转换工具”——OracleDmpVerChange版本转换器。 OracleDmpVerChange是一款专为解决Oracle DMP文件在不同版本之间导入时的兼容性问题设计的实用工具。该工具能够修改DMP...
oracle将汉字转换成拼音
06-08
可以将oracle数据库中保存的中文转换成拼音，拼音首字母等
Oracle实现行列转换的方法分析
09-10
本文将深入探讨Oracle中实现行列转换的几种方法。 首先，对于固定列数的行列转换，假设我们有一个学生成绩表，其中包含学生姓名、科目和分数。如果要将每个学生的各科成绩从多行展示转变为一行展示，可以使用Oracle...
Oracle查询变换
csdnklsdm的博客
08-28 251
子查询非嵌套 定义：where中出现in、not in、exists、not exists，CBO尝试将子查询展开消除filter的过程就是子查询非嵌套 为什么要消除filter？因为filter的驱动表一般是固定的，那么执行计划就是固定的，对于DBA来说，这样的sql不可控，随着时间推移系统中sql可能会越来越慢。消除filter的本意是为了使得执行计划可控，并不代表filter的性能就一定差。 如何产生filter？在子查询中使用hint：/*+ no_unnest / 即可，但是很少能通过 hint
2.6 查询转换
oracle_third3的博客
02-02 1660
2.6 查询转换正在更新内容，请稍后
oracle 查询变换器,万能数据库转换工具_新诚软数据转换器 V1.4 免费版
weixin_35715490的博客
04-12 222
您还在为数据在各个数据库之间转换而烦恼吗?《新诚软数据转换器》是一个万能数据库转换工具，这个数据库转换工具可以为Oracle、SQL Server、Access、DB2、InterBase、SQL Anywhere、dBase、MySQL等多种平台数据库之间进行方便的数据转换。新诚软数据转换器具有对多种数据库的统计、查询、自定义报表、排序、过滤、转换、数据字典、SQL向导、命令窗口、事务缓存处理等...
关于mysql或者oracle数据查询取别名（AS）使用转移字符
qq_42777071的博客
01-06 759
关于mysql或者oracle数据查询取别名（AS）使用转移字符 select name AS “()姓名”,school AS 学校,phone 电话 from student WHERE id=1 mysql或者oracle取的别名不用加“ ”双引号也没什么问题，但是如果别名中带有了英文状态下的括号(),查询就会报错。 那么使用转义字符：" String sql = " select stud...
oracle中的!=与＜＞和^=
热门推荐
想到哪记到哪
08-26 1万+
oracle中的!=与<>和^= != 、 <>、^= 三个符号都表示“不等于”的意思，在逻辑上没有本质区别 但是要主义的是三个符号在表达“不等于”含义的同时，隐含一个“不为空 is not null”的前提，所以使用时null会被过滤掉。 例如数据库三条数据 姓名 性别 张三 男 李四 王五 女 姓名<>‘张三’ :可以查出来李四和王五 性别<>‘男’ ：仅可以查出来王五，李四的性别null被默认过滤掉了 但是官方更推荐“&lt
Oracle中的查询转换简介（上）
chao_19的博客
06-06 1701
Oracle查询转换的概念： 子查询展开 视图合并： 简单视图合并 外连接视图合并 复杂视图合并 星型连接转换 连接谓词推入 连接因式分解 表扩展 表移除
oracle查询优化 之查询转换
qq_25798961的博客
12-05 238
查询优化器使用大量的查询转换来产生新的语义相等的SQL语句，根据用于决定是否应用他们的方法，可以分为两种途径 基于启发式的查询转换：是在满足特定条件时应用的，在大多数情况下他们预计都会引出更好地执行计划 基于成本的查询转换：根据成本估算器计算的成本而应用的，他们会引出与原始语句相比成本更低的执行计划 下列介绍了几种查询转换的用例，便于了解在逻辑优化阶段引擎内部都发生了什么 1.计数转换 ...
oracle的complex,Oracle 18c - Complex sql
weixin_32395459的博客
04-05 67
I have a table with following columns:Emp_ID NumberEmp_flag Varchar2(1)Date_1 DateDate_2 Datecreate_date DateNo PK on this table , there are many records with duplicates of Emp_id..What I need to know...
oracle 数值型函数,字符型函数,日期函数,转换函数,to_char,自动类型转换,日期类型转换...
黑色头发
04-09 320
[color=red]数值型函数[/color] abs:求绝对值函数,如:abs(−5) 5 sqrt:求平方根函数,如:sqrt(2) 1.41421356 power:求幂函数,如:power(2,3) 8 cos:求余弦三角函数,如:cos(3.14159) −1 mod:求除法余数,如:mod(1600, 300) 100 ceil:求大于等于某数的最小整数,如:ceil(2...
Oracle行列转换全攻略
"Oracle 行列转换总结" 在Oracle数据库中，进行行列转换是数据分析和报表制作时常见的需求。本文将详细介绍六种不同的转换方法，包括列转行、行转列、多列转换成字符串、多行转换成字符串、字符串转换成多列以及...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值