조건
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
번호 | 제목 | 글쓴이 | 날짜 | 조회 수 |
---|---|---|---|---|
126 | 부등호 조인 | 운영자 | 2002.09.18 | 56559 |
125 | REPORT 양식 맞추기 | 운영자 | 2002.09.18 | 31280 |
» | PARAMETER값에 따라 변경이 일어나는 문장 | 운영자 | 2002.09.17 | 15661 |
123 | oracle proc 에서 stdarg.h파일사용하기위한 환경설정 | 원우석 | 2004.12.06 | 12508 |
122 | 테이블 열항목을 행항목으로 변경하는 팁 | 구퍼 | 2011.01.11 | 8675 |
121 | NESTED TABLE과 VARRAY사용법과 다른점 | 박상현 | 2005.11.03 | 8642 |
120 | 바로이전 ROW의 값을 참조하고자 할때 | 운영자 | 2002.09.18 | 8493 |
119 | oracle의 procedure에서의 여러 리턴값의 활용 예 | 운영자 | 2002.10.18 | 8151 |
118 | 오라클 DB 링크 만들기 | 구퍼 | 2011.01.06 | 6510 |
117 | toad에서 사용가능한 explan_table생성 script | 박상현 | 2004.05.08 | 6381 |
116 | mssql update예제(select ~ update) | 구퍼 | 2008.10.31 | 6115 |
115 | java에서 array처리 방법 | 운영자 | 2003.01.27 | 6033 |
114 | Instant Client 사용법 | 구퍼 | 2009.12.30 | 5894 |
113 | COLUMN을 ROW로 | 운영자 | 2002.09.18 | 5858 |
112 | select -> update 구문 샘플 | 구퍼 | 2010.01.21 | 5696 |
111 | 그룹 단위별 일련번호 붙이기 | 운영자 | 2002.09.18 | 5668 |
110 | 데이타에 한글로 공백이 있는 경우 값을 가져오지 못하고 오류가 발생하는 경우 처리 | 구퍼 | 2009.03.23 | 5584 |
109 | 현재의 년월일시분초 구하기 | 구퍼 | 2008.09.10 | 5549 |
108 | Conditions | 구퍼 | 2008.09.17 | 5544 |
107 | 순환구조에서 임시테이블에 하위 조직을 엮어서 넣는 프로시져 | 하늘과컴 | 2007.10.01 | 5204 |