사례 1. OPTIMIZER MODE에 따른 인덱스 사용

Presentation on theme: "사례 1. OPTIMIZER MODE에 따른 인덱스 사용"— Presentation transcript:

1 사례 1. OPTIMIZER MODE에 따른 인덱스 사용
인덱스정보 COL A : COL_PK1 : YEAR + HOUSE + SERIAL_NO + HSNO COM B : COM_IX : YEAR + HOUSE + SERIAL_NO COM B : COM_IDX1 : CLASS + DATE QUERY SELECT DISTINCT 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 ( 'C', 'Y', 'I', 'P' ) GROUP BY B.CLASS ; COM Table의 Full Table Scan 이유는? ROWS EXECUTION PLAN 0 SELECT STATEMENT SORT (GROUP BY) NESTED LOOP TABLE ACCESS (FULL) OF 'COM' TABLE ACCESS (BY ROWID) OF 'COL' INDEX (RANGE SCAN) OF 'COL_PK1'(UNIQUE) optimizer mode에 따라 execution plan이 달라질 수도 index를 사용하지 않고 full scan하는 이유는 optimizer mode가 choose(default)이기 때문 7.2.X : optimizer가 OLTP, 소량의 data에 맞게 execution plan 작성. : COM_IDX1 index를 사용할 수도 있다. 7.3.X : data warehousing, batch processing, 대량 : choose, all_rows(DWH), full scan 결합 index의 첫번째 column이 where 절에서 상수와 비교되어야 해당 index 사용. nested loop : driving table의 size를 고려(작을 수록 유리) : 같은 조건이면 from 절의 뒷쪽이 driving table sort merge : 스스로 일의 양을 줄일수 있는 경우 hash join : 연결고리를 hash 함수로 사용, memory 상에서 이루어져 속도 향상 : 대용량에 유리 (only 7.3) B A 대전지원팀

2 사례 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 ; group by 절로 인하여 distinct는 무의미 optimizer_mode=choose(default)이면, analyze된 table과 그렇지 않은 table이 함께 사용될 때 일관성 있는 execution plan을 작성하지 못함. 14page에 optimizer_mode=first_rows라고 지정하여 사용하는 것이 좋다고 되어 있으나, 업무의 성격에 따라 다르게 사용하는 것이 좋다.(OLTP:first_rows, BATCH:all_rows) 7.3에서의 optimizer mode가 choose이면, 통계정보의 유/무에 따라 CBO/RBO 결정 통계정보는 analyze table compute/estimate/delete statistics RBO : 20여가지의 rule에 따라 ranking analyze시에 temp tablespace size가 table size의 4배정도 되어야 다른 appl.에 영향을 주지 않는다. 대전지원팀

3 사례 2. 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이 없는 이유? index를 구성하는 column의 일부만 where 절에 나타나고, 일부가 order by 절에 나타나는 경우에도 index를 사용하는가 ? order by 절에서 index의 첫번째 column을 사용하면 해당 index를 사용. where 절과 order by절에 나타나는 column이 모두 결합 index를 구성하므로 order by 절에 의한 sorting을 index를 사용하여 해결. 결합 index에서의 column의 수 : 5개까지는 정확하게 match하여 찾아내나, 그 이후는 range scan 사용. order by에서 sorting을 줄이는 방법이 tuning의 기본 원리. 적어도 하나 이상의 컬럼이 NOT NULL ROWS EXECUTION PLAN 0 SELECT STATEMENT HINT: CHOOSE TABLE ACCESS (BY ROWID) OF 'FH14' INDEX (RANGE SCAN) OF 'IFH14_KEY1' (UNIQUE) 대전지원팀

4 부분범위(Partial Range Scan) 의 처리
조건을 만족하는 Row 수가 Array Size 에 도달되면 멈춤 전 체 범 위 처 리 부 분 범 위 처 리 1 차 스 캔 2 차 가 공 1 차 스 캔 2 차 가 공 운반단위 운반단위 전체범위처리 : table 전체를 search해야 원하는 결과를 얻을 수 있다. : batch processing에 유리. min, max, sum, group by 절 부분범위처리 : OLTP 이 과정에서는 부분범위처리에 focus. 운반단위 : array size Full Range Scan 후 가공하여 Array Size 만큼 추출 대전지원팀

5 부분범위(Partial Range Scan) 의 처리
부분범위 처리의 성능 결정 사용 된 INDEX에 의해서 결정 주 범위 (Driving Range) 넓다 좁다 부 범위 (Checking Range) 넓다 좁다 성 능 양호 불량 주 / 부 조건의 변경 가능 ? 주범위 : 먼저 읽혀지는 driving range 부범위 : 나중에 읽혀지는 checking range index를 이용하여 주/부 조건을 변경 가능. 대전지원팀

6 사례 3. 년/월이 분리된 경우에도 인덱스 사용 인덱스정보
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 ; 일자 정보가 YY, MM, DD로 나뉘어져 있으나, 조건 검색시 합쳐서 잘못 사용 index column 변형, index 사용불가, full scan 사고방식 전환 : any idea ? 기간을 연별로 나누고 월별로 나눠서 검색 조건을 주면 대전지원팀

7 사례 3. 년/월이 분리된 경우에도 인덱스 사용 개선된 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 ; index 사용 가능. 대전지원팀

8 사례 4. BINDING 변수의 인덱스 사용 확인 테이블 정보 EMP # * EMPNO VARCHAR2(5)
인덱스 정보 EMP : EMP_EMPNO_PK : EMPNO QUERY EMP # * EMPNO VARCHAR2(5) ENAME VARCHAR2(20) DEPTNO VARCHAR2(5) (총 로우 수:13,148) 1) Dynamic SQL에서 주의 2) HOST 변수 선언에서 주의 binding 변수의 data type과 column의 data type이 일치하지 않으면, index 사용불가 execution plan 생성시에는 binding variable의 data type이 무엇인지 모르기 때문에 정상적인 execution plan(index 사용 가능한)이 생성되나, binding 후에 data type이 일치하지 않아 내부 변형이 일어나 index를 사용하지 못함.(실제로는 full scan) to_number(EMPNO) = :B2 형태로 내부변형 EMPNO = to_char(:B2) trace 정보가 충분하지 않을 수도 있다. data modelling 잘못 : number로 바꾸면 like 사용시 불편 empno like ‘1%’ 가 to_char(empno) like ‘1%’ appl.의 성향을 반영하여 data modelling 해야하며, 그렇지 않으면 반드시 대가를 치른다. char, varchar2 < number, date < like 연산자 < char, varchar2 1자리의 data는 char(1byte)를 사용.(varchar2는 2byte) SELECT NVL (DEPTNO, ' ') INTO :B1 FROM EMP WHERE EMPNO = :B2 ; ROWS EXECUTION PLAN 0 SELECT STATEMENT TABLE ACCESS (BY ROWID) OF 'EMP' INDEX (RANGE SCAN) OF 'EMP_EMPNO_PK' (UNIQUE) 대전지원팀

9 사례 5. 인덱스 컬럼의 분리에 따른 문제 해결 인덱스정보
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 비 효율의 발생 부분 설계단계에서 원자화하지 않은 컬럼이 코딩단계에서 어떤 문제로 나타나며, 수행 속도에 어떠한 영향을 주는지 ? 사용 가능한 index 후보는 THISCODE_PK1, THISINPT_IDX1이지만 PK가 우선이므로 A, C의 순서로 되리라 예상되나 이때 join되는 column에 index가 존재하지 않는 연결고리 이상 발생 이러한 경우, 상대방이 먼저 풀리게 되므로 C, A, B의 순서로 처리 이때 연결고리가되는 부분에 substr()을 사용하였기 때문에 변형이 일어나 적절한 index인 THISCODE_PK1를 충분히 활용하지 못함 data modelling에서 물리적 설계로 옮겨갈때, 이러한 원자화 부분을 어떻게 할지를 충분히 고려하지 않고 개발하였기 때문에 이러한 문제 발생. ROWS EXECUTION PLAN 0 SELECT STATEMENT 40 NESTED LOOPS 40 NESTED LOOPS TABLE ACCESS (BY ROWID) OF 'THISINPT' INDEX (RANGE SCAN) OF 'THISINPT_IDX1'(NON-UNIQUE) TABLE ACCESS (BY ROWID) OF 'THISCODE' INDEX (RANGE SCAN) OF 'THISCODE_PK1'(UNIQUE) 40 TABLE ACCESS (BY ROWID) OF 'THISPBSC' INDEX (UNIQUE SCAN) OF 'THISPBSC_PK1'(UNIQUE) C A B 대전지원팀

10 사례 5. 인덱스 컬럼의 분리에 따른 문제 해결 C A B 481 3290 40 480 40 40 THISINPT
THISCODE A THISPBSC B THISINPT_IDX1 THISCODE_PK1 THISPBSC_PK1 481 3290 40 480 40 40 X O X X X ● X O A, C의 순서로하면 nested loop의 수가 480번에서 40번으로 줄어든다. X O INPTDATE PBSCIDNO CODEGUBN + CODENAME + INPTCHRS 대전지원팀

11 사례 5. 인덱스 컬럼의 분리에 따른 문제 해결 문제점 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' ; 대전지원팀

12 사례 6. 의도적 인덱스 컬럼의 변형시 주의점 인덱스정보 SUGA : SUGA_CODE_SE_PK : CODE + SERL
SUGA : SUGA_SERL_IX : SERL + EDSC SUGA : SUGA_SERL_IX2 : PART_CODE + EDSC + SERL + MODEL_NO QUERY SELECT NVL(CODE, ' '), NVL(EDSC, ' ') FROM SUGA WHERE CODE LIKE :B1 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' INDEX (RANGE SCAN) OF 'SUGA_CODE_SE_PK' (UNIQUE) 대전지원팀

13 사례 6. 의도적 인덱스 컬럼의 변형시 주의점 해결방안
SELECT NVL(CODE, ' '), NVL(EDSC, ' ') FROM SUGA WHERE CODE LIKE :B1 AND SERL IN ('CA' , 'DA') AND ROWNUM < 21 ; IN --> OR --> OR 와 ROWNUM 같이 사용 --> FILTER --> 전체범위 처리 SELECT NVL(CODE, ' '), NVL(EDSC, ' ') FROM (SELECT CODE, EDSC FROM SUGA WHERE CODE LIKE :B1 AND SERL = 'CA' <--- 첫번째 실행 UNION ALL SELECT CODE, EDSC WHERE CODE LIKE :B1 AND SERL = 'DA') <--- 두 번째 실행 WHERE ROWNUM < 21 ; SUGA_CODE_SE_PK : SERL + CODE 대전지원팀

14 사례 7. 컬럼값을 연결하여 사용할 때 주의점 인덱스정보 DRAWING : DWG_X1 : DWG1
QUERY SELECT DWG_QTY FROM DRAWING WHERE DWG1 || DWG2 || DWG3 > :DWG1 || :DWG2 || :DWG3 AND ROWNUM = 1 ; > > > DWG1 DWG2 DWG :DWG1 :DWG2 :DWG3 컬럼값 크기비교 입력변수값 대전지원팀

15 사례 7. 컬럼값을 연결하여 사용할 때 주의점 해결방안 컬럼값과 변수값의 길이가 동일하다는 전제조건
SELECT /*+ USE_CONCAT */ DWG_QTY FROM DRAWING WHERE ( (DWG1 = :DWG1 AND DWG2 = :DWG2 AND DWG3 > :DWG3) OR (DWG1 = :DWG1 AND DWG2 > :DWG2) OR (DWG1 > :DWG1) ) AND ROWNUM = 1 ; 대전지원팀

16 사례 8. LOOP 내에 사용되는 SQL의 주의점 인덱스정보 A109 : A109_IND1 : JHCOD + SEQ QUERY
SELECT SEQ INTO :WWSEQ FROM A109 WHERE JHCOD = :B1 AND SUBSTR (JEPUM, 1, :LEN1) = SUBSTR (:WWJEPUM , 1, :LEN1) ; CALL COUNT CPU ELAPSED DISK QUERY CURRENT ROWS PARSE EXECUTE FETCH TOTAL ROWS EXECUTION PLAN 0 SELECT STATEMENT TABLE ACCESS (BY ROWID) OF 'A109' INDEX (RANGE SCAN) OF 'A109_IND1' ( NON - UNIQUE ) 대전지원팀

17 사례 8. LOOP 내에 사용되는 SQL의 주의점 해결방안 WHERE 문 수정
SUBSTR(JEPUM, 1, :LEN1) = SUBSTR(:WWJEPUM, 1, :LEN1) ==> JEPUM LIKE SUBSTR(:WWJEPUM, 1, :LEN1) || '%' 인덱스 수정 A109 : A109_IND1 : JHCOD + JEPUM SELECT SEQ INTO :WWSEQ FROM A109 WHERE JHCOD = :B1 AND JEPUM LIKE SUBSTR(:WWJEPUM, 1, :LEN1) || '%' ; 대전지원팀

18 결합 인덱스의 처리 범위 (둘 다 '='로 쓰인 경우)
SELECT * FROM TAB1 WHERE COL1 = 'A' (분포도가 넓다) AND COL2 = '112' (분포도가 좁다) COL COL ROWID COL COL1 ROWID A A B C A B C A B C A A A A A A A A A A B B INDEX ( COL1 + COL2 ) INDEX ( COL2 + COL1 ) 대전지원팀

19 결합 인덱스의 처리 범위 (둘 다 '=' 로 안 쓰인 경우)
결합 인덱스의 처리 범위 (둘 다 '=' 로 안 쓰인 경우) SELECT * FROM TAB1 WHERE COL1 = 'A' AND COL2 BETWEEN '111' AND '113' COL COL ROWID COL COL1 ROWID A B A C A A A B A C A A A B A C A A B B B A INDEX ( COL1 + COL2 ) INDEX ( COL2 + COL1 ) 대전지원팀

20 사례 9. 결합 인덱스의 컬럼 순서에 따른 차이 인덱스정보
사례 9. 결합 인덱스의 컬럼 순서에 따른 차이 인덱스정보 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 SORT (ORDER BY) TABLE ACCESS (BY ROWID) OF 'S_WORKPLAN' INDEX (RANGE SCAN) OF 'S_WORKPLAN_IDX' (NON-UNIQUE) 해결방안 : 인덱스 칼럼 순서 : DEPT_CODE + GROUP_CODE + WORK_ORDER_DATE 대전지원팀

21 사례 9. 결합 인덱스의 컬럼 순서에 따른 차이 WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE
사례 9. 결합 인덱스의 컬럼 순서에 따른 차이 WORK_ORDER_DATE + DEPT_CODE + GROUP_CODE LIKE '1%' = = 2 DEPT_CODE + GROUP_CODE + WORK_ORDER_DATE = = LIKE '1%' WORK_ ORDER DEPT_ CODE GROUP_ CODE DEPT_ CODE GROUP_ CODE WORK_ ORDER 1A 1A 1A 1A 1B 1B 1B 1B 1C 1C 1C 2A A C A A B C B A C B A B 대전지원팀

22 사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 인덱스정보
사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 인덱스정보 EX_SHIPPER : SHIP_DATE_IX : SDATE + SHIPPER EX_SHIPPER 테이블의 분포도 : SDATE : 1 / 365 , SHIPPER : 1 / 10 QUERY SELECT SHIPPER, COUNT(*), SUM(AMT) FROM EX_SHIPPER WHERE SDATE 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 ) 대전지원팀

23 사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 SHIP_DATE_IX EX_SHIPPER SHIP_DATE_IX
사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 SHIP_DATE_IX EX_SHIPPER SHIP_DATE_IX EX_SHIPPER 2 차 가 공 2 차 가 공 ● ● ● ● SDATE + SHIPPER SHIPPER + SDATE 대전지원팀

24 사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 SHIPPER IN (‘C’, ‘B’, ‘A’)
사례 10. 간단한 결합 인덱스지만 컬럼 순서 유의 EXECUTION PLAN SELECT STATEMENT SORT ( GROUP BY ) CONCATENATION 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 대전지원팀

25 사례 11. 조인시 사용된 결합 인덱스의 컬럼 순서 GFLT950 GFXC130 인덱스정보
# * PART_CODE # * CAR_CODE # * D_DAY # * TEAM_CODE SPUM_CODE ORDER_PRT F_HP_Q GFXC130 # * 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) 대전지원팀

26 사례 11. 조인시 사용된 결합 인덱스의 컬럼 순서 인덱스정보
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; ROWS EXECUTION PLAN 0 SELECT STATEMENT NESTED LOOP TABLE ACCESS (FULL) OF 'GFLT950' TABLE ACCESS (BY ROWID) OF 'GFXC130' INDEX (UNIQUE SCAN) OF 'GFXC130_X1' (UNIQUE) A B 해결 방안 : GFLT950_X1 : PART_CODE + CAR_CODE + D_DAY + TEAM_CODE 대전지원팀

27 사례 12. 결합 인덱스를 구성하는 컬럼 선택 인덱스정보
사례 12. 결합 인덱스를 구성하는 컬럼 선택 인덱스정보 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 TABLE ACCESS (BY ROWID) OF 'GFXC130' INDEX (RANGE SCAN) OF 'GFXC130_X1' (UNIQUE) TABLE ACCESS (BY ROWID) OF 'GFLT920' INDEX (RANGE SCAN) OF 'GFLT920_X2' B A 대전지원팀

28 사례 12. 결합 인덱스를 구성하는 컬럼 선택 해결방안
사례 12. 결합 인덱스를 구성하는 컬럼 선택 6389건 23건 13건 12건 12건 6390건 GFXC130_X1 GFXC130 테이블 B GFLT920_X2 GFLT920 테이블 A 해결방안 GFLT920 A : GFLT920_X2 : PART_CODE + D_DAY 대전지원팀

29 사례 13. 결합 인덱스를 구성하는 컬럼 순서 인덱스정보
사례 13. 결합 인덱스를 구성하는 컬럼 순서 인덱스정보 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 WHERE A.SPUM_CODE = B.SPUM_CODE AND A.PART_CODE = C.PART_CODE AND A.D_DAY = :B1 AND A.TEAM_CODE = :B2 ; ROWS EXECUTION PLAN 0 SELECT STATEMENT NESTED LOOPS NESTED LOOPS TABLE ACCESS ( BY ROWID ) OF 'GFBT400' INDEX ( RANGE SCAN ) OF 'GFBT400_X3' (NON-UNIQ) TABLE ACCESS ( BY ROWID ) OF 'GFXC130' INDEX ( UNIQUE SCAN ) OF 'GFXC130_X1' (UNIQ) TABLE ACCESS ( BY ROWID ) OF 'GFXC440' INDEX ( UNIQUE SCAN ) OF 'GFXC440_X1' (UNIQ) A C B 대전지원팀

30 TEAM_CODE + D_DAY + SHOP_CODE
사례 13. 결합 인덱스를 구성하는 컬럼 순서 GFBT400_X3 GFBT400 GFXC130_X1 GFXC130 GFXC440_X1 GFXC440 391회 391회 391회 391회 391회 ● ● 48276 ● ● ● ● PART_CODE TEAM_CODE + SHOP_CODE + D_DAY SPUM_CODE TEAM_CODE + D_DAY + SHOP_CODE 대전지원팀

31 해결 방안 : TEAM_CODE + D_DAY INDEX 추가 생성
사례 14. 결합 인덱스의 사용을 정확히 확인 인덱스정보 GFBT300 : GFBT300_X1 : D_DAY + SPUM_CODE + SERIAL_NO (PRIMARY KEY) GFBT300 : GFBT300_X2 : SPUM_CODE + D_DAY + ORDER_PRT QUERY SELECT D_DAY, TEAM_CODE, SPUM_CODE, B_F_QTY, ORDER_PRT FROM GFBT300 WHERE D_DAY BETWEEN :VALUE1 AND :VALUE2 AND TEAM_CODE = :VALUE3 ORDER BY ORDER_PRT ; ROWS EXECUTION PLAN 0 SELECT STATEMENT HINT : CHOOSE SORT (ORDER BY) TABLE ACCESS (BY ROWID) OF GFBT300' INDEX (RANGE SCAN) OF GFBT300_X1' (UNIQUE) 해결 방안 : TEAM_CODE + D_DAY INDEX 추가 생성 대전지원팀

32 사례 15. 결합 인덱스를 구성하는 컬럼의 선택 인덱스정보 QUERY
사례 15. 결합 인덱스를 구성하는 컬럼의 선택 인덱스정보 GFST300 DT : GFST300_X2 : YT_CODE + SHOP_CODE + SPUM_CODE GFST100 HD : GFST100_X1 : D_DAY + YT_CODE + SHOP_CODE ( UNIQUE ) GFXC440 C : GFXC250_X3 : YT_CODE + SHOP_CODE + TEAM_CODE GFXC310 D : GFXC310_X1 : YT_CODE + SHOP_CODE ( UNIQUE ) QUERY SELECT DT.D_DAY, DT.YT_CODE, DT.SHOP_CODE, DT.SLIP_NO1, DT.SLIP_NO2, DT.TAX_GUBUN, SUM(DT.BP_AMOUNT1 + DT.HP_AOUNT1) FROM GFST300 DT, GFST100 HD, GFXC250 C, GFXC310 D WHERE C.TEAM_CODE = :B1 AND :B2 BETWEEN C.NEW_FR_DATE AND C.NEW_TO_DATE AND D.SUSU_CHK = 'Y' AND HD.D_DAY = :B3 <--- 상수 값이므로 최초로 풀리는 부분 AND HD.PART_CODE BETWEEN :B4 AND :B5 AND HD.YT_CODE = DT.YT_CODE AND HD.YT_CODE = C.YT_CODE AND HD.SHOP_CODE = DT.SHOP_CODE AND HD.SHOP_CODE = C.SHOP_CODE AND HD.D_DAY = DT.D_DAY AND HD.YT_CODE = D.YT_CODE AND HD.SLIP_NO1 = DT.SLIP_NO1 AND HD.SHOP_CODE = D.SHOP_CODE AND HD.SLIP_NO2 = DT.SLIP_NO2 GROUP BY DT.D_DAY, DT.YT_CODE, DT.SHOP_CODE, DT.SLIP_NO1, DT.SLIP_NO2, DT.TAX_GUBUN ; HD --> D --> C --> DT 대전지원팀

33 사례 15. 결합 인덱스를 구성하는 컬럼의 선택 HD D C DT
사례 15. 결합 인덱스를 구성하는 컬럼의 선택 CALL COUNT CPU ELAPSED DISK QUERY CURRENT ROWS PARSE EXECUTE FETCH ROWS EXECUTION PLAN 0 SELECT STATEMENT SORT ( GROUP BY ) NESTED LOOPS NESTED LOOPS NESTED LOOPS TABLE ACCESS ( BY ROWID ) OF 'GFST100' INDEX ( RANGE SCAN ) OF 'GFST100_X1' ( UNIQUE ) TABLE ACCESS ( BY ROWID ) OF 'GFXC310' INDEX ( UNIQUE SCAN ) OF 'GFXC310_X1' ( UNIQUE ) TABLE ACCESS ( BY ROWID ) OF 'GFXC250' INDEX ( RANGE SCAN ) OF 'GFXC250_X3' ( NON-UNIQUE ) TABLE ACCESS ( BY ROWID ) OF 'GFST300' INDEX ( RANGE SCAN ) OF 'GFST300_X2' ( NON-UNIQUE ) HD D C DT 대전지원팀

34 사례 15. 결합 인덱스를 구성하는 컬럼의 선택 해결 방안 GFXT100 HD GFXC310 D GFXC250 C
사례 15. 결합 인덱스를 구성하는 컬럼의 선택 GFST100_X1 GFXT100 HD GFXC310_X1 GFXC310 D GFXC250_X3 GFXC250 C GFST300_X2 GFST300 DT 20495 1390 20494 627 627 315 1075 138 407607 138 407745 X X O O X X ● O X ● O X O O O O O X X ● X X X D_DAY + YT_CODE + SHOP_CODE SHOP_CODE + YT_CODE YT_CODE + SHOP_CODE + TEAM_CODE YT_CODE + SHOP_CODE + SPUM_CODE 해결 방안 GFST100 HD : GFST100_X1 : D_DAY + PART_CODE + YT_CODE + SHOP_CODE GFST300 DT : GFST300_X2 : YT_CODE + SHOP_CODE + D_DAY + SPUM_CODE 대전지원팀

35 사례 16. OUTER 조인의 정확한 이해 인덱스정보 TAB1 X : TAB1_IX : FLD1 + KEY
KEY FLD1 FLD2 KEY COL1 COL2 A AAB 111 O O X A AA A AAB A AA B AAC 123 B AB B AAC B AB C ABA 222 C AC C ABA C AC D ABB 233 D ABB 233 E ABC 143 E ABC 143 원하는 결과 TAB1 TAB2 인덱스정보 TAB1 X : TAB1_IX : FLD1 + KEY TAB2 Y : TAB2_IX : COL1 + KEY 대전지원팀

36 사례 16. OUTER 조인의 정확한 이해 QUERY
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' AND Y.COL1(+) = 10 ; COL1+KEY 인덱스 사용 가능 KEY+COL1 이라도 사용 가능 대전지원팀

37 사례 17. OUTER 조인에서 IN의 사용 인덱스정보 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(+) IN (10,30,50) ; ORA Error 발생 해결 방안 SELECT X.KEY, X.FLD1, X.FLD2, Y.KEY, Y.COL1, Y.COL2 FROM TAB1 X, (SELECT KEY, COL1, COL2 FROM TAB2 WHERE COL1 IN (10,30,50) ) Y WHERE X.KEY = Y.KEY(+) AND X.FLD1 > 'AAA' ; 대전지원팀

38 사례 18. 여러 개의 OUTER 조인 컬럼을 사용 인덱스정보 STPO120 A : STPO120_X1 : SA + PART
STPO096 B : STPO096_X1 : CENTER + SA + PART QUERY SELECT A.DESCRIPTION, A.MODEL, A.DESRIPTION, NVL(B.QTY1,0), NVL(B.QTY2,0), NVL(B.QTY3, 0), NVL(B.QTY4,0) FROM STPO120 A, STPO096 B WHERE A.SA = B.SA(+) AND A.PART = B.PART(+) AND A.SA = :VALUE1 AND A.PART BETWEEN :VALUE2 AND :VALUE3 AND B.CENTER = :VALUE4 ; EXECUTE PLAN SELECT STATEMENT NESTED LOOP(OUTER) TABLE ACCESS (BY ROWID) OF 'STPO120' INDEX (RANGE SCAN) OF 'STPO120_X1' (UNIQUE) TABLE ACCESS (FULL) OF 'STPO096' 해결 방안 B.CENTER(+) = :VALUE4 A B ALL_ROWS FIRST_ROWS 대전지원팀

39 사례 19. 배타적 관계 모델에서 OUTER 조인 활용
개인고객정보 계좌정보 # * 고객번호 성명 주소 주민등록번호 # * 계좌번호 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 ● 법인고객정보 # * 고객번호 법인명 주소 사업자등록번호 재무상태 매출액 ● 배타적 관계 (EXCLUSIVE) 대전지원팀

40 사례 19. 배타적 관계 모델에서 OUTER 조인 활용
계좌정보 # * 계좌번호 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 * 고객번호 * 고객종류 <- 1 or 2 대전지원팀

41 사례 19. 배타적 관계 모델에서 OUTER 조인 활용
QUERY SELECT A.계좌번호, A.개설일, B.개인고객명, C.법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A.개설일 LIKE '199503%' AND A.고객번호 = B.고객번호 AND A.고객번호 = C.고객번호 ORDER BY A.개설일 ; No Rows Selected SELECT A.계좌번호, A.개설일, B.개인고객명, C.법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A.개설일 LIKE '199503%' AND ( A.고객번호 = B.고객번호 OR A.고객번호 = C.고객번호 ) ORDER BY A.개설일 ; (A=B)*C + (A=C)*B 해결 방안 SELECT A.계좌번호, A.개설일, B.개인고객명, C.법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A.개설일 LIKE '199503%' AND A.고객번호 = B.고객번호(+) AND A.고객번호 = C.고객번호(+) ORDER BY A.개설일 ; 대전지원팀

42 사례 19. 배타적 관계 모델에서 OUTER 조인 활용
계좌정보 # * 계좌번호 개설일 개설지점 비밀번호 실명확인여부 해지상태여부 상품종류 적용금리 개인고객번호 법인고객번호 CASE 2 해결 방안 SELECT A.계좌번호, A.개설일, B.개인고객명, C.법인고객명 FROM 계좌정보 A, 개인고객 B, 법인고객 C WHERE A.개설일 LIKE '199503%' AND A.개인고객번호 = B.고객번호(+) AND A.법인고객번호 = C.고객번호(+) ORDER BY A.개설일 ; 대전지원팀

43 사례 20. OR와 같이 사용하는 OUTER 조인 QUERY
SELECT A.COL1, A.COL2, B.COL3, B.COL4 FROM TAB1 A, TAB2 B WHERE A.PK1 = B.PK1 (+) AND A.PK2 = B.PK2 (+) AND ( A.COL5 = 'AAAA' OR A.COL5 = 'BBBB' OR A.COL5 = 'CCCC' OR A.COL5 = 'DDDD' ) ; EXECUTION PLAN SELECT STATEMENT FILTER NESTED LOOPS OUTER TABLE ACCESS FULL :TAB1 TABLE ACCESS BY ROWID :TAB2 INDEX UNIQUE SCAN :TAB2_PK1+PK2 A B 해결방안 1 각각의 OR 단위로 낱개의 SQL로 분리한뒤 UNION ALL을 사용하여 모음 대전지원팀

44 사례 20. OR와 같이 사용하는 OUTER 조인 해결방안 2 A B EXECUTE PLAN
SELECT STATEMENT FILTER CONCATENATION NESTED LOOPS OUTER TABLE ACCESS BY ROWID TAB1 INDEX RANGE_SCAN OF TAB1_COL5_IDX TABLE ACCESS BY ROWID TAB2 INDEX UNIQUE SCAN :TAB2_PK1+PK2 해결방안 2 SELECT --+ USE_CONCAT A.COL1, A.COL2, B.COL3, B.COL4 FROM TAB1 A, TAB2 B WHERE A.PK1 = B.PK1 (+) AND A.PK2 = B.PK2 (+) AND ( A.COL5 = 'AAAA' OR A.COL5 = 'BBBB' OR A.COL5 = 'CCCC' OR A.COL5 = 'DDDD' ) ; A B 대전지원팀

45 사례 21. NULL의 잘못된 사용과 OUTER 조인
SENSOR_READING # * READING_ID DATE VALUE SENSOR_READING_REVISION # * READING_ID ( FK ) # * REVISION_NO DATE VALUE MODEL READING_ID DATE VALUE READING_ID REVISION_NO DATE VALUE SAMPLE 원하는 결과 READING_ID DATE VALUE REVISION_NO 대전지원팀

46 사례 21. NULL의 잘못된 사용과 OUTER 조인
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 DATE VALUE REVISION_NO X 원하는 결과 나오지 않음 X 대전지원팀

47 사례 21. NULL의 잘못된 사용과 OUTER 조인
개선된 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) ) ; EXECUTION PLAN SELECT STATEMENT FILTER MERGE JOIN OUTER SORT JOIN TABLE ACCESS FULL : SENSOR_READING TABLE ACCESS FULL : SENSOR_READING_REVISION SORT GROUP BY A B READING_ID DATE VALUE REVISION_NO 대전지원팀

48 사례 21. NULL의 잘못된 사용과 OUTER 조인
해결 방안 SELECT A.READING_ID, NVL ( B.DATE, A.DATE ), NVL ( B.VALUE, A.VALUE ), NVL ( B.REVISION_NO , 0 ) FROM SENSOR_READING A, (SELECT b1.READING_ID, B2.REVISION_NO, B1.DATE, b1.VALUE FROM SENSOR_READING_REVISION B1, (SELECT READING_ID, MAX(REVISION_NO) AS REVISION_NO FROM SENSOR_READING_REVISION b2 GROUP BY READING_ID) B2 WHERE B1.READING_ID = B2.READING_ID AND B1.REVISION_NO = B2.REVISION_NO ) B WHERE A.READING_ID = B.READING_ID(+) ORDER BY READING_ID ; READING_ID DATE VALUE REVISION_NO 대전지원팀

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

50 사례 22. 조인하는 테이블의 순서에 따른 차이 인덱스정보
사례 22. 조인하는 테이블의 순서에 따른 차이 인덱스정보 납품실행계획 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 NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY ROWID) OF '수주진행현황' INDEX (RANGE SCAN) OF ' 수주진행현황_PK' (UNIQUE) TABLE ACCESS (BY ROWID) OF '고객' INDEX (UNIQUE SCAN) OF ' 고객_PK' (UNIQUE) TABLE ACCESS (BY ROWID) OF '주문' INDEX (UNIQUE SCAN) OF ' 주문_PK' (UNIQUE) TABLE ACCESS (BY ROWID) OF '납품실행계획' INDEX (RANGE SCAN) OF ' 납품실행계획_PK' (UNIQUE) C D B A 대전지원팀

51 사례 22. 조인하는 테이블의 순서에 따른 차이 7 수주진행현황 고객 주문 납품실행계획 1276 1275 1275 1275
사례 22. 조인하는 테이블의 순서에 따른 차이 수주진행현황 고객 주문 납품실행계획 1276 1275 1275 1275 1275 1275 1275 1243 1243 1263 20 7 No No X X ● ● O ● O 대전지원팀

52 사례 22. 조인하는 테이블의 순서에 따른 차이 SELECT /*+ ORDERED */
사례 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 ; 대전지원팀

53 사례 22. 조인하는 테이블의 순서에 따른 차이 1295 20 7 수주진행현황 납품실행계획 고객 주문 1276 1275 20
사례 22. 조인하는 테이블의 순서에 따른 차이 수주진행현황 납품실행계획 고객 주문 1276 1275 1295 20 20 20 20 20 20 7 No X ● O ● ● X 대전지원팀

54 사례 23. NULL 처리를 위해 DEFAULT 활용
인덱스정보 A01003M20 : A01003M20_U2 : MENU_ID + MAST_MENU_ID (UNIQUE) QUERY ROWS EXECUTION PLAN 0 SELECT STATEMENT 1 TABLE ACCESS ( BY ROWID ) OF 'A01003M20' INDEX ( RANGE SCAN ) OF 'A01003M20_U2' ( UNIQUE ) SELECT DETL, PGM_ID FROM A01003M20 WHERE MENU_ID = :B1 AND NVL(MAST_MENU_ID, ' ') = NVL(:B2, ' ') ; 개선된 QUERY null에 대한 규정을 어떻게 하느냐에 따라서 수행속도에 얼마나 많은 영향을 주는가? column에 변형 : index를 충분히 사용못함 null : 현재 미결정, 미지수, 없다(X) null 값을 다른 유일한 값으로 약속 : ‘X’, 0 mast_menu_id varchar2(10) default ‘X’ not null SELECT DETL, PGM_ID FROM A01003M20 WHERE MENU_ID = :B1 AND MAST_MENU_ID = NVL(:B2, 'X') ; 대전지원팀

55 사례 24. 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) 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 ) ; 주어진 CAR_, BOX_CODE에 대하여 가장 최근의 IO_DATE를 찾아 그에 해당하는 STOCK_Q를 가져오는 subquery가 독립적으로 먼저 풀려 이조건에 맞는(원하는 대로가 아닌) 여러 건의 레코드가 리턴됨 : IO_DATE = ‘ ’ corelative subquery 동일 table을 2번 access하는 문제점 max -> sorting -> 전체범위처리 main query에서 sub query로 넘겨주는 record의 수만큼 일이 많아진다. 대전지원팀

56 사례 24. 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 ; hint에 해당하는 object(index) 환경이 제대로 준비되어야 max : 중복제거, 가장 큰값 -> 전체범위처리 SELECT SUBSTR (MAX (IO_DATE || STOCK_Q), 9, 4) FROM GFLM600 WHERE CAR_CODE = :VALUE1 AND BOX_CODE = :VALUE2; 대전지원팀

57 사례 25. MAX / MIN에서 SQL의 분리 인덱스정보
THISEQPM : THISEQPM_X1 : EQPMDEPT + EQPMPART + EQPMROOM QUERY SELECT (MAX(EQPMROOM) - MIN(EQPMROOM)) + 1 INTO :NEXTROOM FROM THISEQPM WHERE EQPMDEPT = :VALUE1 AND EQPMPART = :VALUE2 ; 개선된 QUERY SELECT /*+ INDEX_ASC(A THISEQPM_X1) */ INV.MAX_ROOM - A.EQPMROOM + 1 INTO :NEXTROOM FROM ( SELECT /*+ INDEX_DESC(THISEQPM THISEQPM_X1) */ EQPMROOM AS MAX_ROOM FROM THISEQPM WHERE EQPMDEPT = :VALUE1 AND EQPMPART = :VALUE2 AND ROWNUM = 1 ) INV , THISEQPM A AND ROWNUM = 1 ; 효율적인 처리를 위해 sql문 분리 max(전체범위) : index_desc, rownum=1(부분범위) min(전체범위) : index_asc, rownum=1(부분범위) 97 page 그림 참조 대전지원팀

58 사례 26. 서브쿼리 사용시 MIN / MAX 활용 인덱스정보 A_TAB : A_TAB_IX1 : ID + SDATE
QUERY SELECT NVL(COUNT(*), 0) INTO :B1 FROM A_TAB WHERE SDATE >= :B2 AND SDATE <= :B3 AND PATH = :B4 AND (ID, SDATE) IN (SELECT ID, SDATE FROM B_TAB WHERE SDATE BETWEEN :B2 AND :B3 AND DEPT = :B7 AND (ID, SDATE, SERL) IN (SELECT ID, SDATE, MIN(SERL) GROUP BY ID, SDATE) ); biz rule이 적용된 sql문 B_TAB : 2번 full scan 대전지원팀

59 사례 26. 서브쿼리 사용시 MIN / MAX 활용 중간 QUERY SELECT NVL(COUNT(*), 0) INTO :B1
FROM A_TAB WHERE SDATE BETWEEN :B2 AND :B3 AND PATH = :B4 AND (ID, SDATE) IN (SELECT ID, SUBSTR(MIN(SDATE || SERL),1,8) FROM B_TAB WHERE SDATE BETWEEN :B2 AND :B3 AND DEPT = :B7 GROUP BY ID, SDATE ); B_TAB 문제점 (원래 SQL과 결과 다름) :B7 = 20 인 경우 원래 SQL은 ③ 번이 선택되고 :B7 에서 걸러져 결과가 나오지 않지만 이 SQL은 ① 번 로우가 나온다. DEPT=:B7 조건이 subquery의 where절에 들어가면 안되고, 나중에 check되어야 ID SDATE SERL DEPT ① ② ③ ④ 대전지원팀

60 사례 26. 서브쿼리 사용시 MIN / MAX 활용 개선된 QUERY * 전제조건 : SERL은 3 자리 숫자이며,
ID + SDATE + SERL이 PK를 구성한다. SELECT NVL(COUNT(*), 0) INTO :B1 FROM A_TAB WHERE SDATE BETWEEN :B2 AND :B3 AND PATH = :B4 AND (ID, SDATE) IN (SELECT V.ID, DECODE(V.DEPT, :B7, V.SDATE, NULL) FROM (SELECT ID, SDATE, SUBSTR(MIN(SERL || DEPT),4,2) AS DEPT FROM B_TAB WHERE SDATE BETWEEN :B2 AND :B3 GROUP BY ID, SDATE ) V ); B_TAB : 1번 access B_TAB SERL에 중복이 발생할 때 문제점 :B7 = 20 인 경우 여기서는 ③번 로우가 선택되었다가 :B7 에서 걸러져 아무 로우도 선택되지 않으나 원래 SQL은 ②번 로우가 선택된다. ID SDATE SERL DEPT ① ② ③ ④ 대전지원팀

61 사례 27. 불필요한 상수 조건 인덱스정보 CPNW : CPNU_PK : DLTF QUERY UPDATE CPNW
사례 27. 불필요한 상수 조건 인덱스정보 CPNW : CPNU_PK : DLTF QUERY UPDATE CPNW SET DLTF = :B1, SYSF = :B2, RTDT = :B3, RITM = :B4, USER = :B5, VALUE = :B6 WHERE (DLTF, SYSF, RTDT, RITM, USER, VALUE) = (SELECT :B11, :B12, :B13, :B14, :B15, :B16 FROM DUAL WHERE (:B19 != 'Y' OR :B19 IS NULL) AND (:B21 = 'Y' OR :B21 IS NOT NULL); 개선된 QUERY where에 사용하여 복잡한 sql문 구성 입력변수는 하나의 값이므로 dual에서 가져올 필요없다. :B21=‘Y’는 :B21 is not null의 subset dual table의 남발 UPDATE CPNW SET DLTF = :B1, SYSF = :B2, RTDT = :B3, RITM = :B4, USER = :B5, VALUE = :B6 WHERE DLTF = :B11 AND SYSF = :B12 AND RTDT = :B13 AND RITM = :B14 AND USER = :B15 AND VALUE = :B16 AND (:B19 != 'Y' OR :B19 IS NULL) AND :B21 = 'Y' ; 대전지원팀

62 사례 28. UNION 과 UNION ALL의 명확한 구분
인덱스정보 HSNO A : HSNO_PK : HSNO + SERIAL_NO COL B : COL_PK1 : YEAR + HOUSE + SERIAL_NO + HSNO + SERIAL_NO2 COM C : COM_IX : YEAR + HOUSE + SERIAL_NO COM C : COM_IDX1 : CLASS + DAT QUERY Query의 목적에 따라 효율화 ROWS EXECUTION PLAN 0 SELECT STATEMENT 48 PROJECTION 48 SORT (UNIQUE) UNION-ALL SORT (GROUP BY) NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY ROWID) OF 'COM' INDEX (RANGE SCAN) OF 'COM_IX' (NON-UNQ) TABLE ACCESS (BY ROWID) OF 'COL' INDEX (RANGE SCAN) OF 'COL_PK' (UNIQUE) TABLE ACCESS (BY ROWID) OF 'HSNO' INDEX (RANGE SCAN) OF 'HSNO_PK' SORT (GROUP BY) NESTED LOOPS NESTED LOOPS TABLE ACCESS (BY ROWID) OF 'COM' INDEX (RANGE SCAN) OF 'COM_IX' (NON-UNQ) TABLE ACCESS (BY ROWID) OF 'COL' INDEX (RANGE SCAN) OF 'COL_PK1' (UNIQUE) TABLE ACCESS (BY ROWID) OF 'HSNO' INDEX (RANGE SCAN) OF 'HSNO_PK' SELECT C.CODE, C.HSNO, A.DESCR, SUM(B.AMT) FROM HSNO A, COL B, COM C WHERE A.HSNO = B.HSNO AND B.YEAR = C.YEAR AND B.HOUSE = C.HOUSE AND C.CODE LIKE :B1 AND C.DAT BETWEEN :B2 AND :B3 AND C.CLASS <> 'C' GROUP BY C.CODE, C.HSNO, A.DESCR UNION AND C.CLASS = 'C' GROUP BY C.CODE, C.HSNO, A.DESCR ; union : sorting (중복 제거) union all : no sorting (중복이 없으리라 예상되는 경우) 대전지원팀

63 사례 29. 불필요한 DUAL 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 오늘 날짜를 where절의 조건에 넣어 처리하고자 할때 select 절뿐만아니라, where절에서도 sysdate 사용 가능 많은 사용자일때, table contention(sys.dual) 불필요한 dual 사용 제한 위의 경우는 DAT가 character type일 경우 like, between 사용 가능 date type : DAT = sysdate : 시분초까지 비교 DAT between trunc(sysdate) and trunc(sysdate) to_char(DAT, ‘yyyymmdd’) = to_char(sysdate, ‘yyyymmdd’) : no index 시간계산이 필요없고, 단순히 날짜만 저장하는 경우, .character type이 유리 SELECT ID, DEPT, WARD, ROOM INTO :B1, :B2, :B3, :B4 FROM PATN WHERE WARD = :C1 AND NVL(DAT,'999999') >= TO_CHAR(SYSDATE, 'YYYYMMDD') ORDER BY DEPT, ROOM ; 대전지원팀

64 사례 30. OR 사용 시 주의할 점 인덱스정보 CHULGOT : CHULGOT_IX : CHULDATE + ITEM (CASE 1) CHULGOT : CH_STATUS_ITEM : STATUS + ITEM (CASE 3,4) CHULGOT : CH_CHULNO : CHULNO (CASE 2) 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 WHERE :SW = 2 AND CHULDATE+0 LIKE '96%' ); where절에 OR를 사용하면 의도하지 않는 방향으로 execution plan 수립 의도는 비상하나, OR의 특성상 나쁜쪽으로 통합(풀다가 안되면 full scan) OR는 union all로 변환 가능 117 page sql문 잘못 대전지원팀

65 사례 30. OR 사용 시 주의할 점 QUERY (CASE 2) SELECT COUNT(*) FROM CHULGOT
WHERE CHULNO = '254' OR (:FILED1 + :FILED2 ) > 0 ; SELECT SUM(CNT) FROM ( SELECT COUNT(*) AS CNT FROM CHULGOT WHERE CHULNO = '254' UNION ALL SELECT COUNT(*) AS CNT WHERE (:FILED1 + :FILED2) > 0 ) ; 개선된 QUERY 미지수와 상수의 비교 : full scan 117 page sql문 잘못 대전지원팀

66 사례 30. 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 ; OR안에 OR : full scan 개선된 query 2는 index 사용 가능, 부분범위처리 가능 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%') ; <---- ① 대전지원팀

67 사례 30. 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 ) ; OR로 인하여 concatenation -> 같은 index를 2번 read OR, IN이 들어가는 where 조건은 가능하면 union all, decode 등을 사용하게 변환 대전지원팀

68 사례 31. OR의 활용 DRW_NO VARCHAR2(16) PRIMARY KEY DESIGN_ID VARCHAR(5)
REVISION_DATE VARCHAR2(8) DRW_NO 구성 ● < 프로그램 코딩으로 해결한 방법> IF D1 = ‘ ’ SELECT * FROM 도면정보 WHERE DRW_NO = :D1 IF NOT FOUND SELECT * FROM 도면정보 WHERE DRW_NO = SUSTR(:D1,1,15) SELECT * FROM 도면정보 WHERE DRW_NO = SUSTR(:D1,1,14) ● OR의 특성을 잘 활용하면 보다 효율적으로 처리 가능 dynamic sql을 잘 모르는 경우의 program 예 최악의 경우 16번의 sql문 수행, 수행 속도 이상 대전지원팀

69 사례 31. OR의 활용 EXECUTION PLAN
COUNT CONCATENATION FILTER TABLE ACCESS ( BY ROWID ) OF ‘ 도면정보 ‘ INDEX ( UNIQUE SCAN ) OF ‘ SYS_C ‘ INDEX ( UNIQUE SCAN ) OF ‘ SYS_C ‘ SELECT * FROM 도면정보 WHERE ( DRW_NO = SUBSTR( :d1 , 1, 8 ) OR DRW_NO = SUBSTR( :d1 , 1, 9 ) OR DRW_NO = SUBSTR( :d1 , 1, 10 ) OR DRW_NO = SUBSTR( :d1 , 1, 11 ) OR DRW_NO = SUBSTR( :d1 , 1, 12 ) OR DRW_NO = SUBSTR( :d1 , 1, 13 ) OR DRW_NO = SUBSTR( :d1 , 1, 14 ) OR DRW_NO = SUBSTR( :d1 , 1, 15 ) OR DRW_NO = :d ) AND ROWNUM = 1 ; ● ● where절에 OR가 여러번 나올 경우 가장 뒤의 OR부터 처리 확률이 가장 많은 조건을 뒷쪽에 기술한다. 대전지원팀

70 사례 32. 두 개의 UPDATE 문을 하나로 통합 QUERY EXEC SQL UPDATE BA003DM
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 BA004DM WHERE BRK_CD = :BRK_CD AND DCL_YEAR = :DCL_YEAR) WHERE BRK_CD = :BRK_CD AND DCL_YEAR = :DCL_YEAR) ; SET DUTY_TOT_AMT = (SELECT CUS_DUTY_TOT_AMT + CONSUM_TAX_TOT_AMT + LATE_FEE FROM BA003DM WHERE BRK_CD = :BRK_CD AND DCL_CD = :DCL_YEAR ; 2개의 sql문에서 공통점을 찾아 1개의 sql문으로 통합 1개 table(BA003DM) update 1개 table을 을 2번 update하면서 3번 access 대전지원팀

71 사례 32. 두 개의 UPDATE 문을 하나로 통합 EXEC SQL UPDATE BA003DM
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) = (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 BA004DM WHERE BRK_CD = :BRK_CD AND DCL_YEAR = :DCL_YEAR ) V1, BA003DM A WHERE BRK_CD = :BRK_CD AND DCL_YEAR = :DCL_YEAR ) WHERE BRK_CD = :BRK_CD AND DCL_CD = :DCL_YEAR ; 1번 update하면서 2번 access loop내에서 반복 수행되는 형태였다면 상당한 수행 속도 개선 효과 대전지원팀

72 사례 33. DECODE를 활용한 SQL 통합 QUERY
SELECT NVL(SUM(DACT_MH),0), NVL(SUM(DACT_AMT),0) FROM S_DAY_ACTSCH WHERE ORDERNO = :B1 AND ITEMNO = :B2; AND ITEMNO = :B2 AND CON_CODE IS NOT NULL; AND (CON_CODE IS NULL OR LENGTH(CON_CODE) = 1 ); 동일한 table에 대하여 비슷한 유형의 where절을 사용하는 sql문을 하나로 통합 if ... then ... else : decode function 대전지원팀

73 사례 33. 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; 3배 이상의 처리 속도 향상 129 page DACT_MH, 0))), 0), 대전지원팀

74 사례 34. 과도한 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 ; decode : data의 조건 비교 가능, 과도하게 사용시 수행 성능 저하 fetch시에 cpu, elapsed time 2 level 이상은 program logic으로 해결 권장 group by에 의해 sorting되므로, parallel query 사용하지 않으면 order by 불필요 대전지원팀

75 사례 34. 과도한 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 ; 가능한 많이, 그러나 꼭 필요한 경우에만 사용 대전지원팀