Group by 절에 의한 자동정렬이 되지 않는 이유

haisins

8년 ago

Group by 절에 의한 자동정렬이 되지 않는 이유

Oracle 10g R2부터 Group By절에 의한 Grouping Column순으로 Sorting되지 않는 문제에 대한 접근 방식입니다.
기존의 고객이 Order by를 사용하지 않고 Group By만을 사용하였다면 문제를 제기할 수 있을 것입니다.

이러한 문제에 대한 대처방식입니다.

Oracle 10g R2의 “New in-Memory Sort Algorithm”에 따른 문제점 및 개선점

======================================================================
>>>> Oracle 10g R2 New Feature – New in-Memory Sort Algorithm 이란?
———————————————————————
1. 새로운 sort 적용 방식
– 기존에는 Sort 알고리즘으로 Sort하였으나 “Hash-based 방식“의 New Feature

2. 성능 개선 효과
   – 충분한 Memory일 경우 (즉 In-Memory Sort)일 경우 효과적
   – Sort operation이 기존 방식에 비해 최대 5~10%까지 빠를 수 있다.

3. SORT특징에 따른 개선 효과
   – 높은 cardinality (Row들의 Distinct가 많은 경우)일 경우 특히 효과적 (HASH방식 이므로)
   – Faster CPU일 경우 더욱 효과적
   – 적은 Column을 Select 했을 경우 특히 효과적 (Hash는 Memory부족에 의해 Disk로 내려가면 꽝)

======================================================================

>>>> New in-Memory Sort Algorithm 의 문제점?
———————————————————————
  “GROUP BY”를 사용한 App가 “ORDER BY”를 기술하지 않더라도 Ordering된 결과를 Display하던
  App들이 10g R2로 오면서 이 기능이 깨지게 되었음.
  즉 반드시 Ordering이 필요하면 “GROUP BY”와 함께 “ORDER BY”를 기술해야 함.
  (참고. 이는 Oracle의 Bug은 아니며 App의 잘못임)

======================================================================

>>>> New in-Memory Sort Algorithm 의 문제점인 “GROUP BY”를 기존 방식으로 사용하기 위해서는?
———————————————————————
1. Optimizer Mode가 RULE일 경우는 발생 안함.
2. OPTIMIZER_FEATURES_ENABLE를 10.1 로 함
3. init.ora “_gby_hash_aggregation_enabled”=FALSE (New방식 사용 안함)
위의 방식 중 3번이 해당 기능 만 막으므로 가장 많이 사용될 것임.
그러나 New in-Memory Sort Algorithm은 아주 유용한 방식이므로 App를 수정할 것을 고객들에 권장할 필요가 있음.
관련 Doc è Note:295819.1 , Bug : 4604970

Subject: Upgrading from 9i to 10g – Potential Query Tuning Related Issues

## 실무사례 ##

select r,

s1,

s2,

s3,

s4,

s5,

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