대용량 데이터베이스 솔루션 1권 #11 SQL 수정 예제

1. 아래의 SQL문을 수정하시오.

  조건 1. 같은 Data를 D/B에서 2번 읽지 않도록 수정

SELECT DISTINCT
       T.NUM,
       T.발행일자,
       T.총액,
       TO_CHAR(T.고객_NUM),
       T.고객명,
  FROM 세금계산 T, 세금계산_지로집금 TG, 지로집금 G, 고객 C
 WHERE T.NUM = TG.세금계산_NUM
   AND TG.지로집금_NUM = G.NUM
   AND G.일자 BETWEEN '&&I_FROM_DATE' AND '&&I_TO_DATE'
   AND T.CHOICE = '고객'
   AND T.고객_NUM = C.NUM
   AND C.지로대상여부 = 'Y'
   AND C.청구사원_NUM = '&&I_SABUN'
 UNION ALL
SELECT T.NUM,
       T.발행일자,
       T.총액,
       T.대리점_NUM || TO_CHAR(T.기타업체_NUM),
       T.고객명,
  FROM 세금계산 T, 세금계산_지로집금 TG, 지로집금 G
 WHERE T.NUM = TG.세금계산_NUM
   AND TG.지로집금_NUM = G.NUM
   AND T.CHOICE <> '고객'
   AND G.일자 BETWEEN '&&I_FROM_DATE' AND '&&I_TO_DATE'

   힌트 1. UNION 의 아래 위 의 읽는 조건 중에 같은 조건이 있다.
          2. SELECT 에서 보여주는 항목은 세금계산 Table에만 있는 항목들이다.
          3. DRIVING 조건은 G.일자 를 비교하는 부분이다. DATA 량을 줄이는(SQL 성능) 가장 결정적인 조건이다.

  정답은 아래와 같다.

SELECT /*+ PUSH_SUBQ INDEX(세금계산 세금계산_PK) */
       T.NUM,
       T.발행일자,
       T.총액,
       DECODE(세금계산.CHOICE, '고객', TO_CHAR(T.고객_NUM), T.대리점_NUM || TO_CHAR(T.기타업체_NUM)) 고객_NUM,
       T.고객명
  FROM 세금계산 T
 WHERE T.NUM IN (
       SELECT /*+ USE_N1 (지로집금, 세금계산_지로집금) */
              TG.세금계산_NUM
         FROM 지로집금 G, 세금계산_지로집금.TG
        WHERE TG_.지로집금_NUM = G.NUM
          AND G.일자 BETWEEN '&&I_FROM_DATE' AND '&&I_TO_DATE' )
   AND EXISTS (
       SELECT 1
         FROM 고객 C
        WHERE T.CHOICE = '고객'
          AND C.NUM = T.고객_NUM
          AND C.지로대상여부 = 'Y'
          AND C.청구사원_NUM = '&&I_SABUN'
        UNION ALL
       SELECT 1
         FROM DUAL
        WHERE T.CHOICE <> '고객' );

해설 1. EXISTS 부분에서 고객이 아닌 모든 경우와 해당 조건을 만족하는 고객에 대해서만 Check 한다.
해설 2. G.일자를 CHECK 하여 조건에 만족하는 G.NUM 만 뽑아서 제공한다.

2. 아래와 같은 표가 출력될수 있도록 SQL문을 작성하시오.

   조건 1. 같은 Data를 D/B에서 2번 읽지 않도록 작성
   조건 2. 회사 총계까지 나오면 100점, 안나오면 50점



  힌트 1. Data를 1번 읽어서 여러번 사용하는 방법
  힌트 2. OUTER JOIN 시 조건을 만족하는 만큼 DATA가 중복된다.

 50점짜리 SQL문 예시

SELECT D.NAME,
       SUM( DECODE( JOB, 'CLERK', SAL)) CLERK,
       SUM( DEOCDE( JOB, 'MANAGER', SAL)) MANAGER,
       SUM( DECODE( JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) ETC,
       SUM( SAL) DEPT_SAL
  FROM EMP E, DEPT D
 WHERE E.DEPTNO = D.DEPTNO
 GROUP BY D.NAME;

 문제점 1. EMP 와 DEPT를 10,000번 JOIN 하였다. -> EMP는 DEPT 별로 GROUP BY 한 후에 50건으로 줄인 후 JOIN을 해도 된다.
 문제검 2. 회사 총계가 나오지 않았다.

  100점짜리 SQL문 예시를 한 번 보자.

SELECT NVL( DEOCDE( B.NO, '1', DNAME), '총계') DNAME,
       SUM( CLERK) CLERK,
       SUM( MANAGER) MANAGER,
       SUM( ETC) ETC,
       SUM( DEPT_SAL) DEPT_SAL
  FROM (
       SELECT D.NAME, CLERK, MANAGER, ETC, DEPT_SAL
         FROM (
              SELECT DEPTNO,
                     SUM( DECODE( JOB, 'CLERK', SAL)) CLERK,
                     SUM( DEOCDE( JOB, 'MANAGER', SAL)) MANAGER,
                     SUM( DECODE( JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) ETC,
                     SUM( SAL) DEPT_SAL
                FROM EMP E
               GROUP BY DEPTNO
              ) E, DEPT D
        WHERE E.DEPTNO = D.DEPTNO
       ) A, (
       SELECT '1' NO FROM DUAL
        UNION ALL
       SELECT '2' NO FROM DUAL
       ) B
 GROUP BY DECODE( B.NO, '1', DNAME);

 해설 1. EMP 만으로 DEPT 별 SUM을 먼저 구한뒤 DEPT 와 JOIN 한다.
           50건 X 50건 JOIN 으로 작업량이 많이 줄어든다.
 해설 2. JOIN 한 결과인 50건의 TABLE을 멍텅구리 OUTER JOIN 을 이용하여 2개씩으로 복사한 뒤,
           GROUP BY를 이용하여 회사총계를 계산하였다.
           OUTER JOIN을 이용한 복사는 MEMORY I/O 이므로 성능상으로는 큰 차이가 없다.