19.(코드+년도+월)별,(코드)별,전체총액을 한번에 원하는 Output 형식의 예 EC_APPLY (수강신청정보) COURSE_CODE YEAR COURSE_SQ_NO MEMBER_TYPE MEMBER_ID APPLY_DATE PAYMENT_METHOD DEPOSIT_AMOUNT DEPOSIT_DATE 과정코드 년도 월 입금총액 14 2000 01 500000 14 2000 02 300000 14 2000 03 1000000 14 2001 01 300000 14 2001 02 9000000 14 소계 128000000 28 2000 01 1000000 28 2000 02 7500000 28 2001 01 10000000 28 2001 02 11000000 28 소계 13000000 총계 98000000 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로 구현해서 처리하기에는 현실적으로 어렵다고 생각하는 것이 보통입니다. 本 사례에서는 이러한 유형의 사례 중 하나를 제시하면서 어떻게 처리해야 속도향상을 얻을 수 있는지를 찾아 보고자 합니다. 중간 생략 ●●● 중간 생략 ●●●

별도의 SQL 에 의해 각각 처리되는 것 이 문제점임 19.(코드+년도+월)별,(코드)별,전체총액을 한번에 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('20000101','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE('20011231','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('20011231','YYYYMMDD') + 1) ORDER BY COURSE_CODE, YEAR, MONTH; SQL#19_01 ① 별도의 SQL 에 의해 각각 처리되는 것 이 문제점임 위의 SQL 은 각 기준에 의한 처리를 하기 위해 별도로 SQL 로 처리 하도록 한 형태 입니다. 따라서 각각의 처리를 위한 SQL 에 의해서 동일한 데이터에 대한 반복처리가 발생하고 있으며, 이로 인해 속도 향상을 보장 받을 수 없는 문제점을 지니고 있습니다. ① 은 과정의 년도별 월별 입금총액을 구하기 위한 부분 ② 는 과정별 입금총액을 구하기 위한 부분 ③ 은 전체 입금총액을 구하기 위한 부분 그리고 ②의 년도와 ③ 의 과정코드에 대해서 임의의 값을 사용 한 이유는 나중에 DECODE(…) 함수 처리에 의해서 리포트 상에서 활용하기 위함  순서를 정렬에 이용하기 위해 ② ③

19.(코드+년도+월)별,(코드)별,전체총액을 한번에 Trace 정보 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.02 0 0 0 0 Execute 1 0.01 0.00 0 0 0 0 Fetch 21 2.10 2.10 14261 29970 0 286 total 23 2.12 2.12 14261 29970 0 286 Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 286 SORT (ORDER BY) 286 VIEW 286 UNION-ALL 255 SORT (GROUP BY) 4381 TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' 10077 INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE) 30 SORT (GROUP BY) 1 SORT (AGGREGATE)

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

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

19.(코드+년도+월)별,(코드)별,전체총액을 한번에 개선된 SQL(CARTESIAN PRODUCT에 의한 처리) SQL#19_02 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('20000101','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE('20011231','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 위의 SQL 은 CARTESIAN Product 에 의한 데이터 복제를 이용하고 있습니다. 이와 같이 처리할 경우에 반복처리로 인한 I/O 상의 부하를 상당 부분 줄일 수 있습니다. 각 기준에 의한 데이터 처리시 구분을 위해 ROWNUM을 활용하고 있는데, ROWNUM 이 1 ~ 3까지의 값이라 할 때 다음과 같이 사용하 고 있습니다. IF ROWNUM = 1 THEN 과정의 년도별 월별 입금총액 처리 ELSE IF ROWNUM = 2 THEN 과정별 입금총액 처리 ELSE 전체입금총액 처리 END IF; ROWNUM 을 활용해서 각 기준에 의한 데이터 처리를 구분하기 위함

19.(코드+년도+월)별,(코드)별,전체총액을 한번에 Trace 정보 call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ---------- Parse 1 0.01 0.00 0 0 0 0 Execute 1 0.00 0.00 0 0 0 0 Fetch 21 0.77 0.80 4858 9991 4 286 total 23 0.78 0.80 4858 9991 4 286 Rows Execution Plan ------- --------------------------------------------------- 0 SELECT STATEMENT GOAL: CHOOSE 286 SORT (ORDER BY) 286 VIEW 286 SORT (GROUP BY) 765 NESTED LOOPS 4 VIEW 4 COUNT (STOPKEY) 3 TABLE ACCESS (FULL) OF 'EC_COURSE' 765 VIEW 765 SORT (GROUP BY) 4381 TABLE ACCESS (BY INDEX ROWID) OF 'EC_APPLY' 10077 INDEX (RANGE SCAN) OF 'EC_APPLY_PK' (UNIQUE)

19.(코드+년도+월)별,(코드)별,전체총액을 한번에 개선 사항 전반적으로 보면 간결한 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('20000101','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE('20011231','YYYYMMDD') + 1 GROUP BY ROLLUP(COURSE_CODE,(YEAR,TO_CHAR(APPLY_DATE,'MM'))); 8i에서는 ROLLUP(COURSE_CODE,(YEAR,TO_CHAR(APPLY_DATE,'MM'))); 이렇게 사용 못하고 ROLLUP(COURSE_CODE, YEAR,TO_CHAR(APPLY_DATE,'MM')); 이렇게 사용할 수 있음 결과는 필요 없는 부분이 나옴, 이부분을 제외하고 있는 기능을 구현한 것이 9i임

19.(코드+년도+월)별,(코드)별,전체총액을 한번에 과정별 소계 부분을 먼저 처리할 수 있는 SQL의 구현 과정코드 년도 월 입금총액 14 소계 128000000 14 2000 01 500000 14 2000 02 300000 14 2000 03 1000000 14 2001 01 300000 14 2001 02 9000000 28 소계 13000000 28 2000 01 1000000 28 2000 02 7500000 28 2001 01 10000000 28 2001 02 11000000 총계 98000000 SQL SQL#19_03 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('20000101','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE('20011231','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)); 중간 생략 ●●● 중간 생략 ●●● Sort부분만 처리 할 수 있도록 수정 중간 생략 ●●● 중간 생략 ●●●

19.(코드+년도+월)별,(코드)별,전체총액을 한번에 과정별 소계 선행 처리 시 소계 부분에 대한 과정명 처리 SQL#19_04 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('20000101','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE('20011231','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(+); Outer join을 한 이유 총계를 표현하는 부분을 처리하기 위해 Outer join을 하지 않으면 총계 부분은 누락된다