1 / 55

사례 1. 년 / 월이 분리된 경우에도 인덱스 사용

사례 1. 년 / 월이 분리된 경우에도 인덱스 사용. 인덱스정보 TAB_A : TAB_A_X1 : YY + MM + DD + SALE_NO 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 ;. 개선된 QUERY. SELECT A.YY || A.MM, DEPT, SUM(SALE_QTY)

dillian
Download Presentation

사례 1. 년 / 월이 분리된 경우에도 인덱스 사용

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. 사례 1. 년/월이 분리된 경우에도 인덱스 사용 • 인덱스정보 • TAB_A : TAB_A_X1 : YY + MM + DD + SALE_NO • 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 ; • 개선된 QUERY SELECT A.YY || A.MM, DEPT, SUM(SALE_QTY) FROM 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 ; DB기술자문팀

  2. EMP # * EMPNO VARCHAR2(5) ENAME VARCHAR2(20) DEPTNO VARCHAR2(5) (총 로우 수:13,148) 사례 2. BINDING 변수의 인덱스 사용 확인 • 테이블 정보 • 인덱스 정보 • EMP : EMP_EMPNO_PK : EMPNO • QUERY 1) Dynamic SQL에서 주의 2) HOST 변수 선언에서 주의 SELECT NVL (DEPTNO, ' ') INTO :B1 FROM EMP WHERE EMPNO = :B2 ; ROWS EXECUTION PLAN ------ ---------------------------------------------- 0 SELECT STATEMENT 13148TABLE ACCESS (BY ROWID) OF 'EMP' 0INDEX (RANGE SCAN) OF 'EMP_EMPNO_PK' (UNIQUE) DB기술자문팀

  3. 사례 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; Order By에 의한 SORT Plan이 없는 이유? ROWS EXECUTION PLAN ---- ----------------------------------------------- 0 SELECT STATEMENT HINT: CHOOSE 5743 TABLE ACCESS (BY ROWID) OF 'FH14' 168188 INDEX (RANGE SCAN) OF 'IFH14_KEY1' (UNIQUE) • 전제: • 적어도 하나이상의 컬럼이 NOT NULL DB기술자문팀

  4. 사례 4. MAX 값의 처리 • 인덱스정보 • GFLM600 : GFLM600_PK : CAR_CODE + BOX_CODE + IO_DATE • QUERY 두 SUB-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 MAX(IO_DATE) FROM GFLM600 B WHERE B.CAR_CODE = :VALUE1 AND B.BOX_CODE = :VALUE2 AND A.CAR_CODE = B.CAR_CODE AND A.BOX_CODE = B.BOX_CODE ) ; DB기술자문팀

  5. 사례 4. MAX 값의 처리 • 해결 방안 • 1) 역순 인덱스 사용 • 2) SUBSTR 사용 (인덱스 없을 때) SELECT /*+ INDEX_DESC(A GFLM600_PK) */ STOCK_Q FROM GFLM600 A WHERE CAR_CODE = :VALUE1 AND BOX_CODE = :VALUE2 AND ROWNUM = 1 ; SELECT SUBSTR (MAX (IO_DATE || STOCK_Q), 9, 4) FROM GFLM600 WHERE CAR_CODE = :VALUE1 AND BOX_CODE = :VALUE2; DB기술자문팀

  6. 사례 5. 불필요한 DUAL • 인덱스정보 • PATN : PATN_PK : ID + DAT (PRIMARY KEY) • PATN : PATN_IDX1 : DEPT + GUBUN • PATN : PATN_IDX2 : WARD + GUBUN • QUERY SELECT ID, DEPT, WARD, ROOM INTO :B1, :B2, :B3, :B4 FROM PATN WHERE WARD = :C1 AND NVL(DAT, '999999') >= (SELECT TO_CHAR(SYSDATE, 'YYMMDD') FROM SYS.DUAL) ORDER BY DEPT, ROOM ; • 개선된 • QUERY SELECT ID, DEPT, WARD, ROOM INTO :B1, :B2, :B3, :B4 FROM PATN WHERE WARD = :C1 AND NVL(DAT,'999999') >= TO_CHAR(SYSDATE, 'YYMMDD') ORDER BY DEPT, ROOM ; DB기술자문팀

  7. 110 A 10 110 B 41 110 C 57 111 A 11 111 B 39 111 C 76 112 A 5 112 B 73 112 C 89 113 A 18 114 A 22 결합 인덱스의 처리 범위 (둘 다 '='로 쓰인 경우) SELECT * FROM TAB1 WHERE COL1 = 'A'(분포도가 넓다) AND COL2 ='112'(분포도가 좁다) COL1 COL2 ROWID COL2 COL1 ROWID A 110 10 A 111 11 A 112 5 A 113 18 A 114 22 A 115 23 A 116 29 A 117 25 A 118 26 B 110 41 B 111 39 INDEX ( COL1 + COL2 ) INDEX ( COL2 + COL1 ) DB기술자문팀

  8. 결합 인덱스의 처리 범위 (둘 다 '=' 로 안 쓰인 경우) SELECT * FROM TAB1 WHERE COL1 = 'A' AND COL2 BETWEEN'111' AND '113' COL1 COL2 ROWID COL2 COL1 ROWID A 110 10 110 B 41 A 11111 110 C 57 111 A 11 A 112 5 111 B 39 A 11318 111 C 76 A 114 22 112 A 5 A 115 23 112 B 73 A 116 29 112 C 89 A 117 25 113 A 18 A 118 26 113 B 44 B 110 41 114 A 22 B 111 39 INDEX ( COL2 + COL1 ) INDEX ( COL1 + COL2 ) DB기술자문팀

  9. 사례 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 ORDER BY ACT_CODE ; ROWS EXECUTION PLAN ----- ---------------------------------------------------- 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기술자문팀

  10. 사례 6. 결합 인덱스의 컬럼 순서에 따른 차이 WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE LIKE '1%' = 10 = 2 DEPT_CODE + GROUP_CODE + WORK_ORDER_DATE = 10 = 2 LIKE '1%' WORK_ ORDER DEPT_ CODE GROUP_ CODE DEPT_ CODE GROUP_ CODE WORK_ ORDER 10 1 1A 10 1 1C 10 1 2A 10 2 1A 10 2 1B 10 2 1C 20 2 1B 20 3 1A 20 3 1C 30 3 1B 30 4 1A 40 4 1B 1A 10 1 1A 10 2 1A 20 3 1A 30 4 1B 10 2 1B 20 2 1B 30 3 1B 40 4 1C 10 1 1C 10 2 1C 20 3 2A 10 1 DB기술자문팀

  11. 사례 7. 간단한 결합 인덱스지만 컬럼 순서 유의 • 인덱스정보 • EX_SHIPPER : SHIP_DATE_IX : SDATE + SHIPPER • EX_SHIPPER 테이블의 분포도 : SDATE : 1 / 365 , SHIPPER : 1 / 10 • QUERY SELECT SHIPPER, COUNT(*), SUM(AMT) FROM EX_SHIPPER WHERESDATE BETWEEN '950101' AND '950430'<- 4 개월 분량, 약 33 만 건 AND SHIPPER IN ('A', 'B', 'C') GROUP BY SHIPPER ORDER BY SUM(AMT) DESC ; 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기술자문팀

  12. 사례 7. 간단한 결합 인덱스지만 컬럼 순서 유의 SHIP_DATE_IX EX_SHIPPER EX_SHIPPER SHIP_DATE_IX 2 차 가 공 2 차 가 공 ● ● ● ● ● ● ● ● ● ● SHIPPER + SDATE SDATE + SHIPPER DB기술자문팀

  13. 사례 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 2 차 차 ● ● 가 가 공 공 ● ● SHIPPER + SDATE + AMT DB기술자문팀

  14. ROWS EXECUTION PLAN ----- -------------------------------------------------- 0 SELECT STATEMENT 184 NESTED LOOP 17718 TABLE ACCESS (FULL) OF 'GFLT950' 184 TABLE ACCESS (BY ROWID) OF 'GFXC130' 184 INDEX (UNIQUE SCAN) OF 'GFXC130_X1' (UNIQUE) • A • B 사례 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 A.CAR_CODE, A.D_DAY, A.PART_CODE, A.SPUM_CODE, A.ORDER_PRT, A.HP_Q, B.TEAM_CDOE, B.UPFR_DATE FROM GFLT950 A, GFXC130 B WHERE A.PART_CODE = B.PART_CODE AND A.ORDER_PRT = 100 AND B.D_DAY = :VALUE1 AND B.TEAM_CODE = :VALUE2 AND B.PART_CODE BETWEEN :VALUE3 AND :VALUE4; • 해결 방안 : GFLT950 A : GFLT950_X1:PART_CODE+ CAR_CODE + D_DAY + TEAM_CODE DB기술자문팀

  15. 사례 8. 조인시 사용된 결합 인덱스의 컬럼 순서 GFXC130 # * PART_CODE # * D_DAY # * TEAM_CODE B_F_QTY UPPER_DATE UP GFLT950 # * PART_CODE # * CAR_CODE # * D_DAY # * TEAM_CODE SPUM_CODE ORDER_PRT F_HP_Q • 인덱스정보 • 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기술자문팀

  16. 사례 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_CODEBETWEEN :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' 13 INDEX (RANGE SCAN) OF 'GFXC130_X1' (UNIQUE) 6389 TABLE ACCESS (BY ROWID) OF 'GFLT920' 6390 INDEX (RANGE SCAN) OF 'GFLT920_X2' • B • A DB기술자문팀

  17. 사례 9. 결합 인덱스를 구성하는 컬럼 선택 13 6390 12 6389 12 23 X O ● ● ● ● ● ● ● ● ● ● ● ● X O GFXC130 테이블 B GFXC130_X1 GFLT920 테이블 A GFLT920_X2 • 해결방안 • GFLT920 A : GFLT920_X2 :PART_CODE + D_DAY DB기술자문팀

  18. 사례 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 A.PART_CODE, A.SPUM_CODE, A.D_DAY, A.B_F_QTY, A.TAG, B.ORDER_PRT, C.DC_CODE FROM GFBT400 A, GFXC440 B, GFXC130 C WHEREA.SPUM_CODE = B.SPUM_CODE AND A.PART_CODE = C.PART_CODE ANDA.D_DAY = :B1 AND A.TEAM_CODE = :B2; ROWS EXECUTION PLAN ----- --------------------------------------------------------- 0 SELECT STATEMENT 391 NESTED LOOPS 391 NESTED LOOPS 391 TABLE ACCESS ( BY ROWID ) OF 'GFBT400' 48276 INDEX ( RANGE SCAN ) OF 'GFBT400_X3' (NON-UNIQ) 391 TABLE ACCESS ( BY ROWID ) OF 'GFXC130' 391 INDEX ( UNIQUE SCAN ) OF 'GFXC130_X1' (UNIQ) 391 TABLE ACCESS ( BY ROWID ) OF 'GFXC440' 391 INDEX ( UNIQUE SCAN ) OF 'GFXC440_X1' (UNIQ) • A • C • B DB기술자문팀

  19. 사례 10. 결합 인덱스를 구성하는 컬럼 순서 GFBT400_X3 • GFBT400 • A GFXC130_X1 • GFXC130 • C GFXC440_X1 • GFXC440 • B 48276 391 391 391 391 391 X O X O X ● ● ● X X O X PART_CODE SPUM_CODE TEAM_CODE + SHOP_CODE + D_DAY • 해결방안 : TEAM_CODE + D_DAY+ SHOP_CODE DB기술자문팀

  20. 사례 11. 조인하는 테이블의 순서에 따른 차이 고객 D # * 고객번호 고객이름 주소 주민등록번호 출고정지구분 주문 B # * 주문번호 * 부서번호 * 고객번호 * 주문일자 * 직원번호 주문량 납품실행계획 A # * 부서번호 # * 주문번호 # * 직원번호 * 주문일자 납품예정일 소요비용 완료여부 수주진행현황 C # * 회사번호 # * 부서번호 # * 주문번호 # * 주문일자 # * 고객번호 직원번호 DB기술자문팀

  21. 사례 11. 조인하는 테이블의 순서에 따른 차이 • 인덱스정보 • 납품실행계획 A : 납품실행_PK : 주문번호+부서번호+ 직원번호 • 주문 B : 주문_PK : 주문번호 • 수주진행현황 C : 수주진행현황_PK : 회사번호 + 부서번호+ 주문번호 + 주문일자 + 고객번호 • 고객 D : 고객_PK : 고객번호 SELECT DISTINCT A.부서번호, A.주문번호, B.주문일자, B.직원번호, B.고객번호, D.출고중지구분 FROM 납품실행계획 A, 주문 B ,수주진행현황 C , 고객 D WHERE A.부서번호= B.부서번호 AND A.주문번호= B.주문번호 AND A.주문일자 = B.주문일자 AND C.부서번호 = B.부서번호 AND C.고객번호 = B.고객번호 AND C.직원번호 = B.직원번호 AND C.주문번호 = B.주문번호 AND C.주문일자 = B.주문일자 AND C.고객번호 = D.고객번호 AND C.부서번호 = 3000 AND C.회사번호 = 3 AND A.완료여부 IS NULL ; ROWS EXECUTION PLAN ---- ------------------------------------------------------------- 0 SELECT STATEMENT 7 SORT (UNIQUE) 7 NESTED LOOPS 1243 NESTED LOOPS 1275 NESTED LOOPS 1275 TABLE ACCESS (BY ROWID) OF '수주진행현황' 1276 INDEX (RANGE SCAN) OF ' 수주진행현황_PK' (UNIQUE) 1275 TABLE ACCESS (BY ROWID) OF '고객' 1275 INDEX (UNIQUE SCAN) OF ' 고객_PK' (UNIQUE) 1243 TABLE ACCESS (BY ROWID) OF '주문' 1275 INDEX (UNIQUE SCAN) OF ' 주문_PK' (UNIQUE) 20TABLE ACCESS (BY ROWID) OF '납품실행계획' 1263 INDEX (RANGE SCAN) OF ' 납품실행계획_PK' (UNIQUE) • C • D • B • A DB기술자문팀

  22. No No 사례 11. 조인하는 테이블의 순서에 따른 차이 수주진행현황 C 납품실행계획 A 고객 D 주문 B 1275 1275 1275 1275 1275 1243 20 7 1275 1276 1263 1243 X X ● ● ● O ● ● ● ● ● ● O DB기술자문팀

  23. 사례 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기술자문팀

  24. No 사례 11. 조인하는 테이블의 순서에 따른 차이 수주진행현황 주문 납품실행계획 고객 7 1275 1276 1295 20 20 20 20 20 20 X ● ● ● O ● ● ● ● ● ● X DB기술자문팀

  25. 사례 12. 인덱스 컬럼의 분리에 따른 문제 해결 • 인덱스정보 • THISCODE A : THISCODE_PK1 : CODEGUBN + CODENAME • THISPBSC B : THISPBSC_PK1 : PBSCIDNO • THISINPT C : THISINPT_IDX1 : INPTDATE + INPTCHRS • QUERY SELECT INPTIDNO,INPTPKND,INPT,INPTLEVL,INPTMNDR,INPTDEPT,INPTCHRS,PBSCPNME, FLOOR(MONTH_BETWEEN(SYSDATE,TO_DATE(PBSCBIRT,'YYMMDD'))/12), PBSCRSEX,PBSCPHNI,CODEDESC FROM THISCODE A, THISPBSC B, THISINPT C WHERE C.INPTCHRS = SUBSTR(A.CODENAME, 1, 1) AND C.INPTDATE = :B1 AND C.INPTGUBN IN ('A', 'B') AND C.INPTBYBY LIKE '1%' AND C.INPTIDNO = B.PBSCIDNO AND A.CODEGUBN = '11'; Access 비 효율의 발생 부분 ROWS EXECUTION PLAN ----- ------------------------------------------------------- 0 SELECT STATEMENT 40 NESTED LOOPS 40 NESTED LOOPS 480 TABLE ACCESS (BY ROWID) OF 'THISINPT' 481 INDEX (RANGE SCAN) OF 'THISINPT_IDX1'(NON-UNIQUE) 40 TABLE ACCESS (BY ROWID) OF 'THISCODE' 3290 INDEX (RANGE SCAN) OF 'THISCODE_PK1'(UNIQUE) 40 TABLE ACCESS (BY ROWID) OF 'THISPBSC' 40 INDEX (UNIQUE SCAN) OF 'THISPBSC_PK1'(UNIQUE) • C • A • B DB기술자문팀

  26. 사례 12. 인덱스 컬럼의 분리에 따른 문제 해결 • THISINPT • C • THISCODE • A • THISPBSC • B THISINPT_IDX1 THISCODE_PK1 THISPBSC_PK1 481 480 480 3290 40 40 40 40 40 X O X X X ● ● ● X O X O PBSCIDNO INPTDATE + INPTCHRS CODEGUBN + CODENAME DB기술자문팀

  27. 사례 12. 인덱스 컬럼의 분리에 따른 문제 해결 • 문제점 • 모델링 잘못: C.INPTCHRS = SUBSTR(A.CODENAME, 1, 1) • 조인순서: THISINPT(C) --> THISCODE(A) • 해결방안 • THISCODE(A) --> THISINPT(C) --> THISPBSC(B) SELECT /*+ ORDERED */ INPTIDNO,INPTPKND,INPT, ..... ............ FROM THISCODE A, THISINPT C, THISPBSC B WHERE C.INPTCHRS = SUBSTR(A.CODENAME, 1, 1) AND C.INPTDATE = :B1 AND C.INPTGUBN IN ('A','B') AND C.INPTBYBY LIKE '1%' AND C.INPTIDNO = B.PBSCIDNO AND A.CODEGUBN = '11' ; DB기술자문팀

  28. 사례 13. 조인을 활용한 GROUP BY의 처리 • 인덱스정보 • SALE : SALE_PROD_NO_SALEDATE : PROD_NO + SALEDATE (NON UNIQUE) • PRODUCT : PK_PRODUCT : PROD_NO (PRIMARY KEY) • QUERY SELECT PROD_NO, SUM ( SALE_AMT * UNIT_PRICE) FROM SALE WHERE PROD_NO BETWEEN 'P20150' AND 'P20200' AND SALEDATE LIKE '1995%' GROUP BY PROD_NO ; DB기술자문팀

  29. PROD_NO PROD_NO + SALEDATE PROD_NO + SALEDATE 해 결 방 안 PROD_NO SALEDATE ●● ●● P20150 19940110 ●● ●● P20150 19950120 ●● ●● P20151 19940102 ●● ●● P20151 19951227 ●● ●● P20200 19940120 ●● ●● P20200 19951216 ●● ●● PROD_NO SALEDATE ● ● ● ● P20150 19940110 ● ● ● ● P20150 19950120 ● ● ● ● P20151 19940102 ● ● ● ● P2015119951227 ● ● ● ● P20190 19940120 ● ● ● ● P2020019951216 ● ● ● ● PROD_NO P20148 P20149 P20150 P20151 ● ● ● P20198 P20199 P20200 ● ● ● 처 리 범 위 처 리 범 위 PRODUCT SALE SALE 사례 13. 조인을 활용한 GROUP BY의 처리 DB기술자문팀

  30. 사례 14. OR 사용 시 주의할 점 • 인덱스정보 • CHULGOT : CHULGOT_IX : CHULDATE + ITEM • CHULGOT : CH_STATUS_ITEM : STATUS + ITEM • CHULGOT : CH_CHULNO : CHULNO • QUERY • (CASE 1) SELECT COUNT(*) FROM CHULGOT WHERE (:SW = 1 AND CHULDATE = '941130') OR (:SW = 2 AND CHULDATE+0 LIKE '96%') ; • 개선된 • QUERY SELECT SUM(CNT) FROM (SELECT COUNT(*) AS CNT FROM CHULGOT WHERE :SW = 1 AND CHULDATE = '941130' UNION ALL SELECT COUNT(*) AS CNT FROM CHULGOT WHERE :SW = 2 AND CHULDATE+0 LIKE '96%' ); DB기술자문팀

  31. 사례 14. OR 사용 시 주의할 점 • QUERY (CASE 2) SELECT COUNT(*) FROM CHULGOT WHERE CHULNO = '254' OR (:FILED1 + :FILED2 ) > 0 ; SELECT MAX(CNT) FROM ( SELECT COUNT(*) AS CNT FROM CHULGOT WHERE CHULNO = '254' UNION ALL SELECT COUNT(*) AS CNT FROM CHULGOT WHERE (:FILED1 + :FILED2) > 0 ) ; • 개선된 • QUERY DB기술자문팀

  32. 사례 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 --+ 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 ; ORDER BY절 없음! • 개선된 • QUERY 2 SELECT --+ RULE CHULNO, CUSTNO, CHULDATE,UNCOST FROM CHULGOT WHERE STATUS LIKE DECODE( :SW, 1, '2%') <--- ② OR STATUS LIKE DECODE( :SW, 1, '1%' , '3%') ;<--- ① 먼저 수행 DB기술자문팀

  33. 사례 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 CHULGOT WHERE STATUS BETWEEN DECODE ( :SW , 1 , 10 , 2, 20 ) AND DECODE ( :SW , 1 , 10 , 2, 40 ) ; DB기술자문팀

  34. 사례 15. OUTER 조인의 정확한 이해 • 원하는 결과 KEY FLD1 FLD2 KEY COL1 COL2 O O O X X A AAB 111 O O O O O A 10 AA A AAB 111 A 10 AA B AAC 123 B 10 AB B AAC 123 B 10 AB C ABA 222 C 10 AC C ABA 222 C 10 AC D ABB 233 D ABB 233 E ABC 143 E ABC 143 TAB2 TAB1 • 인덱스정보 • TAB1 X : TAB1_IX : FLD1 + KEY • TAB2 Y : TAB2_IX : COL1 + KEY DB기술자문팀

  35. 사례 15. OUTER 조인의 정확한 이해 TAB1 X : TAB1_IX : FLD1 + KEY TAB2 Y : TAB2_IX : COL1 + KEY • QUERY SELECT X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2 FROM TAB1 X, TAB2 Y WHERE X.KEY = Y.KEY(+) AND X.FLD1 > 'AAA' AND Y.COL1 = 10 ; • 원하는 결과 나오지 않음 SELECT X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2 FROM TAB1 X, TAB2 Y WHERE X.KEY = Y.KEY(+) AND X.FLD1 > 'AAA' AND (Y.COL1 = 10 OR Y.COL1 IS NULL) ; • COL1+KEY 순서이므로 • 인덱스 사용 불가 • KEY+COL1 이면 인덱스 사용 가능 SELECT X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2 FROM TAB1 X, TAB2 Y WHERE X.KEY = Y.KEY(+) AND X.FLD1 > 'AAA' ANDY.COL1(+) = 10 ; • COL1+KEY 인덱스 사용 가능 • KEY+COL1 이라도 사용 가능 DB기술자문팀

  36. 사례 16. 복잡한 로직을 OUTER 조인으로 해결 • 소개 : OUTER 조인과 DECODE를 이용하여 복잡한 로직을 간단히 해결 • 요구 사항 • 입력된 카드번호에 엠보싱이 요구될 경우 최초 엠보싱일 경우에는 일련번호를 1로 셋팅하고,이미 엠보싱이 된 경우에는 입력된 카드번호의 일련번호 중 최대 번호 (MAX 일련번호)를 찾아서 그 번호에 +1 을 하여 카드엠보싱내역에 INSERT 한다. 카드엠보싱내역 테이블 # * 카드번호 # * 일련번호 유형 접수일자 발행일자 영업점번호 담당자ID DB기술자문팀

  37. 사례 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기술자문팀

  38. 사례 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기술자문팀

  39. 사례 17. DECODE를 활용한 SQL 통합 • QUERY SELECT NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0) FROM S_DAY_ACTSCH WHERE ORDERNO = :B1 AND ITEMNO = :B2; SELECT NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0) FROM S_DAY_ACTSCH WHERE ORDERNO = :B1 AND ITEMNO = :B2 AND CON_CODE IS NOT NULL; SELECT NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0) FROM S_DAY_ACTSCH WHERE ORDERNO = :B1 AND ITEMNO = :B2; AND (CON_CODE IS NULL OR LENGTH(CON_CODE) = 1 ); DB기술자문팀

  40. 사례 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기술자문팀

  41. 사례 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기술자문팀

  42. 사례 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기술자문팀

  43. 사례 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기술자문팀

  44. 사례19. 비숫한 SQL을 통합하여 SUM(DECODE)로 • 해결 방안 EXEC SQL 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 ; DB기술자문팀

  45. 사례 20. DECODE와 GROUP BY의 효율적인 사용 DB기술자문팀

  46. 사례 20. DECODE와 GROUP BY의 효율적인 사용 SELECT 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; 1분 13초 DB기술자문팀

  47. 사례 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월, SUM(DECODE(REG_MM,'199612',CNT,0)) AS 9612월 FROM (SELECT /*+ FULL(QUALIFIER) PARALLEL(QUALIFIER,2) */ JOB_CLSS AS JC, SEX, 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 ; Parallel: 14초 No Parallel : 27초 DB기술자문팀

  48. 사례21. 데이터 복제를 이용하여 빠짐없이 표시 • 소개 : 데이터 복제를 이용하여 기준이 되는 항목에 대하여 데이터가 없어도 • 빠짐없이 모두 표시 피보험자격정보 # * 주민등록번호 성명 자격취득일자 지역코드 (FK) 주소 성별 지역정보 # * 지역코드 지역명 대표지부장 • 위와 같은 환경하에 모든 지역에 대하여 한주일 동안 자격을 취득한 모든 사람을 지역별, 성별로 구분하여 표시 • 원하는 기간에 대해 해당지역에 자격취득자가 없는 경우에도 남녀 항목이 생성되어 “0” 이라는 값을 가짐 DB기술자문팀

  49. 사례21. 데이터 복제를 이용하여 빠짐없이 표시 • 표시 예 지역명 성 별 기준일 +1 +2 +3 +4 +5 +6 3 2 1 0 1 0 남 서울 4 1 0 1 서울 2 1 3 여 ..... ..... ..... ..... ..... ..... ..... ..... ..... 경남 2 1 0 0 1 1 1 남 0 0 0 0 0 0 0 여 경남 0 0 0 0 0 0 0 남 제주 0 0 0 0 0 0 1 여 제주 DB기술자문팀

  50. 사례21. 데이터 복제를 이용하여 빠짐없이 표시 • 간단한 SQL SELECT 지역명, 성별, SUM(DECODE(자격취득일자, :입력일자 , 1), SUM(DECODE(자격취득일자, :입력일자+1, 1), SUM(DECODE(자격취득일자, :입력일자+2, 1), SUM(DECODE(자격취득일자, :입력일자+3, 1), SUM(DECODE(자격취득일자, :입력일자+4, 1), SUM(DECODE(자격취득일자, :입력일자+5, 1), SUM(DECODE(자격취득일자, :입력일자+6, 1), FROM 피보험자자격정보 A, 지역정보 B WHERE A.자격취득일자 BETWEEN :입력일자 AND :입력일자+6 AND A.지역코드 = B.지역코드 GROUP BY B.지역명 , A.성별 ; • 문제점 : “경남지역 여”나 “제주지역 남”처럼 자격을 취득한 로우가 없으면 • 해당로우가 나타나지 않음 DB기술자문팀

More Related