首页 > 其他 > 详细

关于查询转换的一些简单分析(一)

时间:2014-10-30 12:46:31      阅读:212      评论:0      收藏:0      [点我收藏+]

在sql解析器中,在生成执行计划的时候,会在多个执行计划中选择最优的计划,在这个过程中,查询转换就是一个很重要的过程。
虽然最终的执行结果没有变化,但是从优化器的角度来看,查询转换的结果会更好。
大体来说查询转换有以下几种类型。
视图合并
子查询解嵌套
谓词推进
物化视图查询重写

比如
select *from emp where deptno in (select deptno from dept)
会在查询转换中转换为下面的样子.
select e.* from emp e,dept d where e.deptno=d.deptno;
从查询转换的角度来说,两者的查询效果是基本一致的。

--视图合并
select *
from emp e,
     (select deptno from emp) s_view
where e.deptno=s_view.deptno;

70 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3638257876
---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |    71 |  2911 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN         |      |    71 |  2911 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| EMP  |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| EMP  |    14 |    42 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="DEPTNO")


来比较一下是否视图合并的结果。
select
*
from emp e,
     (select /*+no_merge*/ deptno from emp) s_view
where e.deptno=s_view.deptno

70 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2243607326
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    71 |  3621 |     5  (20)| 00:00:01 |
|*  1 |  HASH JOIN          |      |    71 |  3621 |     5  (20)| 00:00:01 |
|   2 |   TABLE ACCESS FULL | EMP  |    14 |   532 |     2   (0)| 00:00:01 |
|   3 |   VIEW              |      |    14 |   182 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    42 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="S_VIEW"."DEPTNO")


可以比较上面两个查询的时候,会发现第二个执行计划的VIEW关键字表示,执行计划是保持原样的,会单独处理视图。
使用圆括号把子查询括起来不一定在sql执行的时候会保证子查询块会单独执行。
这个时候可以根据要求来选择是否选择视图合并。

视图合并在大体来说性能还是不错的,不过在使用order by, 聚合函数,分析函数,group等等的时候,视图合并的功能还是会受到限制或者无法启用。
而且在试图合并的功能受到限制的时候,如果硬要启用视图合并功能,可能查询的结果会有一些变化,这个时候需要你来判断是否确定要启用视图合并。
select e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000;

12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |    14 |   546 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |      |    14 |   546 |     6  (34)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     3  (34)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
|*  5 |   TABLE ACCESS FULL  | EMP  |    13 |   169 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("E"."DEPTNO"="V"."DEPTNO")
   5 - filter("E"."SAL">1000)

 


select /*+ merge(v)*/ e.ename,e.sal,v.avg_salary
from emp e,
(select deptno,avg(sal) avg_salary
from emp oe
group by deptno)v
where e.deptno=v.deptno and e.sal>1000;

12 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 4115741206
----------------------------------------------------------------------------
| Id  | Operation           | Name | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |    68 |  2176 |     6  (34)| 00:00:01 |
|   1 |  HASH GROUP BY      |      |    68 |  2176 |     6  (34)| 00:00:01 |
|*  2 |   HASH JOIN         |      |    68 |  2176 |     5  (20)| 00:00:01 |
|*  3 |    TABLE ACCESS FULL| EMP  |    13 |   325 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("E"."DEPTNO"="DEPTNO")
   3 - filter("E"."SAL">1000)

 

--子查询解嵌套
子查询嵌套和视图合并的不同之处在于视图合并是在select xxx from的部分,而子查询嵌套式在where xxxx的部分。

select *from emp where deptno in (select deptno from dept)
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |    14 |   532 |     2   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |    14 |   532 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("DEPTNO" IS NOT NULL)

         
先来比较一下是否启用子查询嵌套的区别 ,如果不需要启用子查询嵌套,可以使用Hint no_unnest来引导。
select *from emp where deptno in (select /*+ no_unnest*/deptno from dept)
14 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1783302997
------------------------------------------------------------------------------
| Id  | Operation          | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |         |     5 |   190 |     2   (0)| 00:00:01 |
|*  1 |  FILTER            |         |       |       |            |          |
|   2 |   TABLE ACCESS FULL| EMP     |    14 |   532 |     2   (0)| 00:00:01 |
|*  3 |   INDEX UNIQUE SCAN| PK_DEPT |     1 |     3 |     0   (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "DEPT" "DEPT"
              WHERE "DEPTNO"=:B1))
   3 - access("DEPTNO"=:B1)

可以从谓语部分看出来,两个的执行计划还是有很大的不同,第二个执行计划是未启用子查询解嵌套的形式。会在子查询中走索引,然后通过exists子句来和外部查询的deptno字段连接。
而第一个执行计划直接走了一个全表扫描,对于deptno的部分直接使用条件deptno is not null来过滤了。


select *from emp outer
where outer.sal>(select avg(inner.sal) from emp inner where inner.deptno=outer.deptno)
6 rows selected.


Execution Plan
----------------------------------------------------------
Plan hash value: 1245077725
--------------------------------------------------------------------------------
| Id  | Operation            | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |         |     1 |    64 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |         |     1 |    64 |     6  (34)| 00:00:01 |
|   2 |   VIEW               | VW_SQ_1 |     3 |    78 |     3  (34)| 00:00:01 |
|   3 |    HASH GROUP BY     |         |     3 |    21 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP     |    14 |    98 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP     |    14 |   532 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("ITEM_1"="OUTER"."DEPTNO")
       filter("OUTER"."SAL">"AVG(INNER.SAL)")

这个例子中子查询转换为了一个内嵌式图,然后与外部的查询合并连接,相关列成为了连接条件,子查询的剩余部分成为了内嵌视图。

sql的执行方式和下面的sql类似。
select *from emp outer,
(select inner.deptno,avg(inner.sal) avg_sal from emp inner group by inner.deptno)inner where inner.deptno=outer.deptno
and outer.sal>inner.avg_sal;

6 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      |     1 |    64 |     6  (34)| 00:00:01 |
|*  1 |  HASH JOIN           |      |     1 |    64 |     6  (34)| 00:00:01 |
|   2 |   VIEW               |      |     3 |    78 |     3  (34)| 00:00:01 |
|   3 |    HASH GROUP BY     |      |     3 |    21 |     3  (34)| 00:00:01 |
|   4 |     TABLE ACCESS FULL| EMP  |    14 |    98 |     2   (0)| 00:00:01 |
|   5 |   TABLE ACCESS FULL  | EMP  |    14 |   532 |     2   (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("INNER"."DEPTNO"="OUTER"."DEPTNO")
       filter("OUTER"."SAL">"INNER"."AVG_SAL")

关于谓词推进和物化视图的部分,稍后补充。

关于查询转换的一些简单分析(一)

原文:http://blog.itpub.net/23718752/viewspace-1310272/

(0)
(0)
   
举报
评论 一句话评论(0
关于我们 - 联系我们 - 留言反馈 - 联系我们:wmxa8@hotmail.com
© 2014 bubuko.com 版权所有
打开技术之扣,分享程序人生!