DECODE 함수 를 이용한 IF 처리의 효율화

사례연구 4] DECODE 함수를 이용한 IF 처리의 효율화

- 요구사항

오라클의 DECODE 함수는 'IF ... THEN ... ELSE' 에 해당하는 기능을
갖고 있습니다. 그러나 현실적으로 다양한 요구사항에 대한 표현에 있
어 제약사항이 많이 있다는 것이 문제라 할 수 있습니다.

이러한 DECODE 함수를 이용한 IF 처리를 하고자 할 때 어떻게 하면 보다
더 효율적으로 사용할 수 있는지를 찾아 보고자 합니다.

 

EC_EXAM_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + EXAM_NO +
MEMBER_TYPE + MEMBER_ID

※ 참조

데이터 타입 사용 가능한 함수
----------- -----------------------
숫자형 SIGN(), FLOOR(), CEIL(), TRUNC()
문자형 ASCII()
무 시 GREATEST(), LEAST()

숫자형)

비교조건 사용 가능한 함수
-------- ------------------------
= 사용 안해도 됨
< , > SIGN() 또는 FLOOR() 사용
<=, >= SIGN() 또는 CEIL() 사용

문자형)

비교조건 사용 가능한 함수
-------- -------------------------
= 사용 안해도 됨
<, > 한 자리 :
ASCII() 와 함께 SIGN() 사용
자리수 무방 :
LEAST() 사용
(단, 원래값||' ' 표현식 사용)
<=, >= 한 자리 :
ASCII() 와 함께 SIGN() 사용
자리수 무방 :
GREASTEST 또는 LEAST() 사용

 

※ SQL#04_01 ~ SQL#04_05 : ~보다 작다의 연산

IF RESULT < 10 THEN 'A'
ELSE IF RESULT < 20 THEN 'B'
ELSE IF RESULT < 30 THEN 'C'
ELSE IF RESULT < 40 THEN 'D'
ELSE IF RESULT < 50 THEN 'E'
ELSE IF RESULT < 60 THEN 'F'
ELSE IF RESULT < 70 THEN 'G'
ELSE IF RESULT < 80 THEN 'H'
ELSE IF RESULT < 90 THEN 'I'
ELSE 'J'
END IF

Page 33 : SQL#04_01

SELECT YEAR,
DECODE(SIGN(RESULT - 10),-1,'A', DECODE(SIGN(RESULT - 20),-1,'B',
DECODE(SIGN(RESULT - 30),-1,'C', DECODE(SIGN(RESULT - 40),-1,'D',
DECODE(SIGN(RESULT - 50),-1,'E', DECODE(SIGN(RESULT - 60),-1,'F',
DECODE(SIGN(RESULT - 70),-1,'G', DECODE(SIGN(RESULT - 80),-1,'H',
DECODE(SIGN(RESULT - 90),-1,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(SIGN(RESULT - 10),-1,'A', DECODE(SIGN(RESULT - 20),-1,'B',
DECODE(SIGN(RESULT - 30),-1,'C', DECODE(SIGN(RESULT - 40),-1,'D',
DECODE(SIGN(RESULT - 50),-1,'E', DECODE(SIGN(RESULT - 60),-1,'F',
DECODE(SIGN(RESULT - 70),-1,'G', DECODE(SIGN(RESULT - 80),-1,'H',
DECODE(SIGN(RESULT - 90),-1,'I','J')))))))));

 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.04 1 0 1 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 16.76 16.82 5180 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 16.77 16.86 5181 5180 5 50

Rows Row Source Operation
------- ---------------------------------------------------
50 SORT GROUP BY
504056 TABLE ACCESS FULL EC_EXAM_APPLY

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

 

Q.위 SQL의 문제점 및 해결방안은 무엇입니까?
1) 지나치게 많은 함수 사용
2) 50만건의 data를 각 data별로 연산처리함 ('-', sign(), decode())

※ 참조 : 문제점 및 해결방안에 대한 설명 (Page 34 - 35)

 

Page 36 : SQL#04_02 (연산만 변경해 봄)
함수 변경 SIGN -> FLOOR
SIGN에 비해 속도가 느리다

SELECT YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/20), 0,'B',
DECODE(FLOOR(RESULT/30), 0,'C', DECODE(FLOOR(RESULT/40), 0,'D',
DECODE(FLOOR(RESULT/50), 0,'E', DECODE(FLOOR(RESULT/60), 0,'F',
DECODE(FLOOR(RESULT/70), 0,'G', DECODE(FLOOR(RESULT/80), 0,'H',
DECODE(FLOOR(RESULT/90), 0,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/20), 0,'B',
DECODE(FLOOR(RESULT/30), 0,'C', DECODE(FLOOR(RESULT/40), 0,'D',
DECODE(FLOOR(RESULT/50), 0,'E', DECODE(FLOOR(RESULT/60), 0,'F',
DECODE(FLOOR(RESULT/70), 0,'G', DECODE(FLOOR(RESULT/80), 0,'H',
DECODE(FLOOR(RESULT/90), 0,'I','J')))))))));

-> FLOOR()에 의한 return 값에 대한 설명(Page 36 참조)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.01 0 0 0 0
Fetch 5 24.09 24.38 5180 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 24.10 24.39 5180 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

 

Q.이와 같이 FLOOR 함수를 사용한 경우 SQL#04_01과 비교해 보면 성능이
어떻습니까?--> 속도가 느림.

 

Page 38 : SQL#04_03 --> Group by를 이용해서 건수를 줄임

SELECT YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/20), 0,'B',
DECODE(FLOOR(RESULT/30), 0,'C', DECODE(FLOOR(RESULT/40), 0,'D',
DECODE(FLOOR(RESULT/50), 0,'E', DECODE(FLOOR(RESULT/60), 0,'F',
DECODE(FLOOR(RESULT/70), 0,'G', DECODE(FLOOR(RESULT/80), 0,'H',
DECODE(FLOOR(RESULT/90), 0,'I','J'))))))))) AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, RESULT, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, RESULT)
GROUP BY YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/20), 0,'B',
DECODE(FLOOR(RESULT/30), 0,'C', DECODE(FLOOR(RESULT/40), 0,'D',
DECODE(FLOOR(RESULT/50), 0,'E', DECODE(FLOOR(RESULT/60), 0,'F',
DECODE(FLOOR(RESULT/70), 0,'G', DECODE(FLOOR(RESULT/80), 0,'H',
DECODE(FLOOR(RESULT/90), 0,'I','J')))))))));

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 4.42 4.73 5180 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 4.43 4.74 5180 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
589 VIEW
589 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

 

--> 함수의 사용 갯수는 줄어 들지 않고 있음
단 적은 수의 row가 계산에 참여하게 됨(group 때문에 589건만 참여)

 

Page 40 : SQL#04_04 -> 함수 갯수 줄이기

SELECT YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/10), 1,'B',
DECODE(FLOOR(RESULT/10), 2,'C', DECODE(FLOOR(RESULT/10), 3,'D',
DECODE(FLOOR(RESULT/10), 4,'E', DECODE(FLOOR(RESULT/10), 5,'F',
DECODE(FLOOR(RESULT/10), 6,'G', DECODE(FLOOR(RESULT/10), 7,'H',
DECODE(FLOOR(RESULT/10), 8,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/10), 1,'B',
DECODE(FLOOR(RESULT/10), 2,'C', DECODE(FLOOR(RESULT/10), 3,'D',
DECODE(FLOOR(RESULT/10), 4,'E', DECODE(FLOOR(RESULT/10), 5,'F',
DECODE(FLOOR(RESULT/10), 6,'G', DECODE(FLOOR(RESULT/10), 7,'H',
DECODE(FLOOR(RESULT/10), 8,'I','J')))))))));

-> FLOOR()에 의한 return 값에 대한 설명(Page 40 참조)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 20.01 20.67 5177 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 20.02 20.68 5177 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

 

--> 함수만 통일해도 시간이 수초 빨라짐

 

Q.이와 같이 FLOOR 함수를 사용한 경우 SQL#04_02과 비교해 보면 성능이
어떻습니까?

Page 41 - 43 : SQL#04_05 -> 함수을 줄임

SELECT YEAR,
DECODE(CLASS, 0,'A', 1, 'B', 2,'C', 3,'D', 4,'E', 5,'F',
6,'G', 7,'H', 8,'I','J') AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, FLOOR(RESULT/10) CLASS, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, FLOOR(RESULT/10))
GROUP BY YEAR,
DECODE(CLASS, 0,'A', 1, 'B', 2,'C', 3,'D', 4,'E', 5,'F',
6,'G', 7,'H', 8,'I','J');

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 5.44 7.37 5173 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 5.44 7.37 5173 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
64 VIEW
64 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY

 

--> FLOOR(RESULT/10)가 view안에 존재함으로 group by하기 전에
모든 row에 대해 함수를 사용하므로 해당 함수를 group by
이후에 함수를 사용할 수 있도록 함

 

SELECT YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', 1, 'B', 2,'C', 3,'D',
4,'E', 5,'F', 6,'G', 7,'H', 8,'I','J') AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, RESULT, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, RESULT)
GROUP BY YEAR,
DECODE(FLOOR(RESULT/10), 0,'A', 1, 'B', 2,'C', 3,'D',
4,'E', 5,'F', 6,'G', 7,'H', 8,'I','J');

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 4.44 5.53 5180 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 4.44 5.53 5180 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
589 VIEW
589 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

Q.위의 2 개의 SQL의 성능을 비교하면 어떤 것이 더 좋습니까? 그 이유는
무엇입니까?

 

※ SQL#04_06 ~ SQL#04_11 - CEIL사용가능 (~보다 작거나 같다)

IF RESULT =< 10 THEN 'A'
ELSE IF RESULT =< 20 THEN 'B'
ELSE IF RESULT =< 30 THEN 'C'
ELSE IF RESULT =< 40 THEN 'D'
ELSE IF RESULT =< 50 THEN 'E'
ELSE IF RESULT =< 60 THEN 'F'
ELSE IF RESULT =< 70 THEN 'G'
ELSE IF RESULT =< 80 THEN 'H'
ELSE IF RESULT =< 90 THEN 'I'
ELSE 'J'
END IF

Page 45 : SQL#04_06

SELECT YEAR,
DECODE(SIGN(RESULT-10),-1,'A',0,'A', <-- 0 < RESULT <=10
DECODE(SIGN(RESULT-20),-1,'B',0,'B',
DECODE(SIGN(RESULT-30),-1,'C',0,'C',
DECODE(SIGN(RESULT-40),-1,'D',0,'D',
DECODE(SIGN(RESULT-50),-1,'E',0,'E',
DECODE(SIGN(RESULT-60),-1,'F',0,'F',
DECODE(SIGN(RESULT-70),-1,'G',0,'G',
DECODE(SIGN(RESULT-80),-1,'H',0,'H',
DECODE(SIGN(RESULT-90),-1,'I',0,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(SIGN(RESULT-10),-1,'A',0,'A',
DECODE(SIGN(RESULT-20),-1,'B',0,'B',
DECODE(SIGN(RESULT-30),-1,'C',0,'C',
DECODE(SIGN(RESULT-40),-1,'D',0,'D',
DECODE(SIGN(RESULT-50),-1,'E',0,'E',
DECODE(SIGN(RESULT-60),-1,'F',0,'F',
DECODE(SIGN(RESULT-70),-1,'G',0,'G',
DECODE(SIGN(RESULT-80),-1,'H',0,'H',
DECODE(SIGN(RESULT-90),-1,'I',0,'I','J')))))))));

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 16.41 17.55 5179 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 16.42 17.56 5179 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

 

Q.위 SQL의 문제점 무엇입니까?- 필요이상의 함수 사용
- 검색된 모든 row에 대한 함수 사용

Page 46 : SQL#04_07 <-

CEIL 함수 이용
속도는 sing보다 느림

SELECT YEAR,
DECODE(CEIL(RESULT/10),0,'A',1,'A', DECODE(CEIL(RESULT/20),1,'B',
DECODE(CEIL(RESULT/30),1,'C', DECODE(CEIL(RESULT/40),1,'D',
DECODE(CEIL(RESULT/50),1,'E', DECODE(CEIL(RESULT/60),1,'F',
DECODE(CEIL(RESULT/70),1,'G', DECODE(CEIL(RESULT/80),1,'H',
DECODE(CEIL(RESULT/90),1,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(CEIL(RESULT/10),0,'A',1,'A', DECODE(CEIL(RESULT/20),1,'B',
DECODE(CEIL(RESULT/30),1,'C', DECODE(CEIL(RESULT/40),1,'D',
DECODE(CEIL(RESULT/50),1,'E', DECODE(CEIL(RESULT/60),1,'F',
DECODE(CEIL(RESULT/70),1,'G', DECODE(CEIL(RESULT/80),1,'H',
DECODE(CEIL(RESULT/90),1,'I','J')))))))));

-> CEIL()에 의한 return 값에 대한 설명(Page 46 참조)

 

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 25.33 26.37 5179 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 25.34 26.38 5179 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

Q.이와 같이 CEIL 함수를 사용한 경우 SQL#04_06과 비교해 보면 성능이
어떻습니까?

Page 48 : SQL#04_08

<-- 함수 통합

SELECT YEAR,
DECODE(CEIL(RESULT/10),0,'A',1,'A', DECODE(CEIL(RESULT/10),2,'B',
DECODE(CEIL(RESULT/10),3,'C', DECODE(CEIL(RESULT/10),4,'D',
DECODE(CEIL(RESULT/10),5,'E', DECODE(CEIL(RESULT/10),6,'F',
DECODE(CEIL(RESULT/10),7,'G', DECODE(CEIL(RESULT/10),8,'H',
DECODE(CEIL(RESULT/10),9,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(CEIL(RESULT/10),0,'A',1,'A', DECODE(CEIL(RESULT/10),2,'B',
DECODE(CEIL(RESULT/10),3,'C', DECODE(CEIL(RESULT/10),4,'D',
DECODE(CEIL(RESULT/10),5,'E', DECODE(CEIL(RESULT/10),6,'F',
DECODE(CEIL(RESULT/10),7,'G', DECODE(CEIL(RESULT/10),8,'H',
DECODE(CEIL(RESULT/10),9,'I','J')))))))));

-> CEIL()에 의한 return 값에 대한 설명(Page 48 참조)

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 20.75 23.59 5175 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 20.76 23.60 5175 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

-->

함수이 통일로 약간의 시간 단축 효과가 있다

Q.이와 같이 CEIL 함수를 사용한 경우 SQL#04_07과 비교해 보면 성능이
어떻습니까?

 

Page 50 : SQL#04_09

SELECT YEAR,
DECODE(GREATEST(RESULT,10),10,'A',DECODE(GREATEST(RESULT,20),20,'B',
DECODE(GREATEST(RESULT,30),30,'C',DECODE(GREATEST(RESULT,40),40,'D',
DECODE(GREATEST(RESULT,50),50,'E',DECODE(GREATEST(RESULT,60),60,'F',
DECODE(GREATEST(RESULT,70),70,'G',DECODE(GREATEST(RESULT,80),80,'H',
DECODE(GREATEST(RESULT,90),90,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(GREATEST(RESULT,10),10,'A',DECODE(GREATEST(RESULT,20),20,'B',
DECODE(GREATEST(RESULT,30),30,'C',DECODE(GREATEST(RESULT,40),40,'D',
DECODE(GREATEST(RESULT,50),50,'E',DECODE(GREATEST(RESULT,60),60,'F',
DECODE(GREATEST(RESULT,70),70,'G',DECODE(GREATEST(RESULT,80),80,'H',
DECODE(GREATEST(RESULT,90),90,'I','J')))))))));

-> GREATEST()에 의한 return 값에 대한 설명(Page 50 참조)

Q.이와 같이 GREATEST 함수를 사용한 경우 SQL#04_08과 비교해 보면 성능이
어떻습니까?

Page 52 : SQL#04_10

SELECT YEAR,
DECODE(LEAST(RESULT,10),RESULT,'A',DECODE(LEAST(RESULT,20),RESULT,'B',
DECODE(LEAST(RESULT,30),RESULT,'C',DECODE(LEAST(RESULT,40),RESULT,'D',
DECODE(LEAST(RESULT,50),RESULT,'E',DECODE(LEAST(RESULT,60),RESULT,'F',
DECODE(LEAST(RESULT,70),RESULT,'G',DECODE(LEAST(RESULT,80),RESULT,'H',
DECODE(LEAST(RESULT,90),RESULT,'I','J'))))))))) AS CLASS,
COUNT(*)
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR,
DECODE(LEAST(RESULT,10),RESULT,'A',DECODE(LEAST(RESULT,20),RESULT,'B',
DECODE(LEAST(RESULT,30),RESULT,'C',DECODE(LEAST(RESULT,40),RESULT,'D',
DECODE(LEAST(RESULT,50),RESULT,'E',DECODE(LEAST(RESULT,60),RESULT,'F',
DECODE(LEAST(RESULT,70),RESULT,'G',DECODE(LEAST(RESULT,80),RESULT,'H',
DECODE(LEAST(RESULT,90),RESULT,'I','J')))))))));

-> LEAST()에 의한 return 값에 대한 설명(Page 52 참조)

Q.이와 같이 LEAST 함수를 사용한 경우 SQL#04_08과 비교해 보면 성능이
어떻습니까?

 

Page 54 : SQL#04_11

SELECT YEAR,
DECODE(CEIL(RESULT/10), 0,'A', 1,'A', 2,'B', 3,'C', 4,'D', 5,'E',
6,'F', 7,'G', 8,'H', 9,'I','J') AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, RESULT, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, RESULT)
GROUP BY YEAR,
DECODE(CEIL(RESULT/10), 0,'A', 1,'A', 2,'B', 3,'C', 4,'D', 5,'E',
6,'F', 7,'G', 8,'H', 9,'I','J');

다음의 SQL들과의 성능 비교를 통해서 어떠한 차이점이 있는지 구분하시길
바랍니다.

SELECT YEAR,
DECODE(CLASS, 0,'A', 1,'A', 2,'B', 3,'C', 4,'D', 5,'E', 6,'F',
7,'G', 8,'H', 9,'I','J') AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, CEIL(RESULT/10) CLASS, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, CEIL(RESULT/10))
GROUP BY YEAR,
DECODE(CLASS, 0,'A', 1,'A', 2, 'B', 3,'C', 4,'D', 5,'E', 6,'F',
7,'G', 8,'H', 9,'I','J');

Page 56 :

SELECT YEAR,
DECODE(GREATEST(RESULT,10),10,'A',DECODE(GREATEST(RESULT,20),20,'B',
DECODE(GREATEST(RESULT,30),30,'C',DECODE(GREATEST(RESULT,40),40,'D',
DECODE(GREATEST(RESULT,50),50,'E',DECODE(GREATEST(RESULT,60),60,'F',
DECODE(GREATEST(RESULT,70),70,'G',DECODE(GREATEST(RESULT,80),80,'H',
DECODE(GREATEST(RESULT,90),90,'I','J'))))))))) AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, RESULT, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, RESULT)
GROUP BY YEAR,
DECODE(GREATEST(RESULT,10),10,'A',DECODE(GREATEST(RESULT,20),20,'B',
DECODE(GREATEST(RESULT,30),30,'C',DECODE(GREATEST(RESULT,40),40,'D',
DECODE(GREATEST(RESULT,50),50,'E',DECODE(GREATEST(RESULT,60),60,'F',
DECODE(GREATEST(RESULT,70),70,'G',DECODE(GREATEST(RESULT,80),80,'H',
DECODE(GREATEST(RESULT,90),90,'I','J')))))))));

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 4.48 5.53 5178 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 4.49 5.53 5178 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
589 VIEW
589 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

Page 58 :

SELECT YEAR,
DECODE(LEAST(RESULT,10),RESULT,'A',DECODE(LEAST(RESULT,20),RESULT,'B',
DECODE(LEAST(RESULT,30),RESULT,'C',DECODE(LEAST(RESULT,40),RESULT,'D',
DECODE(LEAST(RESULT,50),RESULT,'E',DECODE(LEAST(RESULT,60),RESULT,'F',
DECODE(LEAST(RESULT,70),RESULT,'G',DECODE(LEAST(RESULT,80),RESULT,'H',
DECODE(LEAST(RESULT,90),RESULT,'I','J'))))))))) AS CLASS,
SUM(CNT)
FROM (SELECT YEAR, RESULT, COUNT(*) CNT
FROM EC_EXAM_APPLY
WHERE UPDATE_USER <> 'LANGUAGE_MANAGER'
GROUP BY YEAR, RESULT)
GROUP BY YEAR,
DECODE(LEAST(RESULT,10),RESULT,'A',DECODE(LEAST(RESULT,20),RESULT,'B',
DECODE(LEAST(RESULT,30),RESULT,'C',DECODE(LEAST(RESULT,40),RESULT,'D',
DECODE(LEAST(RESULT,50),RESULT,'E',DECODE(LEAST(RESULT,60),RESULT,'F',
DECODE(LEAST(RESULT,70),RESULT,'G',DECODE(LEAST(RESULT,80),RESULT,'H',
DECODE(LEAST(RESULT,90),RESULT,'I','J')))))))));

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.01 0.01 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 5 4.34 5.87 5177 5180 4 50
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 7 4.35 5.88 5177 5180 4 50

Rows Execution Plan
------- ---------------------------------------------------
0 SELECT STATEMENT GOAL: CHOOSE
50 SORT (GROUP BY)
589 VIEW
589 SORT (GROUP BY)
504056 TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY'

 

 

 

 

 

 

 

 

 

출처 : http://blog.daum.net/hoon_dap/65854