关于查询转换的一些简单分析(一) (r3笔记第37天)

在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") 关于谓词推进和物化视图的部分，稍后补充。