[20171211]HASH GROUP BY not used when using more that 354 aggregate functions.txt
--//http://msutic.blogspot.com/2017/11/hash-group-by-not-used-when-using-more.html
--//当查询354 unique aggregate functions which is using HASH GROUP BY.重复测试看看.
1.环境:
SCOTT@book> @ &r/ver1
PORT_STRING VERSION BANNER
------------------------------ -------------- --------------------------------------------------------------------------------
x86_64/Linux 2.4.xx 11.2.0.4.0 Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
set head off
spool /tmp/354.txt
select ',sum('|| rownum || ') k'||rownum from dual connect by level<=354;
spool off
--//以/tmp/354.txt为蓝本,修改如下sql语句:
$ head /tmp/354.txt
SELECT * FROM (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL < 1000) a,
( SELECT
123 ID
,sum(1) k1
,sum(2) k2
,sum(3) k3
,sum(4) k4
,sum(5) k5
,sum(6) k6
,sum(7) k7
$ tail /tmp/354.txt
,sum(348) k348
,sum(349) k349
,sum(350) k350
,sum(351) k351
,sum(352) k352
,sum(353) k353
,sum(354) k354
FROM DUAL
GROUP BY 123) b
WHERE a.ID = b.ID(+);
SCOTT@book> @ /tmp/354.txt
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 0aumtwvs2ssk9, child number 0
-------------------------------------
SELECT * FROM (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL < 1000) a, (
SELECT 123 ID ,sum(1) k1 ,sum(2) k2 ,sum(3) k3 ,sum(4) k4 ,sum(5) k5
,sum(6) k6 ,sum(7) k7 ,sum(8) k8 ,sum(9) k9 ,sum(10) k10 ,sum(11) k11
,sum(12) k12 ,sum(13) k13 ,sum(14) k14 ,sum(15) k15 ,sum(16) k16
,sum(17) k17 ,sum(18) k18 ,sum(19) k19 ,sum(20) k20 ,sum(21) k21
,sum(22) k22 ,sum(23) k23 ,sum(24) k24 ,sum(25) k25 ,sum(26) k26
,sum(27) k27 ,sum(28) k28 ,sum(29) k29 ,sum(30) k30 ,sum(31) k31
,sum(32) k32 ,sum(33) k33 ,sum(34) k34 ,sum(35) k35 ,sum(36) k36
,sum(37) k37 ,sum(38) k38 ,sum(39) k39 ,sum(40) k40 ,sum(41) k41
,sum(42) k42 ,sum(43) k43 ,sum(44) k44 ,sum(45) k45 ,sum(46) k46
,sum(47) k47 ,sum(48) k48 ,sum(49) k49 ,sum(50) k50 ,sum(51) k51
,sum(52) k52 ,sum(53) k53 ,sum(54) k54 ,sum(55) k55 ,sum(56) k56
,sum(57) k57 ,sum(58) k58 ,sum(59) k59 ,sum(60) k60 ,sum(61) k61
,sum(62) k62 ,sum(63) k63 ,sum(64) k64 ,sum(65) k65 ,sum(66) k66
,sum(67) k67 ,sum(68) k68 ,sum(69) k69 ,sum(70) k70 ,sum(71) k71
,sum(72)
Plan hash value: 2053589652
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4618 | 4 (0)| 00:00:01 | 2293K| 2293K| 1659K (0)|
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | | |
| 3 | CONNECT BY WITHOUT FILTERING| | | | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | |
| 5 | VIEW | | 1 | 4605 | 2 (0)| 00:00:01 | | | |
| 6 | HASH GROUP BY | | 1 | | 2 (0)| 00:00:01 | 677K| 677K| 710K (0)|
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / A@SEL$1
3 - SEL$2
4 - SEL$2 / DUAL@SEL$2
5 - SEL$3 / B@SEL$1
6 - SEL$3
7 - SEL$3 / DUAL@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
--//如果增加1行.注意下划线内容:
$ tail /tmp/354.txt
,sum(349) k349
,sum(350) k350
,sum(351) k351
,sum(352) k352
,sum(353) k353
,sum(354) k354
,sum(355) k355
~~~~~~~~~~~~~~~
FROM DUAL
GROUP BY 123) b
WHERE a.ID = b.ID(+);
SCOTT@book> @ &r/dpc '' ''
PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID 6j1yhy3nu7wzj, child number 0
-------------------------------------
SELECT * FROM (SELECT LEVEL ID FROM DUAL CONNECT BY LEVEL < 1000) a, (
SELECT 123 ID ,sum(1) k1 ,sum(2) k2 ,sum(3) k3 ,sum(4) k4 ,sum(5) k5
,sum(6) k6 ,sum(7) k7 ,sum(8) k8 ,sum(9) k9 ,sum(10) k10 ,sum(11) k11
,sum(12) k12 ,sum(13) k13 ,sum(14) k14 ,sum(15) k15 ,sum(16) k16
,sum(17) k17 ,sum(18) k18 ,sum(19) k19 ,sum(20) k20 ,sum(21) k21
,sum(22) k22 ,sum(23) k23 ,sum(24) k24 ,sum(25) k25 ,sum(26) k26
,sum(27) k27 ,sum(28) k28 ,sum(29) k29 ,sum(30) k30 ,sum(31) k31
,sum(32) k32 ,sum(33) k33 ,sum(34) k34 ,sum(35) k35 ,sum(36) k36
,sum(37) k37 ,sum(38) k38 ,sum(39) k39 ,sum(40) k40 ,sum(41) k41
,sum(42) k42 ,sum(43) k43 ,sum(44) k44 ,sum(45) k45 ,sum(46) k46
,sum(47) k47 ,sum(48) k48 ,sum(49) k49 ,sum(50) k50 ,sum(51) k51
,sum(52) k52 ,sum(53) k53 ,sum(54) k54 ,sum(55) k55 ,sum(56) k56
,sum(57) k57 ,sum(58) k58 ,sum(59) k59 ,sum(60) k60 ,sum(61) k61
,sum(62) k62 ,sum(63) k63 ,sum(64) k64 ,sum(65) k65 ,sum(66) k66
,sum(67) k67 ,sum(68) k68 ,sum(69) k69 ,sum(70) k70 ,sum(71) k71
,sum(72)
Plan hash value: 2672300481
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | E-Rows |E-Bytes| Cost (%CPU)| E-Time | OMem | 1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 4 (100)| | | | |
|* 1 | HASH JOIN OUTER | | 1 | 4631 | 4 (0)| 00:00:01 | 2293K| 2293K| 1664K (0)|
| 2 | VIEW | | 1 | 13 | 2 (0)| 00:00:01 | | | |
| 3 | CONNECT BY WITHOUT FILTERING| | | | | | | | |
| 4 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | |
| 5 | VIEW | | 1 | 4618 | 2 (0)| 00:00:01 | | | |
| 6 | SORT GROUP BY | | 1 | | 2 (0)| 00:00:01 | 20480 | 20480 |18432 (0)|
| 7 | FAST DUAL | | 1 | | 2 (0)| 00:00:01 | | | |
-------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$1
2 - SEL$2 / A@SEL$1
3 - SEL$2
4 - SEL$2 / DUAL@SEL$2
5 - SEL$3 / B@SEL$1
6 - SEL$3
7 - SEL$3 / DUAL@SEL$3
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("A"."ID"="B"."ID")
--//执行计划id=6.从HASH GROUP BY变成了SORT GROUP BY.
