oracle对于子查询的支持做的很好，oracle optimizer会对inline view进行query transfomation，即视图合并，不过也经常带来意想不到的问题。
下面是一个inline view的merge的例子：
1, 创建临时表
create table test1 as select * from dba_objects;
create table test2 as select * from dba_objects;
2, 以下查询语句
select *
from test1 t1,
(select owner,
max(object_id) maxo,
min(object_id) mino,
avg(object_id) avgo
from test2
group by owner) t2
where t1.owner = t2.owner
and t1.object_id > t2.avgo;
3, 下面看下语句的执行计划：
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 90420 | 18M| | 1540 (1)| 00:00:19 |
|* 1 | FILTER | | | | | | |
| 2 | HASH GROUP BY | | 90420 | 18M| | 1540 (1)| 00:00:19 |
|* 3 | HASH JOIN | | 90420 | 18M| 3712K| 1535 (1)| 00:00:19 |
| 4 | TABLE ACCESS FULL| TEST2 | 90420 | 2649K| | 246 (1)| 00:00:03 |
| 5 | TABLE ACCESS FULL| TEST1 | 90420 | 16M| | 247 (2)| 00:00:03 |
--------------------------------------------------------------------------------------Predicate Information (identified by operation id):
--------------------------------------------------- 1 - filter("T1"."OBJECT_ID">SUM("OBJECT_ID")/COUNT("OBJECT_ID"))
3 - access("T1"."OWNER"="OWNER")
这里，oracle对inline view进行了视图合并，其执行过程是：
- 对test2，test1进行hash join。
- 对结果进行group by分组
- 然后过滤object_id大于均值的记录
看下其执行的情况：
26864 rows selected.
Elapsed: 00:11:53.64
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
2172 consistent gets
1081 physical reads
0 redo size
2304647 bytes sent via SQL*Net to client
20182 bytes received via SQL*Net from client
1792 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26864 rows processed
两个表的记录数:66957。结果集数:26864。
而整体的运行时间接近12分钟。
下面调整一下查询的执行计划，使用no_merge hint使用优化器不进行查询装换，对inline view不进行合并：
select /*+ no_merge(t2)*/*
from test1 t1,
(select owner,
max(object_id) maxo,
min(object_id) mino,
avg(object_id) avgo
from test2
group by owner) t2
where t1.owner = t2.owner
and t1.object_id > t2.avgo;26864 rows selected.
Elapsed: 00:00:01.47
Execution Plan
----------------------------------------------------------
Plan hash value: 1122024653
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4521 | 1028K| | 1600 (1)| 00:00:20 |
|* 1 | HASH JOIN | | 4521 | 1028K| 6008K| 1600 (1)| 00:00:20 |
| 2 | VIEW | | 90420 | 4944K| | 251 (3)| 00:00:04 |
| 3 | HASH GROUP BY | | 90420 | 2649K| | 251 (3)| 00:00:04 |
| 4 | TABLE ACCESS FULL| TEST2 | 90420 | 2649K| | 246 (1)| 00:00:03 |
| 5 | TABLE ACCESS FULL | TEST1 | 90420 | 15M| | 247 (2)| 00:00:03 |
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."OWNER"="T2"."OWNER")
filter("T1"."OBJECT_ID">"T2"."AVGO")
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
3934 consistent gets
0 physical reads
0 redo size
1602672 bytes sent via SQL*Net to client
20182 bytes received via SQL*Net from client
1792 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
26864 rows processed
使用no_merge hint，其执行过程是：先对test2先进行group by分组，然后对中间结果集进行hash驱动来join。
对比不同的执行计划，两个执行的时间却相差甚远。
现在打开10046 event，来看一下执行的具体花费的时间和计算的cost：
********************************************************************************
select /*+ no_merge(t2)*/*
from test1 t1,
(select owner,
max(object_id) maxo,
min(object_id) mino,
avg(object_id) avgo
from test2
group by owner) t2
where t1.owner = t2.owner
and t1.object_id > t2.avgo
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1792 0.70 0.68 0 3934 0 26864
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1794 0.70 0.68 0 3934 0 26864
Rows Row Source Operation
------- ---------------------------------------------------
26864 HASH JOIN (cr=3934 pr=0 pw=0 time=569062 us)
121 VIEW (cr=1086 pr=0 pw=0 time=216305 us)
121 HASH GROUP BY (cr=1086 pr=0 pw=0 time=216052 us)
66953 TABLE ACCESS FULL TEST2 (cr=1086 pr=0 pw=0 time=67093 us)
66957 TABLE ACCESS FULL TEST1 (cr=2848 pr=0 pw=0 time=69776 us)
Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
26864 HASH JOIN
121 VIEW
121 HASH (GROUP BY)
66953 TABLE ACCESS (FULL) OF 'TEST2' (TABLE)
66957 TABLE ACCESS (FULL) OF 'TEST1' (TABLE)
********************************************************************************
下面看另外一个merge后的执行情况：
********************************************************************************
select *
from test1 t1,
(select owner,
max(object_id) maxo,
min(object_id) mino,
avg(object_id) avgo
from test2
group by owner) t2
where t1.owner = t2.owner
and t1.object_id > t2.avgocall count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 1792 530.60 518.52 0 2172 0 26864
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 1794 530.60 518.52 0 2172 0 26864
Rows Row Source Operation
------- ---------------------------------------------------
26864 FILTER (cr=2172 pr=0 pw=0 time=518383093 us)
66957 HASH GROUP BY (cr=2172 pr=0 pw=0 time=518245046 us)
147500423 HASH JOIN (cr=2172 pr=0 pw=0 time=147797490 us)
66953 TABLE ACCESS FULL TEST2 (cr=1086 pr=0 pw=0 time=67029 us)
66957 TABLE ACCESS FULL TEST1 (cr=1086 pr=0 pw=0 time=67029 us)Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
26864 FILTER
66957 HASH (GROUP BY)
147500423 HASH JOIN
66953 TABLE ACCESS (FULL) OF 'TEST2' (TABLE)
66957 TABLE ACCESS (FULL) OF 'TEST1' (TABLE)
********************************************************************************
可以很明显的看出，时间花费在什么地方了。在进行hash group by和filter的过程中花费了大量的时间。
因为test1和test2进行hash join的中间结果有147,500,423条。
总结：
无论是inline view的merge还是其它的可选执行计划，最优的那个访问路径一定是具备了以下的条件：
- 优先对查询应用过滤性条件，尽可能多的减少中间结果集。
- 尽可能的只扫描期望得到的那一部分数据，减少不必要的开销。
