您现在的位置是：网站首页> 编程资料编程资料
Oracle CBO几种基本的查询转换详解_oracle_
2023-05-27
412人已围观
简介 Oracle CBO几种基本的查询转换详解_oracle_
在执行计划的开发过程中，转换和选择有这个不同的任务；实际上，在一个查询进行完语法和权限检查后，首先发生通称为“查询转换”的步骤，这里会进行一系列查询块的转换，然后才是“优选”（优化器为了决定最终的执行计划而为不同的计划计算成本从而选择最终的执行计划）。
我们知道查询块是以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 |
----------------------------------------------------------------------------------
二、子查询解嵌套
最典型的就是子查询转变为表连接了，它和视图合并的主要区别就在于它的子查询位于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, last_name, salary, 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| D
相关内容
点击排行
本栏推荐
