Скачать презентацию SQL Tuning 사례 1 OPTIMIZER MODE에 따른 인덱스
a81253096fc835d2774695d322d932c9.ppt
- Количество слайдов: 75
Скачать презентацию SQL Tuning 사례 1 OPTIMIZER MODE에 따른 인덱스
Скачать презентацию SQL Tuning 사례 1 OPTIMIZER MODE에 따른 인덱스
SQL Tuning 사례 1. OPTIMIZER MODE에 따른 인덱스 사용 l 인덱스정보 COL A : COL_PK 1 : YEAR + HOUSE + SERIAL_NO + HSNO COM B : COM_IX : YEAR + HOUSE + SERIAL_NO COM B : COM_IDX 1 : CLASS + DATE l QUERY SELECT FROM WHERE AND AND AND GROUP DISTINCT B. CLASS, COUNT(B. CLASS) ROWS COL A, COM B ---A. YEAR = B. YEAR 0 1564 A. HOUSE = B. HOUSE 1564 A. SERIAL_NO = B. SERIAL_NO 6361 2437 A. AMT >= 100 4437 B. DATE < '950501' B. CLASS IN ( 'C', 'Y', 'I', 'P' ) BY B. CLASS ; EXECUTION PLAN COM Table의 ----------------------Full Table Scan 이유는? SELECT STATEMENT SORT (GROUP BY) NESTED LOOP ŒB TABLE ACCESS (FULL) OF 'COM' FULL TABLE ACCESS (BY ROWID) OF 'COL' A INDEX (RANGE SCAN) OF 'COL_PK 1'(UNIQUE) 1
SQL Tuning l l 사례 1. OPTIMIZER MODE에 따른 인덱스 사용 DISTINCT 불필요 OPTIMIZER MODE 선택 – INIT. ORA : OPTIMIZED_MODE = FIRST_ROWS – SQL*PLUS, PRO*C : SQL> ALTER SESSION SET OPTIMIZER_GOAL = FIRST_ROWS; SELECT --+ FIRST_ROWS B. CLASS, COUNT(B. CLASS) FROM COL A, COM B WHERE A. YEAR = B. YEAR AND A. HOUSE = B. HOUSE AND A. SERIAL_NO = B. SERIAL_NO AND A. AMT >= 100 AND B. DATE < '950501' AND B. CLASS IN ('Y', 'P', 'I', 'C') GROUP BY B. CLASS ; 2
SQL Tuning 사례 2. ORDER BY 절에서도 인덱스 사용 l 인덱스정보 FH 14 : IFH 14_KEY 1 : H 14_ACNT_CODE + H 14_GWANLI_CODE + H 14_MAGAM_YY + H 14_MAGAM_MM + H 14_GUBUN l QUERY SELECT FROM WHERE ORDER H 14_ACNT_CODE, H 14_GWANLI_CODE, H 14_MAGAM_YY, H 14_MAGAM_MM, H 14_GUBUN, H 14_C_AMT, H 14_D_AMT FH 14_MAGAM_YY = : B 0 AND H 14_GUBUN = : B 1 BY H 14_ACNT_CODE, H 14_GWANLI_CODE ; 적어도 하나 이상의 컬럼이 NOT NULL ROWS ---0 5743 168188 Order By에 의한 SORT Plan이 없는 이유? EXECUTION PLAN -----------------------SELECT STATEMENT HINT: CHOOSE TABLE ACCESS (BY ROWID) OF 'FH 14' INDEX (RANGE SCAN) OF 'IFH 14_KEY 1' (UNIQUE) 3
SQL Tuning 부분범위(Partial Range Scan) 의 처리 l 조건을 만족하는 Row 수가 Array Size 에 도달되면 멈춤 부 분 범 위 처 리 전 체 범 위 처 리 1 차 스 캔 • • 2 차 가 공 운반단위 1 차 스 캔 2 차 가 공 운반단위 • • Full Range Scan 후 가공하여 Array Size 만큼 추출 4
SQL Tuning 부분범위(Partial Range Scan) 의 처리 l 부분범위 처리의 성능 결정 사용 된 INDEX에 의해서 결정 주 범위 (Driving Range) 부 범위 (Checking Range) 성능 넓다 넓다 양호 넓다 좁다 불량 좁다 넓다 양호 좁다 좁다 주 / 부 조건의 변경 가능 ? 양호 5
SQL Tuning 사례 3. 년/월이 분리된 경우에도 인덱스 사용 l 인덱스정보 TAB_A : TAB_A_X 1 : YY + MM + DD + SALE_NO l QUERY SELECT A. YY || A. MM, DEPT, SUM(SALE_QTY) FROM TAB_A A WHERE A. YY || A. MM BETWEEN '9410' AND '9504' GROUP BY A. YY || A. MM, DEPT ; 6
SQL Tuning 사례 3. 년/월이 분리된 경우에도 인덱스 사용 l 개선된 QUERY SELECT FROM A. YY || A. MM, DEPT, SUM(SALE_QTY) TAB_A A WHERE (A. YY = '94' AND A. MM BETWEEN '10' AND '12') OR (A. YY = '95' AND A. MM BETWEEN '01' AND '04') GROUP BY A. YY || A. MM, DEPT ; 7
SQL Tuning 사례 4. BINDING 변수의 인덱스 사용 확인 l 테이블 정보 EMP # * EMPNO ENAME DEPTNO VARCHAR 2(5) VARCHAR 2(20) VARCHAR 2(5) (총 로우 수: 13, 148) l 인덱스 정보 EMP : EMP_EMPNO_PK : EMPNO 1) Dynamic SQL에서 주의 2) HOST 변수 선언에서 주의 l QUERY SELECT NVL (DEPTNO, ' ') INTO : B 1 FROM EMP WHERE EMPNO = : B 2 ; ROWS EXECUTION PLAN --------------------------0 SELECT STATEMENT 13148 TABLE ACCESS (BY ROWID) OF 'EMP' 13149 INDEX (RANGE SCAN) OF 'EMP_EMPNO_PK' (UNIQUE) 8
SQL Tuning 사례 5. 인덱스 컬럼의 분리에 따른 문제 해결 l 인덱스정보 THISCODE THISPBSC THISINPT A : THISCODE_PK 1 B : THISPBSC_PK 1 C : THISINPT_IDX 1 : CODEGUBN + CODENAME : PBSCIDNO : INPTDATE + INPTCHRS l QUERY SELECT INPTIDNO, INPTPKND, INPTLEVL, INPTMNDR, INPTDEPT, INPTCHRS, PBSCPNME, FLOOR(MONTH_BETWEEN(SYSDATE, TO_DATE(PBSCBIRT, 'YYMMDD'))/12), PBSCRSEX, PBSCPHNI, CODEDESC FROM THISCODE A, THISPBSC B, THISINPT WHERE C. INPTCHRS = SUBSTR(A. CODENAME, 1, 1) AND C. INPTDATE = : B 1 AND C. INPTGUBN IN ('A', 'B') AND C. INPTBYBY LIKE AND C. INPTIDNO = B. PBSCIDNO '1%' AND A. CODEGUBN = '11'; C Access 비 효율의 발생 부분 ROWS EXECUTION PLAN -------------------------------0 SELECT STATEMENT 40 NESTED LOOPS 480 TABLE ACCESS (BY ROWID) OF 'THISINPT' 481 INDEX (RANGE SCAN) OF 'THISINPT_IDX 1'(NON-UNIQUE) 40 TABLE ACCESS (BY ROWID) OF 'THISCODE' 3290 INDEX (RANGE SCAN) OF 'THISCODE_PK 1'(UNIQUE) 40 TABLE ACCESS (BY ROWID) OF 'THISPBSC' 40 INDEX (UNIQUE SCAN) OF 'THISPBSC_PK 1'(UNIQUE) ŒC A B 9
SQL Tuning 사례 5. 인덱스 컬럼의 분리에 따른 문제 해결 THISINPT_IDX 1 THISINPT 480 481 ŒC THISCODE_PK 1 3290 THISCODE A 40 THISPBSC_PK 1 40 THISPBSC B 40 X O X ● ● X X X O ● X O INPTDATE + INPTCHRS CODEGUBN + CODENAME PBSCIDNO 10
SQL Tuning 사례 5. 인덱스 컬럼의 분리에 따른 문제 해결 l 문제점 C. INPTCHRS = SUBSTR(A. CODENAME, 1, 1) THISINPT(C) --> THISCODE(A) l 해결방안 THISCODE(A) --> THISINPT(C) --> THISPBSC(B) SELECT FROM WHERE AND AND /*+ ORDERED */ INPTIDNO, INPTPKND, INPT, . . . . THISCODE A, THISINPT C, THISPBSC B C. INPTCHRS = SUBSTR(A. CODENAME, 1, 1) C. INPTDATE = : B 1 C. INPTGUBN IN ('A', 'B') AND C. INPTBYBY C. INPTIDNO = B. PBSCIDNO A. CODEGUBN = '11' ; LIKE '1%' 11
SQL Tuning 사례 6. 의도적 인덱스 컬럼의 변형시 주의점 l 인덱스정보 SUGA : SUGA_CODE_SE_PK : CODE + SERL SUGA : SUGA_SERL_IX : SERL + EDSC SUGA : SUGA_SERL_IX 2 : PART_CODE + EDSC + SERL + MODEL_NO l QUERY SELECT FROM NVL(CODE, ' '), NVL(EDSC, ' ') SUGA WHERE CODE LIKE : B 1 AND (SERL||'' = 'CA' OR SERL||'' = 'DA') AND ROWNUM < 21 ; ROWS EXECUTION PLAN ----------------------------0 SELECT STATEMENT 20 COUNT (STOPKEY) 20 TABLE ACCESS (BY ROWID) OF 'SUGA' 6581 INDEX (RANGE SCAN) OF 'SUGA_CODE_SE_PK' (UNIQUE) 12
SQL Tuning 사례 6. 의도적 인덱스 컬럼의 변형시 주의점 l 해결방안 SELECT FROM WHERE AND NVL(CODE, ' '), NVL(EDSC, ' ') SUGA CODE LIKE : B 1 SERL IN ('CA' , 'DA') ROWNUM < 21 ; IN --> OR 와 ROWNUM 같이 사용 --> FILTER --> 전체범위 처리 SELECT NVL(CODE, ' '), NVL(EDSC, ' ') FROM (SELECT CODE, EDSC FROM SUGA WHERE CODE LIKE : B 1 AND SERL = 'CA' UNION ALL SELECT CODE, EDSC FROM SUGA WHERE CODE LIKE : B 1 AND SERL = 'DA') WHERE ROWNUM < 21 ; <--- 첫번째 실행 <--- 두 번째 실행 SUGA_CODE_SE_PK : SERL + CODE 13
SQL Tuning 사례 7. 컬럼값을 연결하여 사용할 때 주의점 l 인덱스정보 DRAWING : DWG_X 1 : DWG 1 DRAWING : DWG_X 2 : DWG 2 DRAWING : DWG_X 3 : DWG 3 l QUERY SELECT FROM WHERE AND DWG_QTY DRAWING DWG 1 || DWG 2 || DWG 3 ROWNUM = 1 ; 컬럼값 > : DWG 1 || : DWG 2 || : DWG 3 크기비교 DWG 1 DWG 2 DWG 3 4 0 12 3 4 35 2 입력변수값 : DWG 1 > > > : DWG 2 : DWG 3 4 0 11 3 3 68 2 14
SQL Tuning 사례 7. 컬럼값을 연결하여 사용할 때 주의점 l 해결방안 컬럼값과 변수값의 길이가 동일하다는 전제조건 SELECT /*+ USE_CONCAT */ DWG_QTY FROM DRAWING WHERE ( (DWG 1 = : DWG 1 AND DWG 2 = : DWG 2 AND DWG 3 > : DWG 3) OR (DWG 1 = : DWG 1 AND DWG 2 > : DWG 2) OR (DWG 1 > : DWG 1) ) AND ROWNUM = 1 ; 15
SQL Tuning 사례 8. LOOP 내에 사용되는 SQL의 주의점 l 인덱스정보 A 109 : A 109_IND 1 : JHCOD + SEQ l QUERY SELECT FROM WHERE AND SEQ INTO : WWSEQ A 109 JHCOD = : B 1 SUBSTR (JEPUM, 1, : LEN 1) = SUBSTR (: WWJEPUM , 1, : LEN 1) ; CALL -------PARSE EXECUTE FETCH -------TOTAL ROWS ------0 8449717 8491439 COUNT ------1 41722 ------83444 CPU -------0. 00 25. 29 3677. 99 -------3703. 28 ELAPSED DISK QUERY CURRENT ROWS ----------- -------0. 00 0 0 36. 67 0 0 4516. 42 255667 17066322 0 41722 ----------- -------4553. 09 255667 17066322 0 41722 EXECUTION PLAN -------------------SELECT STATEMENT TABLE ACCESS (BY ROWID) OF 'A 109' INDEX (RANGE SCAN) OF 'A 109_IND 1' ( NON - UNIQUE ) 16
SQL Tuning 사례 8. LOOP 내에 사용되는 SQL의 주의점 l 해결방안 ¤ WHERE 문 수정 SUBSTR(JEPUM, 1, : LEN 1) = SUBSTR(: WWJEPUM, 1, : LEN 1) SUBSTR(JEPUM ==> JEPUM LIKE SUBSTR(: WWJEPUM, 1, : LEN 1) || '%' ¤ 인덱스 수정 A 109 : A 109_IND 1 : JHCOD + JEPUM SELECT INTO FROM WHERE AND SEQ : WWSEQ A 109 JHCOD = : B 1 JEPUM LIKE SUBSTR(: WWJEPUM, 1, : LEN 1) || '%' ; 17
SQL Tuning 결합 인덱스의 처리 범위 (둘 다 '='로 쓰인 경우) SELECT WHERE AND * FROM TAB 1 COL 1 = 'A' (분포도가 넓다) COL 2 = '112' (분포도가 좁다) COL 1 Œ COL 2 ROWID COL 2 COL 1 A 110 10 110 A 111 11 110 B 41 A 112 5 110 C 57 A 113 18 111 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 ( COL 1 + COL 2 ) Œ ROWID INDEX ( COL 2 + COL 1 ) 18
SQL Tuning 결합 인덱스의 처리 범위 (둘 다 '=' 로 안 쓰인 경우) SELECT * FROM TAB 1 WHERE COL 1 = 'A' AND COL 2 BETWEEN '111' AND '113' COL 1 ROWID COL 2 COL 1 A 110 10 110 B 41 A Œ COL 2 111 11 110 C 57 112 5 111 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 ( COL 1 + COL 2 ) Œ ROWID INDEX ( COL 2 + COL 1 ) 19
SQL Tuning 사례 9. 결합 인덱스의 컬럼 순서에 따른 차이 l 인덱스정보 S_WORKPLAN : S_WORKPLAN_IDX : WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE l 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 : B 1 || '%' AND DEPT_CODE = : B 2 AND GROUP_CODE = : B 3 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-UNIQUE) l 해결방안 : 인덱스 칼럼 순서 : DEPT_CODE + GROUP_CODE + WORK_ORDER_DATE 20
SQL Tuning 사례 9. 결합 인덱스의 컬럼 순서에 따른 차이 WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE LIKE '1%' = 10 = 2 Œ WORK_ ORDER 1 A 1 A 1 B 1 B 1 C 1 C 1 C 2 A DEPT_ CODE 10 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 20 20 20 30 30 40 GROUP_ WORK_ CODE ORDER 1 1 1 2 2 3 3 3 4 4 1 A 1 C 2 A 1 A 1 1 B 1 1 C 1 1 B 1 A 1 C 1 B 1 A 1 B 21
SQL Tuning 사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 l 인덱스정보 EX_SHIPPER : SHIP_DATE_IX : SDATE + SHIPPER EX_SHIPPER 테이블의 분포도 : SDATE : 1 / 365 , SHIPPER : 1 / 10 l QUERY SELECT SHIPPER, COUNT(*), SUM(AMT) FROM EX_SHIPPER WHERE SDATE BETWEEN '950101' 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 ) 22
SQL Tuning 사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 SHIP_DATE_IX EX_SHIPPER SHIP_DATE_IX 2 차 2 ● ● 가 ● 공 EX_SHIPPER 차 가 ● ● ● 공 ● ● SDATE + SHIPPER ● ● SHIPPER + SDATE 23
SQL Tuning 사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 EXECUTION PLAN -------------------------SELECT STATEMENT SORT ( GROUP BY ) CONCATENATION INDEX RANGE SCAN : SHIP_DATE_IX 1 ( NU ) SHIPPER IN (‘C’, ‘B’, ‘A’) SHIP_DATE_IX 1 : SHIPPER + SDATE + AMT SHIP_DATE_IX ● ● 처리범위는 'SHIPPER =' 와 'SDATE BETWEEN. . . ' 에 의해서 결정 2 ● ● 차 가 공 SHIPPER + SDATE + AMT 24
SQL Tuning 사례 11. 조인시 사용된 결합 인덱스의 컬럼 순서 GFLT 950 GFXC 130 # * 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 l 인덱스정보 GFLT 950 GFXC 130 A : GFLT 950_X 1 : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE (PK) A : GFLT 950_X 2 : TEAM_CODE + SPUM_CODE B : GFXC 130_X 1 : D_DAY + PART_CODE + TEAM_CODE (PK) 25
SQL Tuning 사례 11. 조인시 사용된 결합 인덱스의 컬럼 순서 l 인덱스정보 GFLT 950 GFXC 130 A : GFLT 950_X 1 : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE (PK) A : GFLT 950_X 2 : TEAM_CODE + SPUM_CODE B : GFXC 130_X 1 : D_DAY + PART_CODE + TEAM_CODE (PK) l QUERY SELECT FROM WHERE 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 GFLT 950 A, GFXC 130 B 184 NESTED LOOP A. PART_CODE = B. PART_CODE 17718 ŒA TABLE ACCESS (FULL) OF 'GFLT 950' 184 TABLE ACCESS (BY ROWID) OF 'GFXC 130' B A. ORDER_PRT = 100 184 INDEX (UNIQUE SCAN) OF 'GFXC 130_X 1' (UNIQUE) B. D_DAY = : VALUE 1 B. TEAM_CODE = : VALUE 2 B. PART_CODE BETWEEN : VALUE 3 AND : VALUE 4; l 해결 방안 : GFLT 950_X 1 : PART_CODE + CAR_CODE + D_DAY + TEAM_CODE 26
SQL Tuning 사례 12. 결합 인덱스를 구성하는 컬럼 선택 l 인덱스정보 GFLT 920 A : GFLT 920_X 1 : CAR_CODE + D_DAY + PART_CODE + TEAM_CODE (PK) GFLT 920 A : GFLT 920_X 2 : PART_CODE + SPUM_CODE GFXC 130 B : GFXC 130_X 1 : PART_CODE + TEAM_CODE (PRIMARY KEY) l QUERY SELECT FROM WHERE AND AND A. CAR_CODE, A. D_DAY, A. PART_CODE, A. SPUM_CODE, A. ORDER. PRT, A. F_HP_Q, B. TEAM_CODE GFLT 920 A, GFXC 130 B A. PART_CODE = B. PART_CODE A. D_DAY = : VALUE 1 B. PART_CODE BETWEEN : VALUE 3 AND : VALUE 4 B. TEAM_CODE = : VALUE 2; ROWS EXECUTION PLAN --------------------------0 SELECT STATEMENT HINT : CHOOSE 23 NESTED LOOP 12 TABLE ACCESS (BY ROWID) OF 'GFXC 130' ŒB 13 INDEX (RANGE SCAN) OF 'GFXC 130_X 1' (UNIQUE) 6389 TABLE ACCESS (BY ROWID) OF 'GFLT 920' A 6390 INDEX (RANGE SCAN) OF 'GFLT 920_X 2' 27
SQL Tuning 사례 12. 결합 인덱스를 구성하는 컬럼 선택 6389건 13건 12건 23건 12건 6390건 GFXC 130_X 1 GFXC 130 테이블 B GFLT 920_X 2 GFLT 920 테이블 A l 해결방안 GFLT 920 A : GFLT 920_X 2 : PART_CODE + D_DAY 28
SQL Tuning 사례 13. 결합 인덱스를 구성하는 컬럼 순서 l 인덱스정보 GFBT 400 A : GFBT 400_X 3 : TEAM_CODE + SHOP_CODE + D_DAY GFXC 440 B : GFXC 440_X 1 : SPUM_CODE (UNIQUE) GFXC 130 C : GFXC 130_X 1 : PART_CODE (UNIQUE) l QUERY SELECT A. PART_CODE, A. SPUM_CODE, A. D_DAY, A. B_F_QTY, A. TAG, B. ORDER_PRT, C. DC_CODE FROM GFBT 400 A, GFXC 440 B, GFXC 130 C WHERE A. SPUM_CODE = B. SPUM_CODE AND A. PART_CODE = C. PART_CODE AND A. D_DAY = : B 1 ROWS EXECUTION PLAN AND A. TEAM_CODE = : B 2 ; ------------------------------- 0 SELECT STATEMENT 391 NESTED LOOPS 391 TABLE ACCESS ( BY ROWID ) OF 'GFBT 400' ŒA 48276 INDEX ( RANGE SCAN ) OF 'GFBT 400_X 3' (NON-UNIQ) 391 TABLE ACCESS ( BY ROWID ) OF 'GFXC 130' C 391 INDEX ( UNIQUE SCAN ) OF 'GFXC 130_X 1' (UNIQ) 391 TABLE ACCESS ( BY ROWID ) OF 'GFXC 440' B 391 INDEX ( UNIQUE SCAN ) OF 'GFXC 440_X 1' (UNIQ) 29
SQL Tuning 사례 13. 결합 인덱스를 구성하는 컬럼 순서 GFBT 400_X 3 GFBT 400 391회 GFXC 130_X 1 391회 GFXC 130 391회 GFXC 440_X 1 391회 ● 48276 ● ● ● GFXC 440 ● ● ● TEAM_CODE + SHOP_CODE + D_DAY PART_CODE SPUM_CODE TEAM_CODE + D_DAY + SHOP_CODE 30
SQL Tuning 사례 14. 결합 인덱스의 사용을 정확히 확인 l 인덱스정보 GFBT 300 : GFBT 300_X 1 : D_DAY + SPUM_CODE + SERIAL_NO (PRIMARY KEY) GFBT 300 : GFBT 300_X 2 : SPUM_CODE + D_DAY + ORDER_PRT l QUERY SELECT D_DAY, FROM WHERE AND TEAM_CODE, SPUM_CODE, B_F_QTY, ORDER_PRT GFBT 300 D_DAY BETWEEN : VALUE 1 AND : VALUE 2 TEAM_CODE = : VALUE 3 ORDER BY ORDER_PRT ; ROWS EXECUTION PLAN ------------------------------0 SELECT STATEMENT HINT : CHOOSE 9064 SORT (ORDER BY) 29728 TABLE ACCESS (BY ROWID) OF GFBT 300' 29729 INDEX (RANGE SCAN) OF GFBT 300_X 1' (UNIQUE) l 해결 방안 : TEAM_CODE + D_DAY INDEX 추가 생성 31
SQL Tuning 사례 15. 결합 인덱스를 구성하는 컬럼의 선택 l 인덱스정보 GFST 300 GFST 100 GFXC 440 GFXC 310 DT HD C D : : GFST 300_X 2 GFST 100_X 1 GFXC 250_X 3 GFXC 310_X 1 : : YT_CODE D_DAY + YT_CODE + SHOP_CODE + SPUM_CODE YT_CODE + SHOP_CODE ( UNIQUE ) + SHOP_CODE + TEAM_CODE + SHOP_CODE ( UNIQUE ) l QUERY SELECT FROM WHERE AND AND AND GROUP DT. D_DAY, DT. YT_CODE, DT. SHOP_CODE, DT. SLIP_NO 1, DT. SLIP_NO 2, DT. TAX_GUBUN, SUM(DT. BP_AMOUNT 1 + DT. HP_AOUNT 1) GFST 300 DT, GFST 100 HD, GFXC 250 C, GFXC 310 D HD --> C --> DT C. TEAM_CODE = : B 1 : B 2 BETWEEN C. NEW_FR_DATE AND C. NEW_TO_DATE D. SUSU_CHK = 'Y' HD. D_DAY = : B 3 <--- 상수 값이므로 최초로 풀리는 부분 HD. PART_CODE BETWEEN : B 4 AND : B 5 HD. YT_CODE = DT. YT_CODE AND HD. YT_CODE = C. YT_CODE HD. SHOP_CODE = DT. SHOP_CODE AND HD. SHOP_CODE = C. SHOP_CODE HD. D_DAY = DT. D_DAY AND HD. YT_CODE = D. YT_CODE HD. SLIP_NO 1 = DT. SLIP_NO 1 AND HD. SHOP_CODE = D. SHOP_CODE HD. SLIP_NO 2 = DT. SLIP_NO 2 BY DT. D_DAY, DT. YT_CODE, DT. SHOP_CODE, DT. SLIP_NO 1, DT. SLIP_NO 2, DT. TAX_GUBUN ; 32
SQL Tuning CALL -------PARSE EXECUTE FETCH ROWS ------0 138 138 315 20494 20495 627 1075 1390 407607 407745 사례 15. 결합 인덱스를 구성하는 컬럼의 선택 COUNT CPU -------1 0. 06 14 0. 04 61 10. 60 ELAPSED DISK QUERY CURRENT ROWS -------- -------0. 06 0 0 0. 04 0 0 11. 35 38 58338 0 47 EXECUTION PLAN ---------------------------------SELECT STATEMENT SORT ( GROUP BY ) NESTED LOOPS TABLE ACCESS ( BY ROWID ) OF 'GFST 100' ŒHD INDEX ( RANGE SCAN ) OF 'GFST 100_X 1' ( UNIQUE ) TABLE ACCESS ( BY ROWID ) OF 'GFXC 310' D INDEX ( UNIQUE SCAN ) OF 'GFXC 310_X 1' ( UNIQUE ) TABLE ACCESS ( BY ROWID ) OF 'GFXC 250' C INDEX ( RANGE SCAN ) OF 'GFXC 250_X 3' ( NON-UNIQUE ) TABLE ACCESS ( BY ROWID ) OF 'GFST 300' DT INDEX ( RANGE SCAN ) OF 'GFST 300_X 2' ( NON-UNIQUE ) 33
● ● ● GFXC 310_X 1 627 GFXC 310 D GFXC 250 C 315 13901075 627 X O O X GFXC 250_X 3 GFST 300 DT GFST 300_X 2 138 407607 GFXT 100 Œ HD 20494 20495 GFST 100_X 1 사례 15. 결합 인덱스를 구성하는 컬럼의 선택 407745 SQL Tuning X X X O O O X X X ● ● ● l 해결 방안 SHOP_CODE + YT_CODE + SHOP_CODE + TEAM_CODE O X O ● ● ● X D_DAY + YT_CODE + SHOP_CODE 138 X YT_CODE + SHOP_CODE + SPUM_CODE GFST 100 HD : GFST 100_X 1 : D_DAY + PART_CODE + YT_CODE + SHOP_CODE GFST 300 DT : GFST 300_X 2 : YT_CODE + SHOP_CODE + D_DAY + SPUM_CODE 34
SQL Tuning 사례 16. OUTER 조인의 정확한 이해 KEY FLD 1 FLD 2 KEY COL 1 COL 2 A AAB 111 O O A 10 AA A AAB 111 A 10 AA B AAC 123 O O B 10 AB B AAC 123 B 10 AB C ABA 222 O O C 10 AC C ABA 222 C 10 AC D ABB 233 O X D ABB 233 E ABC 143 O X E ABC 143 원하는 결과 TAB 1 TAB 2 l 인덱스정보 TAB 1 X TAB 2 Y : TAB 1_IX : FLD 1 + KEY : TAB 2_IX : COL 1 + KEY 35
SQL Tuning l QUERY 사례 16. OUTER 조인의 정확한 이해 TAB 1 TAB 2 X : TAB 1_IX : FLD 1 + KEY Y : TAB 2_IX : COL 1 + KEY SELECT X. KEY, X. FLD 1, X. FLD 2, Y. KEY, Y. COL 1, Y. COL 2 FROM TAB 1 X, TAB 2 Y WHERE X. KEY = Y. KEY(+) n 원하는 결과 나오지 않음 AND X. FLD 1 > 'AAA' AND Y. COL 1 = 10 ; SELECT X. KEY, X. FLD 1, X. FLD 2, Y. KEY, Y. COL 1, Y. COL 2 FROM TAB 1 X, TAB 2 Y n COL 1+KEY 순서이므로 WHERE X. KEY = Y. KEY(+) 인덱스 사용 불가 AND X. FLD 1 > 'AAA' n KEY+COL 1 이면 인덱스 사용 가능 AND (Y. COL 1 = 10 OR Y. COL 1 IS NULL) ; SELECT X. KEY, X. FLD 1, X. FLD 2, Y. KEY, Y. COL 1, Y. COL 2 FROM TAB 1 X, TAB 2 Y n COL 1+KEY 인덱스 사용 가능 WHERE X. KEY = Y. KEY(+) n KEY+COL 1 이라도 사용 가능 AND X. FLD 1 > 'AAA' AND Y. COL 1(+) = 10 ; 36
SQL Tuning 사례 17. OUTER 조인에서 IN의 사용 l 인덱스정보 TAB 1 X : TAB 1_IX : FLD 1 + KEY TAB 2 Y : TAB 2_IX : COL 1 + KEY l QUERY SELECT X. KEY, X. FLD 1, X. FLD 2, Y. KEY, Y. COL 1, Y. COL 2 FROM TAB 1 X, TAB 2 Y WHERE X. KEY = Y. KEY(+) ORA-01719 Error 발생 AND X. FLD 1 > 'AAA' AND Y. COL 1(+) IN (10, 30, 50) ; l 해결 방안 SELECT FROM X. KEY, X. FLD 1, X. FLD 2, Y. KEY, Y. COL 1, Y. COL 2 TAB 1 X, (SELECT KEY, COL 1, COL 2 FROM TAB 2 WHERE COL 1 IN (10, 30, 50) ) Y WHERE X. KEY = Y. KEY(+) AND X. FLD 1 > 'AAA' ; 37
SQL Tuning 사례 18. 여러 개의 OUTER 조인 컬럼을 사용 l 인덱스정보 STPO 120 STPO 096 A : STPO 120_X 1 : SA + PART B : STPO 096_X 1 : CENTER + SA + PART l QUERY SELECT FROM WHERE AND AND A. DESCRIPTION, A. MODEL, A. DESRIPTION, NVL(B. QTY 1, 0), NVL(B. QTY 2, 0), NVL(B. QTY 3, 0), NVL(B. QTY 4, 0) STPO 120 A, STPO 096 B A. SA = B. SA(+) A. PART = B. PART(+) A. SA = : VALUE 1 A. PART BETWEEN : VALUE 2 AND : VALUE 3 B. CENTER = : VALUE 4 ; l 해결 방안 B. CENTER(+) = : VALUE 4 ALL_ROWS FIRST_ROWS EXECUTE PLAN ---------------------------SELECT STATEMENT NESTED LOOP(OUTER) ŒA TABLE ACCESS (BY ROWID) OF 'STPO 120' INDEX (RANGE SCAN) OF 'STPO 120_X 1' (UNIQUE) TABLE ACCESS (FULL) OF 'STPO 096' FULL B 38
SQL Tuning 사례 19. 배타적 관계 모델에서 OUTER 조인 활용 개인고객정보 계좌정보 # * 고객번호 # * 계좌번호 개설일 성명 ● 주소 개설지점 주민등록번호 비밀번호 실명확인여부 법인고객정보 해지상태여부 상품종류 # * 고객번호 ● 법인명 적용금리 배타적 관계 (EXCLUSIVE) 주소 사업자등록번호 재무상태 매출액 39
SQL Tuning 사례 19. 배타적 관계 모델에서 OUTER 조인 활용 계좌정보 # * 계좌번호 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 * 고객번호 * 고객종류 <- 1 or 2 40
SQL Tuning l QUERY 사례 19. 배타적 관계 모델에서 OUTER 조인 활용 SELECT A. 계좌번호, A. 개설일, B. 개인고객명, C. 법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A. 개설일 LIKE '199503%' n No Rows Selected AND A. 고객번호 = B. 고객번호 AND A. 고객번호 = C. 고객번호 ORDER BY A. 개설일 ; SELECT FROM WHERE AND A. 계좌번호, A. 개설일, B. 개인고객명, C. 법인고객명 계좌정보 A, 개인고객 B, 법인고객 C A. 개설일 LIKE '199503%' ( A. 고객번호 = B. 고객번호 n (A=B)*C + (A=C)*B OR A. 고객번호 = C. 고객번호 ) ORDER BY A. 개설일 ; l 해결 방안 SELECT A. 계좌번호, A. 개설일, B. 개인고객명, C. 법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A. 개설일 LIKE '199503%' AND A. 고객번호 = B. 고객번호(+) AND A. 고객번호 = C. 고객번호(+) ORDER BY A. 개설일 ; 41
SQL Tuning 사례 19. 배타적 관계 모델에서 OUTER 조인 활용 l CASE 2 계좌정보 # * 계좌번호 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 개인고객번호 법인고객번호 l 해결 방안 SELECT A. 계좌번호, A. 개설일, B. 개인고객명, C. 법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A. 개설일 LIKE '199503%' AND A. 개인고객번호 = B. 고객번호(+) AND A. 법인고객번호 = C. 고객번호(+) ORDER BY A. 개설일 ; 42
SQL Tuning 사례 20. OR와 같이 사용하는 OUTER 조인 l QUERY SELECT A. COL 1, A. COL 2, B. COL 3, FROM TAB 1 A, TAB 2 B WHERE A. PK 1 = B. PK 1 (+) AND A. PK 2 = B. PK 2 (+) AND ( A. COL 5 = 'AAAA' OR A. COL 5 = 'BBBB' OR A. COL 5 = 'CCCC' OR A. COL 5 = 'DDDD' ) ; B. COL 4 EXECUTION PLAN --------------------SELECT STATEMENT FILTER NESTED LOOPS OUTER TABLE ACCESS FULL : TAB 1 ŒA TABLE ACCESS BY ROWID : TAB 2 B INDEX UNIQUE SCAN : TAB 2_PK 1+PK 2 l 해결방안 1 각각의 OR 단위로 낱개의 SQL로 분리한뒤 UNION ALL을 사 용하여 모음 43
SQL Tuning 사례 20. OR와 같이 사용하는 OUTER 조인 l 해결방안 2 SELECT --+ USE_CONCAT A. COL 1, A. COL 2, B. COL 3, B. COL 4 FROM TAB 1 A, TAB 2 B WHERE A. PK 1 = B. PK 1 (+) AND A. PK 2 = B. PK 2 (+) AND ( A. COL 5 = 'AAAA' OR A. COL 5 = 'BBBB' OR A. COL 5 = 'CCCC' OR A. COL 5 = 'DDDD' ) ; EXECUTE PLAN ------------------------SELECT STATEMENT FILTER CONCATENATION NESTED LOOPS OUTER TABLE ACCESS BY ROWID TAB 1 ŒA INDEX RANGE_SCAN OF TAB 1_COL 5_IDX TABLE ACCESS BY ROWID TAB 2 B INDEX UNIQUE SCAN : TAB 2_PK 1+PK 2 NESTED LOOPS OUTER TABLE ACCESS BY ROWID TAB 1 INDEX RANGE_SCAN OF TAB 1_COL 5_IDX TABLE ACCESS BY ROWID TAB 2 INDEX UNIQUE SCAN : TAB 2_PK 1+PK 2 44
SQL Tuning 사례 21. NULL의 잘못된 사용과 OUTER 조인 l MODEL SENSOR_READING_REVISION # * READING_ID ( FK ) # * REVISION_NO DATE VALUE # * READING_ID DATE VALUE l SAMPLE l 원하는 결과 READING_ID DATE VALUE READING_ID REVISION_NO DATE VALUE 100 101 102 103 104 960501 960502 060502 960503 960504 100 110 105 200 150 100 102 103 1 2 3 1 1 2 960504 960506 960510 960509 960503 960508 102 110 108 110 205 212 READING_ID 100 101 102 103 104 DATE 960510 960502 960509 960508 960504 VALUE 108 110 212 150 REVISION_NO 3 0 1 2 0 45
SQL Tuning 사례 21. NULL의 잘못된 사용과 OUTER 조인 l QUERY SELECT A. READING_ID, NVL(B. DATE, A. DATE ), NVL (B. VALUE, A. VALUE), NVL(B. REVISION_NO, 0) FROM SENSOR_READING A, SENSOR_READING_REVISION B WHERE A. READING_ID = B. READING_ID (+) AND B. READING_ID || B. REVISION_NO IN (SELECT READING_ID || MAX ( REVISION_NO ) FROM SENSOR_READING_REVISION GROUP BY READING_ID) ; READING_ID 100 101 102 103 104 DATE 960510 960502 960509 960508 960504 VALUE 108 110 212 150 REVISION_NO 3 0 1 2 0 X n 원하는 결과 나오지 않음 X 46
SQL Tuning 사례 21. NULL의 잘못된 사용과 OUTER 조인 l 개선된 QUERY SELECT A. READING_ID, NVL(B. DATE, A. DATE), NVL(B. VALUE, A. VALUE), NVL(B. REVISION_NO, 0) FROM SENSOR_READING A, SENSOR_READING_REVISION B WHERE A. READING_ID = B. READING_ID (+) AND ( B. READING_ID || B. REVISION_NO IS NULL OR B. READING_ID || B. REVISION_NO IN (SELECT READING_ID || MAX ( REVISION_NO ) FROM SENSOR_READING_REVISION GROUP BY READING_ID) ) ; READING_ID 100 101 102 103 104 DATE 960510 960502 960509 960508 960504 VALUE 108 110 212 150 REVISION_NO 3 0 1 2 0 EXECUTION PLAN ---------------------------SELECT STATEMENT FILTER MERGE JOIN OUTER SORT JOIN TABLE ACCESS FULL : SENSOR_READING ŒA SORT JOIN TABLE ACCESS FULL : SENSOR_READING_REVISION ŒB FILTER SORT GROUP BY TABLE ACCESS FULL : SENSOR_READING_REVISION 47
SQL Tuning 사례 21. NULL의 잘못된 사용과 OUTER 조인 l 해결 방안 SELECT A. READING_ID, NVL ( B. DATE, A. DATE ), NVL ( B. VALUE, A. VALUE ), NVL ( B. REVISION_NO , 0 ) FROM SENSOR_READING A, (SELECT b 1. READING_ID, B 2. REVISION_NO, B 1. DATE, b 1. VALUE FROM SENSOR_READING_REVISION B 1, (SELECT READING_ID, MAX(REVISION_NO) AS REVISION_NO FROM SENSOR_READING_REVISION b 2 GROUP BY READING_ID) B 2 WHERE B 1. READING_ID = B 2. READING_ID AND B 1. REVISION_NO = B 2. REVISION_NO ) B WHERE A. READING_ID = B. READING_ID(+) ORDER BY READING_ID ; READING_ID 100 101 102 103 104 DATE 960510 960502 960509 960508 960504 VALUE 108 110 212 150 REVISION_NO 3 0 1 2 0 48
SQL Tuning 사례 22. 조인하는 테이블의 순서에 따른 차이 고객 D 납품실행계획 A 주문 B # * 부서번호 # * 주문번호 * 부서번호 # * 직원번호 * 고객번호 * 주문일자 # * 고객번호 고객이름 주소 주민등록번호 출고정지구분 납품예정일 소요비용 완료여부 수주진행현황 C * 직원번호 # * 회사번호 주문량 # * 부서번호 # * 주문일자 # * 고객번호 직원번호 49
SQL Tuning 사례 22. 조인하는 테이블의 순서에 따른 차이 l 인덱스정보 납품실행계획 주문 수주진행현황 고객 A B C D : : 납품실행_PK 주문_PK 수주진행현황_PK 고객_PK : : 주문번호 + 부서번호 + 주문직원 주문번호 회사번호 + 부서번호 + 주문일자 + 고객번호 SELECT DISTINCT A. 부서번호, A. 주문번호, B. 주문일자, B. 직원번호, B. 고객번호, D. 출고중지구분 FROM 납품실행계획 A , 주문 B , 수주진행현황 C , 고객 D WHERE A. 부서번호 = B. 부서번호 AND A. 주문번호 = B. 주문번호 ROWS EXECUTION PLAN AND A. 주문일자 = B. 주문일자 ----------------------------------0 SELECT STATEMENT AND C. 부서번호 = B. 부서번호 7 SORT (UNIQUE) AND C. 고객번호 = B. 고객번호 7 NESTED LOOPS 1243 NESTED LOOPS AND C. 직원번호 = B. 직원번호 1275 NESTED LOOPS AND C. 주문번호 = B. 주문번호 1275 TABLE ACCESS (BY ROWID) OF '수주진행현황' ŒC AND C. 주문일자 = B. 주문일자 1276 INDEX (RANGE SCAN) OF ' 수주진행현황_PK' (UNIQUE) 1275 TABLE ACCESS (BY ROWID) OF '고객' D AND C. 고객번호 = D. 고객번호 1275 INDEX (UNIQUE SCAN) OF ' 고객_PK' (UNIQUE) AND C. 부서번호 = 3000 1243 TABLE ACCESS (BY ROWID) OF '주문' B 1275 INDEX (UNIQUE SCAN) OF ' 주문_PK' (UNIQUE) AND C. 회사번호 = 3 20 TABLE ACCESS (BY ROWID) OF '납품실행계획' A AND A. 완료여부 IS NULL ; 1263 INDEX (RANGE SCAN) OF ' 납품실행계획_PK' (UNIQUE) 50
사례 22. 조인하는 테이블의 순서에 따른 차이 1275 납품실행계획 주문 1275 No 1243 1263 1275 고객 1275 1276 수주진행현황 1275 SQL Tuning 7 20 No X X ● ● O ● ● ● O 51
SQL Tuning 사례 22. 조인하는 테이블의 순서에 따른 차이 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 ; 52
SQL Tuning 사례 22. 조인하는 테이블의 순서에 따른 차이 수주진행현황 1275 1276 납품실행계획 1295 20 20 주문 고객 20 20 7 No X ● ● ● O ● ● ● X 53
SQL Tuning 사례 23. NULL 처리를 위해 DEFAULT 활용 l 인덱스정보 A 01003 M 20 : A 01003 M 20_U 2 : MENU_ID + MAST_MENU_ID l QUERY SELECT FROM WHERE AND DETL, PGM_ID ROWS ----0 1 2321 (UNIQUE) EXECUTION PLAN ---------------------------SELECT STATEMENT TABLE ACCESS ( BY ROWID ) OF 'A 01003 M 20' INDEX ( RANGE SCAN ) OF 'A 01003 M 20_U 2' ( UNIQUE ) A 01003 M 20 MENU_ID = : B 1 NVL(MAST_MENU_ID, ' ') = NVL(: B 2, ' ') ; l 개선된 QUERY SELECT DETL, PGM_ID FROM A 01003 M 20 WHERE MENU_ID = : B 1 AND MAST_MENU_ID = NVL(: B 2, 'X') ; 54
SQL Tuning 사례 24. MAX 값의 처리 l 인덱스정보 GFLM 600 : GFLM 600_PK : CAR_CODE + BOX_CODE + IO_DATE 두 SUB-Query의 차이는 ? l QUERY SELECT STOCK_Q FROM GFLM 600 n 원하는 대로 결과 나오지 않음 WHERE IO_DATE = (SELECT MAX(IO_DATE) FROM GFLM 600 WHERE CAR_CODE = : VALUE 1 AND BOX_CODE = : VALUE 2) ; SELECT WHERE STOCK_Q FROM GFLM 600 A IO_DATE = (SELECT MAX(IO_DATE) FROM GFLM 600 B WHERE B. CAR_CODE = AND B. BOX_CODE = AND A. CAR_CODE = AND A. BOX_CODE = : VALUE 1 : VALUE 2 B. CAR_CODE B. BOX_CODE ) ; 55
SQL Tuning 사례 24. MAX 값의 처리 l 해결 방안 1) 역순 인덱스 사용 - 효율적 SELECT /*+ INDEX_DESC(A GFLM 600_PK) */ STOCK_Q FROM GFLM 600 A WHERE CAR_CODE = : VALUE 1 AND BOX_CODE = : VALUE 2 AND ROWNUM = 1 ; 2) SUBSTR 사용 (인덱스 없을 때) SELECT SUBSTR (MAX (IO_DATE || STOCK_Q), 9, 4) FROM GFLM 600 WHERE CAR_CODE = : VALUE 1 AND BOX_CODE = : VALUE 2; 56
SQL Tuning 사례 25. MAX / MIN에서 SQL의 분리 l 인덱스정보 THISEQPM : THISEQPM_X 1 : EQPMDEPT + EQPMPART + EQPMROOM l QUERY SELECT FROM WHERE (MAX(EQPMROOM) - MIN(EQPMROOM)) + 1 INTO : NEXTROOM THISEQPMDEPT = : VALUE 1 AND EQPMPART = : VALUE 2 ; l 개선된 QUERY SELECT /*+ INDEX_ASC(A THISEQPM_X 1) */ INV. MAX_ROOM - A. EQPMROOM + 1 INTO : NEXTROOM FROM ( SELECT /*+ INDEX_DESC(THISEQPM_X 1) */ EQPMROOM AS MAX_ROOM FROM THISEQPM WHERE EQPMDEPT = : VALUE 1 AND EQPMPART = : VALUE 2 AND ROWNUM = 1 ) INV , THISEQPM A WHERE EQPMDEPT = : VALUE 1 AND EQPMPART = : VALUE 2 AND ROWNUM = 1 ; 57
SQL Tuning 사례 26. 서브쿼리 사용시 MIN / MAX 활용 l 인덱스정보 A_TAB : A_TAB_IX 1 : ID + SDATE l QUERY SELECT NVL(COUNT(*), FROM A_TAB WHERE SDATE >= : B 2 AND (ID, SDATE) IN (SELECT FROM WHERE AND 0) INTO : B 1 AND SDATE <= : B 3 AND PATH = : B 4 ID, SDATE B_TAB SDATE BETWEEN : B 2 AND : B 3 DEPT = : B 7 (ID, SDATE, SERL) IN (SELECT FROM WHERE GROUP ID, SDATE, MIN(SERL) B_TAB SDATE BETWEEN : B 2 AND : B 3 BY ID, SDATE) ); 58
SQL Tuning 사례 26. 서브쿼리 사용시 MIN / MAX 활용 l 중간 QUERY SELECT FROM WHERE AND NVL(COUNT(*), 0) INTO : B 1 A_TAB SDATE BETWEEN : B 2 AND : B 3 AND PATH = : B 4 (ID, SDATE) IN (SELECT ID, SUBSTR(MIN(SDATE || SERL), 1, 8) FROM B_TAB WHERE SDATE BETWEEN : B 2 AND : B 3 AND DEPT = : B 7 GROUP BY ID, SDATE ); B_TAB l 문제점 (원래 SQL과 결과 다름) : B 7 = 20 인 경우 원래 SQL은 ③ 번이 선택되고 SQL : B 7 에서 걸러져 결과가 나오지 않지만 이 SQL은 ① 번 로우가 나온다. SQL ID ① ② ③ ④ SDATE SERL DEPT 100 100 19970310 3 4 1 2 20 20 10 5 59
SQL Tuning 사례 26. 서브쿼리 사용시 MIN / MAX 활용 l 개선된 QUERY SELECT FROM WHERE AND * 전제조건 : SERL은 3 자리 숫자이며, ID + SDATE + SERL이 PK를 구성한다. SERL NVL(COUNT(*), 0) INTO : B 1 A_TAB SDATE BETWEEN : B 2 AND : B 3 AND PATH = : B 4 (ID, SDATE) IN (SELECT V. ID, DECODE(V. DEPT, : B 7, V. SDATE, NULL) FROM (SELECT ID, SDATE, SUBSTR(MIN(SERL || DEPT), 4, 2) AS DEPT FROM B_TAB WHERE SDATE BETWEEN : B 2 AND : B 3 GROUP BY ID, SDATE ) V ); B_TAB l SERL에 중복이 발생할 때 문제점 : B 7 = 20 인 경우 여기서는 ③번 로우가 선택되었다가 : B 7 에서 걸러져 아무 로우도 선택되지 않으나 원래 SQL은 ②번 로우가 선택된다. SQL ① ② ③ ④ ID 100 100 SDATE 19970310 SERL 3 1 1 2 DEPT 20 20 10 5 60
SQL Tuning 사례 27. 불필요한 상수 조건 l 인덱스정보 CPNW : CPNU_PK : DLTF l QUERY UPDATE SET WHERE l 개선된 QUERY UPDATE SET WHERE AND AND CPNW DLTF = : B 1, SYSF = RITM = : B 4, USER = (DLTF, SYSF, RTDT, (SELECT : B 11, FROM DUAL WHERE (: B 19 AND (: B 21 : B 2, RTDT = : B 3, : B 5, VALUE = : B 6 RITM, USER, VALUE) = : B 12, : B 13, : B 14, : B 15, : B 16 != 'Y' OR : B 19 IS NULL) = 'Y' OR : B 21 IS NOT NULL); NULL CPNW DLTF = : B 1, SYSF = : B 2, RTDT = : B 3, RITM = : B 4, USER = : B 5, VALUE = : B 6 DLTF = : B 11 AND SYSF = : B 12 AND RTDT = : B 13 RITM = : B 14 AND USER = : B 15 AND VALUE = : B 16 (: B 19 != 'Y' OR : B 19 IS NULL) : B 21 = 'Y' ; 61
SQL Tuning 사례 28. UNION 과 UNION ALL의 명확한 구분 l 인덱스정보 HSNO COL COM A B C C l QUERY SELECT FROM WHERE AND AND GROUP UNION SELECT FROM WHERE AND AND GROUP : : HSNO_PK COL_PK 1 COM_IX COM_IDX 1 : : HSNO + SERIAL_NO YEAR + HOUSE + SERIAL_NO + HSNO + SERIAL_NO 2 YEAR + HOUSE + SERIAL_NO CLASS + DAT Query의 목적에 따라 효율화 C. CODE, C. HSNO, A. DESCR, SUM(B. AMT) HSNO A, COL B, COM C A. HSNO = B. HSNO AND B. YEAR = C. YEAR B. HOUSE = C. HOUSE AND C. CODE LIKE : B 1 C. DAT BETWEEN : B 2 AND : B 3 C. CLASS <> 'C' BY C. CODE, C. HSNO, A. DESCR, SUM(B. AMT) HSNO A, COL B, COM C A. HSNO = B. HSNO AND B. YEAR = C. YEAR B. HOUSE = C. HOUSE AND C. CODE LIKE : B 1 C. DAT BETWEEN : B 2 AND : B 3 C. CLASS = 'C' BY C. CODE, C. HSNO, A. DESCR ; ROWS EXECUTION PLAN --------------------------0 SELECT STATEMENT 48 PROJECTION 48 SORT (UNIQUE) 48 UNION-ALL 733 SORT (GROUP BY) 733 NESTED LOOPS 820 NESTED LOOPS 678 TABLE ACCESS (BY ROWID) OF 'COM' 1738 INDEX (RANGE SCAN) OF 'COM_IX' (NON-UNQ) 820 TABLE ACCESS (BY ROWID) OF 'COL' 1498 INDEX (RANGE SCAN) OF 'COL_PK' (UNIQUE) 733 TABLE ACCESS (BY ROWID) OF 'HSNO' 820 INDEX (RANGE SCAN) OF 'HSNO_PK' 0 SORT (GROUP BY) 0 NESTED LOOPS 1391 TABLE ACCESS (BY ROWID) OF 'COM' 6359 INDEX (RANGE SCAN) OF 'COM_IX' (NON-UNQ) 1829 TABLE ACCESS (BY ROWID) OF 'COL' 3220 INDEX (RANGE SCAN) OF 'COL_PK 1' (UNIQUE) 0 TABLE ACCESS (BY ROWID) OF 'HSNO' 0 INDEX (RANGE SCAN) OF 'HSNO_PK' 62
SQL Tuning 사례 29. 불필요한 DUAL l QUERY SELECT INTO FROM WHERE ID, DEPT, WARD, ROOM : B 1, : B 2, : B 3, : B 4 PATN WARD = : C 1 AND NVL(DAT, '999999') >= (SELECT TO_CHAR(SYSDATE, 'YYMMDD') FROM SYS. DUAL) SYS. DUAL ORDER BY DEPT, ROOM ; l 개선된 QUERY SELECT ID, DEPT, WARD, ROOM INTO : B 1, : B 2, : B 3, : B 4 FROM PATN WHERE WARD = : C 1 AND NVL(DAT, '999999') >= TO_CHAR(SYSDATE, 'YYYYMMDD') ORDER BY DEPT, ROOM ; 63
SQL Tuning 사례 30. OR 사용 시 주의할 점 l 인덱스정보 CHULGOT : CHULGOT_IX : CHULDATE + ITEM (CASE 1) CHULGOT : CH_STATUS_ITEM : STATUS + ITEM (CASE 3, 4) CHULGOT : CH_CHULNO : CHULNO (CASE 2) l QUERY (CASE 1) l 개선된 QUERY SELECT FROM WHERE OR COUNT(*) CHULGOT (: SW = 1 AND CHULDATE = '941130') (: SW = 2 AND CHULDATE+0 LIKE '96%') ; SELECT FROM SUM(CNT) (SELECT COUNT(*) FROM CHULGOT WHERE : SW = 1 UNION ALL SELECT COUNT(*) FROM CHULGOT WHERE : SW = 2 AS CNT AND CHULDATE = '941130' AS CNT AND CHULDATE+0 LIKE '96%' ); 64
SQL Tuning 사례 30. OR 사용 시 주의할 점 l QUERY (CASE 2) SELECT FROM WHERE OR l 개선된 QUERY COUNT(*) CHULGOT CHULNO = '254' (: FILED 1 + : FILED 2 ) > 0 ; SELECT FROM SUM(CNT) ( SELECT COUNT(*) FROM CHULGOT WHERE CHULNO = UNION ALL SELECT COUNT(*) FROM CHULGOT WHERE (: FILED 1 AS CNT '254' AS CNT + : FILED 2) > 0 ) ; 65
SQL Tuning 사례 30. OR 사용 시 주의할 점 l 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 ; l 개선된 QUERY 1 SELECT --+ RULE CHULNO, CUSTNO, CHULDATE, UNCOST FROM CHULGOT WHERE (: SW = 1 AND (STATUS LIKE '1%') OR (: SW = 1 AND (STATUS LIKE '2%') OR (: SW = 2 AND (STATUS LIKE '3%') ORDER BY STATUS ; l 개선된 SELECT --+ RULE QUERY 2 CHULNO, CUSTNO, CHULDATE, UNCOST FROM CHULGOT WHERE STATUS LIKE DECODE( : SW, 1, '2%') OR STATUS LIKE DECODE( : SW, 1, '1%' , '3%') ; ORDER BY절 없음! <---- ② <---- ① 66
SQL Tuning 사례 30. OR 사용 시 주의할 점 l 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 ) ; l 개선된 QUERY SELECT --+ RULE CHULNO, CUSTNO, CHULDATE, UNCOST FROM CHULGOT WHERE STATUS BETWEEN DECODE ( : SW , 10 , 2, 20 ) AND DECODE ( : SW , 10 , 2, 40 ) ; 67
SQL Tuning 사례 31. OR의 활용 DRW_NO 구성 132 -218 -12567845 132 -218 -1256784 132 -218 -125678 132 -218 -12567 132 -218 -1256 < 프로그램 코딩으로 해결한 방법> 132 -218 -125 IF D 1 = ‘ 132 -218 -12567845’ 132 -218 -12 SELECT * FROM 도면정보 WHERE DRW_NO = : D 1 132 -218 -1 IF NOT FOUND 132 -218 SELECT * FROM 도면정보 WHERE DRW_NO = SUSTR(: D 1, 1, 15) 169 -730 -34789 IF NOT FOUND 169 -730 -3478 SELECT * FROM 도면정보 WHERE DRW_NO = SUSTR(: D 1, 1, 14) ● ● ● DRW_NO DESIGN_ID REVISION_DATE VARCHAR 2(16) VARCHAR(5) VARCHAR 2(8) PRIMARY KEY 68
SQL Tuning 사례 31. OR의 활용 EXECUTION PLAN -------------------------COUNT CONCATENATION FILTER SELECT * FROM 도면정보 TABLE WHERE ( DRW_NO = SUBSTR( : d 1 , 1, 8 ) INDEX OR DRW_NO = SUBSTR( : d 1 , 1, 9 ) OR DRW_NO = SUBSTR( : d 1 , 1, 10 ) OR DRW_NO = SUBSTR( : d 1 , 1, 11 ) OR DRW_NO = SUBSTR( : d 1 , 1, 12 ) ACCESS ( UNIQUE OR TABLE ● INDEX ACCESS OR DRW_NO = SUBSTR( : d 1 , 1, 14 ) OR DRW_NO = SUBSTR( : d 1 , 1, 15 ) OR DRW_NO = ROWNUM = 1 ; OF ‘ SYS_C 001328 ‘ ) ( BY ROWID ) OF ‘ 도면정보 ‘ ( UNIQUE SCAN ) OF ‘ SYS_C 001328 ‘ ● ● DRW_NO = SUBSTR( : d 1 , 1, 13 ) AND SCAN ) FILTER ● : d 1 ( BY ROWID ) OF ‘ 도면정보 ‘ FILTER TABLE INDEX ACCESS ( BY ROWID ) OF ‘ 도면정보 ‘ ( UNIQUE SCAN ) OF ‘ SYS_C 001328 ‘ 69
SQL Tuning 사례 32. 두 개의 UPDATE 문을 하나로 통합 l QUERY EXEC SQL UPDATE BA 003 DM SET ( DC_STUS_ID, INACT_RSN, TOT_AMT_OF_WON, TOT_AMT_OF_DLR, CUS_DUTY_TOT_AMT, CONSUM_TAX_TOT_AMT, HOLD_DATE) = ( SELECT 'Z', 'A', SUM(AMT_OF_WON), SUM(AMT_OF_DLR), SUM(CUS_DUTY_AMT), SUM(CONSUM_TAX_AMT), TO_DATE(: SYS_DATE, 'YYYYMMDD') FROM BA 004 DM WHERE BRK_CD = : BRK_CD AND DCL_YEAR = : DCL_YEAR) ; : DCL_YEAR EXEC SQL UPDATE BA 003 DM SET DUTY_TOT_AMT = (SELECT CUS_DUTY_TOT_AMT + CONSUM_TAX_TOT_AMT + LATE_FEE FROM BA 003 DM WHERE BRK_CD = : BRK_CD AND DCL_YEAR = : DCL_YEAR) WHERE BRK_CD = : BRK_CD AND DCL_CD = : DCL_YEAR ; 70
SQL Tuning 사례 32. 두 개의 UPDATE 문을 하나로 통합 EXEC SQL UPDATE BA 003 DM SET (DC_STUS_ID, INACT_RSN, TOT_AMT_OF_WON, TOT_AMT_OF_DLR, CUS_DUTY_TOT_AMT, CONSUM_TAX_TOT_AMT, HOLD_DATE, DUTY_TOT_AMT) DUTY_TOT_AMT (SELECT 'Z', 'A', TOT_AMT_OF_WON, TOT_AMT_OF_DLR, CUS_DUTY_TOT_AMT, CONSUM_TAX_TOT_AMT, TO_DATE(: SYS_DATE, 'YYYYMMDD'), CUS_DUTY_TOT_AMT + CONSUM_TAX_TOT_AMT + A. LATE_FEE FROM (SELECT SUM(AMT_OF_WON) AS TOT_AMT_OF_WON, SUM(AMT_OF_DIR) AS TOT_AMT_OF_DLR, SUM(CUS_DUTY_AMT) AS CUS_DUTY_TOT_AMT, SUM(CONSUM_TAX_AMT) AS CONSUM_TAX_TOT_AMT, FROM BA 004 DM WHERE BRK_CD = : BRK_CD AND DCL_YEAR = : DCL_YEAR ) V 1, BA 003 DM A V 1 WHERE BRK_CD = : BRK_CD AND DCL_YEAR = : DCL_YEAR ) WHERE BRK_CD = : BRK_CD AND DCL_CD = : DCL_YEAR ; = 71
SQL Tuning 사례 33. DECODE를 활용한 SQL 통합 l QUERY SELECT FROM WHERE AND NVL(SUM(DACT_MH), 0), NVL(SUM(DACT_AMT), 0) S_DAY_ACTSCH ORDERNO = : B 1 ITEMNO = : B 2; SELECT FROM WHERE AND NVL(SUM(DACT_MH), 0), NVL(SUM(DACT_AMT), 0) S_DAY_ACTSCH ORDERNO = : B 1 ITEMNO = : B 2 CON_CODE IS NOT NULL; SELECT FROM WHERE AND NVL(SUM(DACT_MH), 0), NVL(SUM(DACT_AMT), 0) S_DAY_ACTSCH ORDERNO = : B 1 ITEMNO = : B 2; (CON_CODE IS NULL OR LENGTH(CON_CODE) = 1 ); 72
SQL Tuning 사례 33. DECODE를 활용한 SQL 통합 l 개선된 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), DACT_AMT)) 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 = : B 1 AND ITEMNO = : B 2; 73
SQL Tuning 사례 34. 과도한 DECODE의 사용 l QUERY SELECT FROM WHERE AND GROUP ORDER B. TM, M. TAX, M. PUMMOK, SUM(DECODE(NAPBAN, '1', P. SSU, 0)), SUM(DECODE(NAPBAN, '1', P. KUMEK 1, 0)), SUM(DECODE(NAPBAN, '1', DECODE(B. BOKWAN, '1', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '1', DECODE(B. BOKWAN, '2', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '1', DECODE(B. BOKWAN, '3', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '2', P. SSU, 0)), SUM(DECODE(NAPBAN, '2', P. KUMEK 1, 0)), SUM(DECODE(NAPBAN, '2', DECODE(B. BOKWAN, '1', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '2', DECODE(B. BOKWAN, '2', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '2', DECODE(B. BOKWAN, '3', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '3', P. SSU, 0)), SUM(DECODE(NAPBAN, '3', P. KUMEK 1, 0)), SUM(DECODE(NAPBAN, '3', DECODE(B. BOKWAN, '1', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '3', DECODE(B. BOKWAN, '2', P. KUMEK 2, 0))), SUM(DECODE(NAPBAN, '3', DECODE(B. BOKWAN, '3', P. KUMEK 2, 0))), GGB M, GPM P, MJ B B. YT = P. YT AND B. MAEJANG = P. MAEJANG AND M. CODE = P. PUMMOK P. DATE BETWEEN : B 1 AND : B 2 BY B. TM, M. TAX, M. PUMMOK ; 74
SQL Tuning 사례 34. 과도한 DECODE의 사용 l 개선된 QUERY SELECT FROM WHERE AND GROUP B. TM, M. TAX, M. PUMMOK, SUM(DECODE(NAPBAN, '1', P. SSU, 0)), SUM(DECODE(NAPBAN, '1', P. KUMEK 1, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '11', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '12', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '13', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN, '2', P. SSU, 0)), SUM(DECODE(NAPBAN, '2', P. KUMEK 1, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '21', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '22', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '23', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN, '3', P. SSU, 0)), SUM(DECODE(NAPBAN, '3', P. KUMEK 1, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '31', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '32', P. KUMEK 2, 0)), SUM(DECODE(NAPBAN||B. BOKWAN, '33', P. KUMEK 2, 0)), GGB M, GPM P, MJ B B. YT = P. YT AND B. MAEJANG = P. MAEJANG AND M. CODE = P. PUMMOK P. DATE BETWEEN : B 1 AND : B 2 BY B. TM, M. TAX, M. PUMMOK ; 75
Скачать презентацию SQL Tuning 사례 1 OPTIMIZER MODE에 따른 인덱스
a81253096fc835d2774695d322d932c9.ppt