Group by 절에 의한 자동정렬이 되지 않는 이유
|
Oracle 10g R2부터 Group By절에 의한 Grouping Column순으로 Sorting되지 않는 문제에 대한 접근 방식입니다. 이러한 문제에 대한 대처방식입니다.
Oracle 10g R2의 “New in-Memory Sort Algorithm”에 따른 문제점 및 개선점 ====================================================================== 2. 성능 개선 효과 3. SORT특징에 따른 개선 효과 ====================================================================== >>>> New in-Memory Sort Algorithm 의 문제점?
====================================================================== >>>> New in-Memory Sort Algorithm 의 문제점인 “GROUP BY”를 기존 방식으로 사용하기 위해서는? Subject: Upgrading from 9i to 10g – Potential Query Tuning Related Issues |
## 실무사례 ##
select r,
s1,
s2,
s3,
s4,
s5,
s6
from (
select ” r,
” s1,
‘총계’ s2,
c3 s3,
decode(c4, null, ‘-‘, 0, ‘-‘, round((c3 – c4)*100/c4, 1)) s4,
c1 s5,
decode(c2, null, ‘-‘, 0, ‘-‘, round((c1 – c2)*100/c2, 1)) s6
from (
select sum(decode(yymm, ‘2006’ ||’04’, amts)) c1,
sum(decode(yymm, ‘2006’-1||’04’, amts)) c2,
sum(decode(yymm, ‘2006’-1||’12’, amts)) c3,
sum(decode(yymm, ‘2006’-2||’12’, amts)) c4
from (
select /*+ index (o kts_m2_x1) */yymm,
sum(decode(‘2’, 1, trade_amt, trade_amt_acm)) amts
from kts_m2 o
where yymm in (‘2006’||’04’,
‘2006’-1||’04’,
‘2006’-1||’12’,
‘2006’-2||’12’)
and ie_gbn = ‘E’
group by yymm ) )
union all
select to_char(rownum),
ctr_cd s1,
kor_name s2,
decode(amt4, null, 0, 0, 0, amt4) s3,
amt3 s4,
decode(amt2, null, 0, 0, 0, amt2) s5,
amt1 s6
from (
select max(a.ctr_cd) ctr_cd,
max(b.kor_name) kor_name,
max(a.s2) amt4,
max(decode(a.s2, null, ‘-‘, decode(a.s1, null, ‘-‘, 0, ‘-‘, round(100*(a.s2-a.s1)/a.s1, 1)))) amt3,
-(-a.s4) amt2,
max(decode(a.s4, null, ‘-‘, decode(a.s3, null, ‘-‘, 0, ‘-‘, round(100*(a.s4-a.s3)/a.s3, 1)))) amt1
from ctr_code b,
(
select /*+ index ( kts_ctr kts_ctr_x1 ) */ctr_cd ctr_cd,
sum(decode(yymm, ‘2006’-2||’12’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s1,
sum(decode(yymm, ‘2006’-1||’12’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s2,
sum(decode(yymm, ‘2006’-1||’04’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s3,
sum(decode(yymm, ‘2006’ ||’04’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s4
from kts_ctr
where yymm in (‘2006′-2||’12’,
‘2006’-1||’12’,
‘2006’-1||’04’,
‘2006’||’04’)
and e_gbn = ‘E’
group by ctr_cd ) a
where a.ctr_cd = b.ctr_cd
group by -a.s4, a.ctr_cd
) )
where 1 = 1
and rownum<=11
8i Plan
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=11 Bytes=1K)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=23 Card=11 Bytes=1K)
3 2 UNION-ALL
4 3 VIEW (Cost=8 Card=1 Bytes=52)
5 4 FILTER
6 5 SORT (AGGREGATE) (Card=1 Bytes=18)
7 6 VIEW (Cost=8 Card=3 Bytes=54)
8 7 SORT (GROUP BY) (Card=3 Bytes=111)
9 8 CONCATENATION
10 9 INLIST ITERATOR
11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (Cost=3 Card=1 Bytes=37)
12 11 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (UNIQUE) (Cost=2 Card=1)
13 9 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (Cost=3 Card=1 Bytes=37)
14 13 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (UNIQUE) (Cost=2 Card=1)
15 3 COUNT
16 15 VIEW (Cost=15 Card=10 Bytes=1000)
17 16 SORT (GROUP BY) (Cost=15 Card=10 Bytes=860)
18 17 HASH JOIN (Cost=13 Card=10 Bytes=860)
19 18 VIEW (Cost=11 Card=6 Bytes=330)
20 19 SORT (GROUP BY) (Card=6 Bytes=246)
21 20 CONCATENATION
22 21 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (Cost=3 Card=1 Bytes=41)
23 22 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (UNIQUE) (Cost=2 Card=1)
24 21 INLIST ITERATOR
25 24 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (Cost=3 Card=1 Bytes=41)
26 25 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (UNIQUE) (Cost=2 Card=1)
27 18 TABLE ACCESS (FULL) OF ‘KSTAT.CTR_CODE’ (Cost=1 Card=164 Bytes=5K)
총계 284418742503 12 99534827771 11
1 CN 중국 61914983215 24.4 20827196905 12.1
2 US 미국 41342584390 -3.5 13858758704 .9
3 JP 일본 24027437900 10.7 8299955234 9
4 HK 홍콩 15531092215 -14.3 5875131009 25.1
5 TW 대만 10862932443 10.3 3795723262 9.8
6 DE 독일 10303964211 23.6 3254077284 -5.6
7 SG 싱가포르 7406634297 31 2832927989 38.9
8 IN 인디아(인도) 4597836954 26.6 1787342741 32.9
9 GB 영국 5338843555 -3.2 1738720242 7.3
10 AU 호주 3812063148 12.8 1714611147 44.7
10g Plan
0 SELECT STATEMENT Optimizer=RULE (Cost=8 Card=3 Bytes=366)
1 0 COUNT (STOPKEY)
2 1 VIEW (Cost=8 Card=3 Bytes=366)
3 2 UNION-ALL
4 3 VIEW (Cost=3 Card=1 Bytes=52)
5 4 FILTER
6 5 SORT (AGGREGATE) (Card=1 Bytes=21)
7 6 VIEW (Cost=3 Card=2 Bytes=42)
8 7 HASH (GROUP BY) (Card=2 Bytes=74)
9 8 CONCATENATION
10 9 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (TABLE) (Cost=1 Card=1 Bytes=37)
11 10 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (INDEX (UNIQUE)) (Cost=1 Card=1)
12 9 INLIST ITERATOR
13 12 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (TABLE) (Cost=1 Card=1 Bytes=37)
14 13 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (INDEX (UNIQUE)) (Cost=1 Card=1)
15 3 COUNT
16 15 VIEW (Cost=5 Card=2 Bytes=202)
17 16 HASH (GROUP BY) (Cost=5 Card=2 Bytes=174)
18 17 NESTED LOOPS (Cost=4 Card=2 Bytes=174)
19 18 VIEW (Cost=3 Card=2 Bytes=112)
20 19 HASH (GROUP BY) (Card=2 Bytes=82)
21 20 CONCATENATION
22 21 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (TABLE) (Cost=1 Card=1 Bytes=41)
23 22 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (INDEX (UNIQUE)) (Cost=1 Card=8)
24 21 INLIST ITERATOR
25 24 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (TABLE) (Cost=1 Card=1 Bytes=41)
26 25 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (INDEX (UNIQUE)) (Cost=1 Card=8)
27 18 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.CTR_CODE’ (TABLE) (Cost=1 Card=1 Bytes=31)
28 27 INDEX (UNIQUE SCAN) OF ‘KSTAT.CTR_CODE_PK’ (INDEX (UNIQUE)) (Cost=1 Card=1)
총계 284418742503 12 99534827771 11
1 AD 안도라 832819 -34.4 314756 101.1
2 AE 아랍에미리트 연합 2732731530 5.6 905643855 -5.3
3 AF 아프카니스탄 59631357 -22.7 19712121 -3.7
4 AG 안티가 바부다 4588172 70.4 2389520 86.9
5 AI 앙귈라 135659 -43.9 205 -99.6
6 AL 알바니아 6326901 -20.3 7142128 117.6
7 AM 아르메니아 3149597 45 1658169 142.3
8 AN 네덜란드 열도 10756525 50.5 4937642 78.2
9 AO 앙골라 1517394108 -16.4 203785473 -86.1
10 AQ 안타티카 132169 – 0 –
## 해결책 ##
Init.ora 의 _gby_hash_aggregation_enabled=FALSE 의 경우 group by 절 다음에 Single Column 인 경우에는
정상적인 결과가 나오지만 2개 이상의 Column 이 나오는 경우에는 결과가 달라진다. 즉 위 Union all 의
From 절 In-Line View 의 group by –a.s4 의 하나의 Column 으로 결과를 구하면 정상적으로 Sorting 이
되어져서 결과가 나오지만, 예처럼 –a.s4, a.ctr_cd 처럼 Column 을 하나 더 추가하면 결과가 달라진다.
아래와 같이 group by 절 다음에 order by 절을 추가하여 준다.
where a.ctr_cd = b.ctr_cd
group by -a.s4, a.ctr_cd
order by –a.s4,a.ctr_cd ) )
총계 284418742503 12 99534827771 11
1 CN 중국 61914983215 24.4 20827196905 12.1
2 US 미국 41342584390 -3.5 13858758704 .9
3 JP 일본 24027437900 10.7 8299955234 9
4 HK 홍콩 15531092215 -14.3 5875131009 25.1
5 TW 대만 10862932443 10.3 3795723262 9.8
6 DE 독일 10303964211 23.6 3254077284 -5.6
7 SG 싱가포르 7406634297 31 2832927989 38.9
8 IN 인디아(인도) 4597836954 26.6 1787342741 32.9
9 GB 영국 5338843555 -3.2 1738720242 7.3
10 AU 호주 3812063148 12.8 1714611147 44.7