조건
1.Parameter P_var1 은 한자리 값을 가지는 Number 또는 Char 이다.
2.Sample Table 인 SAM_TB1은
사번 직급 급여 호봉
------ ---- ----- -----
980101 01 1000 1
970102 02 1500 4
980103 01 1100 2
960104 03 1800 8
980105 01 1300 3
970106 02 1500 5
980107 01 1200 2
950108 04 2200 12
960109 03 1700 7
950110 04 2100 11
980111 01 1000 1
930112 05 2500 15
940113 02 1700 5
940114 01 1700 4
940115 02 1700 7
와 같은 Data를 가진다.
3.P_var1의 값에 따라
'1' : 직급에 상관없는 사원의 직급별 급여평균
'2' : 직급이 '01'인 사원의 급여평균
'3' : 직급이 '02'인 사원의 급여평균
'4' : 직급이 '03'인 사원의 급여평균
'5' : 직급이 '04','05'인 사원의 직급별 급여평균
위와 같은 경우에 따라 각 값을 가질 수 있다.
가상으로 만들어낸 경우지만 충분히 있을 수 있는 경우다.
문제 .
각 경우에 따라 SQL문을 하나씩 작성하는 번거로움을 덜고
위의 경우를 모두 만족 시킬 수 있는 SQL을 만들고 싶다.
생각 .
가장쉽게 생각할 수 있는 해결방안은 ?
해법을 보기전에 잠간 생각을 해보자.
엿보기1. 각 경우에 따라 조건절이 DYNAMIC하게 작용하면된다.
2. 각 경우를 IF문으로 분리하면된다.
3. ORACLE에는 IF문을 대신할 수 있는 DECODE 함수가 있다.
물론 조건절에도 사용 가능하다.
해법 .
단계1. 먼저 IF를 이용해 보면 문장이 다음과 같이 분리된다.
'1'인경우 :
SELECT 직급,AVG(급여)
FROM SAM_TB1
GROUP BY 직급;
'2'인경우 :
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '01'
GROUP BY 직급;
'3'인경우
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '02'
GROUP BY 직급;
'4'인경우
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '03'
GROUP BY 직급;
'5'인경우
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '04'
OR 직급 = '05'
GROUP BY 직급;
단계2. 첫번째 문제는 WHERE 여부의 차이를 어떻게 해결하는가
하는것인데, WHERE절만 차이가 있으니 문제를 단순화 시켜보자..
그렇다면
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 어쩌구저쩌구
GROUP BY 직급;
이렇게 될것이다.
단계3. 이제 [어쩌구저쩌구] 만 해결하면된다.
그런데 '1'인 경우는 조건이 필요 없고, '5'인 경우는 2개, 나머지는
하나의 조건이 필요하다.
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '1'인 경우는 모두다, 나머지는 해당조건만 필요 하다.
'5'인 경우는 직급='4' OR 직급 = '5' 가 필요하다 나머지는 필요 없다.
단계4. 첫번째 조건의 '모두다'는 그 조건이 전체 RECORD에 대해서 항상
'참'이면 되고 ('1'인 경우) 나머지는 해당조건만을 만족하면 된다.
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(P_var1,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
GROUP BY 직급;
이렇게 되면 '5'인 경우를 제외한 나머지 경우를 모두 만족시킨다.
단계5 이제는 '5'인 경우만 남았다.
두번째 조건을 달면 된다.
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(P_var1,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(P_var1,'5','05',' ')
GROUP BY 직급;
이게 답이다.
자이제 실제로 PRAMETER(P_var1)를 변화시키면서 결과를 알아 보기로 하자.
다음의 세가지 유형에 대해 테스트 해보기로 한다.
P_var1='1'인경우
SQL> SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(1,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(1,'5','05',' ')
GROUP BY 직급
직 AVG(급여)
-- ---------
01 1216.6667
02 1600
03 1750
04 2150
05 2500
P_var1='2'인경우
SQL> SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(2,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(2,'5','05',' ')
GROUP BY 직급
직 AVG(급여)
-- ---------
01 1216.6667
P_var1='5'인경우
SQL> SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(5,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(5,'5','05',' ')
GROUP BY 직급
직 AVG(급여)
-- ---------
04 2150
05 2500
PLAN.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'SAM_TB1'
PLAN 상으로 FULL SCAN을 하는것을
알 수있다.
즉, 반드시 INDEX SCAN 을 해야하는 경우에는
사용을 제한해야 한다는
것을 알 수있다.
뒷풀이 .
각 경우를 살펴보자
'1' : WHERE 직급 = 직급
OR 직급 = ' ';
'2' : WHERE 직급 = '01'
OR 직급 = ' ';
'3' : WHERE 직급 = '02'
OR 직급 = ' ';
'4' : WHERE 직급 = '03'
OR 직급 = ' ';
'4' : WHERE 직급 = '04'
OR 직급 = '05';
위의 경우를 모두 만족하는 데이타를 찾을 수 있다.
사족 .
물론 COLUMN에 가공이 들어가 찜찜하기는 하다
하지만 이렇게 사용하므로 인해서 얻어지는 효과가 그 역효과를
누르는 경우는 많을것이다.
그렇다면 알고 있어서 나쁠건 없다고 보는데....
대용량 데이타베이스를 고려하기전에 우리가
흔하게 부딪히는 문제중의 하나다.
아는게 힘이다. 알아두자.
1.Parameter P_var1 은 한자리 값을 가지는 Number 또는 Char 이다.
2.Sample Table 인 SAM_TB1은
사번 직급 급여 호봉
------ ---- ----- -----
980101 01 1000 1
970102 02 1500 4
980103 01 1100 2
960104 03 1800 8
980105 01 1300 3
970106 02 1500 5
980107 01 1200 2
950108 04 2200 12
960109 03 1700 7
950110 04 2100 11
980111 01 1000 1
930112 05 2500 15
940113 02 1700 5
940114 01 1700 4
940115 02 1700 7
와 같은 Data를 가진다.
3.P_var1의 값에 따라
'1' : 직급에 상관없는 사원의 직급별 급여평균
'2' : 직급이 '01'인 사원의 급여평균
'3' : 직급이 '02'인 사원의 급여평균
'4' : 직급이 '03'인 사원의 급여평균
'5' : 직급이 '04','05'인 사원의 직급별 급여평균
위와 같은 경우에 따라 각 값을 가질 수 있다.
가상으로 만들어낸 경우지만 충분히 있을 수 있는 경우다.
문제 .
각 경우에 따라 SQL문을 하나씩 작성하는 번거로움을 덜고
위의 경우를 모두 만족 시킬 수 있는 SQL을 만들고 싶다.
생각 .
가장쉽게 생각할 수 있는 해결방안은 ?
해법을 보기전에 잠간 생각을 해보자.
엿보기1. 각 경우에 따라 조건절이 DYNAMIC하게 작용하면된다.
2. 각 경우를 IF문으로 분리하면된다.
3. ORACLE에는 IF문을 대신할 수 있는 DECODE 함수가 있다.
물론 조건절에도 사용 가능하다.
해법 .
단계1. 먼저 IF를 이용해 보면 문장이 다음과 같이 분리된다.
'1'인경우 :
SELECT 직급,AVG(급여)
FROM SAM_TB1
GROUP BY 직급;
'2'인경우 :
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '01'
GROUP BY 직급;
'3'인경우
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '02'
GROUP BY 직급;
'4'인경우
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '03'
GROUP BY 직급;
'5'인경우
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '04'
OR 직급 = '05'
GROUP BY 직급;
단계2. 첫번째 문제는 WHERE 여부의 차이를 어떻게 해결하는가
하는것인데, WHERE절만 차이가 있으니 문제를 단순화 시켜보자..
그렇다면
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 어쩌구저쩌구
GROUP BY 직급;
이렇게 될것이다.
단계3. 이제 [어쩌구저쩌구] 만 해결하면된다.
그런데 '1'인 경우는 조건이 필요 없고, '5'인 경우는 2개, 나머지는
하나의 조건이 필요하다.
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = '1'인 경우는 모두다, 나머지는 해당조건만 필요 하다.
'5'인 경우는 직급='4' OR 직급 = '5' 가 필요하다 나머지는 필요 없다.
단계4. 첫번째 조건의 '모두다'는 그 조건이 전체 RECORD에 대해서 항상
'참'이면 되고 ('1'인 경우) 나머지는 해당조건만을 만족하면 된다.
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(P_var1,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
GROUP BY 직급;
이렇게 되면 '5'인 경우를 제외한 나머지 경우를 모두 만족시킨다.
단계5 이제는 '5'인 경우만 남았다.
두번째 조건을 달면 된다.
SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(P_var1,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(P_var1,'5','05',' ')
GROUP BY 직급;
이게 답이다.
자이제 실제로 PRAMETER(P_var1)를 변화시키면서 결과를 알아 보기로 하자.
다음의 세가지 유형에 대해 테스트 해보기로 한다.
P_var1='1'인경우
SQL> SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(1,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(1,'5','05',' ')
GROUP BY 직급
직 AVG(급여)
-- ---------
01 1216.6667
02 1600
03 1750
04 2150
05 2500
P_var1='2'인경우
SQL> SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(2,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(2,'5','05',' ')
GROUP BY 직급
직 AVG(급여)
-- ---------
01 1216.6667
P_var1='5'인경우
SQL> SELECT 직급,AVG(급여)
FROM SAM_TB1
WHERE 직급 = DECODE(5,'1',직급,
'2','01',
'3','02',
'4','03',
'5','04')
OR 직급 = DECODE(5,'5','05',' ')
GROUP BY 직급
직 AVG(급여)
-- ---------
04 2150
05 2500
PLAN.
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (GROUP BY)
2 1 TABLE ACCESS (FULL) OF 'SAM_TB1'
PLAN 상으로 FULL SCAN을 하는것을
알 수있다.
즉, 반드시 INDEX SCAN 을 해야하는 경우에는
사용을 제한해야 한다는
것을 알 수있다.
뒷풀이 .
각 경우를 살펴보자
'1' : WHERE 직급 = 직급
OR 직급 = ' ';
'2' : WHERE 직급 = '01'
OR 직급 = ' ';
'3' : WHERE 직급 = '02'
OR 직급 = ' ';
'4' : WHERE 직급 = '03'
OR 직급 = ' ';
'4' : WHERE 직급 = '04'
OR 직급 = '05';
위의 경우를 모두 만족하는 데이타를 찾을 수 있다.
사족 .
물론 COLUMN에 가공이 들어가 찜찜하기는 하다
하지만 이렇게 사용하므로 인해서 얻어지는 효과가 그 역효과를
누르는 경우는 많을것이다.
그렇다면 알고 있어서 나쁠건 없다고 보는데....
대용량 데이타베이스를 고려하기전에 우리가
흔하게 부딪히는 문제중의 하나다.
아는게 힘이다. 알아두자.
댓글 0
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
46 | 빠진이빨찾기 II | 운영자 | 2002.09.18 | 3064 |
45 | 빠진이빨찾기 | 운영자 | 2002.09.18 | 3122 |
44 | 순환참조에서의 상위코드로집계 | 운영자 | 2002.09.18 | 3223 |
43 | CONNECT BY의 SORT와 JOIN | 운영자 | 2002.09.18 | 2810 |
42 | REPORT 양식맞추기4 | 운영자 | 2002.09.18 | 2597 |
41 | REPORT 양식 맞추기 III | 운영자 | 2002.09.18 | 3015 |
40 | REPORT 양식 맞추기 II | 운영자 | 2002.09.18 | 2644 |
39 | REPORT 양식 맞추기 | 운영자 | 2002.09.18 | 31359 |
38 | 1:1 JOIN / 1:M JOIN | 운영자 | 2002.09.18 | 3000 |
37 | TUNING-01 | 운영자 | 2002.09.18 | 2737 |
36 | 소계와 합계 | 운영자 | 2002.09.18 | 3487 |
35 | 중복DATA 찾아내기 | 운영자 | 2002.09.17 | 2814 |
34 | GROUP별 DATA 분류 | 운영자 | 2002.09.17 | 2978 |
» | PARAMETER값에 따라 변경이 일어나는 문장 | 운영자 | 2002.09.17 | 15808 |
32 | SELF JOIN | 운영자 | 2002.09.17 | 3408 |
31 | Outer Join | 운영자 | 2002.09.17 | 2918 |
30 | HINTS (출처-Oracle8.0 Tuning Guide) | 운영자 | 2002.09.17 | 2734 |
29 | 단일행 문자 함수 | 운영자 | 2002.09.17 | 2975 |
28 | 단일행 수치 함수 | 운영자 | 2002.09.17 | 2868 |
27 | NOT IN 의 함정 | 운영자 | 2002.09.17 | 2712 |