| Oracle 10g R2부터 Group By절에 의한 Grouping Column순으로 Sorting되지 않는 문제에 대한 접근 방식입니다.
이러한 문제에 대한 대처방식입니다.
Oracle 10g R2의 “New in-Memory Sort Algorithm”에 따른 문제점 및 개선점 ====================================================================== 1. 새로운 sort 적용 방식 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 |
## 실무사례 ##
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 |
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 절을 추가하여 준다.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
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 |