Transcript [튜닝] 오라클 튜닝실무사례 (한글, 사례21가지)

SQL
Tuning
사례 1. 년/월이 분리된 경우에도 인덱스 사용
 인덱스정보
TAB_A : TAB_A_X1 : YY + MM + DD + SALE_NO
 QUERY
SELECT
FROM
WHERE
GROUP
A.YY || A.MM, DEPT, SUM(SALE_QTY)
TAB_A A
A.YY || A.MM BETWEEN '9410' AND '9504'
BY A.YY || A.MM, DEPT ;
 개선된 QUERY
SELECT
FROM
WHERE
A.YY || A.MM, DEPT, SUM(SALE_QTY)
TAB_A A
(A.YY = '94' AND A.MM BETWEEN '10'
AND '12')
OR (A.YY = '95' AND A.MM BETWEEN '01' AND '04')
GROUP BY
DB기술자문팀
A.YY || A.MM, DEPT ;
1
SQL
Tuning
사례 2. BINDING 변수의 인덱스 사용 확인
 테이블 정보
EMP
# * EMPNO
ENAME
DEPTNO
VARCHAR2(5)
VARCHAR2(20)
VARCHAR2(5)
(총 로우 수:13,148)
 인덱스 정보
EMP : EMP_EMPNO_PK :
EMPNO
 QUERY
SELECT
1) Dynamic SQL에서 주의
2) HOST 변수 선언에서 주의
NVL (DEPTNO, ' ')
INTO
:B1
FROM
EMP
WHERE EMPNO = :B2 ;
DB기술자문팀
ROWS
EXECUTION PLAN
------ ---------------------------------------------0
SELECT STATEMENT
13148
TABLE ACCESS (BY ROWID) OF 'EMP'
0
INDEX (RANGE SCAN) OF 'EMP_EMPNO_PK' (UNIQUE)
2
SQL
Tuning
사례 3. ORDER BY 절에서도 인덱스 사용
 인덱스정보
FH14 : IFH14_KEY1 : H14_ACNT_CODE + H14_GWANLI_CODE + H14_MAGAM_YY +
H14_MAGAM_MM + H14_GUBUN
 QUERY
SELECT H14_ACNT_CODE, H14_GWANLI_CODE, H14_MAGAM_YY,
H14_MAGAM_MM, H14_GUBUN, H14_C_AMT, H14_D_AMT
FROM FH14
WHERE H14_MAGAM_YY = :B0 AND H14_GUBUN = :B1
ORDER BY H14_ACNT_CODE, H14_GWANLI_CODE ;
 전제:
적어도 하나이상의 컬럼이
NOT NULL
DB기술자문팀
ROWS
---0
5743
168188
Order By에 의한
SORT Plan이 없는 이유?
EXECUTION PLAN
----------------------------------------------SELECT STATEMENT
HINT: CHOOSE
TABLE ACCESS (BY ROWID) OF 'FH14'
INDEX (RANGE SCAN) OF 'IFH14_KEY1' (UNIQUE)
3
SQL
Tuning
사례 4. MAX 값의 처리
 인덱스정보
GFLM600 : GFLM600_PK : CAR_CODE + BOX_CODE + IO_DATE
두 SUB-Query의 차이는 ?
 QUERY
SELECT STOCK_Q
FROM GFLM600
 원하는 결과 나오지 않음
WHERE IO_DATE = (SELECT MAX(IO_DATE)
FROM GFLM600
WHERE CAR_CODE = :VALUE1
AND BOX_CODE = :VALUE2) ;
SELECT STOCK_Q
FROM GFLM600 A
WHERE IO_DATE = (SELECT
FROM
WHERE
AND
AND
AND
DB기술자문팀
MAX(IO_DATE)
GFLM600 B
B.CAR_CODE =
B.BOX_CODE =
A.CAR_CODE =
A.BOX_CODE =
:VALUE1
:VALUE2
B.CAR_CODE
B.BOX_CODE ) ;
4
SQL
Tuning
사례 4. MAX 값의 처리
 해결 방안
1) 역순 인덱스 사용
SELECT
/*+ INDEX_DESC(A GFLM600_PK) */
STOCK_Q
FROM GFLM600 A
WHERE CAR_CODE = :VALUE1 AND BOX_CODE = :VALUE2
AND ROWNUM = 1 ;
2) SUBSTR 사용 (인덱스 없을 때)
SELECT SUBSTR (MAX (IO_DATE || STOCK_Q), 9, 4)
FROM GFLM600
WHERE CAR_CODE = :VALUE1 AND BOX_CODE = :VALUE2;
DB기술자문팀
5
SQL
Tuning
사례 5. 불필요한 DUAL
 인덱스정보
PATN : PATN_PK
: ID + DAT
(PRIMARY KEY)
PATN : PATN_IDX1 : DEPT + GUBUN
PATN : PATN_IDX2 : WARD + GUBUN
 QUERY
 개선된
QUERY
DB기술자문팀
SELECT
INTO
FROM
WHERE
ID, DEPT, WARD, ROOM
:B1, :B2, :B3, :B4
PATN
WARD = :C1 AND
NVL(DAT, '999999') >= (SELECT
FROM
ORDER BY DEPT, ROOM ;
SELECT
INTO
FROM
WHERE
AND
ORDER
TO_CHAR(SYSDATE, 'YYMMDD')
SYS.DUAL)
ID, DEPT, WARD, ROOM
:B1, :B2, :B3, :B4
PATN
WARD = :C1
NVL(DAT,'999999') >= TO_CHAR(SYSDATE, 'YYMMDD')
BY DEPT, ROOM ;
6
SQL
Tuning
결합 인덱스의 처리 범위 (둘 다 '='로 쓰인 경우)
SELECT * FROM TAB1
WHERE COL1 = 'A'
(분포도가 넓다)
AND COL2 = '112' (분포도가 좁다)


COL1
COL2
ROWID
COL2
COL1
A
110
10
110
A
10
A
111
11
110
B
41
A
112
5
110
C
57
A
113
18
111
A
11
A
114
22
111
B
39
A
115
23
111
C
76
A
116
29
112
A
5
A
117
25
112
B
73
A
118
26
112
C
89
B
110
41
B
111
39
113
114
A
A
18
22
INDEX ( COL1 + COL2 )
DB기술자문팀



ROWID

INDEX ( COL2 + COL1 )
7
SQL
Tuning
결합 인덱스의 처리 범위 (둘 다 '=' 로 안 쓰인 경우)
SELECT * FROM TAB1
WHERE COL1 = 'A'
AND
COL2 BETWEEN '111' AND '113'

COL1
COL2
ROWID
COL2
COL1
A
110
10
110
B
41
A
111
11
110
C
57
112
5
111
A
11
A
113
18
111
B
39
A
114
22
111
C
76
A
115
23
112
A
5
A
116
29
112
B
73
A
117
25
112
C
89
A
118
26
113
A
18
B
110
41
113
B
44
B
111
39
114
A
22
A


INDEX ( COL1 + COL2 )
DB기술자문팀



ROWID
INDEX ( COL2 + COL1 )
8
SQL
Tuning
사례 6. 결합 인덱스의 컬럼 순서에 따른 차이
 인덱스정보
S_WORKPLAN : S_WORKPLAN_IDX : WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE
 QUERY
SELECT ACT_CODE, PART_MODEL_CODE, PART_CHAR_CODE, PART_SPEC,
PART_ROUT_CODE, ORDERNO, ITEMNO, DIN_ACT_MH, WORK_ORDER_NO,
S_MODEL_NO, DRAW_NO, MATR_STATUS, WORK_ORDER_DATE, ROWID
FROM
S_WORKPLAN
WHERE WORK_ORDER_DATE LIKE :B1 || '%'
AND DEPT_CODE = :B2
AND GROUP_CODE = :B3
ROWS EXECUTION PLAN
ORDER BY ACT_CODE ;
----- ---------------------------------------------------0 SELECT STATEMENT
2220
SORT (ORDER BY)
2220
TABLE ACCESS (BY ROWID) OF 'S_WORKPLAN'
46026
INDEX (RANGE SCAN) OF 'S_WORKPLAN_IDX' (NON UNIQ)
 해결방안 : 인덱스 칼럼 순서 : DEPT_CODE + GROUP_CODE + WORK_ORDER_DATE
DB기술자문팀
9
SQL
Tuning
사례 6. 결합 인덱스의 컬럼 순서에 따른 차이
WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE
LIKE '1%'
= 10
= 2


DB기술자문팀
WORK_
ORDER
1A
1A
1A
1A
1B
1B
1B
1B
1C
1C
1C
2A
DEPT_
CODE

10
10
20
30
10
20
30
40
10
10
20
10
DEPT_CODE + GROUP_CODE + WORK_ORDER_DATE
= 10
= 2
LIKE '1%'
GROUP_
CODE
1
2
3
4
2
2
3
4
1
2
3
1
DEPT_
CODE


10
10
10
10
10
10
20
20
20
30
30
40
GROUP_ WORK_
CODE
ORDER
1
1
1
2
2
2
2
3
3
3
4
4

1A
1C
2A
1A
1B
1C
1B
1A
1C
1B
1A
1B
10
SQL
Tuning
사례 7. 간단한 결합 인덱스지만 컬럼 순서 유의
 인덱스정보
EX_SHIPPER : SHIP_DATE_IX :
EX_SHIPPER 테이블의 분포도 :
SDATE + SHIPPER
SDATE : 1 / 365 , SHIPPER : 1 / 10
 QUERY
SELECT
SHIPPER, COUNT(*), SUM(AMT)
FROM
EX_SHIPPER
WHERE
SDATE BETWEEN '950101' AND
AND
SHIPPER IN ('A', 'B', 'C')
GROUP BY SHIPPER
ORDER BY SUM(AMT) DESC ;
'950430'
<- 4 개월 분량, 약 33 만 건
EXECUTION PLAN
---------------------------------------------SELECT STATEMENT COST ESTIMATE:N/A
SORT ( GROUP BY )
TABLE ACCESS BY ROWID EX_SHIPPER ( 1 )
INDEX RANGE SCAN SHIP_DATE_IX ( NU )
DB기술자문팀
11
SQL
Tuning
사례 7. 간단한 결합 인덱스지만 컬럼 순서 유의
SHIP_DATE_IX
EX_SHIPPER
SHIP_DATE_IX
2
차
2
●
●
가
●
공
EX_SHIPPER
차
가
●
●
공
●
●
●
SDATE +
SHIPPER
DB기술자문팀
●
●
SHIPPER +
SDATE
12
SQL
Tuning
사례 7. 간단한 결합 인덱스지만 컬럼 순서 유의
EXECUTION PLAN
--------------------------------------------------SELECT STATEMENT
SORT ( GROUP BY )
CONCATENATION
INDEX RANGE SCAN : SHIP_DATE_IX1 ( NU )
INDEX RANGE SCAN : SHIP_DATE_IX1 ( NU )
INDEX RANGE SCAN : SHIP_DATE_IX1 ( NU )
SHIPPER IN (‘C’, ‘B’, ‘A’)
SHIP_DATE_IX1 : SHIPPER + SDATE + AMT
SHIP_DATE_IX
●
●
처리범위는
'SHIPPER =' 와
'SDATE BETWEEN ...'
에 의해서 결정
2
●
●
●
●
차
가
공
SHIPPER + SDATE + AMT
DB기술자문팀
13
SQL
Tuning
사례 8. 조인시 사용된 결합 인덱스의 컬럼 순서
 인덱스정보
GFLT950 A : GFLT950_X1 : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE (PK)
GFLT950 A : GFLT950_X2 : TEAM_CODE + SPUM_CODE
GFXC130 B : GFXC130_X1 : D_DAY + PART_CODE + TEAM_CODE (PK)
 QUERY
SELECT
FROM
WHERE
AND
AND
AND
AND
A.CAR_CODE, A.D_DAY, A.PART_CODE, A.SPUM_CODE,
A.ORDER_PRT, A.HP_Q,
ROWS EXECUTION PLAN
B.TEAM_CDOE, B.UPFR_DATE
----- -------------------------------------------------0 SELECT STATEMENT
GFLT950 A, GFXC130 B
184
NESTED LOOP
A.PART_CODE = B.PART_CODE 17718
 A
TABLE ACCESS (FULL) OF 'GFLT950'
184
TABLE ACCESS (BY ROWID) OF 'GFXC130'  B
A.ORDER_PRT = 100
184
INDEX (UNIQUE SCAN) OF 'GFXC130_X1' (UNIQUE)
B.D_DAY = :VALUE1
B.TEAM_CODE = :VALUE2
B.PART_CODE BETWEEN :VALUE3 AND :VALUE4;
 해결 방안 :
DB기술자문팀
GFLT950 A :
GFLT950_X1 : PART_CODE + CAR_CODE + D_DAY + TEAM_CODE
14
SQL
Tuning
사례 8. 조인시 사용된 결합 인덱스의 컬럼 순서
GFLT950
GFXC130
# * PART_CODE
# * CAR_CODE
# * D_DAY
# * TEAM_CODE
SPUM_CODE
ORDER_PRT
F_HP_Q
# * PART_CODE
# * D_DAY
# * TEAM_CODE
B_F_QTY
UPPER_DATE
UP
 인덱스정보
GFLT950 A : GFLT950_X1 : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE (PK)
GFLT950 A : GFLT950_X2 : TEAM_CODE + SPUM_CODE
GFXC130 B : GFXC130_X1 : D_DAY + PART_CODE + TEAM_CODE (PK)
DB기술자문팀
15
SQL
Tuning
사례 9. 결합 인덱스를 구성하는 컬럼 선택
 인덱스정보
GFLT920 A : GFLT920_X1 : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE (PK)
GFLT920 A : GFLT920_X2 : PART_CODE + SPUM_CODE
GFXC130 B : GFXC130_X1 : PART_CODE + TEAM_CODE (PRIMARY KEY)
 QUERY
SELECT
A.CAR_CODE, A.D_DAY, A.PART_CODE, A.SPUM_CODE,
A.ORDER.PRT, A.F_HP_Q, B.TEAM_CODE
FROM GFLT920 A, GFXC130 B
WHERE A.PART_CODE = B.PART_CODE
AND A.D_DAY = :VALUE1
AND B.PART_CODE BETWEEN :VALUE3 AND :VALUE4
AND B.TEAM_CODE = :VALUE2;
ROWS EXECUTION PLAN
---- -----------------------------------------------0 SELECT STATEMENT
HINT : CHOOSE
23
NESTED LOOP
12
TABLE ACCESS (BY ROWID) OF 'GFXC130'
 B
13
INDEX (RANGE SCAN) OF 'GFXC130_X1' (UNIQUE)
6389
TABLE ACCESS (BY ROWID) OF 'GFLT920'
 A
6390
INDEX (RANGE SCAN) OF 'GFLT920_X2'
DB기술자문팀
16
SQL
Tuning
사례 9. 결합 인덱스를 구성하는 컬럼 선택
13
12
12
6390
6389
23
X
O
●
●
●
●
●
●
●
●
●
●
●
●
X
O
GFXC130_X1
GFXC130
테이블 B
GFLT920_X2
GFLT920
테이블 A
 해결방안
GFLT920
DB기술자문팀
A : GFLT920_X2 : PART_CODE + D_DAY
17
SQL
Tuning
사례 10. 결합 인덱스를 구성하는 컬럼 순서
 인덱스정보
GFBT400 A : GFBT400_X3 : TEAM_CODE + SHOP_CODE + D_DAY
GFXC440 B : GFXC440_X1 : SPUM_CODE (UNIQUE)
GFXC130 C : GFXC130_X1 : PART_CODE (UNIQUE)
 QUERY
SELECT
FROM
WHERE
AND
AND
AND
DB기술자문팀
A.PART_CODE, A.SPUM_CODE, A.D_DAY, A.B_F_QTY,
A.TAG, B.ORDER_PRT, C.DC_CODE
GFBT400 A, GFXC440 B, GFXC130 C
A.SPUM_CODE = B.SPUM_CODE
A.PART_CODE = C.PART_CODE
A.D_DAY = :B1
ROWS
EXECUTION PLAN
A.TEAM_CODE = :B2; ----- ---------------------------------------------------------
0 SELECT
STATEMENT
391
NESTED LOOPS
391
NESTED LOOPS
391
TABLE ACCESS ( BY ROWID ) OF 'GFBT400'
 A
48276
INDEX ( RANGE SCAN ) OF 'GFBT400_X3' (NON-UNIQ)
391
TABLE ACCESS ( BY ROWID ) OF 'GFXC130'
 C
391
INDEX ( UNIQUE SCAN ) OF 'GFXC130_X1' (UNIQ)
 B
391
TABLE ACCESS ( BY ROWID ) OF 'GFXC440'
391
INDEX ( UNIQUE SCAN ) OF 'GFXC440_X1' (UNIQ)
18
SQL
Tuning
사례 10. 결합 인덱스를 구성하는 컬럼 순서
GFBT400_X3
48276 391
GFBT400
A
GFXC130_X1
391
391
GFXC130
C
GFXC440_X1
391
GFXC440
B
391
X
O
X
O
X
●
●
●
X
X
O
X
TEAM_CODE
+ SHOP_CODE
+ D_DAY
DB기술자문팀
PART_CODE
SPUM_CODE
 해결방안 : TEAM_CODE + D_DAY + SHOP_CODE
19
SQL
Tuning
사례 11. 조인하는 테이블의 순서에 따른 차이
고객 D
납품실행계획 A
주문 B
# * 부서번호
# * 주문번호
# * 주문번호
* 부서번호
# * 직원번호
* 고객번호
* 주문일자
* 주문일자
납품예정일
소요비용
완료여부
# * 고객번호
고객이름
주소
주민등록번호
출고정지구분
수주진행현황 C
* 직원번호
# * 회사번호
주문량
# * 부서번호
# * 주문번호
# * 주문일자
# * 고객번호
직원번호
DB기술자문팀
20
SQL
Tuning
사례 11. 조인하는 테이블의 순서에 따른 차이
 인덱스정보
납품실행계획
주문
수주진행현황
고객
SELECT
FROM
WHERE
AND
AND
AND
AND
AND
AND
AND
AND
AND
AND
AND
DB기술자문팀
A
B
C
D
:
:
:
:
납품실행_PK
주문_PK
수주진행현황_PK
고객_PK
:
:
:
:
주문번호 + 부서번호 + 직원번호
주문번호
회사번호 + 부서번호 + 주문번호 + 주문일자 + 고객번호
고객번호
DISTINCT A.부서번호, A.주문번호, B.주문일자, B.직원번호, B.고객번호,
D.출고중지구분
납품실행계획 A , 주문 B , 수주진행현황 C , 고객 D
A.부서번호 = B.부서번호
ROWS EXECUTION PLAN
A.주문번호 = B.주문번호
---- ------------------------------------------------------------A.주문일자 = B.주문일자
0 SELECT STATEMENT
C.부서번호 = B.부서번호
7 SORT (UNIQUE)
7
NESTED LOOPS
C.고객번호 = B.고객번호
1243
NESTED LOOPS
C.직원번호 = B.직원번호
1275
NESTED LOOPS
 C
1275
TABLE ACCESS (BY ROWID) OF '수주진행현황'
C.주문번호 = B.주문번호
1276
INDEX (RANGE SCAN) OF ' 수주진행현황_PK' (UNIQUE)
C.주문일자 = B.주문일자
1275
TABLE ACCESS (BY ROWID) OF '고객'
 D
C.고객번호 = D.고객번호
1275
INDEX (UNIQUE SCAN) OF ' 고객_PK' (UNIQUE)
1243
TABLE ACCESS (BY ROWID) OF '주문'
C.부서번호 = 3000
 B
1275
INDEX (UNIQUE SCAN) OF ' 주문_PK' (UNIQUE)
C.회사번호 = 3
20
TABLE ACCESS (BY ROWID) OF '납품실행계획'
 A
A.완료여부 IS NULL ;
1263
INDEX (RANGE SCAN) OF ' 납품실행계획_PK' (UNIQUE)
21
사례 11. 조인하는 테이블의 순서에 따른 차이
1275
1275
납품실행계획
A
주문
B
1275
No
1243
1243
1263
1275
고객
D
1275
1276
수주진행현황
C
1275
SQL
Tuning
7
20
No
X
X
●
●
●
●
O
●
●
●
●
●
DB기술자문팀
O
22
SQL
Tuning
사례 11. 조인하는 테이블의 순서에 따른 차이
SELECT
/*+ ORDERED */
DISTINCT A.부서번호, A.주문번호, B.주문일자,
B.직원번호, B.고객번호, D.출고중지구분
FROM 수주진행현황 C , 납품실행계획 A , 고객 D, 주문 B
WHERE A.부서번호 = B.부서번호
AND A.주문번호 = B.주문번호
AND A.주문일자 = B.주문일자
AND C.부서번호 = A.부서번호
AND C.주문번호 = A.주문번호
AND C.주문일자 = A.주문일자
AND C.주문번호 = B.주문번호
AND C.주문일자 = B.주문일자
AND C.고객번호 = D.고객번호
AND C.부서번호 = 3000
AND C.회사번호 = 3
AND A.완료여부 IS NULL ;
DB기술자문팀
23
SQL
Tuning
사례 11. 조인하는 테이블의 순서에 따른 차이
수주진행현황
1275
1276
납품실행계획
1295 20
20
주문
고객
20
20
20
20
7
No
X
●
●
●
O
●
●
●
●
●
●
DB기술자문팀
X
24
SQL
Tuning
사례 12. 인덱스 컬럼의 분리에 따른 문제 해결
 인덱스정보
THISCODE
THISPBSC
THISINPT
 QUERY
SELECT
FROM
WHERE
A : THISCODE_PK1
B : THISPBSC_PK1
C : THISINPT_IDX1
: CODEGUBN + CODENAME
: PBSCIDNO
: INPTDATE + INPTCHRS
INPTIDNO,INPTPKND,INPT,INPTLEVL,INPTMNDR,INPTDEPT,INPTCHRS,PBSCPNME,
FLOOR(MONTH_BETWEEN(SYSDATE,TO_DATE(PBSCBIRT,'YYMMDD'))/12),
PBSCRSEX,PBSCPHNI,CODEDESC
THISCODE
A,
THISPBSC
THISINPT
C.INPTCHRS = SUBSTR(A.CODENAME, 1, 1)
AND
C.INPTDATE = :B1
AND
C.INPTGUBN IN
AND
C.INPTBYBY LIKE
AND
C.INPTIDNO = B.PBSCIDNO
AND
A.CODEGUBN = '11';
DB기술자문팀
B,
('A', 'B')
'1%'
C
Access 비 효율의
발생 부분
ROWS EXECUTION PLAN
----- ------------------------------------------------------0 SELECT
STATEMENT
40 NESTED LOOPS
40
NESTED LOOPS
480
TABLE ACCESS (BY ROWID) OF 'THISINPT'
 C
481
INDEX (RANGE SCAN) OF 'THISINPT_IDX1'(NON-UNIQUE)
40
TABLE ACCESS (BY ROWID) OF 'THISCODE'
 A
3290
INDEX (RANGE SCAN) OF 'THISCODE_PK1'(UNIQUE)
 B
40
TABLE ACCESS (BY ROWID) OF 'THISPBSC'
40
INDEX (UNIQUE SCAN) OF 'THISPBSC_PK1'(UNIQUE)
25
SQL
Tuning
사례 12. 인덱스 컬럼의 분리에 따른 문제 해결
THISINPT_IDX1
481
480
THISINPT
THISCODE_PK1
C
480
3290
40
THISCODE
A
THISPBSC_PK1
40
40
40
THISPBSC
B
40
X
O
X
●
●
X
X
X
O
●
X
O
INPTDATE
+ INPTCHRS
DB기술자문팀
CODEGUBN
PBSCIDNO
+ CODENAME
26
SQL
Tuning
사례 12. 인덱스 컬럼의 분리에 따른 문제 해결
 문제점
모델링 잘못: C.INPTCHRS
=
SUBSTR(A.CODENAME, 1, 1)
조인순서: THISINPT(C) --> THISCODE(A)
 해결방안
THISCODE(A) --> THISINPT(C) --> THISPBSC(B)
SELECT
FROM
WHERE
AND
AND
AND
AND
DB기술자문팀
/*+ ORDERED */
INPTIDNO,INPTPKND,INPT,
.....
............
THISCODE A, THISINPT C, THISPBSC B
C.INPTCHRS = SUBSTR(A.CODENAME, 1, 1)
C.INPTDATE = :B1
C.INPTGUBN IN ('A','B') AND C.INPTBYBY
C.INPTIDNO = B.PBSCIDNO
A.CODEGUBN = '11' ;
LIKE
'1%'
27
SQL
Tuning
사례 13. 조인을 활용한 GROUP BY의 처리
 인덱스정보
SALE
: SALE_PROD_NO_SALEDATE : PROD_NO + SALEDATE (NON UNIQUE)
PRODUCT : PK_PRODUCT
: PROD_NO (PRIMARY KEY)
 QUERY
SELECT
FROM
WHERE
AND
GROUP
DB기술자문팀
PROD_NO, SUM ( SALE_AMT * UNIT_PRICE)
SALE
PROD_NO BETWEEN 'P20150' AND 'P20200'
SALEDATE LIKE '1995%'
BY PROD_NO ;
28
SQL
Tuning
사례 13. 조인을 활용한 GROUP BY의 처리
PROD_NO
PROD_NO + SALEDATE
PROD_NO
SALEDATE
●
●
P20150
●
●
19940110
●
●
P20150
처
리
범
위
●
●
19950120
●
●
P20151
●
●
19940102
●
●
P20151
●
●
19951227
●
●
P20200
●
●
●
●
19951216
●
●
DB기술자문팀
처
해
결
방
안
리
범
위
P20148
P20149
P20150
P20151
●
●
●
P20198
P20199
P20200
●
●
●
19940120
●
●
P20200
PROD_NO
●
●
SALE
PRODUCT
PROD_NO + SALEDATE
PROD_NO
●
●
P20150
●
●
P20150
●
●
P20151
●
●
P20151
●
●
P20190
●
●
P20200
●
●
SALEDATE
●
●
19940110
●
●
19950120
●
●
19940102
●
●
19951227
●
●
19940120
●
●
19951216
●
●
SALE
29
SQL
Tuning
사례 14. OR 사용 시 주의할 점
 인덱스정보
CHULGOT : CHULGOT_IX
: CHULDATE + ITEM
CHULGOT : CH_STATUS_ITEM : STATUS + ITEM
CHULGOT : CH_CHULNO
: CHULNO
 QUERY
(CASE 1)
 개선된
QUERY
DB기술자문팀
SELECT
FROM
WHERE
OR
COUNT(*)
CHULGOT
(:SW = 1
(:SW = 2
SELECT
FROM
SUM(CNT)
(SELECT COUNT(*)
FROM CHULGOT
WHERE :SW = 1
UNION ALL
SELECT COUNT(*)
FROM CHULGOT
WHERE :SW = 2
AND
AND
CHULDATE = '941130')
CHULDATE+0 LIKE '96%') ;
AS CNT
AND
CHULDATE
=
'941130'
AS CNT
AND
CHULDATE+0
LIKE
'96%' );
30
SQL
Tuning
사례 14. OR 사용 시 주의할 점
 QUERY (CASE 2)
 개선된
QUERY
DB기술자문팀
SELECT
FROM
WHERE
OR
COUNT(*)
CHULGOT
CHULNO = '254'
(:FILED1 + :FILED2 )
SELECT
FROM
MAX(CNT)
( SELECT COUNT(*)
FROM CHULGOT
WHERE CHULNO =
UNION ALL
SELECT COUNT(*)
FROM CHULGOT
WHERE (:FILED1
>
0 ;
AS CNT
'254'
AS CNT
+ :FILED2) > 0 ) ;
31
SQL
Tuning
사례 14. OR 사용 시 주의할 점
 QUERY (CASE 3)
SELECT --+ RULE
CHULNO, CUSTNO, CHULDATE,UNCOST
FROM CHULGOT
WHERE (:SW = 1 AND (STATUS LIKE '1%' OR STATUS LIKE ‘2%’) )
OR (:SW = 2 AND (STATUS LIKE '3%') )
ORDER BY STATUS ;
 개선된
QUERY 1
SELECT
FROM
WHERE
OR
OR
ORDER
 개선된
QUERY 2
SELECT
FROM
WHERE
OR
DB기술자문팀
--+ RULE
CHULNO, CUSTNO, CHULDATE,UNCOST
CHULGOT
(:SW = 1 AND (STATUS LIKE '1%')
(:SW = 1 AND (STATUS LIKE '2%')
(:SW = 2 AND (STATUS LIKE '3%')
BY STATUS ;
--+ RULE
CHULNO, CUSTNO, CHULDATE,UNCOST
CHULGOT
STATUS LIKE DECODE( :SW, 1, '2%')
STATUS LIKE DECODE( :SW, 1, '1%' , '3%') ;
ORDER BY절 없음!
<--- ②
<--- ① 먼저 수행
32
SQL
Tuning
사례 14. OR 사용 시 주의할 점
 QUERY (CASE 4)
SELECT --+ RULE
CHULNO, CUSTNO, CHULDATE,UNCOST
FROM CHULGOT
WHERE ( :SW = 1 AND STATUS = 10 )
OR ( :SW = 2 AND STATUS BETWEEN 20
AND
40 ) ;
 개선된 QUERY
SELECT --+ RULE
CHULNO, CUSTNO, CHULDATE,UNCOST
FROM
WHERE
CHULGOT
STATUS BETWEEN
AND
DB기술자문팀
DECODE ( :SW , 1 , 10 , 2, 20 )
DECODE ( :SW , 1 , 10 , 2, 40 ) ;
33
SQL
Tuning
사례 15. OUTER 조인의 정확한 이해
 원하는 결과
KEY
A AAB 111
O
O
B AAC 123
O
O
B 10
AB
C ABA 222
O
O
C 10
AC
D ABB 233
O
X
E ABC 143
O
X
TAB1
A 10
AA
FLD1 FLD2 KEY COL1 COL2
A
AAB
111
A
10
AA
B
AAC
123
B
10
AB
C
ABA
222
C
10
AC
D
ABB
233
E
ABC
143
TAB2
 인덱스정보
TAB1 X
TAB2 Y
DB기술자문팀
: TAB1_IX : FLD1 + KEY
: TAB2_IX : COL1 + KEY
34
SQL
Tuning
 QUERY
사례 15. OUTER 조인의 정확한 이해
TAB1
TAB2
X : TAB1_IX : FLD1 + KEY
Y : TAB2_IX : COL1 + KEY
SELECT
FROM
WHERE
AND
AND
X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2
TAB1 X, TAB2 Y
X.KEY = Y.KEY(+)
 원하는 결과 나오지 않음
X.FLD1 > 'AAA'
Y.COL1 = 10 ;
SELECT
FROM
WHERE
AND
AND
X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2
TAB1 X, TAB2 Y
 COL1+KEY 순서이므로
X.KEY = Y.KEY(+)
인덱스 사용 불가
X.FLD1 > 'AAA'
 KEY+COL1 이면 인덱스 사용 가능
(Y.COL1 = 10 OR Y.COL1 IS NULL) ;
SELECT
FROM
WHERE
AND
AND
X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2
TAB1 X, TAB2 Y
 COL1+KEY 인덱스 사용 가능
X.KEY = Y.KEY(+)
 KEY+COL1 이라도 사용 가능
X.FLD1 > 'AAA'
Y.COL1(+) = 10 ;
DB기술자문팀
35
SQL
Tuning
사례 16. 복잡한 로직을 OUTER 조인으로 해결
 소개 : OUTER 조인과 DECODE를 이용하여 복잡한 로직을 간단히 해결
 요구 사항
입력된 카드번호에 엠보싱이 요구될 경우 최초 엠보싱일 경우에는
일련번호를 1로 셋팅하고,이미 엠보싱이 된 경우에는 입력된
카드번호의 일련번호 중 최대 번호 (MAX 일련번호)를 찾아서 그
번호에 +1 을 하여 카드엠보싱내역에 INSERT 한다.
카드엠보싱내역 테이블
# * 카드번호
# * 일련번호
유형
접수일자
발행일자
영업점번호
담당자ID
DB기술자문팀
36
SQL
Tuning
사례 16. 복잡한 로직을 OUTER 조인으로 해결
 일반적인 로직
EXEC SQL SELECT MAX(일련번호) + 1 INTO :다음일련번호
FROM 카드엠보싱내역
WHERE 카드번호 = :입력카드번호 ;
IF (SQLCA.SQLCODE = = 1403)
/* 카드번호 NOT FOUND */
THEN EXEC SQL INSERT INTO 카드엠보싱내역
VALUES( :입력카드번호, 1, :유형, :접수일자,
:발행일자, :영업점번호, :담당자ID ) ;
ELSE
EXEC SQL INSERT INTO 카드엠보싱내역
VALUES( :입력카드번호, :다음일련번호, :유형, :접수일자,
:발행일자, :영업점번호, :담당자ID ) ;
DB기술자문팀
37
SQL
Tuning
사례 16. 복잡한 로직을 OUTER 조인으로 해결
• 개선된 QUERY
OUTER JOIN을 사용하여 한번에 INSERT로 작업 수행
SORT MERGE로
풀리는 것 방지
INSERT INTO 카드엠보싱내역
SELECT /*+ INDEX_DESC(B TBCARDEMBO_PK) USE_NL(A B) */
:입력카드번호, DECODE(B.카드번호, NULL, 1, B.일련번호+1),
:유형, :접수일자, :발행일자, :담당자ID
FROM
카드엠보싱내역 B,
(SELECT :입력카드번호 AS 입력카드번호 FROM DUAL) A
WHERE A.입력카드번호 = B.카드번호(+)
AND ROWNUM = 1 ;
DB기술자문팀
38
SQL
Tuning
사례 17. DECODE를 활용한 SQL 통합
 QUERY
SELECT
FROM
WHERE
AND
NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0)
S_DAY_ACTSCH
ORDERNO = :B1
ITEMNO = :B2;
SELECT
FROM
WHERE
AND
AND
NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0)
S_DAY_ACTSCH
ORDERNO = :B1
ITEMNO = :B2
CON_CODE IS NOT NULL;
SELECT
FROM
WHERE
AND
AND
NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0)
S_DAY_ACTSCH
ORDERNO = :B1
ITEMNO = :B2;
(CON_CODE IS NULL OR LENGTH(CON_CODE) = 1 );
DB기술자문팀
39
SQL
Tuning
사례 17. DECODE를 활용한 SQL 통합
 개선된 QUERY
SELECT NVL(SUM(DACT_MH ),0),
NVL(SUM(DACT_AMT),0),
NVL(SUM(DECODE( CON_CODE, NULL, 0, DACT_MH )),0),
NVL(SUM(DECODE( CON_CODE, NULL, 0, DACT_AMT)),0),
NVL(SUM(DECODE( CON_CODE, NULL, DACT_MH,
DECODE(LENGTH(CON_CODE), 1, DACT_MH, 0) )), 0),
NVL(SUM(DECODE( CON_CODE, NULL, DACT_AMT,
DECODE(LENGTH(CON_CODE), 1, DACT_AMT,0) )), 0),
FROM
S_DAY_ACTSCH
WHERE ORDERNO = :B1
AND ITEMNO = :B2;
DB기술자문팀
40
SQL
Tuning
사례 18. 과도한 DECODE의 사용
 QUERY
SELECT B.TM, M.TAX, M.PUMMOK,
SUM(DECODE(NAPBAN, '1', P.SSU, 0)),
SUM(DECODE(NAPBAN, '1', P.KUMEK1, 0)),
SUM(DECODE(NAPBAN, '1', DECODE(B.BOKWAN, '1', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '1', DECODE(B.BOKWAN, '2', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '1', DECODE(B.BOKWAN, '3', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '2', P.SSU, 0)),
SUM(DECODE(NAPBAN, '2', P.KUMEK1, 0)),
SUM(DECODE(NAPBAN, '2', DECODE(B.BOKWAN, '1', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '2', DECODE(B.BOKWAN, '2', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '2', DECODE(B.BOKWAN, '3', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '3', P.SSU, 0)),
SUM(DECODE(NAPBAN, '3', P.KUMEK1, 0)),
SUM(DECODE(NAPBAN, '3', DECODE(B.BOKWAN, '1', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '3', DECODE(B.BOKWAN, '2', P.KUMEK2, 0))),
SUM(DECODE(NAPBAN, '3', DECODE(B.BOKWAN, '3', P.KUMEK2, 0))),
FROM GGB M, GPM P, MJ B
WHERE B.YT = P.YT AND B.MAEJANG = P.MAEJANG AND M.CODE = P.PUMMOK
AND P.DATE BETWEEN :B1 AND :B2
GROUP BY B.TM, M.TAX, M.PUMMOK
ORDER BY B.TM, M.TAX, M.PUMMOK ;
DB기술자문팀
41
SQL
Tuning
사례 18. 과도한 DECODE의 사용
 개선된 QUERY
SELECT B.TM, M.TAX, M.PUMMOK,
SUM(DECODE(NAPBAN, '1', P.SSU, 0)),
SUM(DECODE(NAPBAN, '1', P.KUMEK1, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '11', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '12', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '13', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN, '2', P.SSU, 0)),
SUM(DECODE(NAPBAN, '2', P.KUMEK1, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '21', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '22', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '23', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN, '3', P.SSU, 0)),
SUM(DECODE(NAPBAN, '3', P.KUMEK1, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '31', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '32', P.KUMEK2, 0)),
SUM(DECODE(NAPBAN||B.BOKWAN, '33', P.KUMEK2, 0)),
FROM GGB M, GPM P, MJ B
WHERE B.YT = P.YT AND B.MAEJANG = P.MAEJANG AND M.CODE = P.PUMMOK
AND P.DATE BETWEEN :B1 AND :B2
GROUP BY B.TM, M.TAX, M.PUMMOK ;
DB기술자문팀
42
SQL
Tuning
사례 19. 비숫한 SQL을 통합하여 SUM(DECODE)로
 소개 : WHERE 조건이 조금 다르면서 동일한 테이블을 사용하는 SQL통합
 유사한 SQL
EXEC SQL
SELECT MAX(LINE_NO) INTO :TOT_LINE_NO
FROM
BA004DM
WHERE
BRK_CD= :BRK_CD
AND DCL_YEAR = :DCL_YEAR
AND
SBM_SRL_NO = :SBM_SRL_NO
AND
DITC = :DITC ;
EXEC SQL
SELECT COUNT(LINE_NO) INTO :M_LINE_NO
FROM
BA004DM
WHERE BRK_CD = :BRK_CD
AND
DCL_YEAR = :DCL_YEAR
AND
SBM_SRL_NO = :SBM_SRL_NO
AND
DITC = :DITC
AND
LINE_NO < 0 ;
DB기술자문팀
43
SQL
Tuning
사례19. 비숫한 SQL을 통합하여 SUM(DECODE)로
 해결 방안
EXEC SQL
DB기술자문팀
SELECT MAX (LINE_NO) , SUM(DECODE(SIGN(LINE_NO), -1, 1 ))
INTO :TOT_LINE_NO, :M_LINE_NO
FROM BA004DM
WHERE BRK_CD= :BRK_CD
AND DCL_YEAR = :DCL_YEAR
AND SBM_SRL_NO = :SBM_SRL_NO
AND DITC = :DITC ;
44
SQL
Tuning
사례 20. DECODE와 GROUP BY의 효율적인 사용
자격등록마스터 테이블 (약 100 만 건)
요구 사항
등록자 ID
QUAL_ID
NUMBER
등록자명
QUAL_NM
VARCHAR2(10)
1996 년도 자료 중 직종별,
성별로 구분하여 등록 월별로
등록된 건수를 표시하라.
직종코드
JOB_CLSS
CHAR(2)
[힌트]
성별
SEX
CHAR(1)
등록일자
REG_DATE
CHAR(8)
급여
SALARY
NUMBER
가족수
FAMILY_CNT
NUMBER
등급
GRAD_CLSS
CHAR(1)
DB기술자문팀
PRIMARY KEY
GROUP BY 직종별, 성별이 되
어야 하고 세로로 된 데이터
를
가로로 펼치기 위해
DECODE 를 사용한다.
45
SQL
Tuning
SELECT
사례 20. DECODE와 GROUP BY의 효율적인 사용
JOB_CLSS, SEX,
SUM(DECODE(SUBSTR(REG_DATE,1,6),’199601’,1,0)) AS 9601월,
SUM(DECODE(SUBSTR(REG_DATE,1,6),’199602’,1,0)) AS 9602월,
......
SUM(DECODE(SUBSTR(REG_DATE,1,6),’199612’,1,0)) AS 9612월
FROM QUALIFIER
GROUP BY JOB_CLSS, SEX;
2분 24초
 병렬 처리
SELECT
/*+ FULL(QUALIFIER) PARALLEL(QUALIFIER 2) */
JOB_CLSS, SEX,
SUM(DECODE(SUBSTR(REG_DATE,1,6),’199601’,1,0)) AS 9601월,
......
FROM QUALIFIER
GROUP BY JOB_CLSS, SEX
ORDER BY 1, 2;
DB기술자문팀
1분 13초
46
SQL
Tuning
사례 20. DECODE와 GROUP BY의 효율적인 사용
SELECT JC, SEX,
SUM(DECODE(REG_MM,'199601',CNT,0)) AS 9601월,
SUM(DECODE(REG_MM,'199602',CNT,0)) AS 9602월,
SUM(DECODE(REG_MM,'199603',CNT,0)) AS 9603월,
SUM(DECODE(REG_MM,'199604',CNT,0)) AS 9604월,
........
SUM(DECODE(REG_MM,'199610',CNT,0)) AS 9610월,
SUM(DECODE(REG_MM,'199611',CNT,0)) AS 9611월,
Parallel: 14초
SUM(DECODE(REG_MM,'199612',CNT,0)) AS 9612월
FROM (SELECT /*+ FULL(QUALIFIER) PARALLEL(QUALIFIER,2) */
JOB_CLSS AS JC, SEX,
No Parallel : 27초
SUBSTR(REG_DATE,1,6) AS REG_MM,
COUNT(*) AS CNT
FROM QUALIFIER
GROUP BY JOB_CLSS, SEX, SUBSTR(REG_DATE,1,6) )
GROUP BY JC, SEX ;
DB기술자문팀
47
SQL
Tuning
사례21. 데이터 복제를 이용하여 빠짐없이 표시
 소개 : 데이터 복제를 이용하여 기준이 되는 항목에 대하여 데이터가 없어도
빠짐없이 모두 표시
피보험자격정보
# * 주민등록번호
성명
자격취득일자
지역코드 (FK)
주소
지역정보
# * 지역코드
지역명
대표지부장
성별
 위와 같은 환경하에 모든 지역에 대하여 한주일 동안 자격을 취득한 모든
사람을 지역별, 성별로 구분하여 표시
 원하는 기간에 대해 해당지역에 자격취득자가 없는 경우에도 남녀 항목이
생성되어 “0” 이라는 값을 가짐
DB기술자문팀
48
SQL
Tuning
사례21. 데이터 복제를 이용하여 빠짐없이 표시
 표시 예
지역명
DB기술자문팀
성 별 기준일
+1
+2
+3
+4
+5
+6
3
2
1
0
4
1
0
1
.....
.....
.....
서울
남
1
0
서울
여
2
1
3
.....
.....
.....
.....
.....
경남
남
1
1
1
2
1
0
0
경남
여
0
0
0
0
0
0
0
제주
남
0
0
0
0
0
0
0
제주
여
0
0
1
0
0
0
0
.....
49
SQL
Tuning
사례21. 데이터 복제를 이용하여 빠짐없이 표시
 간단한 SQL
SELECT 지역명, 성별, SUM(DECODE(자격취득일자,
SUM(DECODE(자격취득일자,
SUM(DECODE(자격취득일자,
SUM(DECODE(자격취득일자,
SUM(DECODE(자격취득일자,
SUM(DECODE(자격취득일자,
SUM(DECODE(자격취득일자,
FROM 피보험자자격정보 A, 지역정보 B
WHERE A.자격취득일자 BETWEEN :입력일자 AND
AND A.지역코드 = B.지역코드
GROUP BY B.지역명 , A.성별 ;
:입력일자 ,
:입력일자+1,
:입력일자+2,
:입력일자+3,
:입력일자+4,
:입력일자+5,
:입력일자+6,
1),
1),
1),
1),
1),
1),
1),
:입력일자+6
 문제점 : “경남지역 여”나 “제주지역 남”처럼 자격을 취득한 로우가 없으면
해당로우가 나타나지 않음
DB기술자문팀
50
SQL
Tuning
사례21. 데이터 복제를 이용하여 빠짐없이 표시
 해결 방안
GROUP BY 지역명, 성별
서울 남 1 0 5 3 2 1 0
서울 여 2 1 3 4 1 0 1
경남 남 1 0 1 2 1 0 0
제주 여 0 0 1 0 0 0 0
서울 남 ..... : 남
서울 남 0....0 : 여
서울 여 0....0 : 남
서울 여 ..... : 여
경남 남 ..... : 남
경남 남 0....0 : 여
제주 여 0....0 : 남
제주 여 ..... : 여
Group By
DB기술자문팀
남
여
남
여
남
여
남
여
Decode
남여
Join
+
서울 남 .....
서울 남 .....
서울 여 .....
서울 여 .....
경남 남 .....
경남 남 .....
제주 여 .....
제주 여 .....
51
SQL
Tuning

사례21. 데이터 복제를 이용하여 빠짐없이 표시
SQL
SELECT V.지역명,R.성별, SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일 ,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+1,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+2,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+3,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+4,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+5,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+6,
FROM (SELECT B.지역명, A.성별, SUM (DECODE(자격취득일자, :입력일자 ,
SUM (DECODE(자격취득일자, :입력일자+1,
SUM (DECODE(자격취득일자, :입력일자+2,
SUM (DECODE(자격취득일자, :입력일자+3,
SUM (DECODE(자격취득일자, :입력일자+4,
SUM (DECODE(자격취득일자, :입력일자+5,
SUM (DECODE(자격취득일자, :입력일자+6,
FROM 피보험자자격정보 A, 지역정보 B
WHERE A.자격취득일자 BETWEEN :입력일자 AND :입력일자 + 6
AND A.지역코드 = B.지역코드
GROUP BY B.지역명 , A.성별 ) V,
(SELECT '남' AS 성별 FROM
UNION ALL
SELECT '여' AS 성별 FROM
GROUP BY V.지역명, R.성별 ;
DB기술자문팀
1)
1)
1)
1)
1)
1)
1)
AS
AS
AS
AS
AS
AS
AS
SUM1,
SUM2,
SUM3,
SUM4,
SUM5,
SUM6,
SUM7
DUAL
DUAL)
R
52
SQL
Tuning
V.지역명
사례21. 데이터 복제를 이용하여 빠짐없이 표시
V.SUM1
R.성별
서울 남 1 0 5 3 2 1 0
남
서울 남 1 0 5 3 2 1 0
남
서울 남 1 0 5 3 2 1 0
여
서울 남 0 0 0 0 0 0 0
여
서울 여 2 1 3 4 1 0 1
남
서울 여 0 0 0 0 0 0 0
남
서울 여 2 1 3 4 1 0 1
여
서울 여 2 1 3 4 1 0 1
여
경남 남 1 0 1 2 1 0 0
남
경남 남 1 0 1 2 1 0 0
남
경남 남 1 0 1 2 1 0 0
여
경남 남 0 0 0 0 0 0 0
여
제주 여 0 0 1 0 0 0 0
남
제주 여 0 0 0 0 0 0 0
남
제주 여 0 0 1 0 0 0 0
여
제주 여 0 0 1 0 0 0 0
여
DB기술자문팀
D
E
C
O
D
E
GROUP BY
GROUP BY
GROUP BY
GROUP BY
53
SQL
Tuning

SQL
DB기술자문팀
사례21. 데이터 복제를 이용하여 빠짐없이 표시
SELECT
V.지역명, R.성별, SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일 ,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+1,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+2,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+3,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+4,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+5,
SUM (DECODE(V.성별,R.성별,SUM1,0) AS 기준일+6,
FROM (SELECT B.지역명, A.성별, SUM (DECODE(자격취득일자, :입력일자 , 1) AS
SUM (DECODE(자격취득일자, :입력일자+1, 1) AS
SUM (DECODE(자격취득일자, :입력일자+2, 1) AS
SUM (DECODE(자격취득일자, :입력일자+3, 1) AS
SUM (DECODE(자격취득일자, :입력일자+4, 1) AS
SUM (DECODE(자격취득일자, :입력일자+5, 1) AS
SUM (DECODE(자격취득일자, :입력일자+6, 1) AS
FROM (SELECT 자격취득일자, 성별, 지역코드 FROM 피보험자자격정보
WHERE 자격취득일자 BETWEEN :입력일자 AND :입력일자+6 ) A,
지역정보 B
WHERE B.지역코드 = A.지역코드(+)
GROUP BY B.지역명 , A.성별 ) V, (SELECT '남' AS 성별 FROM DUAL
UNION ALL
SELECT '여' AS 성별 FROM DUAL)
GROUP BY V.지역명, R.성별 ;
SUM1,
SUM2,
SUM3,
SUM4,
SUM5,
SUM6,
SUM7
R
54
SQL
Tuning
DB기술자문팀
55