꿈꾸는 개발자, DBA 커뮤니티 구루비

튜닝 질문 드립니다 ㅠ

by 쿨에어 [SQL Query] 오라클 11g [2018.08.20 10:21:00]

질의결과1.png (8,721Bytes)

SQL 기초 지식이 없다보니 ㅜㅜ 쿼리 속도가 너무 느립니다.

각 점검종류(정기, 정밀, 정밀안전진단, 수시) 의 통계 (점검계획수, 점검수, 최근점검일) 을 뽑고 싶은데요....

10초정도 걸립니다 ㅜㅜ 도움 부탁드립니다...

SELECT '정기점검' AS TITLE, '01' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT, SUM(CHK_CNT) AS CHK_CNT, MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
        SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN)  A
        WHERE CHCK_KND_CD IN ('100', 'C0002') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD IN ('100', 'C0002') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD IN ('100', 'C0002') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
) UNION
-- 정밀점검
SELECT '정밀점검' AS TITLE, '02' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT ,SUM(CHK_CNT) AS CHK_CNT ,MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
        SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN)  A
        WHERE CHCK_KND_CD IN ('202', 'C0007') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD IN ('202', 'C0007') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD IN ('202', 'C0007') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
) UNION
-- 정밀안전진단
SELECT '정밀안전진단' AS TITLE, '03' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT ,SUM(CHK_CNT) AS CHK_CNT ,MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
        SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN)  A
        WHERE CHCK_KND_CD IN ('401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD IN ('401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD IN ('401', '402', '403', 'C0004') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
) UNION
-- 수시점검
SELECT '수시점검' AS TITLE, '04' AS SORDER, SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT ,SUM(CHK_CNT) AS CHK_CNT ,MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
        SELECT COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT , 0 AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN)  A
        WHERE CHCK_KND_CD NOT IN ('100', 'C0002', '202', 'C0007', '401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT , COUNT(SAFE_CHK_NO) AS CHK_CNT , '' AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD NOT IN ('100', 'C0002', '202', 'C0007', '401', '402', '403', 'C0004') AND CHCK_DE BETWEEN '20180601' AND '20180629'
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
                UNION
        SELECT 0 AS CHK_PLAN_CNT ,0 AS CHK_CNT , MAX(CHCK_DE) AS MAX_CHCK_DE
        FROM (SELECT SAFE_CHK_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK)  A
        WHERE CHCK_KND_CD NOT IN ('100', 'C0002', '202', 'C0007', '401', '402', '403', 'C0004') AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
        AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
)
ORDER BY SORDER;

by 소주쵝오 [2018.08.20 10:59:26]

union 을 union all 로 변경해보세요~

by 쿨에어 [2018.08.20 11:13:59]

자문자답입니다. ㅎ 글 올리고 게시판 뒤지다가 답을 얻었습니다.

SELECT CHK_KND_CD, 
        DECODE(CHK_KND_CD, 1, '정기점검', 2, '정밀점검', 3, '정밀안전진단', 4, '수시점검') AS TITLE,
        SUM(CHK_PLAN_CNT) AS CHK_PLAN_CNT, SUM(CHK_CNT) AS CHK_CNT, MAX(MAX_CHCK_DE) AS MAX_CHCK_DE
FROM (
    SELECT CHK_KND_CD, COUNT(CHK_PLN_NO) AS CHK_PLAN_CNT, 0 AS CHK_CNT, '' AS MAX_CHCK_DE
    FROM (SELECT CHK_PLN_NO, FACIL_NO, 
            CASE WHEN CHCK_KND_CD IN ('100', 'C0002') THEN 1
                 WHEN CHCK_KND_CD IN ('202', 'C0007') THEN 2
                 WHEN CHCK_KND_CD IN ('401', '402', '403', 'C0004') THEN 3
                 ELSE 4
            END CHK_KND_CD
            , CHCK_DE FROM SCK_CHK_PLN) A
    WHERE 1=1
          AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
          AND CHCK_DE BETWEEN '20180601' AND '20180629'
    GROUP BY CHK_KND_CD
    UNION ALL
-- 점검
    SELECT CHK_KND_CD, 0 AS CHK_PLAN_CNT, COUNT(SAFE_CHK_NO) AS CHK_CNT, '' AS MAX_CHCK_DE
    FROM (SELECT SAFE_CHK_NO, FACIL_NO, 
            CASE WHEN CHCK_KND_CD IN ('100', 'C0002') THEN 1
                 WHEN CHCK_KND_CD IN ('202', 'C0007') THEN 2
                 WHEN CHCK_KND_CD IN ('401', '402', '403', 'C0004') THEN 3
                 ELSE 4
            END CHK_KND_CD
            , CHCK_DE FROM SCK_CHK) A
    WHERE 1=1
          AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
          AND CHCK_DE BETWEEN '20180601' AND '20180629'
    GROUP BY CHK_KND_CD
    UNION ALL
-- 최근점검일
    SELECT CHK_KND_CD, 0 AS CHK_PLAN_CNT, 0 AS CHK_CNT, MAX(CHCK_DE) AS MAX_CHCK_DE
    FROM (SELECT SAFE_CHK_NO, FACIL_NO, 
            CASE WHEN CHCK_KND_CD IN ('100', 'C0002') THEN 1
                 WHEN CHCK_KND_CD IN ('202', 'C0007') THEN 2
                 WHEN CHCK_KND_CD IN ('401', '402', '403', 'C0004') THEN 3
                 ELSE 4
            END CHK_KND_CD
            , CHCK_DE FROM SCK_CHK) A
    WHERE 1=1
          AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')
          AND CHCK_DE <= TO_CHAR(SYSDATE, 'YYYYMMDD')
    GROUP BY CHK_KND_CD
)
GROUP BY CHK_KND_CD
ORDER BY CHK_KND_CD;

by 우리집아찌 [2018.08.20 11:21:32]

   /*   밑의 SQL은 예를 듭겁니다.
        같은 테이블은 한번만 읽게해주세요.
         
        아래의 조건으로 나누시거나
        그룹핑할수 있으시면 GROUP BY 사용하시면 됩니다. ( 안되면 WITH 문으로 정의하셔도 됩니다)
        CHCK_KND_CD IN ('100', 'C0002') -- 정기점검            
        CHCK_KND_CD IN ('202', 'C0007') -- 정밀점검
        CHCK_KND_CD IN ('401', '402', '403', 'C0004') -- 정밀안전진단
   */
 
SELECT COUNT(CASE WHEN CHK_PLN_NO IN ('100', 'C0002') THEN 1 END ) AS CHK_PLAN_CNT  -- 정기점검  
     , COUNT(CASE WHEN CHK_PLN_NO IN ('202', 'C0007') THEN 1 END ) AS CHK_PLAN_CNT  -- 정기점검  
     , COUNT(CASE WHEN CHK_PLN_NO IN ('401', '402', '403', 'C0004') THEN 1 END ) AS CHK_PLAN_CNT  -- 정기점검  
     , 0 AS CHK_CNT 
     , '' AS MAX_CHCK_DE
  FROM (SELECT CHK_PLN_NO, FACIL_NO, CHCK_KND_CD, CHCK_DE FROM SCK_CHK_PLN)  A
 WHERE CHCK_DE BETWEEN '20180601' AND '20180629'
   AND CHCK_KND_CD IN ('100', 'C0002','202', 'C0007','401', '402', '403', 'C0004' )
   AND FACIL_NO IN (SELECT FACIL_NO FROM SCK_MASTER WHERE SIGUNGU_CD = '11500')

by 마농 [2018.08.20 12:40:16]

SELECT DECODE(sorder, 1, '정기점검', 2, '정밀점검', 3, '정밀안전진단', 4, '수시점검') title
     , sorder
     , COUNT(chk_plan_cnt) chk_plan_cnt
     , COUNT(chk_cnt) chk_cnt
     , MAX(chck_de) chck_de
  FROM (SELECT CASE WHEN chck_knd_cd IN ('100', 'C0002')               THEN 1
                    WHEN chck_knd_cd IN ('202', 'C0007')               THEN 2
                    WHEN chck_knd_cd IN ('401', '402', '403', 'C0004') THEN 3
                    ELSE 4 END sorder
             , 1    chk_plan_cnt
             , null chk_cnt
             , null chck_de
          FROM sck_chk_pln
         WHERE facil_no IN (SELECT facil_no FROM sck_master WHERE sigungu_cd = '11500')
           AND chck_de BETWEEN '20180601' AND '20180629'
           AND safe_chk_no IS NOT NULL
         UNION ALL
        SELECT CASE WHEN chck_knd_cd IN ('100', 'C0002')               THEN 1
                    WHEN chck_knd_cd IN ('202', 'C0007')               THEN 2
                    WHEN chck_knd_cd IN ('401', '402', '403', 'C0004') THEN 3
                    ELSE 4 END sorder
             , null chk_plan_cnt
             , CASE WHEN chck_de BETWEEN '20180601' AND '20180629' THEN 1 END chk_cnt
             , chck_de
          FROM sck_chk
         WHERE facil_no IN (SELECT facil_no FROM sck_master WHERE sigungu_cd = '11500')
           AND chck_de <= TO_CHAR(sysdate, 'yyyymmdd')
           AND safe_chk_no IS NOT NULL
        )
 GROUP BY sorder
 ORDER BY sorder
;

by 쿨에어 [2018.08.20 14:18:58]

답변 감사합니다. 많이 배우고 있습니다 ^^

댓글등록

SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code(

) 버튼을 클릭하여 작성 하시면 됩니다.

Rich Text Editor, commentnote

Editor toolbars

Press ALT 0 for help

◢Elements path

로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입

데이터베이스 질문과 답변