19.(코드+년도+월)별,(코드)별,전체총액을 한번에

Presentation on theme: "19.(코드+년도+월)별,(코드)별,전체총액을 한번에"— Presentation transcript:

1 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로 구현해서 처리하기에는 현실적으로 어렵다고 생각하는 것이 보통입니다. 本 사례에서는 이러한 유형의 사례 중 하나를 제시하면서 어떻게 처리해야 속도향상을 얻을 수 있는지를 찾아 보고자 합니다. 중간 생략 ●●● 중간 생략 ●●●

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

3 19.(코드+년도+월)별,(코드)별,전체총액을 한번에
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)

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

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

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

7 19.(코드+년도+월)별,(코드)별,전체총액을 한번에
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)

8 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(' ','YYYYMMDD') AND APPLY_DATE + 0 < TO_DATE(' ','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임

9 19.(코드+년도+월)별,(코드)별,전체총액을 한번에
과정별 소계 부분을 먼저 처리할 수 있는 SQL의 구현 과정코드 년도 월 입금총액 소계 소계 총계 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(' ','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)); 중간 생략 ●●● 중간 생략 ●●● Sort부분만 처리 할 수 있도록 수정 중간 생략 ●●● 중간 생략 ●●●

10 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(' ','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(+); Outer join을 한 이유 총계를 표현하는 부분을 처리하기 위해 Outer join을 하지 않으면 총계 부분은 누락된다