Oracle9i对等价操作的不同执行计划

显示全部楼层 · 2012-5-21 10:19:41

cube看起来最优
SQL> desc t
名称
是否为空? 类型
------------------------------------------------------------------------ -------- ---------------
C1
NUMBER
C2
NUMBER
C3
NUMBER
C4
NUMBER
V
NUMBER
SQL> set autot traceonly
with ta as
(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by c1,c2,c3,c4)
select * from ta
union all
select c1,c2,c3,null,sum(v)v,grouping_id(c1,c2,c3,null)gid from ta group by c1,c2,c3,null
SQL> with ta as
2(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by c1,c2,c3,c4)
3select * from ta
4union all
5select c1,c2,c3,null,sum(v)v,grouping_id(c1,c2,c3,null)gid from ta group by c1,c2,c3,null
6/
已选择200行。
已用时间:00: 00: 01.08
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
12 RECURSIVE EXECUTION OF 'SYS_LE_2_0'
20 TEMP TABLE TRANSFORMATION
32 UNION-ALL
43 VIEW
54 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6601_E72F8E'
63 SORT (GROUP BY)
76 VIEW
87 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6601_E72F8E'

Statistics
----------------------------------------------------------
986recursive calls
12db block gets
336consistent gets
30physical reads
1300redo size
4958bytes sent via SQL*Net to client
646bytes received via SQL*Net from client
15SQL*Net roundtrips to/from client
16sorts (memory)

0sorts (disk)
200rows processed

select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by c1,c2,c3,c4
union all
select c1,c2,c3,null,sum(v)v,grouping_id(c1,c2,c3,null)gid from t group by c1,c2,c3,null

SQL> select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by c1,c2,c3,c4
2union all
3select c1,c2,c3,null,sum(v)v,grouping_id(c1,c2,c3,null)gid from t group by c1,c2,c3,null
4/
已选择200行。
已用时间:00: 00: 00.01
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 UNION-ALL
21 SORT (GROUP BY)
32 TABLE ACCESS (FULL) OF 'T'
41 SORT (GROUP BY)
54 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

6consistent gets

0physical reads

0redo size
4958bytes sent via SQL*Net to client
646bytes received via SQL*Net from client
15SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)
200rows processed

SQL> select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4);
已选择1060行。
已用时间:00: 00: 00.02
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 SORT (GROUP BY)
21 GENERATE (CUBE)
32 SORT (GROUP BY)
43 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

3consistent gets

0physical reads

0redo size
22413bytes sent via SQL*Net to client
1273bytes received via SQL*Net from client
72SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)
1060rows processed

select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by grouping sets((c1,c2,c3,c4),(c1,c2,c3,null));
SQL> select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by grouping sets((c1,c2,c3,c4),(c1,c2,c3,null));
已选择200行。
已用时间:00: 00: 00.02
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=255

06)
14 RECURSIVE EXECUTION OF 'SYS_LE_4_0'
24 RECURSIVE EXECUTION OF 'SYS_LE_4_1'
34 RECURSIVE EXECUTION OF 'SYS_LE_4_2'
40 TEMP TABLE TRANSFORMATION
54 VIEW (Cost=2 Card=1 Bytes=78)
65 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6605_E72F8E' (Co

st=2 Card=1 Bytes=78)

Statistics
----------------------------------------------------------
357recursive calls
25db block gets
84consistent gets

4physical reads
3036redo size
4961bytes sent via SQL*Net to client
646bytes received via SQL*Net from client
15SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)
200rows processed

SQL> select * from(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4))where gid=0 or gid=1;
已选择200行。
已用时间:00: 00: 00.01
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 VIEW
21 FILTER
32 SORT (GROUP BY ROLLUP)
43 TABLE ACCESS (FULL) OF 'T'

Statistics
----------------------------------------------------------

0recursive calls

0db block gets

3consistent gets

0physical reads

0redo size
4845bytes sent via SQL*Net to client
646bytes received via SQL*Net from client
15SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)
200rows processed
[ 本帖最后由〇〇于 2010-3-30 20:35 编辑 ]

千问 · 2012-5-21 10:19:41

11.1
SQL> set autot traceonly
SQL> with ta as
2(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by c1,c2,c3,c4)
3select * from ta
4union all
5select c1,c2,c3,null,sum(v)v,grouping_id(c1,c2,c3,null)gid from ta group by c1,c2,c3,null;
已选择1103887行。
已用时间:00: 00: 24.07
执行计划
----------------------------------------------------------
Plan hash value: 2189726372
----------------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes |TempSpc| Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
|1051K|75M| |3843(85)| 00:00:47 |
| 1 |TEMP TABLE TRANSFORMATION |
| | | |
|
|
| 2 | LOAD AS SELECT |
| | | |
|
|
| 3 |HASH GROUP BY |
|1000K|15M|30M|6179 (1)| 00:01:15 |
| 4 | TABLE ACCESS FULL| T
|1000K|15M| | 861 (1)| 00:00:11 |
| 5 | UNION-ALL
|
| | | |
|
|
| 6 |VIEW
|
|1000K|74M| | 598 (2)| 00:00:08 |
| 7 | TABLE ACCESS FULL| SYS_TEMP_0FD9D661C_EE0D5C |1000K|15M| | 598 (2)| 00:00:08 |
| 8 |HASH GROUP BY |
| 51944 |1166K|34M|3245 (2)| 00:00:39 |
| 9 | VIEW
|
|1000K|21M| | 598 (2)| 00:00:08 |
|10 |TABLE ACCESS FULL | SYS_TEMP_0FD9D661C_EE0D5C |1000K|15M| | 598 (2)| 00:00:08 |
----------------------------------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
262recursive calls
3370db block gets
76191consistent gets
5915physical reads
1508redo size
37193150bytes sent via SQL*Net to client
810032bytes received via SQL*Net from client
73594SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
1103887rows processed
SQL> select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by c1,c2,c3,c4
2union all
3select c1,c2,c3,null,sum(v)v,grouping_id(c1,c2,c3,null)gid from t group by c1,c2,c3,null
4;
已选择1103887行。
已用时间:00: 00: 21.34
执行计划
----------------------------------------------------------
Plan hash value: 3269816419
------------------------------------------------------------------------------------
| Id| Operation | Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT||1051K|16M| |9033(33)| 00:01:49 |
| 1 |UNION-ALL
|| | | |
|
|
| 2 | HASH GROUP BY ||1000K|15M|30M|6179 (1)| 00:01:15 |
| 3 |TABLE ACCESS FULL| T|1000K|15M| | 861 (1)| 00:00:11 |
| 4 | HASH GROUP BY || 51944 | 760K|26M|2855 (2)| 00:00:35 |
| 5 |TABLE ACCESS FULL| T|1000K|14M| | 861 (1)| 00:00:11 |
------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------

1recursive calls

0db block gets
6180consistent gets

0physical reads

0redo size
37212593bytes sent via SQL*Net to client
810032bytes received via SQL*Net from client
73594SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
1103887rows processed
SQL> select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by grouping sets((c1,c2,c3,c4),(c1,c2,c3,null));
已选择1103887行。
已用时间:00: 00: 29.73
执行计划
----------------------------------------------------------
Plan hash value: 2588144637
--------------------------------------------------------------------------------------------------------
| Id| Operation
| Name
| Rows| Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |
|1000K|74M| 869 (2)| 00:00:11 |
| 1 |TEMP TABLE TRANSFORMATION |
| | |
|
|
| 2 | LOAD AS SELECT |
| | |
|
|
| 3 |TABLE ACCESS FULL | T
|1000K|15M| 861 (1)| 00:00:11 |
| 4 | LOAD AS SELECT |
| | |
|
|
| 5 |HASH GROUP BY |
| 1 |65 | 3(34)| 00:00:01 |
| 6 | TABLE ACCESS FULL| SYS_TEMP_0FD9D661F_EE0D5C | 1 |65 | 2 (0)| 00:00:01 |
| 7 | LOAD AS SELECT |
| | |
|
|
| 8 |HASH GROUP BY |
| 1 |54 | 3(34)| 00:00:01 |
| 9 | TABLE ACCESS FULL| SYS_TEMP_0FD9D661F_EE0D5C | 1 |54 | 2 (0)| 00:00:01 |
|10 | VIEW
|
| 1 |78 | 2 (0)| 00:00:01 |
|11 |TABLE ACCESS FULL | SYS_TEMP_0FD9D6620_EE0D5C | 1 |78 | 2 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------

统计信息
----------------------------------------------------------
556recursive calls
7254db block gets
86798consistent gets
16660physical reads
3648redo size
37283054bytes sent via SQL*Net to client
810032bytes received via SQL*Net from client
73594SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)
1103887rows processed
SQL> select * from(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4))where gid=0 or gid=1;
已选择1103887行。
已用时间:00: 00: 20.96
执行计划
----------------------------------------------------------
Plan hash value: 2162167634
---------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 750K|55M| |5321 (1)| 00:01:04 |
| 1 |VIEW
|| 750K|55M| |5321 (1)| 00:01:04 |
|*2 | FILTER
|| | | |
|
|
| 3 |SORT GROUP BY ROLLUP|| 750K|11M|30M|5321 (1)| 00:01:04 |
| 4 | TABLE ACCESS FULL| T|1000K|15M| | 861 (1)| 00:00:11 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0,SYS_OP_BITVEC),SY

S_OP_GROUPING("C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,0,SYS_OP_BITVEC),S

YS_OP_GROUPING("C4",1,0,SYS_OP_BITVEC)))=0 OR

GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0,SYS_OP_BITVEC),SYS_OP_GROUPING(

"C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,0,SYS_OP_BITVEC),SYS_OP_GROUPING

("C4",1,0,SYS_OP_BITVEC)))=1)

统计信息
----------------------------------------------------------

1recursive calls

0db block gets
3090consistent gets

0physical reads

0redo size
28401900bytes sent via SQL*Net to client
810032bytes received via SQL*Net from client
73594SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)
1103887rows processed
SQL>

千问 · 2012-5-21 10:19:41

SQL> select * from(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4))where gid=0 or gid=1 or gid=3 or gi
;
已选择1104770行。
已用时间:00: 00: 20.99
执行计划
----------------------------------------------------------
Plan hash value: 2162167634
---------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 968K|72M| |6071 (1)| 00:01:13 |
| 1 |VIEW
|| 968K|72M| |6071 (1)| 00:01:13 |
|*2 | FILTER
|| | | |
|
|
| 3 |SORT GROUP BY ROLLUP|| 968K|14M|30M|6071 (1)| 00:01:13 |
| 4 | TABLE ACCESS FULL| T|1000K|15M| | 861 (1)| 00:00:11 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0,SYS_OP_BITVEC),SY

S_OP_GROUPING("C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,0,SYS_OP_BITVEC),S

YS_OP_GROUPING("C4",1,0,SYS_OP_BITVEC)))=0 OR

GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0,SYS_OP_BITVEC),SYS_OP_GROUPING(

"C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,0,SYS_OP_BITVEC),SYS_OP_GROUPING

("C4",1,0,SYS_OP_BITVEC)))=1 OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0

,SYS_OP_BITVEC),SYS_OP_GROUPING("C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,

0,SYS_OP_BITVEC),SYS_OP_GROUPING("C4",1,0,SYS_OP_BITVEC)))=3 OR

GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0,SYS_OP_BITVEC),SYS_OP_GROUPING(

"C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,0,SYS_OP_BITVEC),SYS_OP_GROUPING

("C4",1,0,SYS_OP_BITVEC)))=7 OR GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0

,SYS_OP_BITVEC),SYS_OP_GROUPING("C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,

0,SYS_OP_BITVEC),SYS_OP_GROUPING("C4",1,0,SYS_OP_BITVEC)))=15)

统计信息
----------------------------------------------------------

1recursive calls

0db block gets
3090consistent gets

0physical reads

0redo size
28426807bytes sent via SQL*Net to client
810681bytes received via SQL*Net from client
73653SQL*Net roundtrips to/from client

1sorts (memory)

0sorts (disk)
1104770rows processed
SQL> select * from(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4))where gid>-1;
已选择2299600行。
已用时间:00: 01: 12.88
执行计划
----------------------------------------------------------
Plan hash value: 3752562156
---------------------------------------------------------------------------------------
| Id| Operation
| Name | Rows| Bytes |TempSpc| Cost (%CPU)| Time |
---------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT || 50000 |3808K| |2921 (2)| 00:00:36 |
| 1 |VIEW
|| 50000 |3808K| |2921 (2)| 00:00:36 |
|*2 | FILTER
|| | | |
|
|
| 3 |SORT GROUP BY || 50000 | 781K| |2921 (2)| 00:00:36 |
| 4 | GENERATE CUBE|| 50000 | 781K| |2921 (2)| 00:00:36 |
| 5 |SORT GROUP BY || 50000 | 781K|30M|2921 (2)| 00:00:36 |
| 6 | TABLE ACCESS FULL| T|1000K|15M| | 861 (1)| 00:00:11 |
---------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter(GROUPING_ID(BIN_TO_NUM(SYS_OP_GROUPING("C1",8,0,SYS_OP_BITVEC),SY

S_OP_GROUPING("C2",4,0,SYS_OP_BITVEC),SYS_OP_GROUPING("C3",2,0,SYS_OP_BITVEC),S

YS_OP_GROUPING("C4",1,0,SYS_OP_BITVEC)))>(-1))

统计信息
----------------------------------------------------------
99recursive calls
17db block gets
3090consistent gets
12458physical reads

0redo size
58960948bytes sent via SQL*Net to client
1686886bytes received via SQL*Net from client
153308SQL*Net roundtrips to/from client

1sorts (memory)

1sorts (disk)
2299600rows processed
SQL>

千问 · 2012-5-21 10:19:41

more 9i
SQL> select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by grouping sets((c1,c2,c3,c4),(c1,c2,c3,null),(null,c2,null,c4));
已选择300行。
已用时间:00: 00: 01.07
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE (Cost=2 Card=327 Bytes=255

06)
15 RECURSIVE EXECUTION OF 'SYS_LE_5_0'
25 RECURSIVE EXECUTION OF 'SYS_LE_5_1'
35 RECURSIVE EXECUTION OF 'SYS_LE_5_2'
45 RECURSIVE EXECUTION OF 'SYS_LE_5_3'
50 TEMP TABLE TRANSFORMATION
65 VIEW (Cost=2 Card=1 Bytes=78)
76 TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6603_E83952' (Co

st=2 Card=1 Bytes=78)

Statistics
----------------------------------------------------------
1486recursive calls
34db block gets
420consistent gets
35physical reads
3556redo size
6814bytes sent via SQL*Net to client
712bytes received via SQL*Net from client
21SQL*Net roundtrips to/from client
19sorts (memory)

0sorts (disk)
300rows processed

SQL> select * from(select c1,c2,c3,c4,sum(v)v,grouping_id(c1,c2,c3,c4)gid from t group by cube(c1,c2,c3,c4))where gid=0 or gid=1 or gid=10;
已选择300行。
已用时间:00: 00: 00.02
Execution Plan
----------------------------------------------------------
0SELECT STATEMENT Optimizer=CHOOSE
10 VIEW
24 RECURSIVE EXECUTION OF 'SYS_LE_4_0'
34 RECURSIVE EXECUTION OF 'SYS_LE_4_1'
41 TEMP TABLE TRANSFORMATION
54 VIEW
65 VIEW
76 UNION-ALL
87
TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6606_E8395

2'
97
TABLE ACCESS (FULL) OF 'SYS_TEMP_0FD9D6607_E8395

2'

Statistics
----------------------------------------------------------
132recursive calls
22db block gets
67consistent gets

3physical reads
2664redo size
6814bytes sent via SQL*Net to client
712bytes received via SQL*Net from client
21SQL*Net roundtrips to/from client

2sorts (memory)

0sorts (disk)
300rows processed