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

发布于 2016-03-12 04:41:34 | 158 次阅读 | 评论: 0 | 来源: 网友投递

Oracle关系数据库管理系统

Oracle Database，又名Oracle RDBMS，或简称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, 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: 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 |
----------------------------------------------------------------------------------

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);

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);

SQL> select outer.employee_id, outer.last_name, outer.salary, 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, 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 7 AND e1.salary > v.avg_salary 8 AND e1.department_id = 60;

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

Oracle关系数据库管理系统

后端技术

前端技术

数据库

热门框架

常用IDE

其他