{"id":1669,"date":"2018-01-23T20:58:57","date_gmt":"2018-01-23T11:58:57","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=1669"},"modified":"2018-01-23T20:59:29","modified_gmt":"2018-01-23T11:59:29","slug":"group-by-%ec%a0%88%ec%97%90-%ec%9d%98%ed%95%9c-%ec%9e%90%eb%8f%99%ec%a0%95%eb%a0%ac%ec%9d%b4-%eb%90%98%ec%a7%80-%ec%95%8a%eb%8a%94-%ec%9d%b4%ec%9c%a0","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=1669","title":{"rendered":"Group by \uc808\uc5d0 \uc758\ud55c \uc790\ub3d9\uc815\ub82c\uc774 \ub418\uc9c0 \uc54a\ub294 \uc774\uc720"},"content":{"rendered":"

Group by \uc808\uc5d0 \uc758\ud55c \uc790\ub3d9\uc815\ub82c\uc774 \ub418\uc9c0 \uc54a\ub294 \uc774\uc720
\n<\/strong><\/span><\/p>\n

\n\u00a0<\/p>\n

\n\n\n<\/colgroup>\n\n\n
\n

Oracle 10g R2\ubd80\ud130 Group By\uc808\uc5d0 \uc758\ud55c\u00a0Grouping Column\uc21c\uc73c\ub85c Sorting\ub418\uc9c0 \uc54a\ub294 \ubb38\uc81c\uc5d0 \ub300\ud55c \uc811\uadfc \ubc29\uc2dd\uc785\ub2c8\ub2e4.
\uae30\uc874\uc758 \uace0\uac1d\uc774 Order by\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0 Group By\ub9cc\uc744 \uc0ac\uc6a9\ud558\uc600\ub2e4\uba74\u00a0\ubb38\uc81c\ub97c\u00a0\uc81c\uae30\ud560 \uc218 \uc788\uc744 \uac83\uc785\ub2c8\ub2e4.
\n<\/span><\/p>\n

\uc774\ub7ec\ud55c \ubb38\uc81c\uc5d0 \ub300\ud55c \ub300\ucc98\ubc29\uc2dd\uc785\ub2c8\ub2e4.
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

Oracle 10g R2\uc758 “New in-Memory Sort Algorithm”\uc5d0 \ub530\ub978 \ubb38\uc81c\uc810 \ubc0f \uac1c\uc120\uc810<\/strong><\/span>
\n\t\t\t\t\t\t\t<\/span><\/p>\n

======================================================================
>>>> Oracle 10g R2 New Feature – New in-Memory Sort Algorithm \uc774\ub780?<\/strong><\/span>
———————————————————————
1. \uc0c8\ub85c\uc6b4 sort \uc801\uc6a9 \ubc29\uc2dd
\u00a0\u00a0 – \uae30\uc874\uc5d0\ub294 Sort \uc54c\uace0\ub9ac\uc998\uc73c\ub85c Sort\ud558\uc600\uc73c\ub098 “Hash-based \ubc29\uc2dd<\/strong><\/span>“\uc758 New Feature
\n\t\t\t\t\t\t\t<\/span><\/p>\n

2. \uc131\ub2a5 \uac1c\uc120 \ud6a8\uacfc\u00a0\u00a0
\u00a0\u00a0 – \ucda9\ubd84\ud55c Memory\uc77c \uacbd\uc6b0 (\uc989 In-Memory Sort)\uc77c \uacbd\uc6b0 \ud6a8\uacfc\uc801
\u00a0\u00a0 – Sort operation\uc774 \uae30\uc874 \ubc29\uc2dd\uc5d0 \ube44\ud574 \ucd5c\ub300 5~10%\uae4c\uc9c0 \ube60\ub97c \uc218 \uc788\ub2e4.
\n\t\t\t\t\t\t\t<\/span><\/p>\n

3. SORT\ud2b9\uc9d5\uc5d0 \ub530\ub978 \uac1c\uc120 \ud6a8\uacfc
\u00a0\u00a0 – \ub192\uc740 cardinality (Row\ub4e4\uc758 Distinct\uac00 \ub9ce\uc740 \uacbd\uc6b0)\uc77c \uacbd\uc6b0 \ud2b9\ud788 \ud6a8\uacfc\uc801 (HASH\ubc29\uc2dd \uc774\ubbc0\ub85c)
\u00a0\u00a0 – Faster CPU\uc77c \uacbd\uc6b0 \ub354\uc6b1 \ud6a8\uacfc\uc801
\u00a0\u00a0 – \uc801\uc740 Column\uc744 Select \ud588\uc744 \uacbd\uc6b0 \ud2b9\ud788 \ud6a8\uacfc\uc801 (Hash\ub294 Memory\ubd80\uc871\uc5d0 \uc758\ud574 Disk\ub85c \ub0b4\ub824\uac00\uba74 \uaf5d)
\n\t\t\t\t\t\t\t<\/span><\/p>\n

======================================================================
\n<\/span><\/p>\n

>>>> New in-Memory Sort Algorithm \uc758 \ubb38\uc81c\uc810?<\/strong><\/span>
———————————————————————
\u00a0\u00a0“GROUP BY”\ub97c \uc0ac\uc6a9\ud55c App\uac00 “ORDER BY”\ub97c \uae30\uc220\ud558\uc9c0 \uc54a\ub354\ub77c\ub3c4 Ordering\ub41c \uacb0\uacfc\ub97c Display\ud558\ub358
\u00a0\u00a0App\ub4e4\uc774 10g R2\ub85c \uc624\uba74\uc11c \uc774 \uae30\ub2a5\uc774 \uae68\uc9c0\uac8c \ub418\uc5c8\uc74c.
\u00a0\u00a0\uc989 \ubc18\ub4dc\uc2dc Ordering\uc774 \ud544\uc694\ud558\uba74 “GROUP BY”\uc640 \ud568\uaed8 “ORDER BY”\ub97c \uae30\uc220\ud574\uc57c \ud568.
\u00a0\u00a0(\ucc38\uace0. \uc774\ub294 Oracle\uc758 Bug\uc740 \uc544\ub2c8\uba70 App\uc758 \uc798\ubabb\uc784)
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

======================================================================
\n<\/span><\/p>\n

>>>> New in-Memory Sort Algorithm \uc758 \ubb38\uc81c\uc810\uc778 “GROUP BY”\ub97c \uae30\uc874 \ubc29\uc2dd\uc73c\ub85c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294?<\/strong><\/span>
———————————————————————
1. Optimizer Mode\uac00 RULE<\/strong><\/span>\uc77c \uacbd\uc6b0\ub294 \ubc1c\uc0dd \uc548\ud568.
2. OPTIMIZER_FEATURES_ENABLE\ub97c 10.1<\/strong><\/span>\u00a0\u00a0\ub85c \ud568
3. init.ora “_gby_hash_aggregation_enabled”=FALSE<\/strong><\/span>\u00a0\u00a0(New\ubc29\uc2dd \uc0ac\uc6a9 \uc548\ud568)
\uc704\uc758 \ubc29\uc2dd \uc911 3\ubc88\uc774 \ud574\ub2f9 \uae30\ub2a5 \ub9cc \ub9c9\uc73c\ubbc0\ub85c \uac00\uc7a5 \ub9ce\uc774 \uc0ac\uc6a9\ub420 \uac83\uc784.
\uadf8\ub7ec\ub098 New in-Memory Sort Algorithm\uc740 \uc544\uc8fc \uc720\uc6a9\ud55c \ubc29\uc2dd\uc774\ubbc0\ub85c App\ub97c \uc218\uc815\ud560 \uac83\uc744 \uace0\uac1d\ub4e4\uc5d0 \uad8c\uc7a5\ud560 \ud544\uc694\uac00 \uc788\uc74c.
\uad00\ub828 Doc <\/strong><\/span>\u00e8<\/strong><\/span> Note:295819.1<\/strong> , Bug : 4604970<\/strong>
\n\t\t\t\t\t\t\t\t<\/span><\/span><\/p>\n

Subject:\u00a0\u00a0Upgrading from 9i to 10g – Potential Query Tuning Related Issues<\/span><\/p>\n<\/td>\n<\/tr>\n<\/tbody>\n<\/table>\n<\/div>\n

\n\u00a0<\/p>\n

## \uc2e4\ubb34\uc0ac\ub840 ##
\n<\/strong><\/span><\/p>\n

\n\u00a0<\/p>\n

select r,
\n<\/span><\/p>\n

s1,
\n<\/span><\/p>\n

s2,
\n<\/span><\/p>\n

s3,
\n<\/span><\/p>\n

s4,
\n<\/span><\/p>\n

s5,
\n<\/span><\/p>\n

s6
\n<\/span><\/p>\n

from (
\n<\/span><\/p>\n

select ” r,
\n<\/span><\/p>\n

” s1,
\n<\/span><\/p>\n

‘\ucd1d\uacc4’ s2,
\n<\/span><\/p>\n

c3 s3,
\n<\/span><\/p>\n

decode(c4, null, ‘-‘, 0, ‘-‘, round((c3 – c4)*100\/c4, 1)) s4,
\n<\/span><\/p>\n

c1 s5,
\n<\/span><\/p>\n

decode(c2, null, ‘-‘, 0, ‘-‘, round((c1 – c2)*100\/c2, 1)) s6
\n<\/span><\/p>\n

from (
\n<\/span><\/p>\n

select sum(decode(yymm, ‘2006’ ||’04’, amts)) c1,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’-1||’04’, amts)) c2,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’-1||’12’, amts)) c3,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’-2||’12’, amts)) c4
\n<\/span><\/p>\n

from (
\n<\/span><\/p>\n

select \/*+ index (o kts_m2_x1) *\/yymm,
\n<\/span><\/p>\n

sum(decode(‘2’, 1, trade_amt, trade_amt_acm)) amts
\n<\/span><\/p>\n

from kts_m2 o
\n<\/span><\/p>\n

where yymm in (‘2006’||’04’,
\n<\/span><\/p>\n

‘2006’-1||’04’,
\n<\/span><\/p>\n

‘2006’-1||’12’,
\n<\/span><\/p>\n

‘2006’-2||’12’)
\n<\/span><\/p>\n

and ie_gbn = ‘E’
\n<\/span><\/p>\n

group by yymm ) )
\n<\/span><\/p>\n

union all
\n<\/span><\/p>\n

select to_char(rownum),
\n<\/span><\/p>\n

ctr_cd s1,
\n<\/span><\/p>\n

kor_name s2,
\n<\/span><\/p>\n

decode(amt4, null, 0, 0, 0, amt4) s3,
\n<\/span><\/p>\n

amt3 s4,
\n<\/span><\/p>\n

decode(amt2, null, 0, 0, 0, amt2) s5,
\n<\/span><\/p>\n

amt1 s6
\n<\/span><\/p>\n

from (
\n<\/span><\/p>\n

select max(a.ctr_cd) ctr_cd,
\n<\/span><\/p>\n

max(b.kor_name) kor_name,
\n<\/span><\/p>\n

max(a.s2) amt4,
\n<\/span><\/p>\n

max(decode(a.s2, null, ‘-‘, decode(a.s1, null, ‘-‘, 0, ‘-‘, round(100*(a.s2-a.s1)\/a.s1, 1)))) amt3,
\n<\/span><\/p>\n

-(-a.s4) amt2,
\n<\/span><\/p>\n

max(decode(a.s4, null, ‘-‘, decode(a.s3, null, ‘-‘, 0, ‘-‘, round(100*(a.s4-a.s3)\/a.s3, 1)))) amt1
\n<\/span><\/p>\n

from ctr_code b,
\n<\/span><\/p>\n

(
\n<\/span><\/p>\n

select \/*+ index ( kts_ctr kts_ctr_x1 ) *\/ctr_cd ctr_cd,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’-2||’12’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s1,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’-1||’12’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s2,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’-1||’04’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s3,
\n<\/span><\/p>\n

sum(decode(yymm, ‘2006’ ||’04’, decode(‘2’, ‘1’, exp_amt, exp_amt_acm))) s4
\n<\/span><\/p>\n

from kts_ctr
\n<\/span><\/p>\n

where yymm in (‘2006′-2||’12’,
\n<\/span><\/p>\n

‘2006’-1||’12’,
\n<\/span><\/p>\n

‘2006’-1||’04’,
\n<\/span><\/p>\n

‘2006’||’04’)
\n<\/span><\/p>\n

and e_gbn = ‘E’
\n<\/span><\/p>\n

group by ctr_cd ) a
\n<\/span><\/p>\n

where a.ctr_cd = b.ctr_cd
\n<\/span><\/p>\n


\n\t\t\tgroup by -a.s4, a.ctr_cd<\/span>
\n\t\t\t<\/strong>) )
\n<\/span><\/p>\n

where 1 = 1
\n<\/span><\/p>\n

and rownum<=11
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

8i Plan
\n<\/strong><\/span><\/p>\n

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=23 Card=11 Bytes=1K)
\n<\/span><\/p>\n

1 0 COUNT (STOPKEY)
\n<\/span><\/p>\n

2 1 VIEW (Cost=23 Card=11 Bytes=1K)
\n<\/span><\/p>\n

3 2 UNION-ALL
\n<\/span><\/p>\n

4 3 VIEW (Cost=8 Card=1 Bytes=52)
\n<\/span><\/p>\n

5 4 FILTER
\n<\/span><\/p>\n

6 5 SORT (AGGREGATE) (Card=1 Bytes=18)
\n<\/span><\/p>\n

7 6 VIEW (Cost=8 Card=3 Bytes=54)
\n<\/span><\/p>\n

8 7 SORT (GROUP BY) (Card=3 Bytes=111)
\n<\/span><\/p>\n

9 8 CONCATENATION
\n<\/span><\/p>\n

10 9 INLIST ITERATOR
\n<\/span><\/p>\n

11 10 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (Cost=3 Card=1 Bytes=37)
\n<\/span><\/p>\n

12 11 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (UNIQUE) (Cost=2 Card=1)
\n<\/span><\/p>\n

13 9 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (Cost=3 Card=1 Bytes=37)
\n<\/span><\/p>\n

14 13 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (UNIQUE) (Cost=2 Card=1)
\n<\/span><\/p>\n

15 3 COUNT
\n<\/span><\/p>\n

16 15 VIEW (Cost=15 Card=10 Bytes=1000)
\n<\/span><\/p>\n

17 16 SORT (GROUP BY) (Cost=15 Card=10 Bytes=860)
\n<\/span><\/p>\n

18 17 HASH JOIN (Cost=13 Card=10 Bytes=860)
\n<\/span><\/p>\n

19 18 VIEW (Cost=11 Card=6 Bytes=330)
\n<\/span><\/p>\n

20 19 SORT (GROUP BY) (Card=6 Bytes=246)
\n<\/span><\/p>\n

21 20 CONCATENATION
\n<\/span><\/p>\n

22 21 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (Cost=3 Card=1 Bytes=41)
\n<\/span><\/p>\n

23 22 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (UNIQUE) (Cost=2 Card=1)
\n<\/span><\/p>\n

24 21 INLIST ITERATOR
\n<\/span><\/p>\n

25 24 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (Cost=3 Card=1 Bytes=41)
\n<\/span><\/p>\n

26 25 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (UNIQUE) (Cost=2 Card=1)
\n<\/span><\/p>\n


\n\t\t\t27 18 TABLE ACCESS (FULL) OF ‘KSTAT.CTR_CODE’ (Cost=1 Card=164 Bytes=5K)
\n<\/span><\/span><\/p>\n

\n\u00a0<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\ucd1d\uacc4\u00a0\u00a0\u00a0\u00a0284418742503\u00a0\u00a0\u00a0\u00a012\u00a0\u00a0\u00a0\u00a099534827771\u00a0\u00a0\u00a0\u00a011
\n<\/span><\/p>\n

1\u00a0\u00a0\u00a0\u00a0CN\u00a0\u00a0\u00a0\u00a0\uc911\uad6d\u00a0\u00a0\u00a0\u00a061914983215\u00a0\u00a0\u00a0\u00a024.4\u00a0\u00a0\u00a0\u00a020827196905\u00a0\u00a0\u00a0\u00a012.1
\n<\/span><\/p>\n

2\u00a0\u00a0\u00a0\u00a0US\u00a0\u00a0\u00a0\u00a0\ubbf8\uad6d\u00a0\u00a0\u00a0\u00a041342584390\u00a0\u00a0\u00a0\u00a0-3.5\u00a0\u00a0\u00a0\u00a013858758704\u00a0\u00a0\u00a0\u00a0.9
\n<\/span><\/p>\n

3\u00a0\u00a0\u00a0\u00a0JP\u00a0\u00a0\u00a0\u00a0\uc77c\ubcf8\u00a0\u00a0\u00a0\u00a024027437900\u00a0\u00a0\u00a0\u00a010.7\u00a0\u00a0\u00a0\u00a08299955234\u00a0\u00a0\u00a0\u00a09
\n<\/span><\/p>\n

4\u00a0\u00a0\u00a0\u00a0HK\u00a0\u00a0\u00a0\u00a0\ud64d\ucf69\u00a0\u00a0\u00a0\u00a015531092215\u00a0\u00a0\u00a0\u00a0-14.3\u00a0\u00a0\u00a0\u00a05875131009\u00a0\u00a0\u00a0\u00a025.1
\n<\/span><\/p>\n

5\u00a0\u00a0\u00a0\u00a0TW\u00a0\u00a0\u00a0\u00a0\ub300\ub9cc\u00a0\u00a0\u00a0\u00a010862932443\u00a0\u00a0\u00a0\u00a010.3\u00a0\u00a0\u00a0\u00a03795723262\u00a0\u00a0\u00a0\u00a09.8
\n<\/span><\/p>\n

6\u00a0\u00a0\u00a0\u00a0DE\u00a0\u00a0\u00a0\u00a0\ub3c5\uc77c\u00a0\u00a0\u00a0\u00a010303964211\u00a0\u00a0\u00a0\u00a023.6\u00a0\u00a0\u00a0\u00a03254077284\u00a0\u00a0\u00a0\u00a0-5.6
\n<\/span><\/p>\n

7\u00a0\u00a0\u00a0\u00a0SG\u00a0\u00a0\u00a0\u00a0\uc2f1\uac00\ud3ec\ub974\u00a0\u00a0\u00a0\u00a07406634297\u00a0\u00a0\u00a0\u00a031\u00a0\u00a0\u00a0\u00a02832927989\u00a0\u00a0\u00a0\u00a038.9
\n<\/span><\/p>\n

8\u00a0\u00a0\u00a0\u00a0IN\u00a0\u00a0\u00a0\u00a0\uc778\ub514\uc544(\uc778\ub3c4)\u00a0\u00a0\u00a0\u00a04597836954\u00a0\u00a0\u00a0\u00a026.6\u00a0\u00a0\u00a0\u00a01787342741\u00a0\u00a0\u00a0\u00a032.9
\n<\/span><\/p>\n

9\u00a0\u00a0\u00a0\u00a0GB\u00a0\u00a0\u00a0\u00a0\uc601\uad6d\u00a0\u00a0\u00a0\u00a05338843555\u00a0\u00a0\u00a0\u00a0-3.2\u00a0\u00a0\u00a0\u00a01738720242\u00a0\u00a0\u00a0\u00a07.3
\n<\/span><\/p>\n

10\u00a0\u00a0\u00a0\u00a0AU\u00a0\u00a0\u00a0\u00a0\ud638\uc8fc\u00a0\u00a0\u00a0\u00a03812063148\u00a0\u00a0\u00a0\u00a012.8\u00a0\u00a0\u00a0\u00a01714611147\u00a0\u00a0\u00a0\u00a044.7
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

10g Plan
\n<\/strong><\/span><\/p>\n

0 SELECT STATEMENT Optimizer=RULE<\/span> (Cost=8 Card=3 Bytes=366)
\n<\/span><\/p>\n

1 0 COUNT (STOPKEY)
\n<\/span><\/p>\n

2 1 VIEW (Cost=8 Card=3 Bytes=366)
\n<\/span><\/p>\n

3 2 UNION-ALL
\n<\/span><\/p>\n

4 3 VIEW (Cost=3 Card=1 Bytes=52)
\n<\/span><\/p>\n

5 4 FILTER
\n<\/span><\/p>\n

6 5 SORT (AGGREGATE) (Card=1 Bytes=21)
\n<\/span><\/p>\n

7 6 VIEW (Cost=3 Card=2 Bytes=42)
\n<\/span><\/p>\n

8 7 HASH (GROUP BY) (Card=2 Bytes=74)
\n<\/span><\/p>\n

9 8 CONCATENATION
\n<\/span><\/p>\n

10 9 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (TABLE) (Cost=1 Card=1 Bytes=37)
\n<\/span><\/p>\n

11 10 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (INDEX (UNIQUE)) (Cost=1 Card=1)
\n<\/span><\/p>\n

12 9 INLIST ITERATOR
\n<\/span><\/p>\n

13 12 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_M2’ (TABLE) (Cost=1 Card=1 Bytes=37)
\n<\/span><\/p>\n

14 13 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_M2_X1’ (INDEX (UNIQUE)) (Cost=1 Card=1)
\n<\/span><\/p>\n

15 3 COUNT
\n<\/span><\/p>\n

16 15 VIEW (Cost=5 Card=2 Bytes=202)
\n<\/span><\/p>\n

17 16 HASH (GROUP BY) (Cost=5 Card=2 Bytes=174)
\n<\/span><\/p>\n

18 17 NESTED LOOPS (Cost=4 Card=2 Bytes=174)
\n<\/span><\/p>\n

19 18 VIEW (Cost=3 Card=2 Bytes=112)
\n<\/span><\/p>\n

20 19 HASH (GROUP BY) (Card=2 Bytes=82)
\n<\/span><\/p>\n

21 20 CONCATENATION
\n<\/span><\/p>\n

22 21 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (TABLE) (Cost=1 Card=1 Bytes=41)
\n<\/span><\/p>\n

23 22 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (INDEX (UNIQUE)) (Cost=1 Card=8)
\n<\/span><\/p>\n

24 21 INLIST ITERATOR
\n<\/span><\/p>\n

25 24 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.KTS_CTR’ (TABLE) (Cost=1 Card=1 Bytes=41)
\n<\/span><\/p>\n

26 25 INDEX (RANGE SCAN) OF ‘KSTAT.KTS_CTR_X1’ (INDEX (UNIQUE)) (Cost=1 Card=8)
\n<\/span><\/p>\n

27 18 TABLE ACCESS (BY INDEX ROWID) OF ‘KSTAT.CTR_CODE’ (TABLE) (Cost=1 Card=1 Bytes=31)
\n<\/span><\/p>\n

28 27 INDEX (UNIQUE SCAN) OF ‘KSTAT.CTR_CODE_PK’ (INDEX (UNIQUE)) (Cost=1 Card=1)
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\ucd1d\uacc4\u00a0\u00a0\u00a0\u00a0284418742503\u00a0\u00a0\u00a0\u00a012\u00a0\u00a0\u00a0\u00a099534827771\u00a0\u00a0\u00a0\u00a011
\n<\/span><\/p>\n

1\u00a0\u00a0\u00a0\u00a0AD\u00a0\u00a0\u00a0\u00a0\uc548\ub3c4\ub77c\u00a0\u00a0\u00a0\u00a0832819\u00a0\u00a0\u00a0\u00a0-34.4\u00a0\u00a0\u00a0\u00a0314756\u00a0\u00a0\u00a0\u00a0101.1
\n<\/span><\/p>\n

2\u00a0\u00a0\u00a0\u00a0AE\u00a0\u00a0\u00a0\u00a0\uc544\ub78d\uc5d0\ubbf8\ub9ac\ud2b8 \uc5f0\ud569\u00a0\u00a0\u00a0\u00a02732731530\u00a0\u00a0\u00a0\u00a05.6\u00a0\u00a0\u00a0\u00a0905643855\u00a0\u00a0\u00a0\u00a0-5.3
\n<\/span><\/p>\n

3\u00a0\u00a0\u00a0\u00a0AF\u00a0\u00a0\u00a0\u00a0\uc544\ud504\uce74\ub2c8\uc2a4\ud0c4\u00a0\u00a0\u00a0\u00a059631357\u00a0\u00a0\u00a0\u00a0-22.7\u00a0\u00a0\u00a0\u00a019712121\u00a0\u00a0\u00a0\u00a0-3.7
\n<\/span><\/p>\n

4\u00a0\u00a0\u00a0\u00a0AG\u00a0\u00a0\u00a0\u00a0\uc548\ud2f0\uac00 \ubc14\ubd80\ub2e4\u00a0\u00a0\u00a0\u00a04588172\u00a0\u00a0\u00a0\u00a070.4\u00a0\u00a0\u00a0\u00a02389520\u00a0\u00a0\u00a0\u00a086.9
\n<\/span><\/p>\n

5\u00a0\u00a0\u00a0\u00a0AI\u00a0\u00a0\u00a0\u00a0\uc559\uadc8\ub77c\u00a0\u00a0\u00a0\u00a0135659\u00a0\u00a0\u00a0\u00a0-43.9\u00a0\u00a0\u00a0\u00a0205\u00a0\u00a0\u00a0\u00a0-99.6
\n<\/span><\/p>\n

6\u00a0\u00a0\u00a0\u00a0AL\u00a0\u00a0\u00a0\u00a0\uc54c\ubc14\ub2c8\uc544\u00a0\u00a0\u00a0\u00a06326901\u00a0\u00a0\u00a0\u00a0-20.3\u00a0\u00a0\u00a0\u00a07142128\u00a0\u00a0\u00a0\u00a0117.6
\n<\/span><\/p>\n

7\u00a0\u00a0\u00a0\u00a0AM\u00a0\u00a0\u00a0\u00a0\uc544\ub974\uba54\ub2c8\uc544\u00a0\u00a0\u00a0\u00a03149597\u00a0\u00a0\u00a0\u00a045\u00a0\u00a0\u00a0\u00a01658169\u00a0\u00a0\u00a0\u00a0142.3
\n<\/span><\/p>\n

8\u00a0\u00a0\u00a0\u00a0AN\u00a0\u00a0\u00a0\u00a0\ub124\ub35c\ub780\ub4dc \uc5f4\ub3c4\u00a0\u00a0\u00a0\u00a010756525\u00a0\u00a0\u00a0\u00a050.5\u00a0\u00a0\u00a0\u00a04937642\u00a0\u00a0\u00a0\u00a078.2
\n<\/span><\/p>\n

9\u00a0\u00a0\u00a0\u00a0AO\u00a0\u00a0\u00a0\u00a0\uc559\uace8\ub77c\u00a0\u00a0\u00a0\u00a01517394108\u00a0\u00a0\u00a0\u00a0-16.4\u00a0\u00a0\u00a0\u00a0203785473\u00a0\u00a0\u00a0\u00a0-86.1
\n<\/span><\/p>\n

10\u00a0\u00a0\u00a0\u00a0AQ\u00a0\u00a0\u00a0\u00a0\uc548\ud0c0\ud2f0\uce74\u00a0\u00a0\u00a0\u00a0132169\u00a0\u00a0\u00a0\u00a0–\u00a0\u00a0\u00a0\u00a00\u00a0\u00a0\u00a0\u00a0–
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

\n\u00a0<\/p>\n

## \ud574\uacb0\ucc45 ##
\n<\/strong><\/span><\/p>\n

Init.ora \uc758 _gby_hash_aggregation_enabled=FALSE \uc758 \uacbd\uc6b0 group by \uc808 \ub2e4\uc74c\uc5d0 Single Column \uc778 \uacbd\uc6b0\uc5d0\ub294
\n<\/strong><\/span><\/p>\n

\uc815\uc0c1\uc801\uc778 \uacb0\uacfc\uac00 \ub098\uc624\uc9c0\ub9cc<\/strong> 2\uac1c \uc774\uc0c1\uc758 Column \uc774 \ub098\uc624\ub294 \uacbd\uc6b0\uc5d0\ub294 \uacb0\uacfc\uac00 \ub2ec\ub77c\uc9c4\ub2e4.<\/span> \uc989 \uc704 Union all \uc758
\n<\/span><\/p>\n

From \uc808 In-Line View \uc758 group by \u2013a.s4 \uc758 \ud558\ub098\uc758 Column \uc73c\ub85c \uacb0\uacfc\ub97c \uad6c\ud558\uba74 \uc815\uc0c1\uc801\uc73c\ub85c Sorting \uc774
\n<\/span><\/p>\n

\ub418\uc5b4\uc838\uc11c \uacb0\uacfc\uac00 \ub098\uc624\uc9c0\ub9cc, \uc608\ucc98\ub7fc \u2013a.s4, a.ctr_cd \ucc98\ub7fc Column \uc744 \ud558\ub098 \ub354 \ucd94\uac00\ud558\uba74 \uacb0\uacfc\uac00 \ub2ec\ub77c\uc9c4\ub2e4.
\n<\/span><\/p>\n

\uc544\ub798\uc640 \uac19\uc774 group by \uc808 \ub2e4\uc74c\uc5d0 order by \uc808\uc744 \ucd94\uac00\ud558\uc5ec \uc900\ub2e4.
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

where a.ctr_cd = b.ctr_cd
\n<\/span><\/p>\n

group by -a.s4, a.ctr_cd
\n<\/span><\/p>\n

order by \u2013a.s4,a.ctr_cd<\/strong><\/span> ) )
\n<\/span><\/p>\n

\n\u00a0<\/p>\n

\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\ucd1d\uacc4\u00a0\u00a0\u00a0\u00a0284418742503\u00a0\u00a0\u00a0\u00a012\u00a0\u00a0\u00a0\u00a099534827771\u00a0\u00a0\u00a0\u00a011
\n<\/span><\/p>\n

1\u00a0\u00a0\u00a0\u00a0CN\u00a0\u00a0\u00a0\u00a0\uc911\uad6d\u00a0\u00a0\u00a0\u00a061914983215\u00a0\u00a0\u00a0\u00a024.4\u00a0\u00a0\u00a0\u00a020827196905\u00a0\u00a0\u00a0\u00a012.1
\n<\/span><\/p>\n

2\u00a0\u00a0\u00a0\u00a0US\u00a0\u00a0\u00a0\u00a0\ubbf8\uad6d\u00a0\u00a0\u00a0\u00a041342584390\u00a0\u00a0\u00a0\u00a0-3.5\u00a0\u00a0\u00a0\u00a013858758704\u00a0\u00a0\u00a0\u00a0.9
\n<\/span><\/p>\n

3\u00a0\u00a0\u00a0\u00a0JP\u00a0\u00a0\u00a0\u00a0\uc77c\ubcf8\u00a0\u00a0\u00a0\u00a024027437900\u00a0\u00a0\u00a0\u00a010.7\u00a0\u00a0\u00a0\u00a08299955234\u00a0\u00a0\u00a0\u00a09
\n<\/span><\/p>\n

4\u00a0\u00a0\u00a0\u00a0HK\u00a0\u00a0\u00a0\u00a0\ud64d\ucf69\u00a0\u00a0\u00a0\u00a015531092215\u00a0\u00a0\u00a0\u00a0-14.3\u00a0\u00a0\u00a0\u00a05875131009\u00a0\u00a0\u00a0\u00a025.1
\n<\/span><\/p>\n

5\u00a0\u00a0\u00a0\u00a0TW\u00a0\u00a0\u00a0\u00a0\ub300\ub9cc\u00a0\u00a0\u00a0\u00a010862932443\u00a0\u00a0\u00a0\u00a010.3\u00a0\u00a0\u00a0\u00a03795723262\u00a0\u00a0\u00a0\u00a09.8
\n<\/span><\/p>\n

6\u00a0\u00a0\u00a0\u00a0DE\u00a0\u00a0\u00a0\u00a0\ub3c5\uc77c\u00a0\u00a0\u00a0\u00a010303964211\u00a0\u00a0\u00a0\u00a023.6\u00a0\u00a0\u00a0\u00a03254077284\u00a0\u00a0\u00a0\u00a0-5.6
\n<\/span><\/p>\n

7\u00a0\u00a0\u00a0\u00a0SG\u00a0\u00a0\u00a0\u00a0\uc2f1\uac00\ud3ec\ub974\u00a0\u00a0\u00a0\u00a07406634297\u00a0\u00a0\u00a0\u00a031\u00a0\u00a0\u00a0\u00a02832927989\u00a0\u00a0\u00a0\u00a038.9
\n<\/span><\/p>\n

8\u00a0\u00a0\u00a0\u00a0IN\u00a0\u00a0\u00a0\u00a0\uc778\ub514\uc544(\uc778\ub3c4)\u00a0\u00a0\u00a0\u00a04597836954\u00a0\u00a0\u00a0\u00a026.6\u00a0\u00a0\u00a0\u00a01787342741\u00a0\u00a0\u00a0\u00a032.9
\n<\/span><\/p>\n

9\u00a0\u00a0\u00a0\u00a0GB\u00a0\u00a0\u00a0\u00a0\uc601\uad6d\u00a0\u00a0\u00a0\u00a05338843555\u00a0\u00a0\u00a0\u00a0-3.2\u00a0\u00a0\u00a0\u00a01738720242\u00a0\u00a0\u00a0\u00a07.3
\n<\/span><\/p>\n

10\u00a0\u00a0\u00a0\u00a0AU\u00a0\u00a0\u00a0\u00a0\ud638\uc8fc\u00a0\u00a0\u00a0\u00a03812063148\u00a0\u00a0\u00a0\u00a012.8\u00a0\u00a0\u00a0\u00a01714611147\u00a0\u00a0\u00a0\u00a044.7
\n<\/span><\/p>\n","protected":false},"excerpt":{"rendered":"

Group by \uc808\uc5d0 \uc758\ud55c \uc790\ub3d9\uc815\ub82c\uc774 \ub418\uc9c0 \uc54a\ub294 \uc774\uc720 \u00a0 Oracle 10g R2\ubd80\ud130 Group By\uc808\uc5d0 \uc758\ud55c\u00a0Grouping Column\uc21c\uc73c\ub85c Sorting\ub418\uc9c0 \uc54a\ub294 \ubb38\uc81c\uc5d0 \ub300\ud55c \uc811\uadfc \ubc29\uc2dd\uc785\ub2c8\ub2e4.\uae30\uc874\uc758 \uace0\uac1d\uc774 Order by\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0 Group By\ub9cc\uc744 \uc0ac\uc6a9\ud558\uc600\ub2e4\uba74\u00a0\ubb38\uc81c\ub97c\u00a0\uc81c\uae30\ud560 \uc218 \uc788\uc744 \uac83\uc785\ub2c8\ub2e4. \uc774\ub7ec\ud55c \ubb38\uc81c\uc5d0 \ub300\ud55c \ub300\ucc98\ubc29\uc2dd\uc785\ub2c8\ub2e4. \u00a0 Oracle 10g R2\uc758 “New in-Memory Sort Algorithm”\uc5d0 \ub530\ub978 \ubb38\uc81c\uc810 \ubc0f \uac1c\uc120\uc810 ======================================================================>>>> Oracle 10g R2 New Feature […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[14],"tags":[788],"jetpack_featured_media_url":"","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1669"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=1669"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1669\/revisions"}],"predecessor-version":[{"id":1670,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/1669\/revisions\/1670"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=1669"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=1669"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=1669"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}