Oracle CBO几种基本的查询转换详解

Oracle 前端之家
前端之家收集整理的这篇文章主要介绍了Oracle CBO几种基本的查询转换详解，前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。

在执行计划的开发过程中，转换和选择有这个不同的任务；实际上，在一个查询进行完语法和权限检查后，首先发生通称为“查询转换”的步骤，这里会进行一系列查询块的转换，然后才是“优选”（优化器为了决定最终的执行计划而为不同的计划计算成本从而选择最终的执行计划）。

我们知道查询块是以SELECT关键字区分的，查询的书写方式决定了查询块之间的关系，各个查询块通常都是嵌在另一个查询块中或者以某种方式与其相联结；例如：

代码如下:
select * from employees where department_id in (select department_id from departments)

就是嵌套的查询块，不过它们的目的都是去探索如果改变查询写法会不会提供更好的查询计划。

这种查询转换的步骤对于执行用户可以说是完全透明的，要知道转换器可能会在不改变查询结果集的情况下完全改写你的sql语句结构，因此我们有必要重新评估自己的查询语句的心理预期，尽管这种转换通常来说都是好事，为了获得更好更高效的执行计划。

我们现在来讨论一下几种基本的转换：

1.视图合并
2.子查询解嵌套
3.谓语前推
4.物化视图查询重写

一、视图合并

这种方式比较容易理解，它会将内嵌的视图展开成一个独立处理的查询块，或者将其与查询剩余部分合并成一个总的执行计划，转换后的语句基本上不包含视图了。

视图合并通常发生在当外部查询块的谓语包括：

1，能够在另一个查询块的索引中使用的列
2，能够在另一个查询块的分区截断中所使用的列
3，在一个联结视图能够限制返回行数的条件

在这种查询器的转换下，视图并不总会有自己的子查询计划，它会被预先分析并通常情况下与查询的其他部分合并以获得性能的提升，如下例。

代码如下:
sql> set autotrace traceonly explain
-- 进行视图合并
sql> select * from EMPLOYEES a,
2 (select DEPARTMENT_ID from EMPLOYEES) b_view
3 where a.DEPARTMENT_ID = b_view.DEPARTMENT_ID(+)
4 and a.SALARY > 3000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1634680537

----------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3161 | 222K| 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 3161 | 222K| 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMPLOYEES | 103 | 7107 | 3 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 31 | 93 | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------

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

2 - filter("A"."SALARY">3000)
3 - access("A"."DEPARTMENT_ID"="DEPARTMENT_ID"(+))

-- 使用NO_MERGE防止视图被重写
sql> select * from EMPLOYEES a,
2 (select /*+ NO_MERGE */DEPARTMENT_ID from EMPLOYEES) b_view
3 where a.DEPARTMENT_ID = b_view.DEPARTMENT_ID(+)
4 and a.SALARY > 3000;

Execution Plan
----------------------------------------------------------
Plan hash value: 1526679670

-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3161 | 253K| 7 (15)| 00:00:01 |
|* 1 | HASH JOIN RIGHT OUTER| | 3161 | 253K| 7 (15)| 00:00:01 |
| 2 | VIEW | | 107 | 1391 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | EMPLOYEES | 103 | 7107 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------

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

1 - access("A"."DEPARTMENT_ID"="B_VIEW"."DEPARTMENT_ID"(+))
4 - filter("A"."SALARY">3000)

出于某些情况，视图合并会被禁止或限制，如果在一个查询块中使用了分析函数，聚合函数，，集合运算（如union，intersect，minux），order by子句，以及rownum中的任何一种，这种情况都会发生；尽管如此，我们仍然可以使用/*+ MERGE(v) */提示来强制使用视图合并，不过前提一定要保证返回的结果集是一致的！！！如下例：

代码如下:
sql> set autotrace on
-- 使用聚合函数avg导致视图合并失效
sql> SELECT e1.last_name,e1.salary,v.avg_salary
2 FROM hr.employees e1,
3 (SELECT department_id,avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

Execution Plan
----------------------------------------------------------
Plan hash value: 2695105989

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 697 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 697 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 1605 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
filter("E1"."SALARY">"V"."AVG_SALARY")

--使用/*+ MERGE(v) */强制进行视图合并
sql> SELECT /*+ MERGE(v) */ e1.last_name,avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id AND e1.salary > v.avg_salary;

Execution Plan
----------------------------------------------------------
Plan hash value: 3553954154

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 165 | 5610 | 8 (25)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | HASH GROUP BY | | 165 | 5610 | 8 (25)| 00:00:01 |
|* 3 | HASH JOIN | | 3296 | 109K| 7 (15)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 2889 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

二、子查询解嵌套

最典型的就是子查询转变为表连接了，它和视图合并的主要区别就在于它的子查询位于where子句，由转换器进行解嵌套的检测。

下面便是一个子查询==>表连接的例子：

代码如下:
sql> select employee_id,last_name,salary,department_id
2 from hr.employees
3 where department_id in
4 (select department_id
5 from hr.departments where location_id > 1700);

Execution Plan
----------------------------------------------------------
Plan hash value: 432925905

---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 34 | 884 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 34 | 884 | 4 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 4 | 28 | 2 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | DEPT_LOCATION_IX | 4 | | 1 (0)| 00:00:01 |
|* 5 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 10 | | 0 (0)| 00:00:01 |
| 6 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 10 | 190 | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

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

4 - access("LOCATION_ID">1700)
5 - access("DEPARTMENT_ID"="DEPARTMENT_ID")

-- 使用/*+ NO_UNNEST */强制为子查询单独生成执行计划
sql> select employee_id,department_id
2 from hr.employees
3 where department_id in
4 (select /*+ NO_UNNEST */department_id
5 from hr.departments where location_id > 1700);

Execution Plan
----------------------------------------------------------
Plan hash value: 4233807898

--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 10 | 190 | 14 (0)| 00:00:01 |
|* 1 | FILTER | | | | | |
| 2 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID| DEPARTMENTS | 1 | 7 | 1 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | DEPT_ID_PK | 1 | | 0 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

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

1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "HR"."DEPARTMENTS"
"DEPARTMENTS" WHERE "DEPARTMENT_ID"=:B1 AND "LOCATION_ID">1700))
3 - filter("LOCATION_ID">1700)
4 - access("DEPARTMENT_ID"=:B1)


可以看到没有执行子查询解嵌套的查询只使用了FILTER来进行两张表的匹配，谓语信息第一步的查询也没有丝毫的改动，这便意味着对于EMPLOYEES表中返回的107行的每一行，都需要执行一次子查询。虽然在oracle中存在子查询缓存的优化，我们无法判断这两种计划的优劣，不过相比NESTED LOOPS，FILTER运算的劣势是很明显的。

如果包含相关子查询，解嵌套过程一般会将相关子查询转换成一个非嵌套视图，然后与主查询中的表x相联结，如：

代码如下:
sql> select outer.employee_id,outer.last_name,outer.salary,outer.department_id
2 from hr.employees outer
3 where outer.salary >
4 (select avg(inner.salary)
5 from hr.employees inner
6 where inner.department_id = outer.department_id);

Execution Plan
----------------------------------------------------------
Plan hash value: 2167610409

----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 17 | 765 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 17 | 765 | 8 (25)| 00:00:01 |
| 2 | VIEW | VW_SQ_1 | 11 | 286 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMPLOYEES | 107 | 2033 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------

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

1 - access("ITEM_1"="OUTER"."DEPARTMENT_ID")
filter("OUTER"."SALARY">"AVG(INNER.SALARY)")

上面的查询是将子查询转换成视图在与主查询进行hash join，转换后的查询其实像这样：

代码如下:
sql> select outer.employee_id,outer.department_id
2 from hr.employees outer,
3 (select department_id,avg(salary) avg_sal from hr.employees group by department_id) inner
4 where inner.department_id = outer.department_id and outer.salary > inner.avg_sal;

其实这两个语句的执行计划也是一致

三、谓语前推

将谓词从内部查询块推进到一个不可合并的查询块中，这样可以使得谓词条件更早的被选择，更早的过滤掉不需要的数据行，提高效率，同样可以使用这种方式允许某些索引的使用。

代码如下:
-- 谓语前推示例
sql> set autotrace traceonly explain
sql> SELECT e1.last_name,avg(salary) avg_salary
4 FROM hr.employees e2
5 GROUP BY department_id) v
6 WHERE e1.department_id = v.department_id
7 AND e1.salary > v.avg_salary
8 AND e1.department_id = 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 3521487559

-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 41 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | | | | |
| 2 | NESTED LOOPS | | 1 | 41 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | HASH GROUP BY | | 1 | 7 | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 35 | 2 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
|* 7 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 0 (0)| 00:00:01 |
|* 8 | TABLE ACCESS BY INDEX ROWID | EMPLOYEES | 1 | 15 | 1 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------

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

6 - access("DEPARTMENT_ID"=60)
7 - access("E1"."DEPARTMENT_ID"=60)
8 - filter("E1"."SALARY">"V"."AVG_SALARY")

-- 不进行谓语前推
sql> SELECT e1.last_name,avg(salary) avg_salary
4 FROM hr.employees e2
5 WHERE rownum > 1 -- rownum等于同时使用了no_merge和no_push_pred提示，这会同时禁用视图合并和谓语前推
6 GROUP BY department_id) v
7 WHERE e1.department_id = v.department_id
8 AND e1.salary > v.avg_salary
9 AND e1.department_id = 60;

Execution Plan
----------------------------------------------------------
Plan hash value: 3834222907

--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 7 (29)| 00:00:01 |
|* 1 | HASH JOIN | | 3 | 123 | 7 (29)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMPLOYEES | 5 | 75 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | EMP_DEPARTMENT_IX | 5 | | 1 (0)| 00:00:01 |
|* 4 | VIEW | | 11 | 286 | 4 (25)| 00:00:01 |
| 5 | HASH GROUP BY | | 11 | 77 | 4 (25)| 00:00:01 |
| 6 | COUNT | | | | | |
|* 7 | FILTER | | | | | |
| 8 | TABLE ACCESS FULL | EMPLOYEES | 107 | 749 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------

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

1 - access("E1"."DEPARTMENT_ID"="V"."DEPARTMENT_ID")
filter("E1"."SALARY">"V"."AVG_SALARY")
3 - access("E1"."DEPARTMENT_ID"=60)
4 - filter("V"."DEPARTMENT_ID"=60)
7 - filter(ROWNUM>1)

比较上面的两个查询可以看到，在第一个查询中，DEPARTMENT_ID=60谓词被推进到视图v中执行了，这样就使得内部视图查询只需要获得部门号为60的平均薪水就可以了；而在第二个查询中则需要计算每个部门的平均薪水，然后在与外部查询联结的时候使用DEPARTMENT_ID=60条件过滤，相对而言这里为了等待应用谓词条件，查询做了更多的工作。

四、使用物化视图进行查询重写

当为物化视图开启查询重写功能时，CBO优化器会评估相应查询对基表与物化视图的访问成本，如果优化器认为该查询结果从物化视图中获得会更高效，那么就会其自动选择为物化视图来执行，否则则对基表生成查询计划。

还是来看栗子：

代码如下:
sql> set autotrace traceonly explain
sql> select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 1192169904

--------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 33 | 4 (25)| 00:00:01 |
| 1 | HASH GROUP BY | | 11 | 33 | 4 (25)| 00:00:01 |
| 2 | TABLE ACCESS FULL| EMPLOYEES | 107 | 321 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------

-- 创建物化视图日志
sql> create materialized view log on EMPLOYEES with sequence,
2 rowid (EMPLOYEE_ID,DEPARTMENT_ID) including new values;

Materialized view log created.

-- 创建物化视图，并指定查询重写功能
sql> create materialized view mv_t
2 build immediate refresh fast on commit
3 enable query rewrite as
4 select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;

Materialized view created.

sql> select DEPARTMENT_ID,count(EMPLOYEE_ID) from EMPLOYEES group by DEPARTMENT_ID;

Execution Plan
----------------------------------------------------------
Plan hash value: 1712400360

-------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%cpu)| Time |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 12 | 312 | 3 (0)| 00:00:01 |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MV_T | 12 | 312 | 3 (0)| 00:00:01 |
-------------------------------------------------------------------------------------

Note
-----
- dynamic sampling used for this statement (level=2)

可以看到在第二个查询中，虽然是指定的查询EMPLOYEES表，但是优化器自动选择了物化视图的执行路径，因为它判断出物化视图已经记载当前查询需要的结果集数据了，直接访问物化视图会获得更高的效率。

值得注意的是，这里的物化视图查询重写是自动发生的，同样也可以使用/*+ rewrite(mv_t) */提示的方式强制发生查询重写。

总结：

尽管优化器在用户透明的情况下改写了我们的查询结构，不过通常情况下这都是基于CBO优化模式下其判断较为高效的选择，这也是我们所期望的，同时为我们提供了一种学习方法，即在写sql语句的过程中时刻考虑优化器的作用。

原文链接：https://www.f2er.com/oracle/65326.html
CBO查询转换

猜你在找的Oracle相关文章

[Oracle]记一次由sequence引发的enq sv-contention等待事件
数据库版本：11.2.0.4 RAC(1)问题现象从EM里面可以看到，在23号早上8：45~8：55时，数据库...
oracle统计信息的锁定与解锁
（一）问题背景最近在对一个大约200万行数据的表查看执行计划时，发现存在异常，理论上应该...
【转】RMAN删除过期备份或非过期备份
（一）删除备份--DELETE命令用于删除RMAN备份记录及相应的物理文件。当使用RMAN执行备份操...
数据恢复顾问（DRA）
（1）DRA介绍 数据恢复顾问（Data Recovery Advise）是一个诊断和修复数据库的工具，DRA能...
RMAN备份与恢复（一）--认识RMAN
RMAN（Recovery Manager）是Oracle恢复管理器的简称，是集数据库备份（backup）、修复（re...
RMAN备份与恢复（二）--RMAN常用操作学习
（1）连接目标数据库 在RMAN中可以建立与目标数据库或恢复目录数据库的连接。与目标数据库...
ORA-00245问题总结
（1）问题描述 在进行数据库归档备份时（备份归档日志文件和控制文件），有时成功，有时失...
使用RMAN对数据文件进行恢复
（1）备份数据库 在使用RMAN进行数据库恢复之前，先用RMAN进行全库备份 （2）删除数据文件...
Oracle使用fy_recover_data恢复truncate删除的数据
(一)truncate操作概述在生产中，truncate是使用较多的命令，在使用不当的情况下，往往会造...
备份与恢复（四）--RMAN使用恢复目录catalog来保存档案资料库
（一）恢复目录概述RMAN档案资料库用于存储数据库备份、修复以及恢复所需的信息。这些信息...
MySQLMsSQLOracleSqlitePostgre SQLMariadbMongoDBNoSQLHBaseJDBC
undo日志persistent-cmysql-error-postal-codesql-match-almysql-5.6mysql-8.0database-tri安装路径系统错误data_dir丢失文件主从同步sql_mode数据库目录匿名用户character_seID归零数据库位置查询表重复字段查询字段截断日志SUSPECT7391Remote ServeLinked Serve玄学问题登录不上开启远程访问