-- 查询转换
查询转换发生在进行到执行计划的开发过程之前,进行完语法和权限检查之后。优化器为了决定最终的执行计划而为不同的计划计算成本预估之前。
在查询通过了语法和权限检查之后,查询就进入了转换为一系列查询块的转换阶段。
查询块是通过SELECT关键字来定义的。
-- 视图合并常常发生在当外部查询块的谓语包含下列项的时候。
1.能够在另一个查询块的索引中使用的例
2.能够在另一个查询块的分区截断中所使用的例
3.在一个联结视图中能够限制返回行数的条件。
注意:以下情况会阻止视图合并的发生:
1.如果一个查询块包含解析函数或聚合函数
2.集合运算(如UNION、INTERSERCT、MINUS)
3.ORDER BY 子句或者使用了ROWNUM
以上情况可以通过使用MERGE来强制执行视图合并。但是结果集可能会与原来的查询结果不同。
视图合并行为是通过一个隐藏参数_complex_view_merging来控制的。在Oracle9及后续版本中该参数的默认值为TRUE。从Oracle 10版本开始,
转制后的查询将会由优化器进行复查,视图合并以及不合并的查询计划所需成本都会被评估。然后优化器就会选择成本最低的执行计划。
set line 300
set pages 100
set autotrace traceonly
--a. 进行视图合并
select *
from emp a
,( select deptno
from dept ) b
where a.deptno = b.deptno(+)
and a.job = 'CLERK' ;
执行计划
----------------------------------------------------------
Plan hash value: 438843259
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 3 | 123 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."JOB"='CLERK')
3 - access("A"."DEPTNO"="DEPTNO"(+))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
1 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
-- b.没有进行视图合并
select *
from emp a
,( select /*+ no_merge*/deptno
from dept ) b
where a.deptno = b.deptno(+)
and a.job = 'CLERK' ;
执行计划
----------------------------------------------------------
Plan hash value: 1029234253
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 153 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 153 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 3 | 114 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 0 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."JOB"='CLERK')
4 - access("DEPTNO"="A"."DEPTNO")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
-- c.无法进行视图合并
-- ORDER BY
select *
from emp a
,( select deptno
,avg(sal) avg_sal
from emp
group by deptno ) b
where a.deptno = b.deptno
and a.sal >= b.avg_sal ;
执行计划
----------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 64 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."SAL">="B"."AVG_SAL")
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1503 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed
查询转换发生在进行到执行计划的开发过程之前,进行完语法和权限检查之后。优化器为了决定最终的执行计划而为不同的计划计算成本预估之前。
在查询通过了语法和权限检查之后,查询就进入了转换为一系列查询块的转换阶段。
查询块是通过SELECT关键字来定义的。
-- 视图合并常常发生在当外部查询块的谓语包含下列项的时候。
1.能够在另一个查询块的索引中使用的例
2.能够在另一个查询块的分区截断中所使用的例
3.在一个联结视图中能够限制返回行数的条件。
注意:以下情况会阻止视图合并的发生:
1.如果一个查询块包含解析函数或聚合函数
2.集合运算(如UNION、INTERSERCT、MINUS)
3.ORDER BY 子句或者使用了ROWNUM
以上情况可以通过使用MERGE来强制执行视图合并。但是结果集可能会与原来的查询结果不同。
视图合并行为是通过一个隐藏参数_complex_view_merging来控制的。在Oracle9及后续版本中该参数的默认值为TRUE。从Oracle 10版本开始,
转制后的查询将会由优化器进行复查,视图合并以及不合并的查询计划所需成本都会被评估。然后优化器就会选择成本最低的执行计划。
set line 300
set pages 100
set autotrace traceonly
--a. 进行视图合并
select *
from emp a
,( select deptno
from dept ) b
where a.deptno = b.deptno(+)
and a.job = 'CLERK' ;
执行计划
----------------------------------------------------------
Plan hash value: 438843259
------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 123 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER| | 3 | 123 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| EMP | 3 | 114 | 3 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN| PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."JOB"='CLERK')
3 - access("A"."DEPTNO"="DEPTNO"(+))
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
1 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
-- b.没有进行视图合并
select *
from emp a
,( select /*+ no_merge*/deptno
from dept ) b
where a.deptno = b.deptno(+)
and a.job = 'CLERK' ;
执行计划
----------------------------------------------------------
Plan hash value: 1029234253
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 153 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 3 | 153 | 3 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | EMP | 3 | 114 | 3 (0)| 00:00:01 |
| 3 | VIEW PUSHED PREDICATE | | 1 | 13 | 0 (0)| 00:00:01 |
|* 4 | INDEX UNIQUE SCAN | PK_DEPT | 1 | 3 | 0 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("A"."JOB"='CLERK')
4 - access("DEPTNO"="A"."DEPTNO")
统计信息
----------------------------------------------------------
1 recursive calls
0 db block gets
10 consistent gets
0 physical reads
0 redo size
1257 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed
-- c.无法进行视图合并
-- ORDER BY
select *
from emp a
,( select deptno
,avg(sal) avg_sal
from emp
group by deptno ) b
where a.deptno = b.deptno
and a.sal >= b.avg_sal ;
执行计划
----------------------------------------------------------
Plan hash value: 269884559
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 64 | 8 (25)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 64 | 8 (25)| 00:00:01 |
| 2 | VIEW | | 3 | 78 | 4 (25)| 00:00:01 |
| 3 | HASH GROUP BY | | 3 | 21 | 4 (25)| 00:00:01 |
| 4 | TABLE ACCESS FULL| EMP | 14 | 98 | 3 (0)| 00:00:01 |
| 5 | TABLE ACCESS FULL | EMP | 14 | 532 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."DEPTNO"="B"."DEPTNO")
filter("A"."SAL">="B"."AVG_SAL")
统计信息
----------------------------------------------------------
24 recursive calls
0 db block gets
17 consistent gets
0 physical reads
0 redo size
1503 bytes sent via SQL*Net to client
519 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
6 rows processed