SQL*Plus, QUERY Tuning

SQL*Plus, QUERY Tuning

/**
 * 수정전
 */
SELECT 
    B.DNAME
    , SUM(DECODE(A.JOB, 'CLERK', A.SAL)) AS CLERK
    , SUM(DECODE(A.JOB, 'MANAGER', A.SAL)) AS MANAGER
    , SUM(DECODE(A.JOB, 'CLERK', NULL, 'MANAGER', NULL, A.SAL)) AS GITA
    , SUM(SAL) AS DEPT_SAL
FROM EMP A, 
    DEPT B
WHERE A.DEPTNO = B.DEPTNO
GROUP BY B.DNAME;
/**
 * 수정후
 */
SELECT
    B.DNAME
    , CLERK
    , MANAGER
    , ETC
    , DEPT_SAL
FROM
(
    SELECT
        DEPTNO
        , SUM(DECODE(JOB, 'CLERK', SAL)) AS CLERK
        , SUM(DECODE(JOB, 'MANAGER', SAL)) AS MANAGER
        , SUM(DECODE(JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) ETC
        , SUM(SAL) DEPT_SAL
    FROM EMP A
    GROUP BY DEPTNO
) A
, DEPT  B
WHERE A.DEPTNO = B.DEPTNO;
   
/**
 * 수정후-총계 추가
 */
SELECT 
    NVL(DECODE(B.NO, '1', DNAME), '총계') DNAME
    , SUM(CLERK) AS CLERK
    , SUM(MANAGER) AS MANAGER
    , SUM(ETC) AS ETC
    , SUM(DEPT_SAL) AS DEPT_SAL
FROM
(
    SELECT
        B.DNAME
        , CLERK
        , MANAGER
        , ETC
        , DEPT_SAL
    FROM
    (
        SELECT
            DEPTNO
            , SUM(DECODE(JOB, 'CLERK', SAL)) AS CLERK
            , SUM(DECODE(JOB, 'MANAGER', SAL)) AS MANAGER
            , SUM(DECODE(JOB, 'CLERK', NULL, 'MANAGER', NULL, SAL)) ETC
            , SUM(SAL) DEPT_SAL
        FROM EMP A
        GROUP BY DEPTNO
    ) A
    , DEPT  B
    WHERE A.DEPTNO = B.DEPTNO
) A, 
(
    SELECT '1' NO FROM DUAL
    UNION ALL
    SELECT '2' NO FROM DUAL
) B
GROUP BY DECODE(B.NO, '1', DNAME);

원문 출처
Encore – 대용량 데이터베이스

Leave a Reply