Perfect! 대용량 데이터베이스 튜닝Ⅱ.

Perfect! 대용량 데이터베이스 튜닝Ⅱ

1. 데이터 추출 제어를 위한 ROWNUM 사용 시 문제
인덱스정보 EC_COURSE_SQ_PK : COURSE_CODE + YEAR + COURSE_SQ_NO EC_COURSE_SQ_IDX_ : YEAR YEAR Rows 1997 1858 1998 4472 1999 994 과정코드 49의 년도별 데이터 현황 YEAR 1997 1998 1999 49 12 10 COURSE CODE SQL SELECT COURSE_CODE, YEAR, COURSE_SQ_NO, OPEN_YN, END_YN FROM EC_COURSE_SQ WHERE COURSE_CODE = 49 AND YEAR IN ('1999','1998','1997') AND ROWNUM < 31; [ 개요 ] ROWNUM은 추출되는 데이터에 대해서 자동으로 부여되는 순번이라 할 수 있습니다. 일반적으로, 조건을 만족하는 데이터 중 일부 만을 추출하고자 할 때 ROWNUM을 사용합니다. ROWNUM은 추출되는 데이터에 대해서 부여되는 순번이라는 점에서 아래의 SQL 문들을 제외한 어떠한 SQL 들도 데이터를 추출할 수 없습니다. 1) SELECT ENAME,JOB,DEPTNO FROM EMP WHERE ROWNUM = N; (N = 1) 2) SELECT ENAME,JOB,DEPTNO FROM EMP WHERE ROWNUM < N; (N >= 2) 3) SELECT ENAME,JOB,DEPTNO FROM EMP WHERE ROWNUM <= N;(N >= 1) 이러한 데이터의 일부를 추출하고자 할 때의 ROWNUM 사용 시 발생 가능한 문제점은 무엇이며 이에 대한 해결방안은 무엇인지 本 사례를 통해서 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 30 COUNT (STOPKEY) 30 CONCATENATION FILTER TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE) FILTER TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE) 8 FILTER TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE) 문제점 , 1998 년도에 대한 전체범위 처리 년도의 경우는 부분범위 처리를 하지만 ROWNUM < 31 보다는 상당히 많은 처리를 함 전체범위 처리란 조건을 만족하는 모든 데이터를 처리하는 것을 의미하며, 부분범위 처리란 조건을 만족하는 데이터 중 일부의 데이터 만을 처리하는 것을 말합니다.

EC_COURSE_SQ EC_COURSE_SQ F I L T E R F I L T E R 1859 4473 . . . . EC_COURSE_SQ_ IDX_01 CONCATENATION EC_COURSE_SQ_ IDX_01 EC_COURSE_SQ F I L T E R 204 NOTE . . EC_COURSE_SQ_ IDX_01

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 만약에 다음과 같이 SQL을 작성한다면 앞서 제시한 문제점은 해결이 되는가? SELECT COURSE_CODE, YEAR, COURSE_SQ_NO, OPEN_YN, END_YN FROM (SELECT COURSE_CODE, YEAR, COURSE_SQ_NO, OPEN_YN, END_YN FROM EC_COURSE_SQ WHERE COURSE_CODE = 49 AND YEAR = '1997' AND ROWNUM < 31 UNION ALL SELECT COURSE_CODE, YEAR, COURSE_SQ_NO, OPEN_YN, END_YN WHERE COURSE_CODE = 49 AND YEAR = '1998' WHERE COURSE_CODE = 49 AND YEAR = '1999' AND ROWNUM < 31) WHERE ROWNUM < 31; NOTE

Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 30 COUNT (STOPKEY) 30 VIEW UNION-ALL COUNT (STOPKEY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE) COUNT (STOPKEY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE) COUNT (STOPKEY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_IDX_01' (NON-UNIQUE) NOTE 문제점 원안과 동일한 문제점이 발생하고 있다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점을 해결하기 위한 방안 현상을 보면, 년도에 의한 처리할 범위가 넓은 반면에 과정코드에 의한 처리할 범위는 상당히 좁다는 것을 알 수 있습니다. WHERE 절 처리범위(①) 처리범위(②) ① COURSE_CODE = 좁음 넓음 ② YEAR IN ('1999','1998','1997') 넓음 넓음 Driven Driving 물론 과정코드에 의한 처리할 범위가 넓었다면 상황은 달랐을 것입니다. 그 이유는 과정코드에 의한 처리할 범위가 넓었다면 그 만큼 빨리 ROWNUM에 의한 추출할 데이터를 쉽게 추출했을 것이기 때문입니다. 따라서 주요(Driving) 조건으로 인한 처리범위는 넓을 수도 또는 좁을 수도 있겠으나 어떠한 경우이든지 나중(Driven) 조건의 범위가 넓을 경우라면 문제 가 해결 될 수 있을 것 입니다. 처리범위가 넓다는 것은 조건에 의해 추출해야 할 데이터가 많다는 것이며 반면에 처리범위가 좁다는 것은 조건에 의해 추출해야 할 데이터가 적다는 의미입니다. 또한 이는 곧 분포도와 연계해서 생각할 수도 있습니다. 처리범위가 넓다는 것은 분포도가 나쁘다는 것이며 반면에 처리범위가 좁다는 것은 분포도가 좋다는 것과 같은 의미 입니다.

WHERE 절 처리범위(①) 처리범위(②) ① COURSE_CODE = 좁음 넓음 ② YEAR IN ('1999','1998','1997') 넓음 넓음 Driving Driven 개선된 SQL – Driving 조건을 바꿈으로써 부분범위 처리로 유도함(Suppressing) SELECT COURSE_CODE, YEAR, COURSE_SQ_NO, OPEN_YN, END_YN FROM EC_COURSE_SQ WHERE COURSE_CODE = 49 AND YEAR||'' IN ('1999','1998','1997') AND ROWNUM < 31; Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 30 COUNT (STOPKEY) 30 TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE_SQ' INDEX (RANGE SCAN) OF 'EC_COURSE_SQ_PK' (UNIQUE) NOTE

2. 결합인덱스 선행 컬럼의 '=' 을 위한 조인 활용 SQL 다음에 주어진 2개의 SQL을 각각 분석해 보면서
Perfect! 대용량 데이터베이스 튜닝 Ⅱ EC_COURSE (과정정보) COURSE_CODE COURSE_NAME ○ EC_PROGRESS (진도정보) YEAR COURSE_SQ_NO MEMBER_TYPE MEMBER_ID CHAP_NO PARAG_NO COURSE_DATE END_YN 다음에 주어진 2개의 SQL을 각각 분석해 보면서 수행속도에 있어 효율화를 꾀할 수 있는 방안을 찾아보고자 합니다. SQL SELECT COURSE_CODE, COUNT(COURSE_CODE) CNT FROM EC_PROGRESS WHERE COURSE_CODE < 1000 AND YEAR = '2000' GROUP BY ROLLUP(COURSE_CODE); 인덱스정보 EC_COURSE_PK : COURSE_CODE EC_PROGRESS_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + MEMBER_TYPE + MEMBER_ID + CHAP_NO + PARAG_NO SELECT COURSE_CODE, COUNT(COURSE_CODE) CNT FROM EC_PROGRESS WHERE COURSE_CODE < 1000 AND YEAR = '2000' GROUP BY COURSE_CODE; [ 개요 ] 결합 인덱스(Composite index)의 구성에 있어 컬럼 들 간의 순서가 매우 중요하며, 또한 이들에 대해서 사용하려는 조건도 매우 중요합니다. 특히, 선행하는 컬럼 들(Leading columns)에 대해 사용되는 조건이 '=' 일 경우와 범위 검색(Like, Between, <, >, <=, >= 등)인 경우 처리 범위가 매우 다름으로써 처리 속도가 매우 다르다는 것을 경험 할 수 있습니다. 그 이유는 결합 인덱스의 선행하는 컬럼 들에 대해 주어진 조건이 '=' 이 아니라면 그 다음에 오는 '=' 조건의 용도는 범위를 줄이는 역할이 아닌 데이터 체크 용도로만 사용되기 때문입니다. 이와 같이 선행하는 컬럼에 대한 조건이 '=' 이 아닌 경우에 이에 대한 조건이 '=' 이 될 수 있도록 하기 위한 방안을 本 사례를 통해서 찾아 보고자 합니다.

NOTE 2. 결합인덱스 선행 컬럼의 '=' 을 위한 조인 활용 Trace 정보
call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 55 SORT (GROUP BY ROLLUP) INDEX (RANGE SCAN) OF 'EC_PROGRESS_PK' (UNIQUE) NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 54 SORT (GROUP BY) INDEX (RANGE SCAN) OF 'EC_PROGRESS_PK' (UNIQUE)3 NOTE

NOTE 2. 결합인덱스 선행 컬럼의 '=' 을 위한 조인 활용
문제점 COURSE_CODE 에 대한 조건이 부등호이기 때문에 YEAR 에 대한 조건 '=' 은 데이터 체크 용도로써 데이터 처리범위를 줄이기 위한 역할을 수행할 수 없습니다. 따라서 데이터를 추출하기 위한 처리 범위는 오로지 COURSE_CODE 에 의해 결정되며 이로 인해 각 COURSE_CODE 별로 '2000' 년 이외의 년도에 대한 데이터도 모두 포함이 됩니다. 문제점을 해결하기 위한 방안 데이터를 추출하기 위한 처리범위를 각 COURSE_CODE 별로 '2000' 년 데이터 만을 대상으로 하고자 하기 위해선 COURSE_CODE 에 대한 조건을 '=' 로 성립 될 수 있도록 해야 합니다. 이를 위한 방안으로 여러 가지의 방안이 있는데 그 중 한 가지 방안으로 조인을 활용할 수 있습니다. NOTE

NOTE 2. 결합인덱스 선행 컬럼의 '=' 을 위한 조인 활용 해 결 방 안
Perfect! 대용량 데이터베이스 튜닝 Ⅱ COURSE_CODE YEAR < = '2000' COURSE_CODE YEAR = COURSE_CODE = '2000' COURSE_CODE ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● ● 해 결 방 안 NOTE

NOTE 2. 결합인덱스 선행 컬럼의 '=' 을 위한 조인 활용 개선된 SQL
SELECT B.COURSE_CODE, COUNT(B.COURSE_CODE) CNT FROM EC_PROGRESS B, EC_COURSE A WHERE A.COURSE_CODE < 1000 AND B.COURSE_CODE = A.COURSE_CODE AND B.YEAR = '2000' GROUP BY ROLLUP(B.COURSE_CODE); SELECT B.COURSE_CODE, COUNT(B.COURSE_CODE) CNT FROM EC_PROGRESS B, EC_COURSE A WHERE A.COURSE_CODE < 1000 AND B.COURSE_CODE = A.COURSE_CODE AND B.YEAR = '2000' GROUP BY B.COURSE_CODE; NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 55 SORT (GROUP BY ROLLUP) NESTED LOOPS INDEX (RANGE SCAN) OF 'EC_COURSE_PK' (UNIQUE) INDEX (RANGE SCAN) OF 'EC_PROGRESS_PK' (UNIQUE) NOTE

call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 54 SORT (GROUP BY) NESTED LOOPS INDEX (RANGE SCAN) OF 'EC_COURSE_PK' (UNIQUE) INDEX (RANGE SCAN) OF 'EC_PROGRESS_PK' (UNIQUE) NOTE

3. GROUP BY 와 SUM(DECODE(…)) 의 효율적 활용
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 인덱스정보 EC_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + MEMBER_TYPE + MEMBER_ID EC_APPLY_APPLY_DATE_IDX : APPLY_DATE (Non Unique) SQL SELECT COURSE_CODE, NVL(SUM(DECODE(YEAR,'1999',DEPOSIT_AMOUNT,0)),0) Y1999, NVL(SUM(DECODE(YEAR,'2000',DEPOSIT_AMOUNT,0)),0) Y2000, NVL(SUM(DECODE(YEAR,'2001',DEPOSIT_AMOUNT,0)),0) Y2001, NVL(SUM(DECODE(YEAR,'2002',DEPOSIT_AMOUNT,0)),0) Y2002 FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE; [ 개요 ] IF 처리를 하기 위한 방법으로는 여러 가지가 있는데, 이 중 하나로 DECODE 함수 를 활용하는 방법이 있습니다. DECODE 함수는 오라클 에서만 제공하고 있지만 다른 데이터베이스에서도 이와 같은 기능을 사용할 수 있는데, SQL Server, Sybase, DB2에서의 CASE 함수를 사용하는 것이 일례라 할 수 있습니다. 이러한 CASE 함수는 DECODE 함수 보다 효율성이 탁월합니다. 물론, 오라클의 경우도 V8.1.x 부터 CASE 함수를 사용할 수 있습니다. 오라클 환경 하에서 이미 구축되어 운영되고 있는 시스템인 경우, 작성되어 있는 SQL 의 대다수에서 IF 처리를 위해 DECODE 를 사용하고 있습니다. 특히 다양한 정보를 가공해야 하는 대부분의 통계 및 분석 처리를 위해서 SUM 함수를 함께 사용하는 경우가 대부분이라고 해도 지나치지 않을 것입니다. 本 사례에서는 SUM(DECODE(…)) 의 효율적 활용 방법을 찾아 보고자 합니다.

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 561 SORT (GROUP BY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 년도 별 DECODE(…) 처리 GROUP BY EC_APPLY_PK . EC_APPLY 처리범위 넓음 1차가공 2차가공 문제점 WHERE 절에 기술된 조건에 의해 인덱스(EC_APPLY_PK)가 사용되는데 데이터 처리범위가 너무나 넓기 때문에 인덱스 사용으로 인한 속도저하가 발생하고 있습니다. 또한 SUM(DECODE(…)) 문장에서 DECODE() 내에 있는 년도 별 데이터 처리는 추출된 데이터 마다 즉, Row 단위로 수행되고 있으며 특히 ELSE 부분을 처리하고자 0 을 사용한 결과 값 처리를 함으로써 SUM 함수에 의한 0 을 더하는 불필요한 연산이 발생하고 있습니다. NOTE

문제점을 해결하기 위한 방안 데이터를 추출하기 위한 처리범위가 너무 넓기 때문에 조건에 의한 인덱스의 사용이 문제시 되는 경우라면 오히려 FULL TABLE SCAN 을 하도록 하는 것이 하나의 방안이 될 수 있습니다. 물론, 시스템의 환경에 따라 제약이 있을 수 있겠지만 이와 같이 많은 양의 데이터를 처리함에 있어 효율성을 높이기 위해 서 병렬처리(Parallel Processing)를 함께 사용한다면 보다 나은 결과를 얻을 수 있습니다. 또한 SUM(DECODE(…)) 문장에서 DECODE() 내에 있는 년도 별 데이터 처리는 GROUP BY 를 잘 활용 함으로써 전반적으로 내부적인 수행횟수를 감소 시킬 수 있으며 특히, ELSE 부분은 NULL 로 처리하는 것이 SUM 함수에 의한 불필요한 연산을 없앨 수가 있습니다. NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선안에 대한 개요 각 Process 에 의해 SCAN 처리된 결과엔 과정별로 1999,2000,2001, 2002년 데이터가 모두 있음 년도 별 DECODE(…) 처리 과정 별 GROUP BY . 1차가공 2차가공 EC_APPLY 과정/년도 별 GROUP BY처리 Process#1 SCAN 처리 Process#2 SCAN 처리 Process#3 SCAN 처리 Parallel Coordinator Scan#1 Scan#2 Scan#3 NOTE

개선된 SQL 테이블에 대한 전체 범위 처리 ELSE 부분에 대한 NULL 처리 SELECT /*+ FULL(EC_APPLY) */ COURSE_CODE, NVL(SUM(DECODE(YEAR,'1999',DEPOSIT_AMOUNT)),0) Y1999, NVL(SUM(DECODE(YEAR,'2000',DEPOSIT_AMOUNT)),0) Y2000, NVL(SUM(DECODE(YEAR,'2001',DEPOSIT_AMOUNT)),0) Y2001, NVL(SUM(DECODE(YEAR,'2002',DEPOSIT_AMOUNT)),0) Y2002 FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE; SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ COURSE_CODE, NVL(SUM(DECODE(YEAR,'1999',DEPOSIT_AMOUNT)),0) Y1999, NVL(SUM(DECODE(YEAR,'2000',DEPOSIT_AMOUNT)),0) Y2000, NVL(SUM(DECODE(YEAR,'2001',DEPOSIT_AMOUNT)),0) Y2001, NVL(SUM(DECODE(YEAR,'2002',DEPOSIT_AMOUNT)),0) Y2002 FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE; 테이블에 대한 전체 범위 처리 ELSE 부분에 대한 NULL 처리 Parallel processing NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 최적의 SQL SELECT COURSE_CODE, NVL(SUM(DECODE(YEAR,'1999',DEPOSIT_AMOUNT)),0) Y1999, NVL(SUM(DECODE(YEAR,'2000',DEPOSIT_AMOUNT)),0) Y2000, NVL(SUM(DECODE(YEAR,'2001',DEPOSIT_AMOUNT)),0) Y2001, NVL(SUM(DECODE(YEAR,'2002',DEPOSIT_AMOUNT)),0) Y2002 FROM (SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ COURSE_CODE, YEAR, SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE, YEAR) GROUP BY COURSE_CODE; 테이블에 대한 전체 범위 처리 ELSE 부분에 대한 NULL 처리 GROUP BY 활용을 통한 수행횟수 감안 Parallel processing call count cpu elapsed disk query current rows Parse Execute Fetch total NOTE

SQL SELECT COURSE_CODE, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200101',DEPOSIT_AMOUNT,0)),0) M01, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200102',DEPOSIT_AMOUNT,0)),0) M02, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200103',DEPOSIT_AMOUNT,0)),0) M03, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200104',DEPOSIT_AMOUNT,0)),0) M04, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200105',DEPOSIT_AMOUNT,0)),0) M05, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200106',DEPOSIT_AMOUNT,0)),0) M06, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200107',DEPOSIT_AMOUNT,0)),0) M07, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200108',DEPOSIT_AMOUNT,0)),0) M08, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200109',DEPOSIT_AMOUNT,0)),0) M09, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200110',DEPOSIT_AMOUNT,0)),0) M10, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200111',DEPOSIT_AMOUNT,0)),0) M11, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200112',DEPOSIT_AMOUNT,0)),0) M12 FROM EC_APPLY WHERE COURSE_CODE < 1000 AND APPLY_DATE BETWEEN TO_DATE(' ','YYYYMMDD') AND TO_DATE(' ','YYYYMMDD') GROUP BY COURSE_CODE; NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 186 SORT (GROUP BY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' INDEX (RANGE SCAN) OF 'EC_APPLY_APPLY_DATE_IDX' (NON-UNIQUE) NOTE

문제점 WHERE 절에 기술된 조건에 의해 인덱스(EC_APPLY_APPLY_DATE_IDX)가 사용되는데 데이터 처리범위가 너무나 넓기 때문에 인덱스 사용으로 인한 속도저하가 발생 하고 있으며, 또한 SUM(DECODE(…)) 문장에서 DECODE() 내에 있는 월별 데이터 처리는 추출된 데이터 마다 즉, Row 단위로 수행되고 있으며 특히 ELSE 부분을 처리하고자 0 을 사용한 결과 값 처리를 함으로써 SUM 함수에 의한 0 을 더하는 불필요한 연산이 발생하고 있습니다. NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점을 해결하기 위한 방안 데이터를 추출하기 위한 처리범위가 너무 넓기 때문에 조건에 의한 인덱스의 사용이 문제시 되는 경우라면 오히려 FULL TABLE SCAN 을 하도록 하는 것이 하나의 방안이 될 수 있습니다. 또한 이와 같이 많은 양의 데이터를 처리함에 있어 효율성을 높이기 위해서 앞서 설명된 병렬처리(Parallel Processing)를 함께 사용한다면 보다 나은 결과를 얻을 수 있습니다. 또한 SUM(DECODE(…)) 문장에서 DECODE() 내에 있는 월별 데이터 처리는 GROUP BY 를 잘 활용 함으로써 전반적으로 내부적인 수행횟수를 감소 시킬 수 있으며 특히, ELSE 부분은 NULL 로 처리하는 것이 SUM 함수에 의한 불필요한 연산을 없앨 수가 있습니다. NOTE

개선된 SQL SELECT COURSE_CODE, NVL(SUM(DECODE(MONTH,'200101',DEPOSIT_AMOUNT)),0) M01, NVL(SUM(DECODE(MONTH,'200102',DEPOSIT_AMOUNT)),0) M02, NVL(SUM(DECODE(MONTH,'200103',DEPOSIT_AMOUNT)),0) M03, NVL(SUM(DECODE(MONTH,'200104',DEPOSIT_AMOUNT)),0) M04, NVL(SUM(DECODE(MONTH,'200105',DEPOSIT_AMOUNT)),0) M05, NVL(SUM(DECODE(MONTH,'200106',DEPOSIT_AMOUNT)),0) M06, NVL(SUM(DECODE(MONTH,'200107',DEPOSIT_AMOUNT)),0) M07, NVL(SUM(DECODE(MONTH,'200108',DEPOSIT_AMOUNT)),0) M08, NVL(SUM(DECODE(MONTH,'200109',DEPOSIT_AMOUNT)),0) M09, NVL(SUM(DECODE(MONTH,'200110',DEPOSIT_AMOUNT)),0) M10, NVL(SUM(DECODE(MONTH,'200111',DEPOSIT_AMOUNT)),0) M11, NVL(SUM(DECODE(MONTH,'200112',DEPOSIT_AMOUNT)),0) M12 FROM (SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ COURSE_CODE, TO_CHAR(APPLY_DATE,'YYYYMM') MONTH, SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT FROM EC_APPLY WHERE COURSE_CODE < 1000 AND APPLY_DATE BETWEEN TO_DATE(' ','YYYYMMDD') AND TO_DATE(' ','YYYYMMDD') GROUP BY COURSE_CODE, TO_CHAR(APPLY_DATE,'YYYYMM')) GROUP BY COURSE_CODE; NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total NOTE

4. DECODE 함수를 이용한 IF 처리의 효율화
인덱스정보 EC_EXAM_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + EXAM_NO MEMBER_TYPE + MEMBER_ID SQL IF RESULT < 10 THEN 'A' ELSE IF RESULT < 20 THEN 'B' ELSE IF RESULT < 30 THEN 'C' ELSE IF RESULT < 40 THEN 'D' ELSE IF RESULT < 50 THEN 'E' ELSE IF RESULT < 60 THEN 'F' ELSE IF RESULT < 70 THEN 'G' ELSE IF RESULT < 80 THEN 'H' ELSE IF RESULT < 90 THEN 'I' ELSE 'J' END IF SELECT YEAR, DECODE(SIGN(RESULT - 10),-1,'A', DECODE(SIGN(RESULT - 20),-1,'B', DECODE(SIGN(RESULT - 30),-1,'C', DECODE(SIGN(RESULT - 40),-1,'D', DECODE(SIGN(RESULT - 50),-1,'E', DECODE(SIGN(RESULT - 60),-1,'F', DECODE(SIGN(RESULT - 70),-1,'G', DECODE(SIGN(RESULT - 80),-1,'H', DECODE(SIGN(RESULT - 90),-1,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(SIGN(RESULT - 90),-1,'I','J'))))))))); [ 개요 ] DECODE 함수는 오라클에만 적용할 수 있는 함수로 'IF ... THEN ... ELSE'에 해당하는 기능을 갖고 있습니다. 그런데 현실적으로 다양한 요구사항에 대한 표현에 있어 제약사항이 많이 있다는 것이 문제라 할 수 있습니다. DECODE 함수의 제약사항에 의해 발생할 수 있는 이러한 문제들은 V8.1.x 부터는 CASE 함수를 사용할 수 있어서 쉽게 처리할 수 있게 되었습니다. 그러나 과거 부터 구축되어 있는 응용시스템(Applications)의 경우는 대다수의 SQL을 살펴보면 DECODE 함수를 사용을 하고 있고, CASE 함수를 사용할 수 있는 버전을 사용하고 있는 경우도 대다수의 개발자가 이미 DECODE 함수에 익숙해져 있음으로 인해 아직도 이를 사용하고 있는 것이 현상이라 할 수 있겠습니다. 따라서 本 사례에서는 DECODE 함수를 이용한 IF 처리를 하고자 할 때 어떻게 하면 보다 더 효율적으로 사용할 수 있는지를 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 50 SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY' . EC_EXAM_APPLY 점수 별 DECODE(SIGN(연산),…, DECODE(…))…) 처리 GROUP BY 1차가공 2차가공 504056 NOTE

문제점 DECODE(…) 문장에서 DECODE() 내에 있는 점수 별 데이터 처리는 추출된 데이터 마다 즉, Row 단위로 수행되고 있으며 특히 다양한 ELSE 부분 처리를 위해서 너 무 많은 DECODE 함수를 사용하고 있습니다. 문제점을 해결하기 위한 방안 DECODE(…) 문장에서 DECODE() 내에 있는 점수 별 데이터 처리는 GROUP BY를 활용 함으로써 전반적으로 내부적인 수행횟수를 감소 시킬 수 있으며 특히 다양한 ELSE 부분 처리를 위해서 사용한 DECODE 함수의 사용 개수를 줄일 수 있도록 해야 합니다. NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ FLOOR 함수를 사용한 SQL SELECT YEAR, DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/20), 0,'B', DECODE(FLOOR(RESULT/30), 0,'C', DECODE(FLOOR(RESULT/40), 0,'D', DECODE(FLOOR(RESULT/50), 0,'E', DECODE(FLOOR(RESULT/60), 0,'F', DECODE(FLOOR(RESULT/70), 0,'G', DECODE(FLOOR(RESULT/80), 0,'H', DECODE(FLOOR(RESULT/90), 0,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(FLOOR(RESULT/90), 0,'I','J'))))))))); FLOOR 함수는 절삭 된 값을 Return 합니다. RESULT < 10 인 경우 : FLOOR(RESULT/10) → 0 10 ← RESULT < 20 인 경우 : FLOOR(RESULT/20) → 0 20 ← RESULT < 30 인 경우 : FLOOR(RESULT/30) → 0 30 ← RESULT < 40 인 경우 : FLOOR(RESULT/40) → 0 40 ← RESULT < 50 인 경우 : FLOOR(RESULT/50) → 0 50 ← RESULT < 60 인 경우 : FLOOR(RESULT/60) → 0 60 ← RESULT < 70 인 경우 : FLOOR(RESULT/70) → 0 70 ← RESULT < 80 인 경우 : FLOOR(RESULT/80) → 0 80 ← RESULT < 90 인 경우 : FLOOR(RESULT/90) → 0 90 ← RESULT < 100 인 경우 : FLOOR(RESULT/100) → 0

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 50 SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY‘ NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL SELECT YEAR, DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/20), 0,'B', DECODE(FLOOR(RESULT/30), 0,'C', DECODE(FLOOR(RESULT/40), 0,'D', DECODE(FLOOR(RESULT/50), 0,'E', DECODE(FLOOR(RESULT/60), 0,'F', DECODE(FLOOR(RESULT/70), 0,'G', DECODE(FLOOR(RESULT/80), 0,'H', DECODE(FLOOR(RESULT/90), 0,'I','J'))))))))) AS CLASS, SUM(CNT) FROM (SELECT YEAR, RESULT, COUNT(*) CNT FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, RESULT) GROUP BY YEAR, DECODE(FLOOR(RESULT/90), 0,'I','J'))))))))); GROUP BY 활용을 통한 수행횟수 감소 NOTE

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 50 SORT (GROUP BY) VIEW SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY' NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL SELECT YEAR, DECODE(FLOOR(RESULT/10), 0,'A', DECODE(FLOOR(RESULT/10), 1,'B', DECODE(FLOOR(RESULT/10), 2,'C', DECODE(FLOOR(RESULT/10), 3,'D', DECODE(FLOOR(RESULT/10), 4,'E', DECODE(FLOOR(RESULT/10), 5,'F', DECODE(FLOOR(RESULT/10), 6,'G', DECODE(FLOOR(RESULT/10), 7,'H', DECODE(FLOOR(RESULT/10), 8,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(FLOOR(RESULT/10), 8,'I','J'))))))))); FLOOR 함수는 절삭 된 값을 Return 합니다. IF 조건의 처리 단위가 10 단위라는 특이점이 있습니다. 따라서 다음과 같이 표현할 수가 있습니다. RESULT < 10 인 경우 : FLOOR(RESULT/10) → 0 10 ← RESULT < 20 인 경우 : FLOOR(RESULT/10) → 1 20 ← RESULT < 30 인 경우 : FLOOR(RESULT/10) → 2 30 ← RESULT < 40 인 경우 : FLOOR(RESULT/10) → 3 40 ← RESULT < 50 인 경우 : FLOOR(RESULT/10) → 4 50 ← RESULT < 60 인 경우 : FLOOR(RESULT/10) → 5 60 ← RESULT < 70 인 경우 : FLOOR(RESULT/10) → 6 70 ← RESULT < 80 인 경우 : FLOOR(RESULT/10) → 7 80 ← RESULT < 90 인 경우 : FLOOR(RESULT/10) → 8 90 ← RESULT < 100 인 경우 : FLOOR(RESULT/10) → 9

개선된 SQL SELECT YEAR, DECODE(CLASS, 0,'A', 1,'B', 2,'C', 3,'D', 4,'E', 5,'F', 6,'G', 7,'H', 8,'I','J') AS CLASS, SUM(CNT) FROM (SELECT YEAR, FLOOR(RESULT/10) CLASS, COUNT(*) CNT FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, FLOOR(RESULT/10)) GROUP BY YEAR, 6,'G', 7,'H', 8,'I','J'); GROUP BY 활용을 통한 수행횟수 감안 FLOOR 함수에 대한 적절한 표현식 사용으로 인해 FLOOR 및 DECODE 함수 의 개수를 줄임 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 50 SORT (GROUP BY) 64 VIEW SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY' NOTE

개선된 SQL SELECT YEAR, DECODE(FLOOR(RESULT/10), 0,'A', 1, 'B', 2,'C', 3,'D', 4,'E', 5,'F', 6,'G', 7,'H', 8,'I','J') AS CLASS, SUM(CNT) FROM (SELECT YEAR, RESULT, COUNT(*) CNT FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, RESULT) GROUP BY YEAR, 4,'E', 5,'F', 6,'G', 7,'H', 8,'I','J'); GROUP BY 활용을 통한 수행횟수 감안 FLOOR 함수에 대한 적절한 표현식 사용으로 인해 FLOOR 및 DECODE 함수 의 개수를 줄임 GROUP BY 활용을 통한 FLOOR 함수의 사용횟수 감소 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 50 SORT (GROUP BY) VIEW SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY NOTE

IF RESULT =< 10 THEN 'A' ELSE IF RESULT =< 20 THEN 'B' ELSE IF RESULT =< 30 THEN 'C' ELSE IF RESULT =< 40 THEN 'D' ELSE IF RESULT =< 50 THEN 'E' ELSE IF RESULT =< 60 THEN 'F' ELSE IF RESULT =< 70 THEN 'G' ELSE IF RESULT =< 80 THEN 'H' ELSE IF RESULT =< 90 THEN 'I' ELSE 'J' END IF SQL SELECT YEAR, DECODE(SIGN(RESULT-10),-1,'A',0,'A', DECODE(SIGN(RESULT-20),-1,'B',0,'B', DECODE(SIGN(RESULT-30),-1,'C',0,'C', DECODE(SIGN(RESULT-40),-1,'D',0,'D', DECODE(SIGN(RESULT-50),-1,'E',0,'E', DECODE(SIGN(RESULT-60),-1,'F',0,'F', DECODE(SIGN(RESULT-70),-1,'G',0,'G', DECODE(SIGN(RESULT-80),-1,'H',0,'H', DECODE(SIGN(RESULT-90),-1,'I',0,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(SIGN(RESULT-90),-1,'I',0,'I','J'))))))))); NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ CEIL 함수를 사용한 SQL SELECT YEAR, DECODE(CEIL(RESULT/10), 0,'A',1,'A', DECODE(CEIL(RESULT/20),1,'B', DECODE(CEIL(RESULT/30),1,'C', DECODE(CEIL(RESULT/40),1,'D', DECODE(CEIL(RESULT/50),1,'E', DECODE(CEIL(RESULT/60),1,'F', DECODE(CEIL(RESULT/70),1,'G', DECODE(CEIL(RESULT/80),1,'H', DECODE(CEIL(RESULT/90),1,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(CEIL(RESULT/90),1,'I','J'))))))))); CEIL 함수는 절상 된 값을 Return 합니다. RESULT = 0 인 경우 : CEIL(RESULT/10) → 0 0 < RESULT <= 10 인 경우 : CEIL(RESULT/10) → 1 10 < RESULT <= 20 인 경우 : CEIL(RESULT/20) → 1 20 < RESULT <= 30 인 경우 : CEIL(RESULT/30) → 1 30 < RESULT <= 40 인 경우 : CEIL(RESULT/40) → 1 40 < RESULT <= 50 인 경우 : CEIL(RESULT/50) → 1 50 < RESULT <= 60 인 경우 : CEIL(RESULT/60) → 1 60 < RESULT <= 70 인 경우 : CEIL(RESULT/70) → 1 70 < RESULT <= 80 인 경우 : CEIL(RESULT/80) → 1 80 < RESULT <= 90 인 경우 : CEIL(RESULT/90) → 1 90 < RESULT <= 100 인 경우 : CEIL(RESULT/100) → 1

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 50 SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_EXAM_APPLY' NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SELECT YEAR, DECODE(CEIL(RESULT/10), 0,'A',1,'A', DECODE(CEIL(RESULT/10),2,'B', DECODE(CEIL(RESULT/10),3,'C', DECODE(CEIL(RESULT/10),4,'D', DECODE(CEIL(RESULT/10),5,'E', DECODE(CEIL(RESULT/10),6,'F', DECODE(CEIL(RESULT/10),7,'G', DECODE(CEIL(RESULT/10),8,'H', DECODE(CEIL(RESULT/10),9,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(CEIL(RESULT/10),9,'I','J'))))))))); CEIL 함수는 절상된 값을 Return 합니다. IF 조건의 처리 단위가 10 단위라는 특이점이 있습니다. 따라서 다음과 같이 표현할 수가 있습니다. RESULT = 0 인 경우 : CEIL(RESULT/10) → 0 0 < RESULT <= 10 인 경우 : CEIL(RESULT/10) → 1 10 < RESULT <= 20 인 경우 : CEIL(RESULT/10) → 2 20 < RESULT <= 30 인 경우 : CEIL(RESULT/10) → 3 30 < RESULT <= 40 인 경우 : CEIL(RESULT/10) → 4 40 < RESULT <= 50 인 경우 : CEIL(RESULT/10) → 5 50 < RESULT <= 60 인 경우 : CEIL(RESULT/10) → 6 60 < RESULT <= 70 인 경우 : CEIL(RESULT/10) → 7 70 < RESULT <= 80 인 경우 : CEIL(RESULT/10) → 8 80 < RESULT <= 90 인 경우 : CEIL(RESULT/10) → 9 90 < RESULT <= 100 인 경우 : CEIL(RESULT/10) → 10

Perfect! 대용량 데이터베이스 튜닝 Ⅱ GREATEST 함수를 사용한 SQL SELECT YEAR, DECODE(GREATEST(RESULT,10),10,'A',DECODE(GREATEST(RESULT,20),20,'B', DECODE(GREATEST(RESULT,30),30,'C',DECODE(GREATEST(RESULT,40),40,'D', DECODE(GREATEST(RESULT,50),50,'E',DECODE(GREATEST(RESULT,60),60,'F', DECODE(GREATEST(RESULT,70),70,'G',DECODE(GREATEST(RESULT,80),80,'H', DECODE(GREATEST(RESULT,90),90,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(GREATEST(RESULT,90),90,'I','J'))))))))); GREATEST 함수는 큰 값을 식별하기 위한 함수입니다. 다른 함수들과는 달리 비교되는 값들의 데이터 타입에 전혀 영향을 받지 않으며, 특히 SIGN, CEIL, FLOOR 처럼 별도의 연산을 할 필요가 없습니다. RESULT <= 10 인 경우 : GREATEST(RESULT,10) → 10 10 < RESULT <= 20 인 경우 : GREATEST(RESULT,20) → 20 20 < RESULT <= 30 인 경우 : GREATEST(RESULT,30) → 30 30 < RESULT <= 40 인 경우 : GREATEST(RESULT,40) → 40 40 < RESULT <= 50 인 경우 : GREATEST(RESULT,50) → 50 50 < RESULT <= 60 인 경우 : GREATEST(RESULT,60) → 60 60 < RESULT <= 70 인 경우 : GREATEST(RESULT,70) → 70 70 < RESULT <= 80 인 경우 : GREATEST(RESULT,80) → 80 80 < RESULT <= 90 인 경우 : GREATEST(RESULT,90) → 90 90 < RESULT <= 100 인 경우 : GREATEST(RESULT,100) → 100

Perfect! 대용량 데이터베이스 튜닝 Ⅱ LEAST 함수를 사용한 SQL SELECT YEAR, DECODE(LEAST(RESULT,10),RESULT,'A',DECODE(LEAST(RESULT,20),RESULT,'B', DECODE(LEAST(RESULT,30),RESULT,'C',DECODE(LEAST(RESULT,40),RESULT,'D', DECODE(LEAST(RESULT,50),RESULT,'E',DECODE(LEAST(RESULT,60),RESULT,'F', DECODE(LEAST(RESULT,70),RESULT,'G',DECODE(LEAST(RESULT,80),RESULT,'H', DECODE(LEAST(RESULT,90),RESULT,'I','J'))))))))) AS CLASS, COUNT(*) FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, DECODE(LEAST(RESULT,90),RESULT,'I','J'))))))))); LEAST 함수는 작은 값을 식별하기 위한 함수입니다. GREATEST 와 마찬가지로 비교되는 값들의 데이터 타입에 전혀 영향을 받지 않으며, 특히 SIGN, CEIL, FLOOR 처럼 별도의 연산을 할 필요가 없습니다. RESULT <= 10 인 경우 : LEAST(RESULT,10) → RESULT 10 < RESULT <= 20 인 경우 : LEAST(RESULT,20) → RESULT 20 < RESULT <= 30 인 경우 : LEAST(RESULT,30) → RESULT 30 < RESULT <= 40 인 경우 : LEAST(RESULT,40) → RESULT 40 < RESULT <= 50 인 경우 : LEAST(RESULT,50) → RESULT 50 < RESULT <= 60 인 경우 : LEAST(RESULT,60) → RESULT 60 < RESULT <= 70 인 경우 : LEAST(RESULT,70) → RESULT 70 < RESULT <= 80 인 경우 : LEAST(RESULT,80) → RESULT 80 < RESULT <= 90 인 경우 : LEAST(RESULT,90) → RESULT 90 < RESULT <= 100 인 경우 : LEAST(RESULT,100) → RESULT

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL SELECT YEAR, DECODE(CEIL(RESULT/10), 0,'A', 1,'A', 2,'B', 3,'C', 4,'D', 5,'E', 6,'F', 7,'G', 8,'H', 9,'I','J') AS CLASS, SUM(CNT) FROM (SELECT YEAR, RESULT, COUNT(*) CNT FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, RESULT) GROUP BY YEAR, 6,'F', 7,'G', 8,'H', 9,'I','J'); GROUP BY 활용을 통한 수행횟수 감안 CEIL 함수에 대한 적절한 표현식 사용으로 인해 CEIL 및 DECODE 함수 의 개수를 줄임 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SELECT YEAR, DECODE(GREATEST(RESULT,10),10,'A',DECODE(GREATEST(RESULT,20),20,'B', DECODE(GREATEST(RESULT,30),30,'C',DECODE(GREATEST(RESULT,40),40,'D', DECODE(GREATEST(RESULT,50),50,'E',DECODE(GREATEST(RESULT,60),60,'F', DECODE(GREATEST(RESULT,70),70,'G',DECODE(GREATEST(RESULT,80),80,'H', DECODE(GREATEST(RESULT,90),90,'I','J'))))))))) AS CLASS, SUM(CNT) FROM (SELECT YEAR, RESULT, COUNT(*) CNT FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, RESULT) GROUP BY YEAR, DECODE(GREATEST(RESULT,90),90,'I','J'))))))))); GROUP BY 활용을 통한 수행횟수 감안 GREATEST 함수 사용으로 인해 SIGN 또는 CEIL 함수 사용시 발생하던 불 필요한 연산이 없어짐 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SELECT YEAR, DECODE(LEAST(RESULT,10),RESULT,'A',DECODE(LEAST(RESULT,20),RESULT,'B', DECODE(LEAST(RESULT,30),RESULT,'C',DECODE(LEAST(RESULT,40),RESULT,'D', DECODE(LEAST(RESULT,50),RESULT,'E',DECODE(LEAST(RESULT,60),RESULT,'F', DECODE(LEAST(RESULT,70),RESULT,'G',DECODE(LEAST(RESULT,80),RESULT,'H', DECODE(LEAST(RESULT,90),RESULT,'I','J'))))))))) AS CLASS, SUM(CNT) FROM (SELECT YEAR, RESULT, COUNT(*) CNT FROM EC_EXAM_APPLY WHERE UPDATE_USER <> 'LANGUAGE_MANAGER' GROUP BY YEAR, RESULT) GROUP BY YEAR, DECODE(LEAST(RESULT,90),RESULT,'I','J'))))))))); GROUP BY 활용을 통한 수행횟수 감안 LEAST 함수 사용으로 인해 SIGN 또는 CEIL 함수 사용시 발생하던 불 필요한 연산이 없어짐 NOTE

5. LOOP 내의 데이터 연산을 SQL 로 처리 소개 아래의 Output 형식으로 데이터를 처리하고자 합니다. ① ② ③
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 소개 아래의 Output 형식으로 데이터를 처리하고자 합니다. 과정 년도 1월 2월 3월 4월 5월 ●●● 10월 11월 12월 합계 ① 중간 생략 ●●● 중간 생략 ●●● 중간 생략 ●●● [ 개요 ] 어떠한 처리를 함에 있어서 가능한 한 SQL 수행 횟수를 줄이면서 각각이 한 번 수행 될 때 마다 가급적 많은 양의 데이터 처리가 될 수 있도록 튜닝 시 고려해야만 합니다. 예를 들어, 처리하고자 하는 데이터가 1억 건이라 할 때 이를 처리하고자 1건씩 처리하는 SQL 을 1억번을 수행한 경우와 하나의 SQL로 1억건을 범위 처리로 수행하는 경우를 생각해 본다면 그 이유는 명백합니다. 어떻게 생각하면 동일한 데이터를 처리함에 있어 처리 방법의 차이만 있어 보이나, 단적인 예로 SQL 의 수행 횟수를 비교해 보면 1억 배의 차이가 난다는 것을 알 수 있습니다. 또한 위의 예에서 1억 건의 데이터를 추출하여 별도로 추가적인 조건 처리 및 연산 처리를 필요로 한다거나 또는 수행해야 할 SQL 이 있다면 쉽게 생각 할 수 있는 것은 LOOP 를 수행토록 하고 LOOP 내에서 그 만큼(즉, 1억 건)을 처리할 수 있도록 하는 것이 하나의 방법이 될 수 있을 것입니다. 이러한 경우 성능 향상을 얻기 위해선 어떻게 해야 할 것인가? 本 사례에서는 LOOP 수행에 의한 연산 처리를 하나의 SQL 로 처리할 수 있도록 함으로써 성능 향상을 얻을 수 있다는 것을 제시함으로써 SQL 의 활용성을 제고해 보고자 합니다. ② Tot: ●●● ③

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리 인덱스정보
EC_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + MEMBER_TYPE + MEMBER_ID EC_APPLY_APPLY_DATE_IDX : APPLY_DATE (Non Unique) SOURCE DECLARE CURSOR C1 IS SELECT COURSE_CODE,YEAR, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'01',DEPOSIT_AMOUNT,0)),0) M01, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'02',DEPOSIT_AMOUNT,0)),0) M02, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'03',DEPOSIT_AMOUNT,0)),0) M03, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'04',DEPOSIT_AMOUNT,0)),0) M04, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'05',DEPOSIT_AMOUNT,0)),0) M05, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'06',DEPOSIT_AMOUNT,0)),0) M06, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'07',DEPOSIT_AMOUNT,0)),0) M07, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'08',DEPOSIT_AMOUNT,0)),0) M08, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'09',DEPOSIT_AMOUNT,0)),0) M09, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'10',DEPOSIT_AMOUNT,0)),0) M10, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'11',DEPOSIT_AMOUNT,0)),0) M11, NVL(SUM(DECODE(TO_CHAR(APPLY_DATE,'MM'),'12',DEPOSIT_AMOUNT,0)),0) M12 FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE,YEAR; NOTE

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리 ① ② ③ /* 변수 선언부 중간 생략 */ BEGIN
Perfect! 대용량 데이터베이스 튜닝 Ⅱ /* 변수 선언부 중간 생략 */ BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_YEAR, H_MONTH01_AMT,H_MONTH02_AMT,H_MONTH03_AMT,H_MONTH04_AMT, H_MONTH05_AMT,H_MONTH06_AMT,H_MONTH07_AMT,H_MONTH08_AMT, H_MONTH09_AMT,H_MONTH10_AMT,H_MONTH11_AMT,H_MONTH12_AMT; EXIT WHEN C1%NOTFOUND; H_YEAR_AMT := H_MONTH01_AMT+H_MONTH02_AMT+H_MONTH03_AMT+H_MONTH04_AMT+ H_MONTH05_AMT+H_MONTH06_AMT+H_MONTH07_AMT+H_MONTH08_AMT+ H_MONTH09_AMT+H_MONTH10_AMT+H_MONTH11_AMT+H_MONTH12_AMT; H_MONTH01_TAMT := H_MONTH01_TAMT + H_MONTH01_AMT; ... 중간 생략 H_MONTH12_TAMT := H_MONTH12_TAMT + H_MONTH12_AMT; H_TOT_AMT := H_TOT_AMT + H_YEAR_AMT; END LOOP; CLOSE C1; END; / ① ② ③ NOTE

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리
문제점 CURSOR 에 선언된 SQL 에 있어서는 WHERE 절에 기술된 조건에 의해 인덱스 (EC_APPLY_PK)가 사용되는데 데이터 처리범위가 너무나 넓기 때문에 인덱스 사용으로 인한 속도저하가 발생하고 있습니다. 또한 SUM(DECODE(…)) 문장에 서 DECODE() 내에 있는 년도 및 월별 데이터 처리는 추출된 데이터 마다 즉, Row 단위로 수행되고 있으며 해당 년/월에 대한 데이터 구분을 위해 이에 대한 가공이 이루어지고 있으며 특히 ELSE 부분을 0 을 사용한 결과 값 처리를 함으로 인해 SUM 함수에 의한 0 을 더하는 불필요한 연산이 발생하고 있습니다. LOOP 내의 기술된 부분에 있어서는 년도 별 금액의 합을 구하기 위한 연산 처리와 월 별 금액의 누계를 구하기 위한 연산이 발생하고 있습니다. NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점을 해결하기 위한 방안 데이터를 추출하기 위한 처리범위가 너무 넓기 때문에 조건에 의한 인덱스의 사용이 문제시 되는 경우라면 오히려 FULL TABLE SCAN 을 하도록 하는 것이 하나의 방안이 될 수 있으며 경우에 따라선 병렬처리(Parallel Processing)를 함께 사용한다면 보다 나은 결과를 얻을 수 있습니다. 또한 SUM(DECODE(…)) 문장에서 DECODE() 내에 있는 년도 및 월별 데이터 처리는 GROUP BY 를 잘 활용 함으로써 전반적으로 내부적인 수행횟수를 감소 시킬 수 있으며 특히, 해당 년/월에 대한 데이터 구분을 위한 부분을 효율화 하는 것과 ELSE 부분을 NULL 로 처리함으로써 SUM 함수에 의한 불필요한 연산을 피할 수 있도록 할 수 있습니다. 그리고 LOOP 내의 년도 별 금액 또는 월 별 금액의 누계를 구하기 위한 연산 처리 부분을 가능하다면 SQL 상에서 구할 수 있도록 해야 하겠습니다. NOTE

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리 개선된 SOURCE(1 단계)
DECLARE CURSOR C1 IS SELECT COURSE_CODE,YEAR, NVL(SUM(DECODE(MONTH,'01',DEPOSIT_AMOUNT)),0) M01, NVL(SUM(DECODE(MONTH,'02',DEPOSIT_AMOUNT)),0) M02, NVL(SUM(DECODE(MONTH,'03',DEPOSIT_AMOUNT)),0) M03, NVL(SUM(DECODE(MONTH,'04',DEPOSIT_AMOUNT)),0) M04, NVL(SUM(DECODE(MONTH,'05',DEPOSIT_AMOUNT)),0) M05, NVL(SUM(DECODE(MONTH,'06',DEPOSIT_AMOUNT)),0) M06, NVL(SUM(DECODE(MONTH,'07',DEPOSIT_AMOUNT)),0) M07, NVL(SUM(DECODE(MONTH,'08',DEPOSIT_AMOUNT)),0) M08, NVL(SUM(DECODE(MONTH,'09',DEPOSIT_AMOUNT)),0) M09, NVL(SUM(DECODE(MONTH,'10',DEPOSIT_AMOUNT)),0) M10, NVL(SUM(DECODE(MONTH,'11',DEPOSIT_AMOUNT)),0) M11, NVL(SUM(DECODE(MONTH,'12',DEPOSIT_AMOUNT)),0) M12, NVL(SUM(DEPOSIT_AMOUNT),0) YEAR_AMT FROM (SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ COURSE_CODE,YEAR,TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE,YEAR,TO_CHAR(APPLY_DATE,'MM')) GROUP BY COURSE_CODE,YEAR ORDER BY COURSE_CODE,YEAR; 각 과정에 대한 년도 별 금액의 합 까지 반영함 NOTE

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리 ② ③ /* 변수 선언부 중간 생략 */ ... BEGIN
Perfect! 대용량 데이터베이스 튜닝 Ⅱ /* 변수 선언부 중간 생략 */ ... BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_YEAR, H_MONTH01_AMT,H_MONTH02_AMT,H_MONTH03_AMT,H_MONTH04_AMT, H_MONTH05_AMT,H_MONTH06_AMT,H_MONTH07_AMT,H_MONTH08_AMT, H_MONTH09_AMT,H_MONTH10_AMT,H_MONTH11_AMT,H_MONTH12_AMT, H_YEAR_AMT; EXIT WHEN C1%NOTFOUND; H_MONTH01_TAMT := H_MONTH01_TAMT + H_MONTH01_AMT; ... 중간 생략 H_MONTH12_TAMT := H_MONTH12_TAMT + H_MONTH12_AMT; H_TOT_AMT := H_TOT_AMT + H_YEAR_AMT; END LOOP; CLOSE C1; END; / 각 과정의 년도별 금액 합을 구하는 연산이 없음 ② ③ NOTE

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리 개선된 SOURCE(2 단계)
CURSOR C1 IS SELECT COURSE_CODE, NVL(YEAR,'TOT;') YEAR, NVL(SUM(DECODE(MONTH,'01',DEPOSIT_AMOUNT)),0) M01,NVL(SUM(DECODE(MONTH,'02',DEPOSIT_AMOUNT)),0) M02, NVL(SUM(DECODE(MONTH,'03',DEPOSIT_AMOUNT)),0) M03,NVL(SUM(DECODE(MONTH,'04',DEPOSIT_AMOUNT)),0) M04, NVL(SUM(DECODE(MONTH,'05',DEPOSIT_AMOUNT)),0) M05,NVL(SUM(DECODE(MONTH,'06',DEPOSIT_AMOUNT)),0) M06, NVL(SUM(DECODE(MONTH,'07',DEPOSIT_AMOUNT)),0) M07,NVL(SUM(DECODE(MONTH,'08',DEPOSIT_AMOUNT)),0) M08, NVL(SUM(DECODE(MONTH,'09',DEPOSIT_AMOUNT)),0) M09,NVL(SUM(DECODE(MONTH,'10',DEPOSIT_AMOUNT)),0) M10, NVL(SUM(DECODE(MONTH,'11',DEPOSIT_AMOUNT)),0) M11,NVL(SUM(DECODE(MONTH,'12',DEPOSIT_AMOUNT)),0) M12, NVL(SUM(DEPOSIT_AMOUNT),0) YEAR_AMT FROM (SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ COURSE_CODE,YEAR,TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE,YEAR,TO_CHAR(APPLY_DATE,'MM') UNION ALL SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ TO_NUMBER(NULL) COURSE_CODE,NULL YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH, WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY TO_CHAR(APPLY_DATE,'MM')) GROUP BY COURSE_CODE, NVL(YEAR,'TOT;') ORDER BY 1, 2;; 각 과정에 대한 년도 별 금액의 합 까지 반영함 각 월별 금액의 누계 까지 반영 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ LOOP 내의 모든 연산 불필요 /* 변수 선언부 중간 생략 */ ... BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_YEAR, H_MONTH01_AMT,H_MONTH02_AMT,H_MONTH03_AMT,H_MONTH04_AMT, H_MONTH05_AMT,H_MONTH06_AMT,H_MONTH07_AMT,H_MONTH08_AMT, H_MONTH09_AMT,H_MONTH10_AMT,H_MONTH11_AMT,H_MONTH12_AMT, H_YEAR_AMT; EXIT WHEN C1%NOTFOUND; ... 중간 생략 END LOOP; CLOSE C1; END; / NOTE 문제점 LOOP 내의 각 월별 금액의 누계 처리를 위해 SQL 이 추가 됨으로 인해서 같은 데이터를 한번 더 범위 처리를 해야 하는 문제가 발생함.

NOTE 5. LOOP 내의 데이터 연산을 SQL 로 처리 개선된 SOURCE(3 단계) CARTESIAN PRODUCT 사용
CURSOR C1 IS SELECT /*+ ORDERED USE_NL(A B) */ DECODE(COL1, 1, COURSE_CODE, NULL) COURSE_CODE, DECODE(COL1, 1, YEAR, 'Tot:') YEAR, NVL(SUM(DECODE(MONTH,'01',DEPOSIT_AMOUNT)),0) M01,NVL(SUM(DECODE(MONTH,'02',DEPOSIT_AMOUNT)),0) M02, NVL(SUM(DECODE(MONTH,'03',DEPOSIT_AMOUNT)),0) M03,NVL(SUM(DECODE(MONTH,'04',DEPOSIT_AMOUNT)),0) M04, NVL(SUM(DECODE(MONTH,'05',DEPOSIT_AMOUNT)),0) M05,NVL(SUM(DECODE(MONTH,'06',DEPOSIT_AMOUNT)),0) M06, NVL(SUM(DECODE(MONTH,'07',DEPOSIT_AMOUNT)),0) M07,NVL(SUM(DECODE(MONTH,'08',DEPOSIT_AMOUNT)),0) M08, NVL(SUM(DECODE(MONTH,'09',DEPOSIT_AMOUNT)),0) M09,NVL(SUM(DECODE(MONTH,'10',DEPOSIT_AMOUNT)),0) M10, NVL(SUM(DECODE(MONTH,'11',DEPOSIT_AMOUNT)),0) M11,NVL(SUM(DECODE(MONTH,'12',DEPOSIT_AMOUNT)),0) M12, NVL(SUM(DEPOSIT_AMOUNT),0) YEAR_AMT FROM (SELECT ROWNUM COL1 FROM EC_COURSE WHERE ROWNUM <= 2) A, (SELECT /*+ FULL(EC_APPLY) PARALLEL(EC_APPLY,3) */ COURSE_CODE,YEAR,TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) DEPOSIT_AMOUNT FROM EC_APPLY WHERE COURSE_CODE < 1000 AND YEAR BETWEEN '1999' AND '2002' GROUP BY COURSE_CODE,YEAR,TO_CHAR(APPLY_DATE,'MM')) B GROUP BY DECODE(COL1, 1, COURSE_CODE, NULL),DECODE(COL1, 1, YEAR, 'Tot:') ORDER BY 1, 2; CARTESIAN PRODUCT 사용 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ /* 변수 선언부 중간 생략 */ ... BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_YEAR, H_MONTH01_AMT,H_MONTH02_AMT,H_MONTH03_AMT,H_MONTH04_AMT, H_MONTH05_AMT,H_MONTH06_AMT,H_MONTH07_AMT,H_MONTH08_AMT, H_MONTH09_AMT,H_MONTH10_AMT,H_MONTH11_AMT,H_MONTH12_AMT, H_YEAR_AMT; EXIT WHEN C1%NOTFOUND; ... 중간 생략 END LOOP; CLOSE C1; END; / LOOP 내의 모든 연산 불필요 NOTE 개선된 점 월별 금액의 누계 처리를 위해서 같은 데이터를 한번 더 범위 처리를 함으로써 발생했던 문제점이 해소되었음.

6. LOOP 내의 LOGIC 을 OUTER 조인을 통한 DECODE 처리
전체 등록된 과정에 대해서 2002년 개설된 과정이면 E00 아니면 N00 으로 처리 하고자 합니다. 특히 E00 인 경우엔 최소/최대 차수 정보를 구하고 이때의 최소값과 최대값이 같다면 최대 차수 값을 99 로 처리 하고자 합니다. EC_COURSE (과정정보) COURSE_CODE COURSE_NAME EC_COURSE_SQ (과정차수정보) YEAR COURSE_SQ_NO … ○ 인덱스정보 EC_COURSE : EC_COURSE_PK : COURSE_CODE EC_COURSE_SQ : EC_COURSE_SQ_PK : COURSE_CODE + YEAR + COURSE_SQ_NO EC_COURSE_SQ_IDX_01 : YEAR (Non Unique) [ 개요 ] 本 사례에서는 LOOP 수행에 있어 그것의 내부에 존재하는 추가적인 조건과 더불어 SQL 에 대한 처리 부분을 하나의 SQL 로 처리토록 함으로써 성능 향상을 얻을 수 있는 방법을 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SOURCE DECLARE CURSOR C1 IS SELECT COURSE_CODE,COURSE_NAME FROM EC_COURSE ORDER BY COURSE_CODE, COURSE_NAME; /* 변수 선언부 중간 생략 */ BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_COURSE_NAME; EXIT WHEN C1%NOTFOUND; SELECT COUNT(*) INTO H_CNT FROM EC_COURSE_SQ WHERE COURSE_CODE = H_COURSE_CODE AND YEAR = '2002'; IF H_CNT <> 0 THEN H_CHK_EXIST := 'E00'; SELECT MIN(COURSE_SQ_NO), MAX(COURSE_SQ_NO) INTO H_MIN_SQ_NO, H_MAX_SQ_NO FROM EC_COURSE_SQ IF H_MIN_SQ_NO = H_MAX_SQ_NO THEN H_MAX_SQ_NO := 99; END IF; ELSE H_CHK_EXIST := 'N00'; H_MIN_SQ_NO := 0; H_MAX_SQ_NO := 0; END IF; ... 중간 생략 END LOOP; CLOSE C1; END; / NOTE

문제점 LOOP 내의 기술된 부분을 보면 LOOP 의 수행횟수 만큼 두 개의 SQL 이 실행 되도록 되어 있다는 것과 특정 처리를 위한 IF 조건이 존재함으로 인해 성능 향상을 얻기가 어렵다는 것입니다. 문제점을 해결하기 위한 방안 LOOP 내에 별도로 작성되어 있는 두 SQL의 실행과 IF 조건에 대한 처리를 가능하다면 하나의 SQL 상에서 구현할 수 있도록 해야 하겠습니다. 주어진 SOURCE 를 분석해 보면, 전체 등록된 과정 중에는 2002년도에 개설된 과정도 있는 반면에 그렇지 않은 경우도 있는 것으로 생각되며 이들 중 개설이 되었든 그렇지 않든 상관없이 데이터 처리가 되어야 되는 것으로 보면, 이는 OUTER 조인의 개념과 유사하다는 판단이 되며 이 때 DECODE 를 활용한 IF 조건에 대한 처리를 수행할 수 있도록 전개하면 되겠습니다. NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SOURCE(1 단계 - User Defined Function을 사용한 MIN/MAX 최적화 적용) CREATE OR REPLACE FUNCTION F_MINMAXSQ(A_COURSE_CODE IN NUMBER, A_YEAR IN VARCHAR2) RETURN VARCHAR2 IS H_MIN_SQ_NO VARCHAR2(3) := NULL; H_MAX_SQ_NO VARCHAR2(3) := NULL; BEGIN SELECT /*+ INDEX_DESC(B EC_COURSE_SQ_PK) */ SUBSTR(TO_CHAR(D.MINSQ,'09'),2,2) MINSQ, SUBSTR(TO_CHAR(B.COURSE_SQ_NO,'09'),2,2) MAXSQ INTO H_MIN_SQ_NO, H_MAX_SQ_NO FROM EC_COURSE_SQ B, (SELECT /*+ INDEX_ASC(C EC_COURSE_SQ_PK) */ C.COURSE_CODE,C.YEAR,C.COURSE_SQ_NO MINSQ FROM EC_COURSE_SQ C WHERE C.COURSE_CODE = A_COURSE_CODE AND C.YEAR = A_YEAR AND ROWNUM = 1) D WHERE B.COURSE_CODE = D.COURSE_CODE AND B.YEAR = D.YEAR AND ROWNUM = 1; RETURN H_MIN_SQ_NO||H_MAX_SQ_NO; END; / MIN 과 MAX 값에 대한 처리를 관련 힌트를 사용해서 최적화 함 SUBSTR 함수를 사용해서 최소차수와 최대차수 값을 구하고자 할 때 COURSE_SQ_NO 컬럼이 고정길이가 아니기 때문에 SUBSTR 함수를 사용하기가 어렵다. 이를 해결하고자 TO_CHAR 함수 사용시 포맷 부분을 '09'을 사용한 것이다. 이렇게 할 경우 어떻게 컬럼 값이 정의되는 지를 예를 들면 다음과 같다: SELECT TO_CHAR(-5, '09')||TO_CHAR(19, '09') FROM DUAL; → ' ' 지정한 길이(2자리) 보다 컬럼의 값이 작기 때문에 나머지 부분이 0 으로 채워져 있으며, 또한 숫자형 데이터에 TO_CHAR 함수를 사용한 결과 부호를 나타내는 한 자리가 더 추가됨을 알 수 있다.

참고 : MIN 과 MAX 값 처리에 대한 최적화 EC_COURSE_SQ_PK 과정 년도 차수 . ROWNUM =1 에 의해서 처리할 범위에 속하지 않는다 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ DECLARE CURSOR C1 IS SELECT COURSE_CODE, COURSE_NAME, MINSQ, DECODE(CHK_EXIST,'E00',DECODE(MAXSQ,MINSQ,99,MAXSQ),MAXSQ) MAXSQ, CHK_EXIST FROM (SELECT COURSE_CODE, COURSE_NAME, NVL(SUBSTR(MINMAXSQ,1,2),0) MINSQ, NVL(SUBSTR(MINMAXSQ,3,2),0) MAXSQ, DECODE(MINMAXSQ,NULL,'N00','E00') CHK_EXIST FROM (SELECT COURSE_CODE,COURSE_NAME, F_MINMAXSQ(COURSE_CODE, '2002') MINMAXSQ FROM EC_COURSE)) ORDER BY COURSE_CODE, COURSE_NAME; /* 변수 선언부 중간 생략 */ BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_COURSE_NAME,H_MIN_SQ_NO,H_MAX_SQ_NO,H_CHK_EXIST; EXIT WHEN C1%NOTFOUND; ... 중간 생략 END LOOP; CLOSE C1; END; / LOOP 내의 SQL과 IF 조건에 의한 처리가 모두 없어짐 NOTE

개선된 SOURCE(2 단계 - Outer 조인을 통한 DECODE 처리) DECLARE CURSOR C1 IS SELECT COURSE_CODE, COURSE_NAME, MINSQ, DECODE(CHK_EXIST,'E00',DECODE(MAXSQ,MINSQ,99,MAXSQ),MAXSQ) MAXSQ, CHK_EXIST FROM (SELECT /*+ USE_NL(A B) */ A.COURSE_CODE, A.COURSE_NAME, NVL(MIN(B.COURSE_SQ_NO),0) MINSQ, NVL(MAX(B.COURSE_SQ_NO),0) MAXSQ, MAX(DECODE(B.COURSE_CODE,NULL,'N00','E00')) CHK_EXIST FROM EC_COURSE_SQ B, EC_COURSE A WHERE A.COURSE_CODE = B.COURSE_CODE(+) AND B.YEAR(+)||'' = '2002' GROUP BY A.COURSE_CODE, A.COURSE_NAME) ORDER BY COURSE_CODE, COURSE_NAME; /* 변수 선언부 중간 생략 */ BEGIN OPEN C1; LOOP FETCH C1 INTO H_COURSE_CODE,H_COURSE_NAME,H_MIN_SQ_NO,H_MAX_SQ_NO,H_CHK_EXIST; EXIT WHEN C1%NOTFOUND; 중간 생략 END LOOP; CLOSE C1; END; / LOOP 내의 SQL과 IF 조건에 의한 처리가 모두 없어짐 NOTE

7. LOGIC에 의한 INSERT를 INSERT…SELECT~ 로 해결
Perfect! 대용량 데이터베이스 튜닝 Ⅱ EC_DANGA_HIST (단가코드 별 이력 정보) CODE /* 단가코드 */ SEQ_NO /* 순번 */ PRICE /* 단가 */ BEGIN_YMD /* 적용 시작일 */ 코드 순번 단가 적용시작일 P P P P P P P P P SAMPLE 인덱스정보 EC_DANGA_HIST_PK : CODE + SEQ_NO 입력된 단가코드에 대한 이력관리를 하고자 합니다. 만약에 입력하고자 하는 단가코드에 대해 최초의 입력일 경우에는 순번을 001로 해서 입력 되도록 하고, 이미 하나 이상의 이력이 존재할 경우에는 일련번호를 그 단가코드의 순번 중 최대 순번을 구해서 +1 을 해서 이력정보 테이블에 입력하고자 합니다. [ 개요 ] 本 사례에서는 LOGIC 에 의해서 구현된 INSERT 작업을 경우에 따라서는 LOGIC 처리 없이도 흔히 사용하고 있는 INSERT...SELECT...문장 하나로 해결할 수 있음을 제시하고자 합니다.

구현된 LOGIC (PL/SQL 로 작성됨을 가정) SELECT SUBSTR(TO_CHAR(MAX(SEQ_NO) + 1,'099'),2,3) INTO H_NEXT_SEQ FROM EC_DANGA_HIST WHERE CODE = H_CODE; IF H_NEXT_SEQ IS NULL THEN INSERT INTO EC_DANGA_HIST VALUES(H_CODE,'001', H_PRICE, H_BEGIN_YMD); ELSE VALUES(H_CODE, H_NEXT_SEQ, H_PRICE, H_BEGIN_YMD); END IF; NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선안에 대한 개요 - OUTER 조인의 활용 즉, 변수를 통해서 입력하는 값 들을 어떤 테이블에 있는 데이터라고 가정하고 단가 이력정보 테이블을 놓고 생각해 보면 OUTER 조인을 해야 한다는 것을 쉽게 알 수 있습니다. 단가 이력정보 테이블의 경우 입력하고자 하는 단가코드에 대 한 이력이 있을 수도 있고 없을 수도 있습니다. 따라서 이것을 참조 테이블로 간주한다면, 위에서 가정한대로 입력할 값 들에 대한 테이블은 OUTER 조인 상 에서의 기준 테이블에 해당합니다. 왜냐하면 이들 데이터는 반드시 단가 이력 정보 테이블에 입력 되어야 할 것이기 때문입니다. NOTE

단가코드 별 이력정보 테이블 코드 순번 단가 적용시작일 P P P P P . P 코드 단가 적용시작일 입력 값 들에 대한 테이블 P P P P P P = NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL INSERT INTO EC_DANGA_HIST SELECT /*+ INDEX_DESC(A EC_DANGA_HIST_PK) USE_NL(A B) */ B.CODE, DECODE(A.CODE,NULL, '001',SUBSTR(TO_CHAR(A.SEQ_NO + 1,'099'),2,3)), B.PRICE, B.BEGIN_YMD FROM EC_DANGA_HIST A, (SELECT H_CODE AS CODE, H_PRICE AS PRICE, H_BEGIN_YMD AS BEGIN_YMD FROM EC_DANGA_HIST WHERE ROWNUM = 1) B WHERE B.CODE = A.CODE(+) AND ROWNUM = 1; 입력하는 값 들을 임의의 테이블에 있는 데이터로 제어하고자 하는 부분 위 SQL 의 DECODE(…) 구문을 NVL2 함수를 사용해서 다음과 같이 전환할 수도 있습니다: INSERT INTO EC_DANGA_HIST SELECT /*+ INDEX_DESC(A EC_DANGA_HIST_PK) USE_NL(A B) */ B.CODE, NVL2(A.CODE,SUBSTR(TO_CHAR(A.SEQ_NO + 1,'099'),2,3),'001'), B.PRICE, B.BEGIN_YMD FROM EC_DANGA_HIST A, (SELECT H_CODE AS CODE, H_PRICE AS PRICE, H_BEGIN_YMD AS BEGIN_YMD FROM EC_DANGA_HIST WHERE ROWNUM = 1) B WHERE B.CODE = A.CODE(+) AND ROWNUM = 1;

8. SELF-서브쿼리에 대한 해결 8. SELF-서브쿼리에 대한 해결 EC_CUSTOMER (위탁고객정보) BRNCOD /* 지점 */ CMPID /* 고객ID */ STSDAT /* 변경일자 */ STS /* 고객상태 */ AMT /* 위탁금액 */ 지점 고객ID 변경일자 상태 위탁금액 SAMPLE 인덱스정보 EC_CUSTOMER_PK : BRNCOD+STSDAT+CMPID+STS EC_CUSTOMER_IDX1 : BRNCOD+CMPID+STSDAT+STS EC_CUSTOMER_IDX2 : STSDAT 변경일자가 ' ' 이후인 경우에 대해서 다음과 같은 결과를 얻고자 합니다. [ 개요 ] 일상적으로 자주 사용하는 SELF-Join과 SELF-Subquery의 경우 모두 같은 테이블을 반복해서 사용 함으로써 원하는 결과를 추출한다는 것을 알 수 있습니다. 사실 부득이한 경우라면 달리 생각해볼 수 있는 가능성 조차 없겠지만, 그런 경우가 아님에도 불구하고 그렇게 한다고 하면 분명히 이는 문제가 될 수 있는 것입니다. 本 사례에서는 SELF-Subquery에 있어서 문제점을 생각해 보면서 이를 해결하기 위한 방안을 찾아 보고자 합니다. 지점 고객ID 변경일자 상태 위탁금액 OUTPUT

NOTE 8. SELF-서브쿼리에 대한 해결 SQL SELECT BRNCOD, CMPID, STSDAT, STS, AMT
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL SELECT BRNCOD, CMPID, STSDAT, STS, AMT FROM EC_CUSTOMER WHERE (BRNCOD, CMPID, STSDAT, STS) IN (SELECT BRNCOD, CMPID, STSDAT, MIN(STS) WHERE (BRNCOD, CMPID, STSDAT) IN (SELECT BRNCOD, CMPID, MIN(STSDAT) WHERE STSDAT > ' ' OR (STSDAT = ' ' AND STS = 1) GROUP BY BRNCOD, CMPID) GROUP BY BRNCOD, CMPID, STSDAT); 최초의 고객상태 최초의 변경일자 문제점 EC_CUSTOMER 테이블을 3 번 반복해서 사용토록 함으로써 반복 처리되는 만큼의 부하가 발생하고 있습니다. 사실 STSDAT 의 최소값을 구한 뒤 STS 의 최소값을 구하려고 한 것이 문제의 발단이라 할 수 있습니다. NOTE

NOTE 8. SELF-서브쿼리에 대한 해결 Rows Execution Plan
0 SELECT STATEMENT GOAL: CHOOSE 4 NESTED LOOPS 5 VIEW OF 'VW_NSO_1' 5 SORT (GROUP BY) NESTED LOOPS VIEW OF 'VW_NSO_2' SORT (GROUP BY) CONCATENATION TABLE ACCESS (BY INDEX ROWID) OF 'EC_CUSTOMER' INDEX (RANGE SCAN) OF 'EC_CUSTOMER_IDX2' (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'EC_CUSTOMER' INDEX (RANGE SCAN) OF 'EC_CUSTOMER_IDX2' (NON-UNIQUE) TABLE ACCESS (BY INDEX ROWID) OF 'EC_CUSTOMER' INDEX (RANGE SCAN) OF 'EC_CUSTOMER_IDX2' (NON-UNIQUE) 4 TABLE ACCESS (BY INDEX ROWID) OF 'EC_CUSTOMER' 8 INDEX (UNIQUE SCAN) OF 'EC_CUSTOMER_PK' (UNIQUE) NOTE

8. SELF-서브쿼리에 대한 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점 해결하기 위한 방안 조건을 만족하는 최초의 변경일자(MIN(STSDAT)와 최초의 상태(MIN(STS)를 함께 구할 수 있도록 한다면 EC_CUSTOMER 테이블에 대한 반복적인 사용은 피할 수 있 을 것 입니다. 개선된 SQL(1 단계) 테이블에 대한 처리횟수 감소 SELECT BRNCOD, CMPID, STSDAT, STS, AMT FROM EC_CUSTOMER WHERE (BRNCOD, CMPID, STSDAT||STS) IN (SELECT BRNCOD, CMPID, MIN(STSDAT||STS) WHERE STSDAT > ' ' OR (STSDAT = ' ' AND STS = 1) GROUP BY BRNCOD, CMPID); CONCATENATION 으로 풀림 NOTE 문제점 Sub-query 에 있어 OR 조건이 CONCATENATION 으로 실행

8. SELF-서브쿼리에 대한 해결 CONCATENATION 의 문제점
SELECT BRNCOD, CMPID, MIN(STSDAT||STS) FROM EC_CUSTOMER WHERE STSDAT > ' ' OR (STSDAT = ' ' AND STS = 1) GROUP BY BRNCOD, CMPID; 테이블에 대한 반복된 처리 CONCATENATION EC_CUSTOMER_IDX2 . EC_CUSTOMER 추출된 Data 위의 SQL 은 곧 다음의 SQL 과 같은 실행원리를 갖고 있다: SELECT BRNCOD, CMPID, MIN(STSDAT||STS) FROM (SELECT BRNCOD, CMPID, STSDAT, STS FROM EC_CUSTOMER WHERE STSDAT = ' ' AND STS = 1 UNION ALL SELECT BRNCOD, CMPID, STSDAT, STS WHERE STSDAT > ' ') GROUP BY BRNCOD, CMPID;

NOTE 8. SELF-서브쿼리에 대한 해결 CONCATENATION 의 해결 방안
Perfect! 대용량 데이터베이스 튜닝 Ⅱ CONCATENATION 의 해결 방안 WHERE STSDAT > ' ' OR (STSDAT = ' ' AND STS = 1) STSDAT 는 ' ' 보다 크거나 같다는 것이며, STS 에 (1,2,3,4)와 같이 4 가지의 경우가 있다고 할 때 위의 조건을 해석해 보면 STSDAT = ' ' 경우 STS = 1 인 것만을 대상으로 하고, STSDAT > ' ' 일 경우는 STS의 4 가지 모두를 대상으로 하겠다는 것입니다. 즉, IF STSDAT = ' ' THEN STS = 1 ELSE IF STSDAT > ' ' THEN STS = STS END IF 따라서 다음과 같이 표현할 수 있습니다: WHERE STSDAT >= ' ' AND STS = DECODE(STSDAT,' ', 1, STS) NOTE

NOTE 8. SELF-서브쿼리에 대한 해결 개선된 SQL(2 단계)
SELECT BRNCOD, CMPID, STSDAT, STS, AMT FROM EC_CUSTOMER WHERE (BRNCOD, CMPID, STSDAT||STS) IN (SELECT BRNCOD, CMPID, MIN(STSDAT||STS) WHERE STSDAT >= ' ' AND STS = DECODE(STSDAT,' ', 1, STS) GROUP BY BRNCOD, CMPID); CONCATENATION 풀림 방지 문제점 Main-query 가 수행될 때 만약에 STSDAT 에 의한 인덱스를 사용해야 한다면 STSDAT||STS 으로 인해 STSDAT 에 의한 인덱스 사용이 불가능(Suppressing) NOTE

8. SELF-서브쿼리에 대한 해결 개선된 SQL(3 단계)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(3 단계) SELECT BRNCOD, CMPID, STSDAT, STS, AMT FROM EC_CUSTOMER WHERE (BRNCOD, CMPID, STSDAT, STS) IN (SELECT BRNCOD, CMPID, SUBSTR(MIN(STSDAT||STS),1,8) MIN_DAT, SUBSTR(MIN(STSDAT||STS),9,1) MIN_STS WHERE STSDAT >= ' ' AND STS = DECODE(STSDAT,' ', 1, STS) GROUP BY BRNCOD, CMPID); 문제점 1 ~ 3 단계 별로 SQL 에 대한 개선이 점진적으로 모색은 되었으나, 전반적으로 SELF-서브쿼리 형태로 작성됨 위의 SQL 은 다음의 SELF-조인과 같은 실행원리를 갖고 있다. SELECT C.BRNCOD, C.CMPID, C.STSDAT, C.STS, C.AMT FROM EC_CUSTOMER C, (SELECT BRNCOD, CMPID, SUBSTR(MIN(STSDAT||STS),1,8) MIN_DAT, SUBSTR(MIN(STSDAT||STS),9,1) MIN_STS FROM EC_CUSTOMER WHERE STSDAT >= ' ' AND STS = DECODE(STSDAT,' ', 1, STS) GROUP BY BRNCOD, CMPID) B WHERE C.BRNCOD = B.BRNCOD AND C.CMPID = B.CMPID AND C.STSDAT = B.MIN_DAT AND C.STS = B.MIN_STS;

NOTE 8. SELF-서브쿼리에 대한 해결 개선된 SQL(4 단계)
SELECT BRNCOD, CMPID, STSDAT, STS, AMT FROM (SELECT BRNCOD, CMPID, STSDAT, STS, MIN(STSDAT||STS) OVER(PARTITION BY BRNCOD, CMPID) MIN_DATSTS, AMT FROM EC_CUSTOMER WHERE STSDAT >= ' ' AND STS = DECODE(STSDAT,' ', 1, STS)) WHERE STSDAT = SUBSTR(MIN_DATSTS,1,8) AND STS = SUBSTR(MIN_DATSTS,9,1); 개선 사항 분석용 함수 MIN(…) OVER(…) 의 사용으로 인해 EC_CUSTOMER 테이블을 한 번만 처리할 수 있도록 개선함 NOTE

NOTE 8. SELF-서브쿼리에 대한 해결 Rows Execution Plan
Perfect! 대용량 데이터베이스 튜닝 Ⅱ Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 4 VIEW 12 WINDOW (SORT) TABLE ACCESS (BY INDEX ROWID) OF 'EC_CUSTOMER' INDEX (RANGE SCAN) OF 'EC_CUSTOMER_IDX2' (NON-UNIQUE) NOTE

9. 나열형 설계로 인한 문제점 해결 9. 나열형 설계로 인한 문제점 해결 소개 '등록일자'와 '수정일자'를 '일자'라는 동일한 속성으로 볼 수도 있으나, 각각을 고유한 속성으로 보고 나열하는 식으로 설계했으며, '등록일자'와 '수정일자' 에 대해서 각각 (MAIN_CODE, ENTRY_YMD) 와 (MAIN_CODE, UPDATE_YMD) 형태의 결합 인덱스를 구성 인덱스정보 EC_PROG_PROGRESS01_PK : MAIN_CODE + PROGRAM_ID EC_PROG_PROGRESS01_IDX01 : MAIN_CODE + ENTRY_YMD EC_PROG_PROGRESS01_IDX02 : MAIN_CODE + UPDATE_YMD EC_PROG_PROGRESS01 (프로그램 별 진척정보) PROGRAM_ID /* PGM ID */ MAIN_CODE /* 등록코드 */ ENTRY_YMD /* 등록일자 */ UPDATE_YMD /* 수정일자 */ [ 개요 ] 대부분의 설계자는 테이블을 정규화 함으로써 테이블의 개수와 더불어 데이터 건수가 증가하는 것 보다는 컬럼 들을 옆으로 나열함으로써 테이블의 개수와 데이터 건수가 감소되는 것을 선호하는 경우가 있습니다. 사실 정규화나 반정규화나 어떤 것이 정답(正答) 이라고 하기엔 어려운 점이 있으나, 어떠한 형태이든지 처리되는 데이터 량은 거의 동일하다고 할 수 있습니다. 本 사례에서는 컬럼 별 데이터의 특성을 볼 때 같다고 할 수 있는 컬럼들을 테이블 설계 시 별도의 컬럼으로 설계함으로써 나타날 수 있는 SQL의 특성과 데이터 처리시 발생할 수 있는 문제점, 그리고 인덱스 구성 시 발생할 수 있는 문제점 등을 살펴보고, 이를 해결하기 위한 방안을 찾아 보고자 합니다.

GROUP BY 처리를 위한 컬럼 선정시 어려움 발생
9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ 'MC2004-PJ09' 에 등록된 전체 프로그램을 대상으로 '2004' 년도에 새롭게 등록되거나 수정이 발생한 프로그램 현황을 월별로 조회하 고자 합니다. GROUP BY 처리를 위한 컬럼 선정시 어려움 발생 원하는 Output 형식의 예 월 등록건수 수정건수 SELECT , COUNT(ENTRY_YMD), COUNT(UPDATE_YMD) FROM EC_PROG_PROGRESS01 WHERE MAIN_CODE = 'MC2004-PJ09' AND (ENTRY_YMD LIKE '2004%' OR UPDATE_YMD LIKE '2004%') GROUP BY ; ? 1월 2월 3월 실제는 존재하지 않지만 조회 되도록 한 것 4월 중간 생략 ●●● 10월 NOTE 11월 ? 12월

NOTE 9. 나열형 설계로 인한 문제점 해결 SQL(양방향 Outer 조인)
SELECT A.MONTH||'월', A.ENTRY_CNT, NVL(B.UPDATE_CNT,0) FROM (SELECT SUBSTR(ENTRY_YMD,5,2) MONTH, COUNT(ENTRY_YMD) ENTRY_CNT FROM EC_PROG_PROGRESS01 WHERE MAIN_CODE = 'MC2004-PJ09' AND ENTRY_YMD LIKE '2004%' GROUP BY SUBSTR(ENTRY_YMD,5,2)) A, (SELECT SUBSTR(UPDATE_YMD,5,2) MONTH, COUNT(UPDATE_YMD) UPDATE_CNT WHERE MAIN_CODE = 'MC2004-PJ09' AND UPDATE_YMD LIKE '2004%' GROUP BY SUBSTR(UPDATE_YMD,5,2)) B WHERE A.MONTH = B.MONTH(+) UNION SELECT B.MONTH||'월', NVL(A.ENTRY_CNT,0), B.UPDATE_CNT WHERE A.MONTH(+) = B.MONTH; NOTE

NOTE 9. 나열형 설계로 인한 문제점 해결 Rows Execution Plan
Perfect! 대용량 데이터베이스 튜닝 Ⅱ Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 4 SORT (UNIQUE) 7 UNION-ALL 3 MERGE JOIN (OUTER) SORT (JOIN) VIEW SORT (GROUP BY) INDEX (RANGE SCAN) OF 'EC_PROG_PROGRESS01_IDX01' (NON-UNIQUE) SORT (JOIN) VIEW SORT (GROUP BY) INDEX (RANGE SCAN) OF 'EC_PROG_PROGRESS01_IDX02' (NON-UNIQUE) 4 MERGE JOIN (OUTER) SORT (JOIN) NOTE

9. 나열형 설계로 인한 문제점 해결 9. 나열형 설계로 인한 문제점 해결 문제점 테이블에 대한 데이터 처리를 4 번 하고 있습니다. 즉, ENTRY_YMD 에 의한 2 번, UPDATE_YMD 에 의한 2 번씩 처리 함으로써 반복 처리하는 만큼의 과부하가 발생하고 있는 것입니다. 개선된 SQL(1 단계) SELECT MONTH||'월', SUM(ENTRY_CNT), SUM(UPDATE_CNT) FROM (SELECT SUBSTR(ENTRY_YMD,5,2) MONTH, COUNT(ENTRY_YMD) ENTRY_CNT, 0 UPDATE_CNT FROM EC_PROG_PROGRESS01 WHERE MAIN_CODE = 'MC2004-PJ09' AND ENTRY_YMD LIKE '2004%' GROUP BY SUBSTR(ENTRY_YMD,5,2) UNION ALL SELECT SUBSTR(UPDATE_YMD,5,2) MONTH, 0 ENTRY_CNT, COUNT(UPDATE_YMD) UPDATE_CNT AND UPDATE_YMD LIKE '2004%' GROUP BY SUBSTR(UPDATE_YMD,5,2)) GROUP BY MONTH||'월'; NOTE

9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 4 SORT (GROUP BY) 7 VIEW 7 UNION-ALL SORT (GROUP BY) INDEX (RANGE SCAN) OF 'EC_PROG_PROGRESS01_IDX01' (NON-UNIQUE) SORT (GROUP BY) INDEX (RANGE SCAN) OF 'EC_PROG_PROGRESS01_IDX02' (NON-UNIQUE) 문제점 생각하기에 따라 같은 특성을 지니고 있다 할 수 있는 ENTRY_YMD, UPDATE_YMD 를 나열하는 형태로 각각을 컬럼으로 테이블 상에 설계 한 경우이기 때문에 어쩔 수 없이 테이블을 두 번 처리해야만 한다. NOTE

MAIN_CODE+ UPDATE_YMD
9. 나열형 설계로 인한 문제점 해결 9. 나열형 설계로 인한 문제점 해결 現 문제점의 개요 각각의 추출 데이터에 대한 GROUP BY UNION ALL 테이블에 대한 반복된 처리 SORT SORT PROGRESS 테이블 PROGRESS 테이블 추출된 Data 추출된 Data . . . . NOTE MAIN_CODE+ ENTRY_YMD MAIN_CODE+ UPDATE_YMD

9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점 해결하기 위한 방안 - 정규화를 통한 설계 변경 인덱스의 개수는 나열했을 경우 보다 줄어들 것이며, 데이터 처리량도 많이 개선할 만한 형태의 간결한 SQL 작성이 가능해 질 것이다. 정규화 했을 경우에 대한 오해 무엇보다도 데이터 건수가 증가할 것이며 이로 인해 처리할 데이터 량이 오히려 정규화 전 보다 증가할 것이다! 200만 2000 Bytes 처리를 위한 데이터 량은 거의 동일함 100만 4000 Bytes NOTE

처리할 데이터에 대한 DECODE() 처리가 부하 가능
9. 나열형 설계로 인한 문제점 해결 9. 나열형 설계로 인한 문제점 해결 정규화(Ⅰ안)를 통한 설계 변경이 다음과 같다면, 인덱스정보 EC_PROG_PROGRESS02_PK : MAIN_CODE + PROGRAM_ID YMD_GBN EC_PROG_PROGRESS02_IDX01 : MAIN_CODE + YMD EC_PROG_PROGRESS02 (프로그램 별 진척정보) PROGRAM_ID /* PGM ID */ MAIN_CODE /* 등록코드 */ YMD_GBN /* 일자구분 */ YMD /* 일자 */ 처리할 데이터에 대한 DECODE() 처리가 부하 가능 개선된 SQL(2 단계) SELECT SUBSTR(YMD,5,2)||'월' MONTH, NVL(COUNT(DECODE(YMD_GBN,'E',YMD)),0) ENTRY_CNT, NVL(COUNT(DECODE(YMD_GBN,'U',YMD)),0) UPDATE_CNT FROM EC_PROG_PROGRESS02 WHERE MAIN_CODE = 'MC2004-PJ09' AND YMD LIKE '2004%' GROUP BY SUBSTR(YMD,5,2)||'월'; NOTE

처리할 데이터에 대한 DECODE() 처리의 부하 감소
9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(3 단계) SELECT MONTH||'월', NVL(SUM(DECODE(YMD_GBN,'E',CNT)),0) ENTRY_CNT, NVL(SUM(DECODE(YMD_GBN,'U',CNT)),0) UPDATE_CNT FROM (SELECT SUBSTR(YMD,5,2) MONTH, YMD_GBN, COUNT(YMD) CNT FROM EC_PROG_PROGRESS02 WHERE MAIN_CODE = 'MC2004-PJ09' AND YMD LIKE '2004%' GROUP BY SUBSTR(YMD,5,2),YMD_GBN) GROUP BY MONTH||'월'; 처리할 데이터에 대한 DECODE() 처리의 부하 감소 NOTE

9. 나열형 설계로 인한 문제점 해결 개선된 SQL(4 단계 – 존재하지 않는 '월' 에 대한 처리)
SELECT B.MONTH||'월', SUM(DECODE(A.MONTH,B.MONTH,DECODE(A.YMD_GBN,'E',CNT),0)) ENTRY_CNT, SUM(DECODE(A.MONTH,B.MONTH,DECODE(A.YMD_GBN,'U',CNT),0)) UPDATE_CNT FROM (SELECT SUBSTR(YMD,5,2) MONTH, YMD_GBN, COUNT(YMD) CNT FROM EC_PROG_PROGRESS02 WHERE MAIN_CODE = 'MC2004-PJ09' AND YMD LIKE '2004%' GROUP BY SUBSTR(YMD,5,2),YMD_GBN) A, (SELECT SUBSTR(TO_CHAR(ROWNUM,'09'),2,2) MONTH FROM EC_COURSE WHERE ROWNUM <= 12) B GROUP BY B.MONTH||'월'; CARTESIAN PRODUCT '01' ~ '12' 까지의 월에 대한 데이터를 제어하기 위함 위의 SQL은 다음의 앞서 제시된 정규화를 통한 설계 변경 중 Ⅰ안을 토대로 작성한 것이며, CARTESIAN Product 에 의한 데이터 복제를 이용하고 있습니다. 그런데, 여기에서 처럼 반복적인 DECODE의 사용은 SQL을 길고도 복잡하게 하며, 수행속도에도 많은 영향을 줄 수 있습니다.

9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ CARTESIAN PRODUCT 처리(1월에 한해 등록 42건 수정 70건이 발생했다고 가정) 01 E E E E E E E E E E E E U U U U U U U U U U U U 01 E E E E E E E E E E E E U U U U U U U U U U U U 01 E U 70 08 09 10 11 12 ( ) , SELECT … FROM 카테시안 곱 D E C O 위의 그림은 1월에 한해서 등록 42건, 수정 70건이 발생했다는 가정하에 CARTESIAN PRODUCT 에 의한 결과와 DECODE 함수 처리에 의한 결과를 각각 나타낸 것입니다.

9. 나열형 설계로 인한 문제점 해결 개선된 SQL(5 단계) SELECT B.MONTH||'월',
SUM(DECODE(A.MONTH||A.YMD_GBN,B.MONTH||'E',CNT,0)) ENTRY_CNT, SUM(DECODE(A.MONTH||A.YMD_GBN,B.MONTH||'U',CNT,0)) UPDATE_CNT FROM (SELECT SUBSTR(YMD,5,2) MONTH, YMD_GBN, COUNT(YMD) CNT FROM PROG_PROGRESS02 WHERE MAIN_CODE = 'MC2004-PJ09' AND YMD LIKE '2004%' GROUP BY SUBSTR(YMD,5,2),YMD_GBN) A, (SELECT SUBSTR(TO_CHAR(ROWNUM,'09'),2,2) MONTH FROM EC_COURSE WHERE ROWNUM <= 12) B GROUP BY B.MONTH||'월'; 위의 SQL은 앞서 작성한 SQL에 대한 DECODE의 사용에 있어서의 문제점을 해결하고자 한 것입니다. 즉, DECODE의 depth를 1단계로 간략하게 표현하고 있습니다. 이러한 카테시안 곱 처리로 인한 문제점은 무엇이 있을까? 원래의 데이터를 경우의 수 만큼 복제(생성)를(을) 한 후 복제된 데이터를 대상으로 한 선별작업이 행해져야 합니다. 이 때, 원래의 데이터를 복제하는 것은 별도의 물리적인 I/O가 없어서 별반 문제가 없다고 하더라도, 복제에 의해 생성된 데이터가 (수백/수천/수억)건이라면 이들을 대상으로 한 그 후의 선별작업이 문제될 수 있습니다. 이러한 문제점을 없앨 수 있는 방법은 없을까? (Outer 조인의 활용) 물론, Outer 조인을 행할 때의 조인 방식은 NL 조인이 보장되지 않는다면 SM/HASH 조인을 행하게 될 것입니다.

9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(6 단계 – 존재하지 않는 '월' 에 대한 처리 시 Outer 조인 활용) SELECT B.MONTH||'월', NVL(SUM(DECODE(A.YMD_GBN, 'E', A.CNT)),0) ENTRY_CNT, NVL(SUM(DECODE(A.YMD_GBN, 'U', A.CNT)),0) UPDATE_CNT FROM (SELECT SUBSTR(YMD,5,2) MONTH, YMD_GBN, COUNT(YMD) CNT FROM EC_PROG_PROGRESS02 WHERE MAIN_CODE = 'MC2004-PJ09' AND YMD LIKE '2004%' GROUP BY SUBSTR(YMD,5,2),YMD_GBN) A, (SELECT SUBSTR(TO_CHAR(ROWNUM,'09'),2,2) MONTH FROM EC_COURSE WHERE ROWNUM <= 12) B WHERE B.MONTH = A.MONTH(+) GROUP BY B.MONTH||'월'; OUTER Join NOTE

NOTE 9. 나열형 설계로 인한 문제점 해결 정규화(Ⅱ안)를 통한 설계 변경이 다음과 같다면, 인덱스정보
EC_PROG_PROGRESS03_PK : MAIN_CODE + PROGRAM_ID EC_PROG_PROGRESS04_PK : PROGRAM_ID + YMD_GBN EC_PROG_PROGRESS04_IDX01 : YMD + YMD_GBN EC_PROG_PROGRESS03 (프로그램 등록정보) PROGRAM_ID /* PGM ID */ MAIN_CODE /* 등록코드 */ EC_PROG_PROGRESS04 (프로그램 별 진척정보) YMD_GBN /* 일자구분 */ YMD /* 일자 */ ○ 개선된 SQL(7 단계) SELECT SUBSTR(B.YMD,5,2)||'월' MONTH, NVL(COUNT(DECODE(B.YMD_GBN,'E',B.YMD)),0) ENTRY_CNT, NVL(COUNT(DECODE(B.YMD_GBN,'U',B.YMD)),0) UPDATE_CNT FROM EC_PROG_PROGRESS03 A, EC_PROG_PROGRESS04 B WHERE A.PROGRAM_ID = B.PROGRAM_ID AND A.MAIN_CODE = 'MC2004-PJ09' AND B.YMD LIKE '2004%' GROUP BY SUBSTR(B.YMD,5,2)||'월'; 테이블 간 조인의 빈번 가능성 존재 NOTE

NOTE 9. 나열형 설계로 인한 문제점 해결 개선된 SQL(8 단계) SELECT B.MONTH||'월',
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(8 단계) SELECT B.MONTH||'월', NVL(SUM(DECODE(B.YMD_GBN,'E',B.CNT)),0) ENTRY_CNT, NVL(SUM(DECODE(B.YMD_GBN,'U',B.CNT)),0) UPDATE_CNT FROM EC_PROG_PROGRESS03 A, (SELECT PROGRAM_ID,SUBSTR(YMD,5,2) MONTH,YMD_GBN,COUNT(YMD) CNT FROM EC_PROG_PROGRESS04 WHERE YMD LIKE '2004%' GROUP BY PROGRAM_ID,SUBSTR(YMD,5,2),YMD_GBN) B WHERE A.PROGRAM_ID = B.PROGRAM_ID AND A.MAIN_CODE = 'MC2004-PJ09' GROUP BY B.MONTH||'월'; 테이블 간의 불필요한 조인을 줄임 NOTE

NOTE 9. 나열형 설계로 인한 문제점 해결 개선된 SQL(9 단계 – 존재하지 않는 '월' 에 대한 처리)
SELECT C.MONTH||'월', NVL(SUM(DECODE(D.YMD_GBN,'E',D.CNT)),0) ENTRY_CNT, NVL(SUM(DECODE(D.YMD_GBN,'U',D.CNT)),0) UPDATE_CNT FROM (SELECT B.MONTH, A.PROGRAM_ID FROM EC_PROG_PROGRESS03 A, (SELECT SUBSTR(TO_CHAR(ROWNUM,'09'),2,2) MONTH FROM EC_COURSE WHERE ROWNUM <= 12) B WHERE A.MAIN_CODE = 'MC2004-PJ09') C, (SELECT PROGRAM_ID, SUBSTR(YMD,5,2) MONTH, YMD_GBN, COUNT(YMD) CNT FROM EC_PROG_PROGRESS04 WHERE YMD LIKE '2004%' GROUP BY PROGRAM_ID,SUBSTR(YMD,5,2),YMD_GBN) D WHERE C.PROGRAM_ID = D.PROGRAM_ID(+) AND C.MONTH = D.MONTH(+) GROUP BY C.MONTH||'월'; '01' ~ '12' 까지의 월에 대한 데이터를 제어하기 위함 OUTER Join NOTE

9. 나열형 설계로 인한 문제점 해결 Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선 사항 전반적으로 보면, 정규화에 의한 설계 변경이 가능하거나 또는 정규화된 설계가 이미 되어있었다면 데이터 처리에 있어서 테이블에 대한 반복 처 리를 하지 않아도 되는 형태의 SQL 작성이 가능함으로써 성능 개선을 할 수 있었으며, 또한 일자에 대한 인덱스의 개수도 줄일 수 있었다고 할 수 있습니다. NOTE

10. SUMMARY 테이블을 활용한 현재일까지의 처리
B A T C H EC_APPLY (수강신청정보) COURSE_CODE YEAR COURSE_SQ_NO MEMBER_TYPE MEMBER_ID APPLY_DATE PAYMENT_METHOD DEPOSIT_AMOUNT DEPOSITOR DEPOSIT_DATE BANK COMPANY_NO RECOMMENDER EC_SUMMARY_DEPOSIT (월별 과정별 금액 집계) YMD /* 처리일자 */ COURSE_CODE/* 과정코드 */ DEPOSIT_AMOUNT /* 금액누계 */ 집계일자 과정 금액 중간 생략 ●●● SAMPLE 소개 집계테이블의 일자는 해당 월에 최종적으로 집계작업을 수행한 날짜를 의미하며 금액은 월 단위로 해당 월의 1일부터 처리일자 까지의 누계가 입력되어 있습니다. 인덱스정보 EC_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + MEMBER_TYPE + MEMBER_ID EC_APPLY_APPLY_DATE_IDX : APPLY_DATE (Non Unique) EC_SUMMARY_DEPOSIT_PK : YMD + COURSE_CODE [ 개요 ] 대량의 데이터를 이용해서 각종 분석을 위한 처리를 원할 때 액세스의 효율화만으로 원하는 수행속도를 얻기가 어려울 때가 많이 있습니다. 이러한 경우에는 대체로 집계테이블과 진행테이블을 추가하는 식의 반정규화 (Denormalization) 를 하나의 방법으로 채택하기도 합니다. 물론 이와 같이 원하는 결과를 추출하고자 중간에 활용하기 위한 테이블을 추가하는 경우에는 추가적으로 발생 가능한 오버헤드(Overhead)는 감안해야 합니다. 그러나 이러한 방법을 사용함으로써 만족할 만한 수행 속도를 얻을 수 있기에 대다수 많이 사용하고 있기도 합니다. 本 사례에서는 현재일까지의 데이터를 처리하고자 할 때 집계테이블과 온라인 상의 테이블을 함께 효율적으로 활용할 수 있도록 함으로써 사용자에게 최대한의 융통성은 물론 실시간으로 처리된 데이터에 대한 처리를 효율적으로 할 수 있는 방안을 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 2002년 4월 25일부터 시스템 장애로 인해 집계 테이블을 갱신하지 못한 상황에서 2002년 1월 부터 2002년 4월 28일 현재까지의 월별 누계금액 을 조회하려고 합니다. 원하는 Output 형식의 예 SELECT DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200204', TO_CHAR(APPLY_DATE,'YYYYMM')||SUBSTR(' ',7,2), TO_CHAR(LAST_DAY(APPLY_DATE),'YYYYMMDD')) YMD, COURSE_CODE, SUM(DEPOSIT_AMOUNT) AS S_DEPOSIT FROM EC_APPLY WHERE APPLY_DATE BETWEEN TO_DATE(' ','YYYYMMDD') AND TO_DATE(' ','YYYYMMDD') GROUP BY DECODE(TO_CHAR(APPLY_DATE,'YYYYMM'),'200204', TO_CHAR(LAST_DAY(APPLY_DATE),'YYYYMMDD')), COURSE_CODE; 집계일자 과정 집계금액 중간 생략 ●●● NOTE 4월의 경우 현재일자로 조회 중간 생략 ●●●

개선된 SQL(1 단계) SELECT YMD, COURSE_CODE, DEPOSIT_AMOUNT AS S_DEPOSIT FROM EC_SUMMARY_DEPOSIT WHERE YMD BETWEEN ' ' AND ' ' UNION ALL SELECT ' ' YMD, SUM(DEPOSIT_AMOUNT) AS S_DEPOSIT FROM EC_APPLY WHERE APPLY_DATE BETWEEN TO_DATE(' ','YYYYMMDD') AND TO_DATE(' ','YYYYMMDD') GROUP BY ' ', COURSE_CODE; 집계테이블과 온라인테이블 을 함께 활용 4월 데이터 처리는 1일부터 28일 까지 조건을 부여함 위의 SQL은 앞서 제시된 SQL과는 달리 2002년 1월부터 3월까지는 집계테이블을 이용토록 함으로써 나름대로 수행속도 면에서 향상을 꾀한 것이라 할 수 있습니다. 그러나 2002년 4월 24일까지는 집계된 데이터가 있음에도 불구하고 시스템 장애로 인한 집계처리의 미수행 부분 때문에 4월에 대한 집계는 전혀 이용하지 않고 있습니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE UNION-ALL TABLE ACCESS (BY INDEX ROWID) OF 'EC_SUMMARY_DEPOSIT' INDEX (RANGE SCAN) OF 'EC_SUMMARY_DEPOSIT_PK' (UNIQUE) SORT (GROUP BY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' INDEX (RANGE SCAN) OF 'EC_APPLY_APPLY_DATE_IDX' (NON-UNIQUE) 문제점 2002년 3월까지의 집계는 집계테이블에서 처리토록 하되, 2002년 4월의 집계는 집계테이블을 이용할 수 없어서 4월 1일부터 4월 28일 현재까지는 모두 온라인 테이블에서 처리토록 함으로써 수행속도 저하 NOTE 문제점에 대한 해결방안 4월의 데이터를 처리함에 있어서 모두 온라인 테이블을 이용하기 보다는 1월부터 4월 24일까지 최대한 집계테이블을 활용할 수 있도록 해야 합니다.

現 해결 방안의 개요 ●●● 집계 안됨 4월28일 (현재) 4월25일 (장애발생) 1 월 SUM 2 월 SUM 3 월 SUM 현재 월 SUM 집계테이블 사용 온라인테이블 사용 NOTE 1월부터 4월 24일까지 최대한 집계테이블을 활용 4월 25일부터 4월 28일 현재까지는 온라인 상의 테이블을 활용

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(2 단계) SELECT DECODE(LEAST(YMD,' '),YMD,YMD,' ') YMD, COURSE_CODE, SUM(S_DEPOSIT) FROM (SELECT YMD, COURSE_CODE, DEPOSIT_AMOUNT AS S_DEPOSIT FROM EC_SUMMARY_DEPOSIT WHERE YMD BETWEEN ' ' AND ' ' UNION ALL SELECT ' ' YMD, COURSE_CODE, SUM(DEPOSIT_AMOUNT) AS S_DEPOSIT FROM EC_APPLY WHERE APPLY_DATE BETWEEN TO_DATE(' ','YYYYMMDD') AND TO_DATE(' ','YYYYMMDD') GROUP BY ' ', COURSE_CODE) GROUP BY DECODE(LEAST(YMD,' '),YMD,YMD,' '), COURSE_CODE; 시스템 장애 발생 전까지의 범위로 수정 시스템 장애 발생 일부터 현재시점 으로 수정 위의 SQL 은 앞서 제시된 SQL 과는 달리 2002년 1월부터 4월 24일(장애 발생 전) 까지 최대한 집계테이블을 이용토록 하고, 나머지 25일(장애 발생시점) 부터 28일(현재시점) 까지의 처리 만을 온라인 상의 테이블을 사용함으로써 수행속도 면에서 보다 나은 결과를 얻을 수가 있는 형태라 할 수 있습니다. 2002년 1월 1일부터 4월 24일까지 집계테이블을 최대한 사용할 수 있도록 한 결과 인라인 뷰의 데이터 중에는 시스템 장애 전까지의 4월 데이터가 있음을 알 수 있습니다. 그런데 4월 데이터의 경우 현재일로 집계일자를 처리해야 하므로 DECODE(…) 함수 처리를 했습니다. DECODE(LEAST(YMD,' '),YMD,YMD,' ') 의 의미는 다음과 같습니다. IF YMD <= ' ' THEN 일자 값을 그대로 활용 ELSE 일자 값을 ' ' 로 활용 END IF

최적의 SQL – 집계테이블 활용을 통한 실시간 처리로 구현 SELECT DECODE(LEAST(YMD,TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1),'YYYYMMDD'),'YYYYMMDD')), YMD,YMD,TO_CHAR(SYSDATE,'YYYYMMDD')) AS YMD, COURSE_CODE, SUM(S_DEPOSIT) FROM (SELECT YMD, COURSE_CODE, DEPOSIT_AMOUNT AS S_DEPOSIT FROM EC_SUMMARY_DEPOSIT WHERE YMD BETWEEN ' ' AND TO_CHAR(SYSDATE,'YYYYMMDD') UNION ALL SELECT TO_CHAR(SYSDATE,'YYYYMMDD') YMD,COURSE_CODE,SUM(DEPOSIT_AMOUNT) AS S_DEPOSIT FROM EC_APPLY WHERE APPLY_DATE > (SELECT /*+ INDEX_DESC(B EC_SUMMARY_DEPOSIT_PK) */ TO_DATE(YMD,'YYYYMMDD') FROM EC_SUMMARY_DEPOSIT B WHERE ROWNUM = 1) AND APPLY_DATE <= SYSDATE GROUP BY ' ', COURSE_CODE) GROUP BY DECODE(LEAST(YMD,TO_CHAR(LAST_DAY(ADD_MONTHS(SYSDATE,-1),'YYYYMMDD'),'YYYYMMDD')), YMD,YMD,TO_CHAR(SYSDATE,'YYYYMMDD')), COURSE_CODE; 최대한 집계 테이블을 활용하고자 함 실시간 처리까지 반영하기 위해서 위의 SQL 은 2단계에서 제시된 SQL 에 조금 더 확장한 형태입니다. 대부분이 집계를 위한 Batch 작업들은 정해 놓은 일정계획에 의해 실행되는데 주로 야간(영업시간 이후)에 한 번 수행되곤 합니다. 하지만 경우에 따라선 주간(영업시간 중)에도 역시나 분 단위 또는 시간 단위, 기타 정해진 시간대에 수시로 수행할 수도 있습니다. 만약에 이와 같이 어느 때든 정해진 계획에 의해 야간에 한 번이 아닌 수시로 수행이 되는 상황에서 本 예제에서 처럼 집계테이블을 활용하면서 실시간 데이터에 대한 처리까지 하고자 한다면 적어도 최종적으로 집계된 일자에 대한 정보를 이용할 수 있어야 할 것입니다. 따라서 최종적으로 집계된 일자에 대한 정보를 찾고자 집계테이블에서 찾아 오도록 해야 할 것입니다. 또한 실시간 데이터에 대한 처리까지 감안하여 SYSDATE에 대해 '<=' 조건을 부여해야 할 것입니다. 온라인 테이블의 데이터 처리 시점을 찾기 위해 집계테이블 을 활용

11. 일/월별 SUMMARY 테이블을 활용한 ONLINE 처리
Perfect! 대용량 데이터베이스 튜닝 Ⅱ B A T C H B A T C H EC_APPLY (수강신청정보) COURSE_CODE YEAR COURSE_SQ_NO MEMBER_TYPE MEMBER_ID APPLY_DATE PAYMENT_METHOD DEPOSIT_AMOUNT DEPOSITOR DEPOSIT_DATE BANK COMPANY_NO RECOMMENDER EC_DAYSUM_AMT (일별 과정별 금액 집계) YMD /* 입금일자 */ COURSE_CODE/* 과정코드 */ S_DEPOSIT /* 금액 */ EC_MONTHSUM_AMT (월별 과정별 금액 집계) YM /* 입금년월 */ COURSE_CODE/* 과정코드 */ S_DEPOSIT /* 금액 */ 소개 일별 집계테이블은 일일 마감처리에 의해서 생성되며, 월별 집계테이블은 월 마감처리에 의해서 생성 관리되고 있습니다. 인덱스정보 EC_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO + MEMBER_TYPE + MEMBER_ID EC_APPLY_APPLY_DATE_IDX : APPLY_DATE (Non Unique) EC_DAYSUM_AMT_PK : YMD + COURSE_CODE EC_MONTHSUM_AMT_PK : YM + COURSE_CODE [ 개요 ] 本 사례에서는 일별 집계테이블과 월별 집계테이블을 함께 효율적으로 활용할 수 있도록 함으로써 사용자에게 최대한의 융통성은 물론 실시간으로 처리된 데이터에 대한 처리를 효율적으로 할 수 있는 방안을 찾아 보고자 합니다.

2002년 2월 13일부터 8월 18일까지의 기간 동안에 입금된 금액에 대한 합계를 월별 과정별로 조회하려고 합니다. 기간에 대한 조건은 화면 상에서 변수에 의해서 입력됨. (FR_YMD - 시작일 TO_YMD – 종료일) 단, FR_YMD와 TO_YMD의 값은 특정 월의 1일과 말일에 해당하는 값이 아닌 월의 중간 일에 해당하는 값이 입력된다고 가정. 원하는 Output 형식의 예 SELECT TO_CHAR(APPLY_DATE,'YYYYMM') AS YM, COURSE_CODE, SUM(DEPOSIT_AMOUNT) AS S_DEPOSIT FROM EC_APPLY WHERE APPLY_DATE BETWEEN TO_DATE(:FR_YMD,'YYYYMMDD') AND TO_DATE(:TO_YMD,'YYYYMMDD') GROUP BY TO_CHAR(APPLY_DATE,'YYYYMM'), COURSE_CODE; 집계일자 과정 집계금액 중간 생략 ●●● 문제점 2002년 2월 13일부터 8월 18일까지 모두 수강신청정보 테이블 (온라인테이블)에서 처리 함으로써 수행속도 저하 발생 → 실시간 처리 곤란 NOTE 중간 생략 ●●●

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(1 단계) – 일별 집계테이블 이용 일별 집계테이블 사용 SELECT SUBSTR(YMD,1,6) AS YM, COURSE_CODE, SUM(S_DEPOSIT) AS S_DEPOSIT FROM EC_DAYSUM_AMT WHERE YMD BETWEEN :FR_YMD AND :TO_YMD GROUP BY SUBSTR(YMD,1,6), COURSE_CODE; 문제점 기간에 대한 조건으로 보면, (2월 13일 ~ 2월 28일), (8월 1일 ~ 8월 18일)에 해당 하는 처리는 일별 집계테이블을 이용해야 하며, 반면에 3월 ~ 7월 까지는 월별 집계 테이블을 활용할 수 있었음에도 불구하고 모두 일별 집계테이블을 사용하도록 했음 문제점에 대한 해결방안 (2월 13일 ~ 2월 28일), (8월 1일 ~ 8월 18일)에 해당하는 처리는 일별 집계테이블을 이용토록 하고, 3월 ~ 7월 까지는 월별 집계테이블을 활용 할 수 있도록 합니다. 위의 SQL 은 앞서 제시된 SQL 과는 달리 일별 집계테이블을 사용함으로써 만족할 만한 수행속도를 얻을 수 있도록 한 것이다. 그러나 실제로 잘 살펴보면, 일별 집계테이블을 사용해야 하는 부분은 2002년 2월 13일부터 2월 말일까지와 8월 1일부터 8월 18일까지이며, 나머지 3월, 4월, 5월, 6월, 7월 까지는 월별 집계테이블을 사용할 수 있다는 것을 알 수 있습니다.

現 해결 방안의 개요 일별집계테이블 사용 월별집계테이블 사용 일별집계테이블 사용 ADD_MONTHS(TO_DATE(:FR_YMD,'YYYYMMDD'),1) ADD_MONTHS(TO_DATE(:TO_YMD,'YYYYMMDD'),-1) 2월13일 ~ 2월28일 3월 집계 4월 집계 5월 집계 6월 집계 7월 집계 8월1일 ~ 8월18일 :FR_YMD :TO_YMD LAST_DAY(TO_DATE(:FR_YMD,'YYYYMMDD')) SUBSTR(:TO_YMD,1,6)||'01' NOTE 2월의 경우는 :FR_YMD부터 말일까지, 8월의 경우는 월초부터 :TO_YMD 까지를 범위로 일별 집계테이블을 사용 3월,4월,5월,6월,7월은 월별 집계테이블을 사용

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(2 단계) – 일별 집계테이블과 월별 집계테이블을 동시에 이용 FR_YMD 부터 FR_YMD 의 월말 또는 TO_YMD 의 월초부터 TO_YMD까지 처리 SELECT SUBSTR(YMD,1,6) AS YM, COURSE_CODE, SUM(S_DEPOSIT) AS S_DEPOSIT FROM EC_DAYSUM_AMT WHERE YMD BETWEEN :FR_YMD AND TO_CHAR(LAST_DAY(TO_DATE(:FR_YMD,'YYYYMMDD')),'YYYYMMDD') OR YMD BETWEEN SUBSTR(:TO_YMD,1,6)||'01' AND :TO_YMD GROUP BY SUBSTR(YMD,1,6), COURSE_CODE UNION ALL SELECT YM, COURSE_CODE, S_DEPOSIT FROM EC_MONTHSUM_AMT WHERE YM BETWEEN TO_CHAR(ADD_MONTHS(TO_DATE(:FR_YMD,'YYYYMMDD'),1),'YYYYMM') AND TO_CHAR(ADD_MONTHS(TO_DATE(:TO_YMD,'YYYYMMDD'),-1),'YYYYMM') ORDER BY 1, 2; FR_YMD 의 익월 부터 TO_YMD의 전월까지의 처리 위의 SQL 은 앞서 제시된 SQL 에서의 문제점을 해결하고자 작성된 것 입니다. 즉, 2002년 2월 13일부터 2월 28일 또는 8월 1일부터 8월 18일까지는 일별 집계테이블로부터 처리되게 했으며, 나머지 3월부터 7월까지는 월별 집계테이블로부터 처리되게 했습니다. 따라서 보다 나은 수행속도를 낼 수 있는 형태라 할 수 있습니다. LAST_DAY(날짜) – 날짜(date 형)의 해당 월의 마지막 날짜를 출력 ADD_MONTHS(날짜,N) – 날짜(date 형)에 N 개월을 더한 날짜를 출력

12. 최근 일정기간의 잔액현황에 대한 온라인 조회 인덱스정보
EC_ACCOUNT_REMAINDER (계좌별 일별 거래) ACCOUNT_NO /*계좌번호*/ YMD /*마감일 */ REMAINDER /*잔액 */ 계좌번호 거래마감일 잔액 SAMPLE 인덱스정보 EC_ACCOUNT_REMAINDER_PK : ACCOUNT_NO + YMD 특정 계좌(' ')의 기준일(1996년 4월 16일)을 근거로 3개월 前일부터 기준일 현재까지의 잔액에 대한 현황을 구하고자 합니다. [ 개요 ] 本 사례에서는 어떤 범위의 잔액 현황이라도 온라인 조회가 가능하도록 할 수 있는 방안을 찾아 보고자 합니다. 여기서의 온라인 이란 실시간 처리를 의미하며, 특히 SQL에 의한 화면상에서의 직접 조회 함을 의미합니다.

NOTE 12. 최근 일정기간의 잔액현황에 대한 온라인 조회 원하는 Output 형식의 예 ① ② ③
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 원하는 Output 형식의 예 계좌번호 거래마감일 잔액 예치기간 예치일수 ~ ~ ~ ~ ~ ① ② ③ ①의 경우, 기준일(1996년 4월 16일)에 대한 3개월 前일은 1996년 1월 16일 인데 거래마감일이 1996년 1월 12일이기 때문에 예치기간의 시작일을 1996년 1월 16일로 표시 ②의 경우, 이들 예치기간의 종료일은 각각의 (다음거래마감일 – 1)로 표시 ③의 경우, 조건에 해당하는 기간 내의 마지막 거래에 해당하므로 다음거래 마감일을 구할 수 없기에 기준일로 표시 NOTE

12. 최근 일정기간의 잔액현황에 대한 온라인 조회 ① ② ③ 예치일수 계산 기준일 3개월 前일 기준 가장 최근의 것을 의미
CURSOR C1 IS SELECT ACCOUNT_NO, YMD, REMAINDER FROM EC_ACCOUNT_REMAINDER WHERE ACCOUNT_NO = ' ' AND YMD >= (SELECT MAX(YMD) FROM EC_ACCOUNT_REMAINDER AND YMD <= TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')) AND YMD <= ' ' ORDER BY YMD; /* 변수선언부 중간생략 */ BEGIN SELECT TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD') INTO H_YMD FROM DUAL; OPEN C1; LOOP FETCH C1 INTO H_ACCT_NO, H_FR_YMD, H_REMAINDER; EXIT WHEN C1%NOTFOUND; IF H_FR_YMD < H_YMD THEN H_FR_YMD := H_YMD; END IF; SELECT TO_CHAR(TO_DATE(MIN(YMD),'YYYYMMDD') - 1,'YYYYMMDD') INTO H_TO_YMD FROM EC_ACCOUNT_REMAINDER WHERE ACCOUNT_NO = ' ' AND YMD > H_FR_YMD AND YMD <= ' '; IF H_TO_YMD IS NULL THEN H_TO_YMD := ' '; END IF; H_TERM := TO_DATE(H_TO_YMD,'YYYYMMDD') - TO_DATE(H_FR_YMD,'YYYYMMDD') + 1; ... 중간 생략 END LOOP; CLOSE C1; END; / ① ② ③ 위에 제시된 내용은 LOGIC으로 구현해서 LOOP 내에서 모든 처리가 이루어질 수 있도록 한 것입니다. 평잔 공식 a) 사이 일자 수 : 91 b) 3 개월간 평균 잔액 : (22ⅹ ⅹ ⅹ ⅹ ⅹ ) ÷ 91 = 818,131 원 예치일수 계산

NOTE 12. 최근 일정기간의 잔액현황에 대한 온라인 조회
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점 LOGIC 에 의한 LOOP 내에서의 처리로 인해 속도저하 발생 문제점 해결 방안 OUTER 조인을 활용한 데이터 처리 구현을 통해 하나의 SQL 로 처리토록 함 특히, 예치기간의 종료일을 처리하기 위해서 ROWNUM 을 활용(인라인 뷰 이용) 現 해결 방안의 개요 ① ③ NOTE ② ROWNUM

NOTE 12. 최근 일정기간의 잔액현황에 대한 온라인 조회 개선된 SQL(1 단계 – ROWNUM을 이용한 OUTER 조인)
SELECT ACCOUNT_NO, YMD, REMAINDER, FR_YMD, TO_YMD, (TO_DATE(TO_YMD,'YYYYMMDD') - TO_DATE(FR_YMD,'YYYYMMDD') + 1) TERM FROM (SELECT V1.ACCOUNT_NO, V1.YMD, V1.REMAINDER, V1.FR_YMD, DECODE(V2.TO_YMD, NULL,' ', V2.TO_YMD) TO_YMD FROM (SELECT ROWNUM RN, ACCOUNT_NO, YMD, REMAINDER, DECODE(GREATEST(YMD,TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3) ,'YYYYMMDD')), YMD, YMD, TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')) FR_YMD FROM EC_ACCOUNT_REMAINDER WHERE ACCOUNT_NO = ' ' AND YMD >= (SELECT MAX(YMD) AND YMD <= TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')) AND YMD <= ' ') V1, (SELECT ROWNUM RN, ACCOUNT_NO, TO_CHAR(TO_DATE(YMD,'YYYYMMDD') - 1,'YYYYMMDD') TO_YMD AND YMD <= ' ') V2 WHERE V1.RN + 1 = V2.RN(+)) ORDER BY ACCOUNT_NO, YMD; ② ① NOTE ③

12. 최근 일정기간의 잔액현황에 대한 온라인 조회 개선된 SQL(2 단계 – ROWNUM을 이용한 조인) ② ① ③
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(2 단계 – ROWNUM을 이용한 조인) SELECT ACCOUNT_NO, YMD, REMAINDER, FR_YMD, TO_YMD,(TO_DATE(TO_YMD,'YYYYMMDD') - TO_DATE(FR_YMD,'YYYYMMDD') + 1) TERM FROM (SELECT V1.ACCOUNT_NO, V1.YMD, V1.REMAINDER, V1.FR_YMD, V2.TO_YMD FROM (SELECT ROWNUM RN, ACCOUNT_NO, YMD, REMAINDER, DECODE(GREATEST(YMD,TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')),YMD, YMD, TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')) FR_YMD FROM EC_ACCOUNT_REMAINDER WHERE ACCOUNT_NO = ' ' AND YMD >= (SELECT MAX(YMD) FROM EC_ACCOUNT_REMAINDER AND YMD <= TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')) AND YMD <= ' ') V1, (SELECT ROWNUM RN, ACCOUNT_NO, TO_YMD FROM (SELECT ACCOUNT_NO, TO_CHAR(TO_DATE(YMD,'YYYYMMDD') - 1,'YYYYMMDD') TO_YMD AND YMD <= ' ' UNION ALL SELECT ' ' ACCOUNT_NO, ' ' TO_YMD FROM EC_COURSE WHERE ROWNUM = 1)) V2 WHERE V1.RN + 1 = V2.RN) ORDER BY ACCOUNT_NO, YMD; ② ① 이와 같이 특정 계좌 하나에 대해서 처리를 하는 경우는 위와 같이 구현할 수도 있습니다. 위의 SQL 이 앞의 SQL과의 차이점은 다음과 같습니다. ②에 대한 부분이 수정되었으며 그리고 ③에 대한 부분이 수정되었습니다. 이것이 가능하게 된 이유는 두 번째 인라인 뷰 내부에 UNION ALL 을 사용해서 임의로 처리하는 부분이 추가되었기 때문입니다. ③ KEY POINT

NOTE 12. 최근 일정기간의 잔액현황에 대한 온라인 조회 개선 1,2단계에서의 전반적인 문제점
기준일 3개월 前일 기준 가장 최근의 것을 구하는 부분의 최적화 필요 전반적으로 SELF 조인에 의한 처리 기준일 3개월 前일 기준 가장 최근의 것을 구하는 부분 최적화 SELECT /*+ INDEX_DESC(A EC_ACCOUNT_REMAINDER_PK) */ YMD FROM EC_ACCOUNT_REMAINDER A WHERE ACCOUNT_NO = ' ' AND YMD <= TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD') AND ROWNUM = 1; NOTE

NOTE 12. 최근 일정기간의 잔액현황에 대한 온라인 조회 최적의 SQL(분석용 함수 LEAD() OVER()에 의한 처리)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 최적의 SQL(분석용 함수 LEAD() OVER()에 의한 처리) SELECT ACCOUNT_NO, YMD, REMAINDER, FR_YMD, TO_YMD, (TO_DATE(TO_YMD,'YYYYMMDD') - TO_DATE(FR_YMD,'YYYYMMDD') + 1) TERM FROM (SELECT ACCOUNT_NO, YMD, REMAINDER, DECODE(GREATEST(YMD, TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')),YMD,YMD, TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3),'YYYYMMDD')) FR_YMD, LEAD(TO_CHAR(TO_DATE(YMD,'YYYYMMDD')-1,'YYYYMMDD'),1,' ') OVER(ORDER BY YMD ASC) TO_YMD FROM EC_ACCOUNT_REMAINDER WHERE ACCOUNT_NO = ' ' AND YMD >= (SELECT /*+ INDEX_DESC(A EC_ACCOUNT_REMAINDER_PK) */ YMD FROM EC_ACCOUNT_REMAINDER A AND YMD <= TO_CHAR(ADD_MONTHS(TO_DATE(' ','YYYYMMDD'),-3), 'YYYYMMDD') AND ROWNUM = 1) AND YMD <= ' '); NOTE

13. '명'이 아닌 '순위'에 의한 Top-N 처리 인덱스정보 EMP_PK : EMPNO 원하는 결과
SELECT SAL, ENAME, JOB, EMPNO FROM EMP ORDER BY SAL DESC, ENAME ASC; EMP (사원정보) EMPNO ENAME JOB SAL SAL ENAME JOB EMPNO 5000 KING PRESIDENT 3000 FORD ANALYST 3000 SCOTT ANALYST 2975 JONES MANAGER 2850 BLAKE MANAGER 2450 CLARK MANAGER 1600 ALLEN SALESMAN 1500 TURNER SALESMAN 1300 MILLER CLERK 1250 MARTIN SALESMAN 1250 WARD SALESMAN 1100 ADAMS CLERK 950 JAMES CLERK 800 SMITH CLERK 급여액을 기준으로 상위 10 등까지에 해당하는 사람들을 아래와 같이 조회 하고자 합니다. 원하는 결과 RANK SAL ENAME JOB EMPNO KING PRESIDENT 7839 FORD ANALYST SCOTT ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN TURNER SALESMAN MILLER CLERK MARTIN SALESMAN WARD SALESMAN [ 개요 ] 예를 들어 급여가 많은 순서로 10위 까지, 점수가 높은 학생 순으로 10등 까지와 같이 순위별 처리를 요구하는 경우가 많이 발생합니다. 개념이 다소 다르긴 하지만 어떤 '순위'가 아닌 '명'으로 처리를 요구할 경우도 있습니다. 일반적으로 우리는 이러한 처리를 하고자 할 때 'Top-N' 처리를 한다고 말합니다. 本 사례에서는 '순위' 에 의한 'Top-N' 처리를 하고자 할 때 '순위' 에 대한 값을 표현할 수 있는 방안을 찾아 보고자 합니다. ※ 급여액이 같을 경우 사원 명의 오름차순

NOTE 13. '명'이 아닌 '순위'에 의한 Top-N 처리 SQL
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL SELECT ROWNUM RANK, SAL, ENAME, JOB, EMPNO FROM EMP A WHERE 10 > (SELECT COUNT(*) FROM EMP B WHERE B.SAL > A.SAL) ORDER BY SAL DESC, ENAME ASC; 순위에 대한 표현이 제대로 안됨 RANK SAL ENAME JOB EMPNO KING PRESIDENT SCOTT ANALYST FORD ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN TURNER SALESMAN MILLER CLERK WARD SALESMAN MARTIN SALESMAN NOTE

( ) NOTE 13. '명'이 아닌 '순위'에 의한 Top-N 처리 해결 방안(ORDER BY 사용) 現 해결 방안의 개요
SELECT ROWNUM RANK, SAL, ENAME, JOB, EMPNO FROM (SELECT SAL, ENAME, JOB, EMPNO FROM EMP A WHERE 10 > (SELECT COUNT(*) FROM EMP B WHERE B.SAL > A.SAL) ORDER BY SAL DESC, ENAME ASC); 인라인 뷰에 의한 선행처리 現 해결 방안의 개요 5000 KING PRESIDENT 3000 FORD ANALYST 3000 SCOTT ANALYST 2975 JONES MANAGER 2850 BLAKE MANAGER 2450 CLARK MANAGER 1600 ALLEN SALESMAN 1500 TURNER SALESMAN 1300 MILLER CLERK 1250 MARTIN SALESMAN 1250 WARD SALESMAN ( ) SELECT ROWNUM, … FROM NOTE

( ) 13. '명'이 아닌 '순위'에 의한 Top-N 처리 해결 방안(GROUP BY 사용) 現 해결 방안의 개요
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 해결 방안(GROUP BY 사용) SELECT ROWNUM RANK, -SAL SAL, ENAME, JOB, EMPNO FROM (SELECT -SAL SAL, ENAME, JOB, EMPNO FROM EMP A WHERE 10 > (SELECT COUNT(*) FROM EMP B WHERE B.SAL > A.SAL) GROUP BY -SAL, ENAME, JOB, EMPNO); 인라인 뷰에 의한 선행처리 現 해결 방안의 개요 KING PRESIDENT FORD ANALYST SCOTT ANALYST JONES MANAGER BLAKE MANAGER CLARK MANAGER ALLEN SALESMAN TURNER SALESMAN MILLER CLERK MARTIN SALESMAN WARD SALESMAN SELECT ROWNUM, -SAL,… FROM ( ) 위의 해결방안에서의 핵심 포인트: GROUP BY –SAL 은 ORDER BY SAL DESC은 같다는 개념.

13. '명'이 아닌 '순위'에 의한 Top-N 처리 해결 방안(분석용 함수 사용) ① ②
SELECT ROWNUM RANK, SAL, ENAME, JOB, EMPNO FROM (SELECT RANK() OVER(ORDER BY SAL DESC, ENAME ASC) RK, SAL, ENAME, JOB, EMPNO FROM EMP) WHERE RK <= 10; ① SELECT ROWNUM RANK, SAL, ENAME, JOB, EMPNO FROM (SELECT RANK() OVER(ORDER BY SAL DESC) RK, SAL, ENAME, JOB, EMPNO FROM EMP) WHERE RK <= 10; ② 이 예제의 경우, ROW_NUMBER( ) OVER( ) 를 사용하면 순위에 대한 표현을 쉽게 할 수는 있겠지만 문제는 상위 10 등에 해당하는 사람을 구분하기가 어렵습니다. '순위'에 대한 표현이 요구사항과 다르기 때문에 이에 대한 처리는 ROWNUM을 사용해야 합니다. ①의 경우는 Sort 의 기준이 상반되는 것으로 인해 RANK( ) OVER( ) 처리에 의한 결과 값이 ROW_NUMBER( ) OVER( ) 와 같습니다. ②의 경우는 ENAME에 대한 Sort 결과가 요구사항과 다릅니다. '순위'에 대한 표현이 요구사항과 다르기 때문에 이에 대한 처리는 ROWNUM을 사용해야 합니다.

NOTE 13. '명'이 아닌 '순위'에 의한 Top-N 처리 해결 방안(최적안)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 해결 방안(최적안) SELECT ROWNUM RANK, SAL, ENAME, JOB, EMPNO FROM (SELECT RANK() OVER(ORDER BY SAL DESC) RK, SAL, ENAME, JOB, EMPNO FROM (SELECT SAL, ENAME, JOB, EMPNO FROM EMP ORDER BY SAL DESC, ENAME)) WHERE RK <= 10; NOTE

14. 기준에 의한 일련번호에 대한 재처리 인덱스정보 원하는 결과 EMP_PK : EMPNO
(사원정보) EMPNO ENAME JOB SAL DEPTNO 각 부서별로 사원번호의 오름차순으로 정렬한 결과를 조회 합니다. 이 때, 일련번호에 대한 부분을 아래와 같이 나타 낼 수 있어야 합니다. 원하는 결과 SEQ EMPNO ENAME DEPT JOB CLARK 10 MANAGER KING 10 PRESIDENT MILLER 10 CLERK SMITH 20 CLERK JONES 20 MANAGER SCOTT 20 ANALYST ADAMS 20 CLERK FORD 20 ANALYST ALLEN 30 SALESMAN WARD 30 SALESMAN MARTIN 30 SALESMAN BLAKE 30 MANAGER TURNER 30 SALESMAN JAMES 30 CLERK OUTPUT [ 개요 ] 本 사례에서는 Output 상에 일련번호를 표현함에 있어서 기준이 바뀔 때마다 일련번호를 다시 부여할 수 있는 방안을 찾아 보고자 합니다. 각각의 부서에 대해서 일련번호가 다시 부여

NOTE 14. 기준에 의한 일련번호에 대한 재처리 해결방안의 전반적인 개요 T R A N S F O M ※구하고자 하는 값:
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 해결방안의 전반적인 개요 SELECT ROWNUM SEQ2, EMPNO, ENAME, DEPTNO, JOB FROM (SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP ORDER BY DEPTNO, EMPNO); DEPT EMPNO SEQ SEQ2 EMPNO ENAME DEPT JOB CLARK 10 MANAGER KING 10 PRESIDENT MILLER 10 CLERK SMITH 20 CLERK JONES 20 MANAGER SCOTT 20 ANALYST ADAMS 20 CLERK FORD 20 ANALYST ALLEN 30 SALESMAN WARD 30 SALESMAN MARTIN 30 SALESMAN BLAKE 30 MANAGER TURNER 30 SALESMAN JAMES 30 CLERK T R A N S F O M 부서10 : 3건 부서20 : 5건 부서30 : 6건 前부서 까지의 누적건수 활용 NOTE ※구하고자 하는 값: 부서 10 : SEQ = SEQ2의 값 부서 20 : SEQ = SEQ2의 값 – 부서10의 건수 부서 30 : SEQ = SEQ2의 값 – (부서10의 건수 + 부서20의 건수)

( ) , ( ) NOTE 14. 기준에 의한 일련번호에 대한 재처리 SQL(부서별 누적건수 구함) 現 SQL의 개요
SELECT A.DEPTNO, SUM(B.CNT) CNT FROM (SELECT DEPTNO, COUNT(*) CNT FROM EMP GROUP BY DEPTNO) A, (SELECT DEPTNO, COUNT(*) CNT FROM EMP GROUP BY DEPTNO) B WHERE A.DEPTNO >= B.DEPTNO GROUP BY A.DEPTNO; 現 SQL의 개요 SELECT A.DEPTNO, SUM(B.CNT) FROM WHERE A.DEPTNO >= B.DEPTNO GROUP BY A.DEPTNO; R E S U L T ( ) , ( ) 10 3 20 5 30 6 10 3 20 5 30 6 10 3 20 8 30 14 NOTE

( ) , ( ) NOTE 14. 기준에 의한 일련번호에 대한 재처리 SQL(부서별 前 부서까지의 누적건수 구함)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(부서별 前 부서까지의 누적건수 구함) SELECT A.DEPTNO, NVL(SUM(B.CNT),0) CNT FROM (SELECT DEPTNO, COUNT(*) CNT FROM EMP GROUP BY DEPTNO) A, (SELECT DEPTNO, COUNT(*) CNT FROM EMP GROUP BY DEPTNO) B WHERE A.DEPTNO > B.DEPTNO(+) GROUP BY A.DEPTNO; 現 SQL의 개요 SELECT A.DEPTNO, NVL(SUM(B.CNT),0) FROM WHERE A.DEPTNO > B.DEPTNO(+) GROUP BY A.DEPTNO; R E S U L T ( ) , ( ) 10 3 20 5 30 6 10 3 20 5 30 6 10 0 20 3 30 8 NOTE

14. 기준에 의한 일련번호에 대한 재처리 SQL (1 단계)
SELECT (R1.SEQ2 - R2.CNT) SEQ, R1.EMPNO, R1.ENAME, R1.DEPTNO, R1.JOB FROM (SELECT ROWNUM SEQ2, EMPNO, ENAME, DEPTNO, JOB FROM (SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP ORDER BY DEPTNO, EMPNO)) R1, (SELECT A.DEPTNO, NVL(SUM(B.CNT),0) CNT FROM (SELECT DEPTNO, COUNT(*) CNT GROUP BY DEPTNO) A, (SELECT DEPTNO, COUNT(*) CNT GROUP BY DEPTNO) B WHERE A.DEPTNO > B.DEPTNO(+) GROUP BY A.DEPTNO) R2 WHERE R1.DEPTNO = R2.DEPTNO; Self-Join 위의 구현된 SQL 은 결국엔 SELF 조인을 3번 한 형태입니다. 따라서 수행속도 측면에선 만족스럽지 못합니다.

( ) ; 14. 기준에 의한 일련번호에 대한 재처리 SQL(부서별 前 부서까지의 누적건수 구하는 부분 최적화)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(부서별 前 부서까지의 누적건수 구하는 부분 최적화) SELECT DEPTNO, NVL(SUM(CNT) OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) ACCU_CNT FROM (SELECT DEPTNO, COUNT(*) CNT FROM EMP GROUP BY DEPTNO); 現 SQL의 개요 SELECT DEPTNO, NVL(SUM(CNT) OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) FROM 부서별 前 부서까지의 누적건수 구하는 부분의 최적화 SELECT DEPTNO, NVL(SUM(CNT) OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) ACCU_CNT FROM (SELECT DEPTNO, COUNT(*) CNT FROM EMP GROUP BY DEPTNO); 분석용 함수 SUM(CNT) OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) 의 의미는 DEPTNO 의 오름차순으로 정렬해 놓은 상태에서 가장 앞 행부터 하나 이전 행의 CNT 까지의 값을 합한다 는 의미입니다. R E S U L T ( ) 10 3 20 5 30 6 10 0 20 3 30 8 ;

14. 기준에 의한 일련번호에 대한 재처리 14. 기준에 의한 일련번호에 대한 재처리 SQL (2 단계) – 부서별 前 부서까지의 누적건수 구하는 부분의 최적화 반영 SELECT (R1.SEQ2 - R2.CNT) SEQ, R1.EMPNO, R1.ENAME, R1.DEPTNO, R1.JOB FROM (SELECT ROWNUM SEQ2, EMPNO, ENAME, DEPTNO, JOB FROM (SELECT EMPNO, ENAME, DEPTNO, JOB FROM EMP ORDER BY DEPTNO, EMPNO)) R1, (SELECT DEPTNO, NVL(SUM(CNT) OVER(ORDER BY DEPTNO ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING),0) CNT FROM (SELECT DEPTNO, COUNT(*) CNT GROUP BY DEPTNO)) R2 WHERE R1.DEPTNO = R2.DEPTNO; Self-Join NOTE 전반적으로 1 단계에서의 SQL과 비교해 볼 때, SQL이 간략하게 작성이 되었으며 또한 SELF 조인을 1 번은 생략할 수 있어서 수행속도가 개선됨

NOTE 14. 기준에 의한 일련번호에 대한 재처리 최적의 SQL(분석용 함수 ROW_NUMBER() OVER() 사용)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 최적의 SQL(분석용 함수 ROW_NUMBER() OVER() 사용) SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY DEPTNO, EMPNO) SEQ, EMPNO, ENAME, DEPTNO, JOB FROM EMP; 분석용 함수 ROW_NUMBER() OVER() 를 사용함으로 인해 매우 간략한 형태의 SQL 작성이 가능해졌고, SELF 조인 없이 바로 처리할 수 있도록 됨으로써 수행속도가 개선됨 NOTE

15. 누계 값 산정시 데이터 중복에 대한 누계 처리 인덱스정보 요구사항
EC_DAILY_SALES (일별 판매현황) YMD /* 판매일자 */ SALES_GBN /* 판매수량 구분 */ SALES_QTY /* 판매수량 */ 판매일자 수량구분 판매수량 인덱스정보 EC_DAILY_SALES_IDX01 : YMD (Non Unique) 요구사항 조회 기간은 2005년 2월 1일부터 2월 28일로 판매일자별, 판매량 별로 판매량의 누계를 구하고자 합니다. [ 개요 ] 本 사례에서는 일반적으로 어떤 기준에 의한 특정 값의 누계 값을 구하고자 할 때 데이터의 중복성을 고려하지 않았을 경우의 주의할 점을 짚어 보고 이에 대한 해결 방안 및 효율적인 처리방안을 찾아 보고자 합니다. 중간 생략 ●●● SAMPLE

15. 누계 값 산정시 데이터 중복에 대한 누계 처리 원하는 Output 형식의 예 구현된 SQL
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 원하는 Output 형식의 예 판매일자 판매수량 누적판매량 구현된 SQL SELECT A.YMD, A.SALES_QTY, SUM(B.SALES_QTY) TOT_QTY FROM EC_DAILY_SALES A, EC_DAILY_SALES B WHERE A.YMD BETWEEN ' ' AND ' ' AND A.YMD >= B.YMD GROUP BY A.YMD, A.SALES_QTY; 문제점 판매량의 누계를 처리함에 있어서 데이터 중복을 고려치 않음으로써 일부 일자에 대한 누계 처리가 잘못되고 있습니다. 위의 SQL 은 2002년 2월 8일, 2월 12일에 있어서 데이터의 중복을 고려치 않음으로써 원하는 2월 8일과 2월 12일에 대한 판매량 누계가 제대로 처리되지 않습니다. 중간 생략 ●●● SAMPLE

( ) , ( ) NOTE 15. 누계 값 산정시 데이터 중복에 대한 누계 처리 문제점에 대한 개요
SELECT A.YMD, A.SALES_QTY, SUM(B.SALES_QTY) FROM WHERE A.YMD BETWEEN ' ' AND ' ' AND A.YMD >= B.YMD GROUP BY A.YMD, A.QTY; ( ) , ( ) R E S U L T NOTE 판매량 누적 시 문제 발생

15. 누계 값 산정시 데이터 중복에 대한 누계 처리 SQL(1 단계) 위 SQL의 잠재적인 문제점
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(1 단계) SELECT A.YMD, A.SALES_QTY, SUM(B.SALES_QTY) TOT_QTY FROM (SELECT YMD, SALES_QTY FROM EC_DAILY_SALES WHERE YMD BETWEEN ' ' AND ' ' GROUP BY YMD, SALES_QTY) A, (SELECT YMD, SALES_QTY FROM EC_DAILY_SALES GROUP BY YMD, SALES_QTY) B WHERE A.YMD > B.YMD OR (A.YMD = B.YMD AND A.SALES_QTY >= B.SALES_QTY) GROUP BY A.YMD, A.SALES_QTY; Self-Join 판매일자까지만 구분 판매수량 까지 구분 위 SQL의 잠재적인 문제점 판매일자 수량구분 판매수량 ● ● ● ● 위의 SQL 은 판매수량까지 구분토록 조건으로 처리를 하였으나, 예를 든 상황처럼 특정 판매일자에 판매수량구분까지 같은 데이터가 존재한다면 판매량 누계가 제대로 처리되지 않습니다. 만약에 이런 경우라면, 판매수량에 대한 구별을 못하게 됨

NOTE 15. 누계 값 산정시 데이터 중복에 대한 누계 처리 SQL(2 단계)
SELECT A.YMD, A.SALES_QTY, SUM(B.T_SALES_QTY) TOT_QTY FROM (SELECT YMD, SALES_QTY FROM EC_DAILY_SALES WHERE YMD BETWEEN ' ' AND ' ' GROUP BY YMD, SALES_QTY) A, (SELECT YMD, SALES_QTY, SUM(SALES_QTY) T_SALES_QTY GROUP BY YMD, SALES_QTY) B WHERE A.YMD > B.YMD OR (A.YMD = B.YMD AND A.SALES_QTY >= B.SALES_QTY) GROUP BY A.YMD, A.SALES_QTY; Self-Join NOTE 전반적으로 1 단계 ~ 2 단계까지의 SQL 에서 보면, SELF 조인을 행함으로써 반복된 처리를 하고 있다는 문제점이 있습니다.

NOTE 15. 누계 값 산정시 데이터 중복에 대한 누계 처리 최적의 SQL(분석용 함수 SUM() OVER() 사용)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 최적의 SQL(분석용 함수 SUM() OVER() 사용) SELECT YMD, SALES_QTY, SUM(T_SALES_QTY) OVER(ORDER BY YMD ASC, SALES_QTY ASC RANGE UNBOUNDED PRECEDING) TOT_QTY FROM (SELECT YMD, SALES_QTY, SUM(SALES_QTY) T_SALES_QTY FROM EC_DAILY_SALES WHERE YMD BETWEEN ' ' AND ' ' GROUP BY YMD, SALES_QTY); 분석용 함수 SUM() OVER() 를 사용함으로 인해 매우 간략한 형태의 SQL 작성이 가능해졌고, SELF 조인 없이 바로 처리할 수 있도록 됨으로써 수행속도가 개선됨 NOTE

16. 하나의 SQL 로 달력(CALENDAR) 처리
CREATE TABLE EC_DAY(DAY CHAR(2)); INSERT INTO EC_DAY VALUES ('01'); INSERT INTO EC_DAY VALUES ('02'); INSERT INTO EC_DAY VALUES ('03'); … 중간 생략 INSERT INTO EC_DAY VALUES ('30'); INSERT INTO EC_DAY VALUES ('31'); EC_DAY (일자관리 테이블) DAY /* 일자(01~31) */ 가정 2005년 3월 1일을 화요일이라고 가정할 때, 첫 번째 화요일부터 토요일 까지를 1주(週)로 하고 나머지 주에 대한 처리는 일요일부터 토요일까지 를 또 다른 한 주로 처리하고자 합니다. Output (예 : 2005년 3월) SUN MON TUE WED THU FRI SAT 1주 2주 3주 4주 5주 [ 개요 ] 本 사례에서는 SQL 을 사용해서 달력을 만들기 위한 방안을 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 전반적인 처리 과정에 대한 개요 해당 월에 대한 일자를 생성합니다. 일자 별 요일 구분을 찾아야 합니다. TO_CHAR(TO_DATE(일자,'YYYYMMDD'),'D') 일요일:1, 월요일:2, 화요일:3, 수요일:4, 목요일:5, 금요일:6, 토요일:7 일자 별로 주에 대한 구분을 결정합니다. 이 때, 주요 핵심 포인트는 일자 별 요일구분 값과 EC_DAY 의 일자 값을 이용해서 주에 대한 구분에 필요한 값을 찾아내는 것입니다. 일자 별 주에 대한 구분 값 처리 구하고자 년/월에 대해서 어떤 요일이든 해당월의 1일이 될 수 있습니다. 여기서 일자에 대한 요일구분 값을 N, 일자 값을 R 이라고 했을 때, 1일을 (일,월,화,수,목,금,토) 각각에 대해 가정하고 각 주 별로 해당하는 요일에 대한 (N – R + 1)을 계산합니다. NOTE

1일이 일요일이라면(1일 ~ 7일까지가 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 1 = 1 = 1 = 1 = 1 = 1 = 1 = -6 = -6 = -6 = -6 = -6 = -6 = -6 = -13 = -13 = -13 = -13 = -13 = -13 = -13 넷째 주 다섯째 주 = -20 = -20 = -20 = -20 = -20 = -20 = -20 = -27 = -27 = -27 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 1일이 월요일이라면(1일 ~ 6일까지가 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 2 = 2 = 2 = 2 = 2 = 2 = -5 = -5 = -5 = -5 = -5 = -5 = -5 = -12 = -12 = -12 = -12 = -12 = -12 = -12 넷째 주 다섯째 주 = -19 = -19 = -19 = -19 = -19 = -19 = -19 = -26 = -26 = -26 = -26 NOTE

1일이 화요일이라면(1일 ~ 5일까지가 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 3 = 3 = 3 = 3 = 3 = -4 = -4 = -4 = -4 = -4 = -4 = -4 = -11 = -11 = -11 = -11 = -11 = -11 = -11 넷째 주 다섯째 주 = -18 = -18 = -18 = -18 = -18 = -18 = -18 = -25 = -25 = -25 = -25 = -25 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 1일이 수요일이라면(1일 ~ 4일까지가 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 4 = 4 = 4 = 4 = -3 = -3 = -3 = -3 = -3 = -3 = -3 = -10 = -10 = -10 = -10 = -10 = -10 = -10 넷째 주 다섯째 주 = -17 = -17 = -17 = -17 = -17 = -17 = -17 = -24 = -24 = -24 = -24 = -24 = -24 NOTE

1일이 목요일이라면(1일 ~ 3일까지가 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 5 = 5 = 5 = -2 = -2 = -2 = -2 = -2 = -2 = -2 = -9 = -9 = -9 = -9 = -9 = -9 = -9 넷째 주 다섯째 주 = -16 = -16 = -16 = -16 = -16 = -16 = -16 = -23 = -23 = -23 = -23 = -23 = -23 = -23 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 1일이 금요일이라면(1일 ~ 2일까지가 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 6 = 6 = -1 = -1 = -1 = -1 = -1 = -1 = -1 = -8 = -8 = -8 = -8 = -8 = -8 = -8 넷째 주 다섯째 주 여섯째 주 = -15 = -15 = -15 = -15 = -15 = -15 = -15 = -22 = -22 = -22 = -22 = -22 = -22 = -22 = -29 NOTE

1일이 토요일이라면(1일만 첫째 주로 결정됨) 첫째 주 둘째 주 셋째 주 = 7 = 0 = 0 = 0 = 0 = 0 = 0 = 0 = -7 = -7 = -7 = -7 = -7 = -7 = -7 넷째 주 다섯째 주 여섯째 주 = -14 = -14 = -14 = -14 = -14 = -14 = -14 = -21 = -21 = -21 = -21 = -21 = -21 = -21 = -28 = -28 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 주에 대한 구분 처리시 규칙의 발견 (N – R + 1) 1주 1, 2, 3, 4, 5, 6, 7 N1 2주 -6, -5, -4, -3, -2, -1, 0 N1 - 7 3주 -13, -12, -11, 10, -9, -8, -7 N1 - 14 4주 -20, -19, -18, -17, -16, -15, -14 N1 - 21 5주 -27,-26,-25,-24,-23,-22,-21 N1 - 28 6주 -29, -28 N1 - 35 요일구분 : N 일자 : R 매월1일의 요일구분 : N1 NOTE 어떤 경우이든, 매월 첫째 주 (N - R + 1)의 값 = 매월 1일의 요일구분 값(N1)

SELECT YMD, N, DECODE(LEAST(CHK,1), 1, '1주', DECODE(LEAST(CHK,-6), -6, '2주', DECODE(LEAST(CHK,-13), -13, '3주', DECODE(LEAST(CHK,-20), -20, '4주', DECODE(LEAST(CHK,-27), -27, '5주', '6주'))))) WEEK_GBN FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))); LEAST 함수는 작은 값을 식별하기 위한 함수입니다. GREATEST 와 마찬가지로 비교되는 값들의 데이터 타입에 전혀 영향을 받지 않으며, 특히 SIGN, CEIL, FLOOR 처럼 별도의 연산을 할 필요가 없습니다. CHK >= 1 인 경우 : LEAST(CHK,1) → 1 6 <= CHK < 1 인 경우 : LEAST(CHK,-6) → -6 13 <= CHK < -6 인 경우 : LEAST(CHK,-13) → -13 20 <= CHK < -13 인 경우 : LEAST(CHK,-20) → -20 27 <= CHK < -20 인 경우 : LEAST(CHK,-27) → -27 29 <= CHK < -27 인 경우 : LEAST(CHK,-29) → -29

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(2 단계) SELECT YMD, N, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주') WEEK_GBN FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))); 앞서 1단계의 SQL 에서의 중첩된 DECODE(…) 함수의 처리를 간략하게 처리토록 작성된 형태 입니다.

데이터 처리과정에 대한 개요 일자 요일 주 주 SUN MON TUE WED THU FRI SAT 1주 1주 1주 1주 1주 2주 2주 4주 4주 5주 5주 5주 5주 5주 주 주 주 주 주 주 주 주 주 주 주 주 주 주 D E C O . . 주 단위로 GROUP BY 처리 하면 된다. NOTE SELECT 주, DECODE(요일,1,일자) SUN, DECODE(요일,2,일자) MON, DECODE(요일,3,일자) TUE, DECODE(요일,4,일자) WED, DECODE(요일,5,일자) THU, DECODE(요일,6,일자) FRI, DECODE(요일,7,일자) SAT …

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(최종) SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주') WEEK_GBN, TO_CHAR(SUBSTR(MAX(DECODE(N,1,YMD)),7,2),'99') SUN, TO_CHAR(SUBSTR(MAX(DECODE(N,2,YMD)),7,2),'99') MON, TO_CHAR(SUBSTR(MAX(DECODE(N,3,YMD)),7,2),'99') TUE, TO_CHAR(SUBSTR(MAX(DECODE(N,4,YMD)),7,2),'99') WED, TO_CHAR(SUBSTR(MAX(DECODE(N,5,YMD)),7,2),'99') THU, TO_CHAR(SUBSTR(MAX(DECODE(N,6,YMD)),7,2),'99') FRI, TO_CHAR(SUBSTR(MAX(DECODE(N,7,YMD)),7,2),'99') SAT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))) GROUP BY DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주'); NOTE

SELECT DECODE(WEEK_GBN,'1주',SUBSTR(MONTH,1,4)||'년 '||SUBSTR(MONTH,5,2)||'월') MONTH, WEEK_GBN, SUN, MON, TUE, WED, THU, FRI, SAT FROM (SELECT SUBSTR(YMD,1,6) MONTH, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN, TO_CHAR(SUBSTR(MAX(DECODE(N,1,YMD)),7,2),'99') SUN, TO_CHAR(SUBSTR(MAX(DECODE(N,2,YMD)),7,2),'99') MON, TO_CHAR(SUBSTR(MAX(DECODE(N,3,YMD)),7,2),'99') TUE, TO_CHAR(SUBSTR(MAX(DECODE(N,4,YMD)),7,2),'99') WED, TO_CHAR(SUBSTR(MAX(DECODE(N,5,YMD)),7,2),'99') THU, TO_CHAR(SUBSTR(MAX(DECODE(N,6,YMD)),7,2),'99') FRI, TO_CHAR(SUBSTR(MAX(DECODE(N,7,YMD)),7,2),'99') SAT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE(SUBSTR(YMD,1,6)||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '2005'||B.DAY||A.DAY YMD, TO_CHAR(LAST_DAY(TO_DATE('2005'||B.DAY,'YYYYMM')), 'YYYYMMDD') LAST_YMD, ROWNUM FROM EC_DAY A, EC_DAY B WHERE B.DAY <= 12) WHERE YMD <= LAST_YMD)) GROUP BY SUBSTR(YMD,1,6), DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주')); 위의 SQL 에서 ① 은 해당 년도(2005년)의 全月(1월부터 12월)까지의 일자를 생성해서, 일자 별로 요일구분 값 해당 월의 1일에 대한 요일 구분 값 일자 값 등을 구하는 처리를 하기 위한 부분입니다. ①

17. 달력 상에 월 매출액을 주(週)단위로 처리 인덱스정보 가정 Output (예 : 2005년 3월)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ EC_DAY (일자관리 테이블) DAY /* 일자(01~31) */ EC_SALES (판매 테이블) YMD /* 판매일자 */ ITEM /* 제품코드 */ SALES_AMT /* 판매금액 */ CREATE TABLE EC_DAY(DAY CHAR(2)); INSERT INTO EC_DAY VALUES ('01'); INSERT INTO EC_DAY VALUES ('02'); INSERT INTO EC_DAY VALUES ('03'); … 중간 생략 INSERT INTO EC_DAY VALUES ('30'); INSERT INTO EC_DAY VALUES ('31'); 인덱스정보 EC_SALES_PK : YMD + ITEM 가정 Output (예 : 2005년 3월) 2005년 3월 1일을 화요일이라고 가정할 때, 첫 번째 화요일부터 토요일까지를 1주(週)로 하고 나머지 주에 대한 처리는 일요일부터 토요일까지를 또 다른 한 주로 처리하고자 합니다. SUN MON TUE WED THU FRI SAT AMT 1주 2주 3주 4주 5주 [ 개요 ] 일상적으로 보면, 사용자가 원하는 각종 리포트에 있어서 일별/ 주(週)별 /월별/분기별/반기별/년별 등의 처리를 원하는 형태들이 많이 있습니다. 리포트 마다 다를 수는 있겠지만 이러한 리포트에서 처리하는 데이터 량은 상당히 많다는 것도 사실입니다. 그 뿐만 아니라 리포트 자체의 output 의 특성과 기타 등등의 이유로 말미암아 하나의 SQL로 처리하기에는 너무나 어려운 점이 많이 내재되어 있습니다. 本 사례에서는 특정 월에 대한 매출액을 한 주 단위로 구분 처리하되, 해당 월의 달력의 포맷에 맞춰서 처리하고자 할 때 이에 대한 해결 방안을 찾아 보고자 합니다.

. . 17. 달력 상에 월 매출액을 주(週)단위로 처리 달력 생성에 대한 개요 D E C O 일자 요일 주 주 단위로
일자 요일 주 주 SUN MON TUE WED THU FRI SAT 1주 1주 1주 1주 1주 2주 2주 4주 4주 5주 5주 5주 5주 5주 주 주 주 주 주 주 주 주 주 주 주 주 주 주 D E C O . . 주 단위로 GROUP BY 처리 하면 된다. 위의 처리 과정에 해당하는 SQL 은 다음과 같습니다. SELECT YMD, N, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))); SELECT 주, DECODE(요일,1,일자) SUN, DECODE(요일,2,일자) MON, DECODE(요일,3,일자) TUE, DECODE(요일,4,일자) WED, DECODE(요일,5,일자) THU, DECODE(요일,6,일자) FRI, DECODE(요일,7,일자) SAT …

NOTE 17. 달력 상에 월 매출액을 주(週)단위로 처리 SQL(달력 생성)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(달력 생성) SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주') WEEK_GBN, TO_CHAR(SUBSTR(MAX(DECODE(N,1,YMD)),7,2),'99') SUN, TO_CHAR(SUBSTR(MAX(DECODE(N,2,YMD)),7,2),'99') MON, TO_CHAR(SUBSTR(MAX(DECODE(N,3,YMD)),7,2),'99') TUE, TO_CHAR(SUBSTR(MAX(DECODE(N,4,YMD)),7,2),'99') WED, TO_CHAR(SUBSTR(MAX(DECODE(N,5,YMD)),7,2),'99') THU, TO_CHAR(SUBSTR(MAX(DECODE(N,6,YMD)),7,2),'99') FRI, TO_CHAR(SUBSTR(MAX(DECODE(N,7,YMD)),7,2),'99') SAT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')), 'DD')))) GROUP BY DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주'); NOTE

. . 17. 달력 상에 월 매출액을 주(週)단위로 처리 판매액의 처리과정에 대한 개요 D E C O 주 단위로
일자 요일 주 판매액 주 SUN MON TUE WED THU FRI SAT 1주 1주 1주 1주 1주 2주 2주 4주 4주 5주 5주 5주 5주 5주 주 주 주 주 주 주 주 주 주 주 주 주 주 주 D E C O . . 주 단위로 GROUP BY 처리 하면 된다. 위의 처리 과정에 해당하는 SQL 은 다음과 같습니다. SELECT YMD, N, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN, SALES_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3, (N1-21) N4, (N1-28) N5, SALES_AMT FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE(' ','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R, FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD LIKE '200503%' GROUP BY YMD))); SELECT 주, DECODE(요일,1,금액) SUN, DECODE(요일,2,금액) MON, DECODE(요일,3,금액) TUE, DECODE(요일,4,금액) WED, DECODE(요일,5,금액) THU, DECODE(요일,6,금액) FRI, DECODE(요일,7,금액) SAT …

17. 달력 상에 월 매출액을 주(週)단위로 처리 SQL(판매액에 대한 처리)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(판매액에 대한 처리) SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주') WEEK_GBN, SUM(DECODE(N,1,SALES_AMT)) SUN, SUM(DECODE(N,2,SALES_AMT)) MON, SUM(DECODE(N,3,SALES_AMT)) TUE, SUM(DECODE(N,4,SALES_AMT)) WED, SUM(DECODE(N,5,SALES_AMT)) THU, SUM(DECODE(N,6,SALES_AMT)) FRI, SUM(DECODE(N,7,SALES_AMT)) SAT, SUM(SALES_AMT) TOT_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5, SALES_AMT FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE(' ','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R, SALES_AMT FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD LIKE '200503%' GROUP BY YMD))) GROUP BY DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주', '6주'); 주 단위 판매액의 합 위의 SQL 에서는 일자별 판매액을 먼저 구해 놓고서, 그 후에 일자에 대한 요일구분, 주에 대한 구분 처리를 하도록 해서 최종적으로 주 단위로 GROUP BY에 의해 처리 했습니다.

UNION ALL 17. 달력 상에 월 매출액을 주(週)단위로 처리 구현 가능한 SQL 에 대한 개요
1주 주 3주 4주 5주 주 SUN MON TUE WED THU FRI SAT 1주 주 3주 4주 5주 주 SUN MON TUE WED THU FRI SAT TOT UNION ALL Key Point 요구사항에 의한 output 상에는 판매일자와 판매일별 판매금액의 합, 그리고 주 단위의 판매금액의 합이 함께 조회되어야 하는 특성이 있습니다. 특히 어떤 주에 있어서 판매일자 부분이 먼저 판매금액 부분이 나중에 표현될 수 있도록 해야 합니다. 따라서 조인을 해서는 안되며, UNION ALL 에 의한 처리가 필요합니다. UNION ALL 사용시 주의할 사항 UNION ALL 을 사용해서 각 SQL에 의한 결과를 합하고자 할 때는 각 SQL에 의한 컬럼 별 데이터 유형이 컬럼 순서 별로 일치해야 하며 컬럼의 개수 또한 같아야 합니다.

17. 달력 상에 월 매출액을 주(週)단위로 처리 SQL(1 단계)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(1 단계) SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN, TO_NUMBER(SUBSTR(MAX(DECODE(N,1,YMD)),7,2), '99') SUN, TO_NUMBER(SUBSTR(MAX(DECODE(N,2,YMD)),7,2), '99') MON, TO_NUMBER(SUBSTR(MAX(DECODE(N,3,YMD)),7,2), '99') TUE, TO_NUMBER(SUBSTR(MAX(DECODE(N,4,YMD)),7,2), '99') WED, TO_NUMBER(SUBSTR(MAX(DECODE(N,5,YMD)),7,2), '99') THU, TO_NUMBER(SUBSTR(MAX(DECODE(N,6,YMD)),7,2), '99') FRI, TO_NUMBER(SUBSTR(MAX(DECODE(N,7,YMD)),7,2), '99') SAT, TO_NUMBER(NULL) TOT_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))) GROUP BY DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') UNION ALL SUM(DECODE(N,1,SALES_AMT)) SUN, SUM(DECODE(N,2,SALES_AMT)) MON, SUM(DECODE(N,3,SALES_AMT)) TUE, SUM(DECODE(N,4,SALES_AMT)) WED, SUM(DECODE(N,5,SALES_AMT)) THU, SUM(DECODE(N,6,SALES_AMT)) FRI, SUM(DECODE(N,7,SALES_AMT)) SAT, SUM(SALES_AMT) TOT_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5, SALES_AMT TO_CHAR(TO_DATE(' ','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R, SALES_AMT FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD LIKE '200503%' GROUP BY YMD))) ORDER BY 1; 위 SQL 에서의 첫 번째 SQL 상에 TO_NUMBER(…) 함수의 처리를 함으로써 컬럼 순서 별로 두 번째 SQL 에 의한 컬럼 별 데이터 유형과 일치하도록 했으며, TO_NUMBER (NULL)에 의한 임의적인 컬럼을 추가함으로써 두 번째 SQL 과의 컬럼 개수가 같도록 했습니다.

OUTPUT에 맞추기 위해 다소의 가공 처리가 필요
17. 달력 상에 월 매출액을 주(週)단위로 처리 17. 달력 상에 월 매출액을 주(週)단위로 처리 OUTPUT에 맞추기 위해 다소의 가공 처리가 필요 SQL(1 단계) 에 의한 결과 주 SUN MON TUE WED THU FRI SAT TOT 1주 주 주 주 주 3주 4주 주 주 5주 NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(2 단계) SELECT DECODE(FLAG,'1', WEEK_GBN, NULL) WEEK_GBN, SUN, MON, TUE, WED, THU, FRI, SAT, DECODE(FLAG,'2',TOT_AMT) TOT_AMT FROM (SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN, '1' FLAG, TO_NUMBER(SUBSTR(MAX(DECODE(N,1,YMD)),7,2), '99') SUN, TO_NUMBER(SUBSTR(MAX(DECODE(N,2,YMD)),7,2), '99') MON, TO_NUMBER(SUBSTR(MAX(DECODE(N,3,YMD)),7,2), '99') TUE, TO_NUMBER(SUBSTR(MAX(DECODE(N,4,YMD)),7,2), '99') WED, TO_NUMBER(SUBSTR(MAX(DECODE(N,5,YMD)),7,2), '99') THU, TO_NUMBER(SUBSTR(MAX(DECODE(N,6,YMD)),7,2), '99') FRI, TO_NUMBER(SUBSTR(MAX(DECODE(N,7,YMD)),7,2), '99') SAT, TO_NUMBER(NULL) TOT_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))) GROUP BY DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') UNION ALL SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN, '2' FLAG, SUM(DECODE(N,1,SALES_AMT)) SUN, SUM(DECODE(N,2,SALES_AMT)) MON, SUM(DECODE(N,3,SALES_AMT)) TUE, SUM(DECODE(N,4,SALES_AMT)) WED, SUM(DECODE(N,5,SALES_AMT)) THU, SUM(DECODE(N,6,SALES_AMT)) FRI, SUM(DECODE(N,7,SALES_AMT)) SAT, SUM(SALES_AMT) TOT_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5, SALES_AMT FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE(' ','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R, SALES_AMT FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD LIKE '200503%' GROUP BY YMD))) ORDER BY 1, 2); 각 SQL 에 의해 처리된 결과에 대한 식별을 하기 위해 임의의 컬럼 FLAG를 추가했으며, 이를 이용해서 DECODE(…) 처리하도록 했습니다.

17. 달력 상에 월 매출액을 주(週)단위로 처리 특정 일자 후(後)의 판매정보가 전혀 없을 경우의 처리
현재일자를 2005년 3월 19일 이라고 가정할 경우, 4주부터 5주까지의 처리를 다음과 하기를 원합니다. (2005년 3월 19일 현재) SUN MON TUE WED THU FRI SAT AMT 1주 2주 3주 4주 5주 이와 같이 존재하지 않는 데이터를 마치 존재하는 데이터 처럼 처리하려면 CARTESIAN PRODUCT 를 통한 처리를 해야 합니다. 3월 19일을 현재로 봤을 때, 4주 ~ 5주는 없는 데이터

17. 달력 상에 월 매출액을 주(週)단위로 처리 SQL(1 단계) – 판매정보에 대한 CARTESIAN PRODUCT 처리
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(1 단계) – 판매정보에 대한 CARTESIAN PRODUCT 처리 SELECT V2.WEEK_GBN, SUM(DECODE(V2.N,1,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) SUN, SUM(DECODE(V2.N,2,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) MON, SUM(DECODE(V2.N,3,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) TUE, SUM(DECODE(V2.N,4,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) WED, SUM(DECODE(V2.N,5,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) THU, SUM(DECODE(V2.N,6,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) FRI, SUM(DECODE(V2.N,7,DECODE(V1.YMD,V2.YMD,V1.SALES_AMT))) SAT, SUM(DECODE(V1.YMD,V2.YMD,V1.SALES_AMT)) TOT_AMT FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD BETWEEN ' ' AND ' ' GROUP BY YMD) V1, (SELECT YMD, N, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD'))))) V2 GROUP BY V2.WEEK_GBN; CARTESIAN PRODUCT 사용 가능하면, 중첩된 DECODE(…) 함수의 처리를 간략하게 처리토록 작성하는 것이 바람직합니다.

SELECT V2.WEEK_GBN, SUM(DECODE(V2.N||V1.YMD,1||V2.YMD,V1.SALES_AMT)) SUN, SUM(DECODE(V2.N||V1.YMD,2||V2.YMD,V1.SALES_AMT)) MON, SUM(DECODE(V2.N||V1.YMD,3||V2.YMD,V1.SALES_AMT)) TUE, SUM(DECODE(V2.N||V1.YMD,4||V2.YMD,V1.SALES_AMT)) WED, SUM(DECODE(V2.N||V1.YMD,5||V2.YMD,V1.SALES_AMT)) THU, SUM(DECODE(V2.N||V1.YMD,6||V2.YMD,V1.SALES_AMT)) FRI, SUM(DECODE(V2.N||V1.YMD,7||V2.YMD,V1.SALES_AMT)) SAT, SUM(DECODE(V1.YMD,V2.YMD,V1.SALES_AMT)) TOT_AMT FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD BETWEEN ' ' AND ' ' GROUP BY YMD) V1, (SELECT YMD, N, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD'))))) V2 GROUP BY V2.WEEK_GBN; 위의 SQL은 1단계에서 나타난 중첩된 DECODE(…) 함수에 의한 처리를 간략하게 표현한 형태입니다.

17. 달력 상에 월 매출액을 주(週)단위로 처리 SQL(최종) ① ②
Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL(최종) SELECT DECODE(FLAG,'1',WEEK_GBN, NULL) WEEK_GBN,SUN,MON,TUE,WED,THU,FRI,SAT,DECODE(FLAG,'2',TOT_AMT) TOT_AMT FROM (SELECT DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN, '1' FLAG, TO_NUMBER(SUBSTR(MAX(DECODE(N,1,YMD)),7,2), '99') SUN,TO_NUMBER(SUBSTR(MAX(DECODE(N,2,YMD)),7,2), '99') MON, TO_NUMBER(SUBSTR(MAX(DECODE(N,3,YMD)),7,2), '99') TUE,TO_NUMBER(SUBSTR(MAX(DECODE(N,4,YMD)),7,2), '99') WED, TO_NUMBER(SUBSTR(MAX(DECODE(N,5,YMD)),7,2), '99') THU,TO_NUMBER(SUBSTR(MAX(DECODE(N,6,YMD)),7,2), '99') FRI, TO_NUMBER(SUBSTR(MAX(DECODE(N,7,YMD)),7,2), '99') SAT, TO_NUMBER(NULL) TOT_AMT FROM (SELECT YMD, N, (N - TO_NUMBER(R) + 1) CHK, N1, (N1-7) N2,(N1-14) N3,(N1-21) N4,(N1-28) N5 FROM (SELECT YMD, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'D') N, TO_CHAR(TO_DATE('200503'||'01','YYYYMMDD'),'D') N1,TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'DD') R FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD')))) GROUP BY DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') UNION ALL SELECT V2.WEEK_GBN, '2' FLAG,SUM(DECODE(V2.N||V1.YMD,1||V2.YMD,V1.SALES_AMT)) SUN, SUM(DECODE(V2.N||V1.YMD,2||V2.YMD,V1.SALES_AMT)) MON,SUM(DECODE(V2.N||V1.YMD,3||V2.YMD,V1.SALES_AMT)) TUE, SUM(DECODE(V2.N||V1.YMD,4||V2.YMD,V1.SALES_AMT)) WED,SUM(DECODE(V2.N||V1.YMD,5||V2.YMD,V1.SALES_AMT)) THU, SUM(DECODE(V2.N||V1.YMD,6||V2.YMD,V1.SALES_AMT)) FRI,SUM(DECODE(V2.N||V1.YMD,7||V2.YMD,V1.SALES_AMT)) SAT, SUM(DECODE(V1.YMD,V2.YMD,V1.SALES_AMT)) TOT_AMT FROM (SELECT YMD, SUM(SALES_AMT) SALES_AMT FROM EC_SALES WHERE YMD BETWEEN ' ' AND ' ' GROUP BY YMD) V1, (SELECT YMD, N, DECODE(CHK,N1,'1주',N2,'2주',N3,'3주',N4,'4주',N5,'5주','6주') WEEK_GBN FROM (SELECT '200503'||DAY YMD FROM EC_DAY WHERE ROWNUM <= TO_CHAR(LAST_DAY(TO_DATE('200503','YYYYMM')),'DD'))))) V2 GROUP BY V2.WEEK_GBN ORDER BY 1, 2); ① ② ①은 달력을 생성하기 위한 부분이며, ②는 앞서 작성한 CARTESIAN PRODUCT 에 의한 처리 부분입니다.

18. 년 매출액을 주(週) 단위로 구분 처리 인덱스정보 EC_SALES_PK : YMD + ITEM 요구사항
SALES_AMT /* 판매금액 */ SUN MON TUE WED THU FRI SAT 1주 /01 01/02 01/03 2주 /04 01/05 01/06 01/07 01/08 01/09 01/10 3주 /11 01/12 01/13 01/14 01/15 01/16 01/17 4주 /18 01/19 01/20 01/21 01/22 01/23 01/24 5주 /25 01/26 01/27 01/28 01/29 01/30 01/31 6주 /01 02/02 02/03 02/04 02/05 02/06 02/07 52주 /19 12/20 12/21 12/22 12/23 12/24 12/25 53주 /26 12/27 12/28 12/29 12/30 12/31 (2004년) (가정 1) 2004년 1월 1일 목요일부터 그 다음주 수요일까지를 1주(週)로 처리하고자 합니다. 즉, 해당 년도의 1월 1일 기준으로 7일간을 한 주로 처리를 합니다. 중간 생략 ●●● (가정 2) 2004년 1월 1일 목요일부터 1월 4일까지 를 첫째 주(週)로 보고, 두째 주 부터는 월요일부터 일요일까지를 1주로 합니다. 즉, ISO 표준에 의한 주에 대한 처리를 합니다. 요구사항 [ 개요 ] 本 사례에서는 특정 년도에 대한 매출액을 한 주(週) 단위로 구분 처리하고자 할 때, 주에 대한 기준을 어떻게 하느냐에 따라서 무엇이 달라지며 그에 따른 고려할 사항을 찾아 보고자 합니다. 2004년의 년간 판매내역을 토대로 월별로 주간 단위의 매출금액에 대한 합을 구하고자 합니다.

NOTE 18. 년 매출액을 주(週) 단위로 구분 처리 가정(1) : SQL
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 가정(1) : SQL SELECT SUBSTR(YMD,1,6) MONTH, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'WW') WEEK_GBN, SUM(SALES_AMT) MONTH_WEEK_TOT FROM EC_SALES WHERE YMD LIKE '2004%' GROUP BY SUBSTR(YMD,1,6), TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'WW'); TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'WW')의 처리결과는 1 ~ 52 또는 53 이 됩니다. 특히, 12월의 마지막 주에 대한 처리가 경우에 따라 53번째 주에 대한 처리를 할 수 있습니다. NOTE

NOTE 18. 년 매출액을 주(週) 단위로 구분 처리 가정(2) : SQL
SELECT SUBSTR(YMD,1,6) MONTH, TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW') WEEK_GBN, SUM(SALES_AMT) MONTH_WEEK_TOT FROM EC_SALES WHERE YMD LIKE '2004%' GROUP BY SUBSTR(YMD,1,6), TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW'); 53번째 주 처리시 문제발생 TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW') 의 처리결과는 1 ~ 52 입니다. 따라서 12월의 마지막 주에 대한 처리에 있어 53번째 주에 대한 처리가 문제될 수 있습니다. SUN MON TUE WED THU FRI SAT 1주 /01 01/02 01/03 2주 /04 01/05 01/06 01/07 01/08 01/09 01/10 52주 /19 12/20 12/21 12/22 12/23 12/24 12/25 1주 /26 12/27 12/28 12/29 12/30 12/31 (2004년) 중간 생략 ●●● NOTE

18. 년 매출액을 주(週) 단위로 구분 처리 가정(2) : 개선된 SQL
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 가정(2) : 개선된 SQL SELECT SUBSTR(YMD,1,6) MONTH, DECODE(SUBSTR(YMD,1,6),'200412', DECODE(TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW'),'01','53', TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW')), TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW')) WEEK_GBN, SUM(SALES_AMT) MONTH_WEEK_TOT FROM EC_SALES WHERE YMD LIKE '2004%' GROUP BY SUBSTR(YMD,1,6), TO_CHAR(TO_DATE(YMD,'YYYYMMDD'),'IW')); 위의 SQL 은 DECODE(…) 처리에 의해서 12월 마지막 주에 대해 53번째 주로의 처리가 가능하도록 한 형태입니다. IF 년월 < 2004년 12월 THEN 주구분 = TO_CHAR(TO_DATE(일자,'YYYYMMDD'),'IW') ELSE IF TO_CHAR (TO_DATE(YMD,'YYYYMMDD'),'IW') = '01' THEN 주구분 = '53' END IF;

19. (코드+년도+월)별,(코드)별,전체총액을 한번에
원하는 Output 형식의 예 EC_APPLY (수강신청정보) COURSE_CODE YEAR COURSE_SQ_NO MEMBER_TYPE MEMBER_ID APPLY_DATE PAYMENT_METHOD DEPOSIT_AMOUNT DEPOSIT_DATE 과정코드 년도 월 입금총액 소계 소계 총계 2000년, 2001년도의 개설과정에서 과정코드 100 미만 과정을 대상으로 과정의 년도별 월별 입금총액, 과정별 입금총액, 전체 입금총액의 현황을 구하려고 합니다. 중간 생략 ●●● 중간 생략 ●●● 중간 생략 ●●● 인덱스정보 EC_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO MEMBER_TYPE + MEMBER_ID EC_APPLY_APPLY_DATE_IDX : APPLY_DATE 중간 생략 ●●● 중간 생략 ●●● [ 개요 ] 일상적으로 보면, 사용자가 원하는 각종 리포트에 있어서 품목별 판매현황, 품목별 년도별 판매현황, 품목별 년도별 월별 현황, 품목별 월별, etc 등 기타 다양한 기준 하에서 판매현황에 대한 요청을 하는 경우가 많이 있습니다. 앞서 일부 사례에서 얘기한 바와 같이, 리포트 마다 다를 수는 있겠지만 이러한 유형의 리포트에서 처리하고자 하는 데이터는 그 양이 방대하며, 리포트 자체의 output의 특성과 기타 등등의 이유로 말미암아 SQL로 구현해서 처리하기에는 현실적으로 어렵다고 생각하는 것이 보통입니다. 本 사례에서는 이러한 유형의 사례 중 하나를 제시하면서 어떻게 처리해야 속도향상을 얻을 수 있는지를 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL SELECT DECODE(COURSE_CODE,999,'총계',COURSE_CODE) COURSE_CODE, DECODE(YEAR,'9999','소계',YEAR) YEAR, MONTH, TOT_AMT FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) TOT_AMT FROM EC_APPLY WHERE COURSE_CODE < 100 AND YEAR IN ('2001','2000') AND APPLY_DATE + 0 >= TO_DATE(' ','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE(' ','YYYYMMDD') + 1 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') UNION ALL SELECT COURSE_CODE, '9999' YEAR, NULL MONTH, GROUP BY COURSE_CODE SELECT 999 COURSE_CODE, NULL YEAR, NULL MONTH, AND APPLY_DATE + 0 < TO_DATE(' ','YYYYMMDD') + 1) ORDER BY COURSE_CODE, YEAR, MONTH; ① 별도의 SQL 에 의해 각각 처리되는 것 이 문제점임 ② 위의 SQL 은 각 기준에 의한 처리를 하기 위해 별도로 SQL 로 처리하도록 한 형태 입니다. 따라서 각각의 처리를 위한 SQL 에 의해서 동일한 데이터에 대한 반복처리가 발생하고 있으며, 이로 인해 속도 향상을 보장 받을 수 없는 문제점을 지니고 있습니다. ① 은 과정의 년도별 월별 입금총액을 구하기 위한 부분 ② 는 과정별 입금총액을 구하기 위한 부분 ③ 은 전체 입금총액을 구하기 위한 부분 그리고 ②의 년도와 ③ 의 과정코드에 대해서 임의의 값을 사용 한 이유는 나중에 DECODE(…) 함수 처리에 의해서 리포트 상에서 활용하기 위함 ③

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 286 SORT (ORDER BY) VIEW UNION-ALL SORT (GROUP BY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) SORT (GROUP BY) SORT (AGGREGATE) NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점 테이블에 대한 데이터 처리를 3 번 하고 있습니다. 즉, 과정의 년도별 월별 입금총액을 구하기 위한 ① 에 의한 1 번, 과정별 입금총액을 구하기 위한 ② 에 의한 1 번, 마지막 전체입금총액을 구하기 위한 ③ 에 의한 1 번씩 처리하는 만큼의 과부하가 발생하고 있는 것입니다. 문제점 해결하기 위한 방안 - CARTESIAN PRODUCT을 활용한 [데이터 복제]에 의한 반복처리의 절감 (現 작업량)Ⅹ(1/3) 로 개선! NOTE

참고 : CARTESIAN PRODUCT 에 의한 처리의 개요 19. (코드+년도+월)별,(코드)별,전체총액을 한번에 1 2 3 ( ) , SELECT … FROM 카테시안 곱 D E C O 위의 그림은 CARTESIAN PRODUCT에 의한 결과와 DECODE 함수 처리에 의한 결과만을 각각 나타낸 것입니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(CARTESIAN PRODUCT에 의한 처리) SELECT DECODE(COURSE_CODE,999,'총계',COURSE_CODE) COURSE_CODE, DECODE(YEAR,'9999','소계',YEAR) YEAR, MONTH, TOT_AMT FROM (SELECT DECODE(RN, 3, 999, COURSE_CODE) COURSE_CODE, DECODE(RN, 1, YEAR, 2, '9999', NULL) YEAR, DECODE(RN, 1, MONTH, NULL) MONTH, SUM(TOT_AMT) TOT_AMT FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) TOT_AMT FROM EC_APPLY WHERE COURSE_CODE < 100 AND YEAR IN ('2001','2000') AND APPLY_DATE + 0 >= TO_DATE(' ','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE(' ','YYYYMMDD') + 1 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')) V1, (SELECT ROWNUM RN FROM EC_COURSE WHERE ROWNUM <= 3) V2 GROUP BY DECODE(RN, 3, 999, COURSE_CODE), DECODE(RN, 1, YEAR, 2, '9999', NULL), DECODE(RN, 1, MONTH, NULL)) ORDER BY COURSE_CODE, YEAR, MONTH; CARTESIAN PRODUCT ROWNUM 을 활용해서 각 기준에 의한 데이터 처리를 구분하기 위함 위의 SQL 은 CARTESIAN Product 에 의한 데이터 복제를 이용하고 있습니다. 이와 같이 처리할 경우에 반복처리로 인한 I/O 상의 부하를 상당 부분 줄일 수 있습니다. 각 기준에 의한 데이터 처리시 구분을 위해 ROWNUM을 활용하고 있는데, ROWNUM 이 1 ~ 3까지의 값이라 할 때 다음과 같이 사용하고 있습니다. IF ROWNUM = 1 THEN 과정의 년도별 월별 입금총액 처리 ELSE IF ROWNUM = 2 THEN 과정별 입금총액 처리 ELSE 전체입금총액 처리 END IF;

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 286 SORT (ORDER BY) VIEW SORT (GROUP BY) NESTED LOOPS VIEW COUNT (STOPKEY) TABLE ACCESS (FULL) OF 'EC_COURSE' VIEW SORT (GROUP BY) TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선 사항 전반적으로 보면 간결한 SQL 작성이 가능하며, 데이터 처리에 있어서도 테이블에 대한 반복 처리를 하지 않음으로써 상당히 개선되었습니다. 참고 : Oracle 9I 인 경우 SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) "Total Amt" FROM EC_APPLY WHERE COURSE_CODE < 100 AND YEAR IN ('2001','2000') AND APPLY_DATE + 0 >= TO_DATE(' ','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE(' ','YYYYMMDD') + 1 GROUP BY ROLLUP(COURSE_CODE,(YEAR,TO_CHAR(APPLY_DATE,'MM'))); NOTE

과정별 소계 부분을 먼저 처리할 수 있는 SQL의 구현 과정코드 년도 월 입금총액 소계 소계 총계 SQL SELECT DECODE(COURSE_CODE,999,'총계',COURSE_CODE) COURSE_CODE, DECODE(YEAR,'0000','소계',YEAR) YEAR, MONTH, TOT_AMT FROM (SELECT DECODE(RN, 3, 999, COURSE_CODE) COURSE_CODE, DECODE(RN, 1, YEAR, 2, '0000', NULL) YEAR, DECODE(RN, 1, MONTH, NULL) MONTH, SUM(TOT_AMT) TOT_AMT FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) TOT_AMT FROM EC_APPLY WHERE COURSE_CODE < 100 AND YEAR IN ('2001','2000') AND APPLY_DATE + 0 >= TO_DATE(' ','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE(' ','YYYYMMDD') + 1 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')) V1, (SELECT ROWNUM RN FROM EC_COURSE WHERE ROWNUM <= 3) V2 GROUP BY DECODE(RN, 3, 999, COURSE_CODE), DECODE(RN, 1, YEAR, 2, '0000', NULL), DECODE(RN, 1, MONTH, NULL)); 중간 생략 ●●● 중간 생략 ●●● 중간 생략 ●●● 중간 생략 ●●● NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 과정별 소계 선행 처리 시 소계 부분에 대한 과정명 처리 SELECT DECODE(B.COURSE_CODE,999,'총계',B.COURSE_CODE) COURSE_CODE, DECODE(B.YEAR,'0000',A.COURSE_NAME) COURSE_NAME, DECODE(B.YEAR,'0000','소계',B.YEAR) YEAR, B.MONTH, B.TOT_AMT FROM EC_COURSE A, (SELECT DECODE(RN, 3, 999,COURSE_CODE) COURSE_CODE, DECODE(RN, 1,YEAR, 2,'0000',NULL) YEAR, DECODE(RN, 1,MONTH,NULL) MONTH, SUM(TOT_AMT) TOT_AMT FROM (SELECT COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM') MONTH, SUM(DEPOSIT_AMOUNT) TOT_AMT FROM EC_APPLY WHERE COURSE_CODE < 100 AND YEAR IN ('2001','2000') AND APPLY_DATE + 0 >= TO_DATE(' ','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE(' ','YYYYMMDD') + 1 GROUP BY COURSE_CODE, YEAR, TO_CHAR(APPLY_DATE,'MM')) V1, (SELECT ROWNUM RN FROM EC_COURSE WHERE ROWNUM <= 3) V2 GROUP BY DECODE(RN, 3,999,COURSE_CODE), DECODE(RN, 1,YEAR, 2,'0000',NULL), DECODE(RN, 1,MONTH,NULL)) B WHERE B.COURSE_CODE = A.COURSE_CODE(+); NOTE

20. 서브쿼리에서 IN 과 EXISTS 의 동시 사용
인덱스정보 EC_COURSE_PK : COURSE_CODE EC_APPLY_PK : COURSE_CODE + YEAR + COURSE_SQ_NO MEMBER_TYPE + MEMBER_ID EC_COURSE (과정정보) COURSE_CODE COURSE_NAME EC_APPLY (수강신청정보) YEAR COURSE_SQ_NO MEMBER_TYPE MEMBER_ID APPLY_DATE PAYMENT_METHOD DEPOSIT_AMOUNT DEPOSITOR DEPOSIT_DATE BANK COMPANY_NO RECOMMENDER ○ SQL SELECT A.COURSE_CODE, A.COURSE_NAME FROM EC_COURSE A WHERE A.COURSE_CODE IN (SELECT COURSE_CODE FROM EC_APPLY WHERE YEAR = '2000' GROUP BY COURSE_CODE HAVING COUNT(COURSE_CODE) < 500) OR NOT EXISTS (SELECT 'X' FROM EC_APPLY C WHERE C.COURSE_CODE = A.COURSE_CODE AND C.YEAR = '2000'); 전체 과정(COURSE_CODE)에 대하여 2000년도에 신청자가 전혀 없거나 신청자가 500명 미만인 과정의 과정코드와 과정명을 조회하고자 합니다. [ 개요 ] 서브쿼리(Subquery)는 조인(Join)과 더불어 자주 사용되고 있습니다. 현재 오라클 기반에서 사용할 수 있는 서브쿼리의 종류로는 Nested 서브쿼리, Correlated 서브쿼리, Inline view, Scalar 서브쿼리 등을 예로 들 수 있습니다. 흔히 이들 서브쿼리의 용도를 생각해 볼 때, 조인에 대한 대안으로써 채택할 수 있을지 없을지를 가늠해야 합니다. 물론 역으로 서브쿼리에 대한 대안으로써 조인을 채택할 수 있을런지도 생각해 볼 수 있습니다. 本 사례에서는 요구사항의 특성으로 인해 SQL의 WHERE 절에 IN과 EXISTS를 동시에 사용할 수 밖에 없는 경우를 제시하면서 이럴 경우에 효과적으로 처리할 수 있는 방안을 찾아 보고자 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SQL (개선안) SELECT A.COURSE_CODE, A.COURSE_NAME FROM EC_COURSE A WHERE A.COURSE_CODE IN (SELECT COURSE_CODE FROM EC_APPLY WHERE YEAR = '2000' GROUP BY COURSE_CODE HAVING COUNT(COURSE_CODE) < 500) UNION ALL WHERE NOT EXISTS (SELECT 'X' FROM EC_APPLY C WHERE C.COURSE_CODE = A.COURSE_CODE AND C.YEAR = '2000'); Nested Subquery Correlated Subquery NOTE Correlated subquery와 Nested subquery 의 실행이 각각 될 수 있도록 하고자 UNION ALL을 사용한 형태로 전환한 형태입니다.

Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE SORT (UNIQUE) UNION-ALL NESTED LOOPS VIEW OF 'VW_NSO_1' FILTER SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_APPLY' TABLE ACCESS (BY INDEX ROWID) OF 'EC_COURSE' INDEX (UNIQUE SCAN) OF 'EC_COURSE_PK' (UNIQUE) FILTER TABLE ACCESS (FULL) OF 'EC_COURSE' INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) NOTE

Perfect! 대용량 데이터베이스 튜닝 Ⅱ 문제점 IN 다음의 서브쿼리 수행 시 인덱스 미사용 신청자가 500명 미만인 조건 처리를 위한 SQL 과 신청자가 전혀 없는 조건 처리를 위한 SQL에 의해서 과정정보(EC_COURSE)와 신청정보(EC_APPLY) 가 두 번씩 사용 문제점을 해결하기 위한 방안 신청정보(EC_APPLY)에 대한 인덱스 미사용 현상을 피할 수 있도록 하는 방안이 별도로 강구되어야 하며, 이에 의한 처리 결과와 과정정보 (EC_COURSE)에 대한 처리가 한 번의 작업에 의해 될 수 있어야 합니다. 따라서 전반적으로 과정정보(EC_COURSE)와 신청정보(EC_APPLY)에 대한 사용이 한 번씩 될 수 있도록 하고자 Outer 조인에 의한 처리를 하면 됩니다. NOTE

SQL (최종) 2000년에 신청자가 있는 과정에 대해서 과정별로 신청자에 대한 인원현황을 구해 놓도록 처리하 고자 하는 부분임 SELECT A.COURSE_CODE, A.COURSE_NAME FROM EC_COURSE A, (SELECT B.COURSE_CODE, COUNT(B.COURSE_CODE) CNT FROM EC_APPLY B, EC_COURSE C WHERE B.COURSE_CODE = C.COURSE_CODE AND B.YEAR = '2000' GROUP BY B.COURSE_CODE) D WHERE A.COURSE_CODE = D.COURSE_CODE(+) AND (D.CNT < 500 OR D.CNT IS NULL); 위의 SQL 은 앞서 구현된 SQL 과는 달리 과정정보(EC_COURSE)와 신청정보 (EC_APPLY)에 대한 처리를 전반적으로 한 번씩 할 수 있도록 하기 위해서 조인(Join)을 사용하고 있는 형태 입니다. 특히, 2000년에 신청자가 있는 과정별로 인원현황을 먼저 처리토록 한 결과에는 2000년에 신청자가 없는 과정에 대한 데이터는 없을 것이기 때문에 신청자가 전혀 없는 과정에 대한 데이터를 처리하기 위해서는 아우터(Outer) 조인을 해야 합니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ Trace 정보 call count cpu elapsed disk query current rows Parse Execute Fetch total Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE FILTER MERGE JOIN (OUTER) SORT (JOIN) TABLE ACCESS (FULL) OF 'EC_COURSE' SORT (JOIN) VIEW SORT (GROUP BY) NESTED LOOPS TABLE ACCESS (FULL) OF 'EC_COURSE' INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) NOTE

21. 배타적 관계성을 지닌 테이블 간의 조인 21. 배타적 관계성을 지닌 테이블 간의 조인 소개 계좌정보를 중심으로 개인고객과 법인고객 간의 관계성을 살펴보면, 임의의 하나의 계좌에 있어서 해당 계좌가 개인계좌 이거나 법인계좌 둘 중 하나일 것입니다. 다시 말하면 임의의 한 계좌가 개인계좌이면서 법인계좌일 수는 없다는 것입니다. 이렇게 볼 때, 개인고객과 법인고객을 상호 배타적(Exclusive)이라고 합니다. ○ EC_PERSONAL (개인고객) CUSTOMER_ID NAME /* 성명 */ EC_ACCOUNT (계좌정보) ACCOUNT_NO OPEN_YMD /* 개설일 */ CUSTOMER_ID /* 고객번호 */ CUSTOMER_GBN /* 고객구분 – 개인 2 – 법인 */ EC_COMPANY (법인고객) NAME /* 법인명 */ 인덱스정보 EC_ACCOUNT_PK : ACCOUNT_NO EC_ACCOUNT_IDX01 : OPEN_YMD EC_PERSONAL_PK : CUSTOMER_ID EC_COMPANY_PK : CUSTOMER_ID [ 개요 ] 本 사례에서는 테이블 상호간에 배타적(EXCLUSIVE) 관계성을 지니고 있을 경우, 이들 테이블 간 조인에 있어 아우터(Outer) 조인의 활용 사례를 소개하고자 합니다.

NOTE 21. 배타적 관계성을 지닌 테이블 간의 조인
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 2004년 3월에 개설된 계좌현황에 대해서 다음과 같이 조회하고자 합니다. NOTE

21. 배타적 관계성을 지닌 테이블 간의 조인 SQL ① ② ① - 결과가 한 건도 조회되지 않는다.
SELECT A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME FROM EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C WHERE A.OPEN_YMD LIKE '200403%' AND A.CUSTOMER_ID = B.CUSTOMER_ID AND A.CUSTOMER_ID = C.CUSTOMER_ID ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC; ① SELECT A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME FROM EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C WHERE A.OPEN_YMD LIKE '200403%' AND (A.CUSTOMER_ID = B.CUSTOMER_ID OR A.CUSTOMER_ID = C.CUSTOMER_ID) ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC; ② ①의 경우 … A.CUSTOMER_ID = B.CUSTOMER_ID 와 A.CUSTOMER_ID = C.CUSTOMER_ID 를 동시에 만족하는 데이터가 없기 때문에 결과가 한 건도 조회되질 않습니다. ②의 경우 A.CUSTOMER_ID = B.CUSTOMER_ID 또는 A.CUSTOMER_ID = C.CUSTOMER_ID 라 했을 때 처리 결과는 (A.CUSTOMER_ID = B.CUSTOMER_ID) 의 처리 결과가 법인고객(C)의 로우(rows) 만큼의 카테시안 곱(Cartesian Product) 과 (A.CUSTOMER_ID = C.CUSTOMER_ID) 의 처리 결과가 개인고객(B)의 로우(rows) 만큼의 카테시안 곱(Cartesian Product) 의 합한 결과 가 조회됩니다. ① - 결과가 한 건도 조회되지 않는다. ② - 조회는 되나 옳은 결과가 아니다.

21. 배타적 관계성을 지닌 테이블 간의 조인 개선된SQL ① ②
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된SQL SELECT A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME FROM EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C WHERE A.OPEN_YMD LIKE '200403%' AND A.CUSTOMER_ID = B.CUSTOMER_ID(+) AND A.CUSTOMER_ID = C.CUSTOMER_ID(+) ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC; OUTER 조인 활용 ① SELECT A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME FROM EC_ACCOUNT A, EC_PERSONAL B, EC_COMPANY C WHERE A.OPEN_YMD LIKE '200403%' AND DECODE(A.CUSTOMER_GBN,'1',A.CUSTOMER_ID) = B.CUSTOMER_ID(+) AND DECODE(A.CUSTOMER_GBN,'2',A.CUSTOMER_ID) = C.CUSTOMER_ID(+) ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC; ②의 경우는 고객구분에 대한 DECODE(…) 함수 처리를 통해서 ①의 경우 보다는 불필요한 인덱스 블록에 대한 처리를 감소시키고자 구현한 형태입니다. 참고로 고객구분에 의해서 DECODE(…) 의 결과가 둘 중 하나는 NULL이 될 것이며, 이에 의해서 다음과 같이 조건이 성립되어 처리될 것입니다. 고객구분이 '1' 이라면, AND A.CUSTOMER_ID = B.CUSTOMER_ID(+) AND NULL = C.CUSTOMER_ID(+) 고객구분이 '2' 라면, AND NULL = B.CUSTOMER_ID(+) AND A.CUSTOMER_ID = C.CUSTOMER_ID(+) ②

NOTE 21. 배타적 관계성을 지닌 테이블 간의 조인 SQL 인덱스정보
EC_ACCOUNT01_PK : ACCOUNT_NO EC_ACCOUNT01_IDX01 : OPEN_YMD EC_PERSONAL_PK : CUSTOMER_ID EC_COMPANY_PK : CUSTOMER_ID ○ EC_PERSONAL (개인고객) CUSTOMER_ID NAME /* 성명 */ EC_ACCOUNT01 (계좌정보) ACCOUNT_NO OPEN_YMD /* 개설일 */ CUSTOMER_ID1 /* 개인 고객번호 */ CUSTOMER_ID2 /* 법인 고객번호 */ EC_COMPANY (법인고객) NAME /* 법인명 */ 계좌정보에 있어 개인고객 번호과 법인고객 번호가 별개의 컬럼으로 존재한다면 SQL SELECT A.ACCOUNT_NO, A.OPEN_YMD, B.NAME, C.NAME FROM EC_ACCOUNT01 A, EC_PERSONAL B, EC_COMPANY C WHERE A.OPEN_YMD LIKE '200403%' AND A.CUSTOMER_ID1 = B.CUSTOMER_ID(+) AND A.CUSTOMER_ID2 = C.CUSTOMER_ID(+) ORDER BY A.OPEN_YMD, A.ACCOUNT_NO DESC; NOTE

22. 잘못된 OUTER 조인에서 비롯된 NULL 처리
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 인덱스정보 EC_S_READING_PK : READING_ID EC_S_READING_REV_PK : READING_ID + REVISION_NO ○ EC_S_READING_REV (센서 리딩 개정) READING_ID REVISION_NO YMD VALUE EC_S_READING (센서 리딩) YMD VALUE READING_ID REVISION_NO YMD VALUE ○ READING_ID YMD VALUE SAMPLE 원하는 결과 [ 개요 ] 本 사례에서는 흔히 아우터(Outer) 조인에 의한 사용자 요구사항을 처리하고자 할 때, 아우터 조인의 잘못된 이해에서 비롯되었다고 할수 있는 NULL을 사용하는 부분에 대해서 그것의 문제점과 해결방안을 찾아 보고자 합니다. READING_ID YMD VALUE REVISION_NO

SQL SELECT A.READING_ID, NVL(B.YMD, A.YMD), NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0) FROM EC_S_READING A, EC_S_READING_REV B WHERE A.READING_ID = B.READING_ID(+) AND B.READING_ID||B.REVISION_NO IN (SELECT READING_ID||MAX (REVISION_NO) FROM EC_S_READING_REV GROUP BY READING_ID); 원하는 결과를 얻을 수 없다. 처리 결과 READING_ID YMD VALUE REVISION_NO 위의 SQL 의 경우, 조건 절에 있는 서브쿼리의 결과를 생각해 보면 READING_ID 가 101 과 104 인 것은 없습니다. 아우터(Outer) 조인에서 보면, 참조성 테이블과 관련있는 모든 컬럼에 대해 (+) 표현를 해야 됩니다. 그렇다면 위의 SQL 의 경우는 B.READING_ID||B.REVISION_NO(+) IN (서브쿼리) 식으로 수정하면 될 것 같지만 실상은 IN 과 더불어 (+) 표현을 사용할 수 없기 때문에 예시한 바와 같이 수정할 수는 없습니다. ※ 관련된 에러번호: ORA-01719: outer join operator (+) not allowed in operand of OR or IN

NOTE 22. 잘못된 OUTER 조인에서 비롯된 NULL 처리 개선된 SQL(1 단계)
Perfect! 대용량 데이터베이스 튜닝 Ⅱ 개선된 SQL(1 단계) SELECT A.READING_ID, NVL(B.YMD, A.YMD), NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0) FROM EC_S_READING A, EC_S_READING_REV 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 EC_S_READING_REV GROUP BY READING_ID)); Rows Execution Plan 0 SELECT STATEMENT GOAL: CHOOSE 5 FILTER NESTED LOOPS (OUTER) TABLE ACCESS (FULL) OF 'EC_S_READING' TABLE ACCESS (BY INDEX ROWID) OF 'EC_S_READING_REV' INDEX (RANGE SCAN) OF 'EC_S_READING_REV_PK' (UNIQUE) FILTER SORT (GROUP BY) TABLE ACCESS (FULL) OF 'EC_S_READING_REV' NOTE

개선된 SQL(2 단계) SELECT A.READING_ID, NVL(B.YMD, A.YMD), NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0) FROM EC_S_READING A, (SELECT B1.READING_ID, B1.REVISION_NO, B1.YMD, B1.VALUE FROM EC_S_READING_REV B1, (SELECT READING_ID, MAX(REVISION_NO) AS REVISION_NO FROM EC_S_READING_REV 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(+); ① ①은 각 READING_ID 별 REVISION_NO의 최대값을 지니고 있는 데이터를 추출하고자 SELF-조인 형식으로 먼저 처리토록 한 것입니다. ①에 의한 결과와 EC_S_READING 과 아우터 조인을 할 수 있도록 함으로써 NULL 사용이 필요 없게 되었습니다. 그러나 ①에 의한 처리시 SELF-조인을 해결할 수 있는 방안을 모색 해야 할 여지는 남아 있습니다.

Perfect! 대용량 데이터베이스 튜닝 Ⅱ SELF-조인 제거 최적의 SQL SELECT A.READING_ID, NVL(B.YMD, A.YMD), NVL(B.VALUE, A.VALUE), NVL(B.REVISION_NO, 0) FROM EC_S_READING A, (SELECT READING_ID, REVISION_NO, YMD, VALUE FROM (SELECT READING_ID, REVISION_NO, YMD, VALUE, MAX(REVISION_NO) OVER(PARTITION BY READING_ID) AS MAX_REVISION_NO FROM EC_S_READING_REV) WHERE REVISION_NO = MAX_REVISION_NO) B WHERE A.READING_ID = B.READING_ID(+); ① 분석용 함수 MAX(…) OVER(…) 함수 사용으로 SELF-조인을 제거 MAX(…) OVER(…) 처리에 의한 결과 READING_ID REVISION_NO YMD VALUE MAX_REVISION_NO ①의 경우, 오라클 8i 부터 사용 가능한 분석용 함수를 사용함으로써 각 READING_ID 별 REVISION_NO의 최대값을 지니고 있는 데이터를 추출하고자 SELF-조인을 없앤 형태입니다.

Perfect! 대용량 데이터베이스 튜닝Ⅱ.

Similar presentations

Presentation on theme: "Perfect! 대용량 데이터베이스 튜닝Ⅱ."— Presentation transcript:

Similar presentations

About project

지원

로그인

Auth with social network:

Perfect! 대용량 데이터베이스 튜닝Ⅱ.

Similar presentations

Presentation on theme: "Perfect! 대용량 데이터베이스 튜닝Ⅱ."— Presentation transcript:

Similar presentations

About project

지원