조회쿼리 시간단축

* 질문 등록 시 다음의 내용을 꼭 기입하여 주세요.

OS	Window7 32bit, Linux 64bit 등
CUBRID Ver.	[cubrid_rel] 수행 결과
CUBRID TOOL Ver.	[도움말]-[버전정보] 확인
응용 환경(API)	java, php, odbc 등 입력

* CUBRID 응용 오류, SQL 오류 또는 SQL 튜닝 관련된 문의는 반드시 다음의 내용을 추가해 주세요. 비밀글이나 비밀 댓글도 가능합니다.
* 저희가 상황을 이해하고, 재현이 가능해야 알 수 있는 문제들이 많습니다. 가능한 정보/정황들을 부탁합니다.

 

에러 내용 및 재현 방법	재현 가능한 Source와 SQL
관련 테이블(인덱스, 키정보 포함) 정보	CUBRID 홈 디렉토리 아래 log 디렉토리 압축

-------------- 아래에 질문 사항을 기입해 주세요. ------------------------------------------------------------------------
아래의 조회 쿼리에서  개선할 방법좀 알려주세요

esfcckhd테이블 데이터 약 10만건

 

빨간색 쿼리 a테이블 조회건수 약29000건 11초

주황색 쿼리 x테이블 조회건수 약29000건 11초

하늘색 쿼리 y테이블 조회건수 약 1,460,000건 cross join 사용으로 데이터 증가  약 940초

전체 쿼리 조회시 약 105초 

 

전체를 조회하면 시간이 105초인데  y부분까지만 조회하면 시간이 늘어나는데 cross 조인 때문에 그런건가요?

시간을 단축시켜야하는데 솔루션 부탁드립니다.

 

  SELECT DISTINCT *
          FROM (SELECT t.SYS_ID
                      ,t.CK_NO                      /* 점검번호 */
                      ,t.MOD_LOG_CNT                /* 수정이력 회차 */
                      ,t.PERIOD_AUTO_RULE
                      ,u.IN_YR                      /* 년도 */
                      ,u.IN_MM                      /* 월 */
                      ,u.YR_WK                      /* 주차 */
                      ,u.SC_DATE                    /* 계획일자 */
                      ,t.DEFAULT_TYPE
                      ,CASE WHEN t.PERIOD_AUTO_RULE = 'D001' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-1, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'D002' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-2, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'D003' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-3, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'D005' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-5, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'D007' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-7, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'D014' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-14, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'D021' THEN TO_CHAR(TO_DATE(t.SC_DATE, 'YYYYMMDD')-21, 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'M001' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -1), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'M002' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -2), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'M003' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -3), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'M006' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -6), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'M009' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -9), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'Y001' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -12), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'Y002' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -24), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'Y003' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -36), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'Y004' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -48), 'YYYYMMDD')
                            WHEN t.PERIOD_AUTO_RULE = 'Y005' THEN TO_CHAR(ADD_MONTHS(TO_DATE(t.SC_DATE, 'YYYYMMDD'), -60), 'YYYYMMDD')
                            ELSE t.SC_DATE
                       END CREATE_DT
                  FROM (SELECT y.*
                              ,CASE WHEN y.PERIOD_TYP = 'D' OR y.PERIOD_CRE_RULE = '0' THEN s1.SC_DATE
                                    WHEN y.PERIOD_TYP = 'WK' THEN
                                         CASE WHEN y.PERIOD_CRE_RULE = '1' AND NVL(y.PERIOD_DAY, '') != '' THEN s2.SC_DATE
                                              ELSE s1.SC_DATE
                                         END
                                    ELSE CASE WHEN y.PERIOD_CRE_RULE = '1' AND  NVL(y.PERIOD_DAY, '') != '' AND NVL(y.PERIOD_WK, '') != '' THEN s3.SC_DATE
                                              ELSE s1.SC_DATE
                                         END
                               END SC_DATE
                          FROM (SELECT x.*
                                      ,TO_DATE(x.DEFAULT_DATE, 'YYYYMMDD') DEFAULT_DATE2
                                      ,CASE WHEN x.DEFAULT_TYPE = 'A' THEN -1 ELSE 0 END DEFAULT_CHECK
                                      ,(ABS(TO_NUMBER(TO_CHAR(SYSDATE, 'YYYY'))-TO_NUMBER(TO_CHAR(TO_DATE(x.DEFAULT_DATE, 'YYYYMMDD'), 'YYYY'))) +3) *
                                       CASE WHEN x.PERIOD_TYP = 'WK' THEN (53 / x.PERIOD_CYCLE)
                                            WHEN x.PERIOD_TYP = 'MON' THEN (12 / x.PERIOD_CYCLE)
                                            WHEN x.PERIOD_TYP = 'QRT' THEN 5
                                            WHEN x.PERIOD_TYP = 'HLF' THEN 3
                                            WHEN x.PERIOD_TYP = 'YR' THEN x.PERIOD_CYCLE
                                            WHEN x.PERIOD_TYP = 'D' THEN (365/x.PERIOD_CYCLE)
                                       END INTERVAL_CNT
                                  FROM (SELECT a.SYS_ID
                                              ,a.CK_NO
                                              ,a.MOD_LOG_CNT
                                              ,a.PERIOD_CRE_RULE
                                              ,a.PERIOD_CYCLE
                                              ,a.PERIOD_TYP
                                              ,a.PERIOD_AUTO_RULE
                                              ,CASE WHEN a.PERIOD_TYP = 'D' THEN a.PERIOD_CYCLE
                                                    WHEN a.PERIOD_TYP = 'WK' THEN 7*a.PERIOD_CYCLE
                                                    WHEN a.PERIOD_TYP = 'MON' THEN
                                                         CASE WHEN  a.PERIOD_CRE_RULE = '1' THEN a.PERIOD_CYCLE
                                                              ELSE 30*a.PERIOD_CYCLE
                                                         END
                                                    WHEN a.PERIOD_TYP = 'QRT' THEN
                                                         CASE WHEN  a.PERIOD_CRE_RULE = '1' THEN 3
                                                              ELSE 90*a.PERIOD_CYCLE
                                                         END
                                                    WHEN a.PERIOD_TYP = 'HLF' THEN
                                                         CASE WHEN  a.PERIOD_CRE_RULE = '1' THEN 6
                                                              ELSE 180*a.PERIOD_CYCLE
                                                         END
                                                    WHEN a.PERIOD_TYP = 'YR' THEN
                                                         CASE WHEN  a.PERIOD_CRE_RULE = '1' THEN 12*a.PERIOD_CYCLE
                                                              ELSE 360*a.PERIOD_CYCLE
                                                         END
                                               END P_DAY
                                              ,NVL((SELECT CASE WHEN MAX(s1.PLAN_DATE) IS NULL OR MAX(s1.PLAN_DATE) < a.PERIOD_BAS_DATE THEN a.PERIOD_BAS_DATE
                                                                ELSE MAX(s1.PLAN_DATE)
                                                           END
                                                      FROM ESFCCKSC s1
                                                     WHERE s1.SYS_ID = a.SYS_ID
                                                       AND s1.STS IN ('C', 'U')
                                                       AND s1.CK_NO = a.CK_NO), a.PERIOD_BAS_DATE) DEFAULT_DATE
                                              ,NVL((SELECT CASE WHEN MAX(s1.PLAN_DATE) IS NULL OR MAX(s1.PLAN_DATE) < a.PERIOD_BAS_DATE THEN 'A'
                                                                ELSE 'B'
                                                           END
                                                      FROM ESFCCKSC s1
                                                     WHERE s1.SYS_ID = a.SYS_ID
                                                       AND s1.STS IN ('C', 'U')
                                                       AND s1.CK_NO = a.CK_NO), 'A') DEFAULT_TYPE
                                              ,a.PERIOD_WK
                                              ,CASE WHEN a.PERIOD_DAY = '1' THEN '2'
                                                    WHEN a.PERIOD_DAY = '2' THEN '3'
                                                    WHEN a.PERIOD_DAY = '3' THEN '4'
                                                    WHEN a.PERIOD_DAY = '4' THEN '5'
                                                    WHEN a.PERIOD_DAY = '5' THEN '6'
                                                    WHEN a.PERIOD_DAY = '6' THEN '7'
                                                    WHEN a.PERIOD_DAY = '7' THEN '1'
                                               END PERIOD_DAY /* 요일 보정 */
                                          FROM ESFCCKHD a
                                         WHERE a.SYS_ID = 'IIAC'
                                           AND a.NEW_YN = 'Y'
                                           AND a.MASTER_STS = 'USY'
                                           AND a.CK_TYP = 'DAI'
                                           AND a.STS IN ('C', 'U')
                                           --AND a.PERIOD_TYP IN ('WK', 'MON', 'QRT', 'HLF', 'YR')
                                         
                                       ) x) y
                    CROSS JOIN (SELECT LEVEL LVL
                                  FROM DB_ROOT
                                CONNECT BY LEVEL < y.INTERVAL_CNT) z
               LEFT OUTER JOIN ESFCSCDT s1
                            ON s1.SYS_ID = y.SYS_ID
                           --AND s1.IN_YR  = TO_CHAR(SYSDATE, 'YYYY')
                           AND s1.SC_DATE = CASE WHEN y.PERIOD_CRE_RULE = '1' AND y.PERIOD_TYP IN ('MON', 'QRT', 'HLF', 'YR') AND y.PERIOD_WK > '' AND y.PERIOD_DAY > '' THEN NULL
                                                 WHEN y.PERIOD_CRE_RULE = '1' AND y.PERIOD_TYP IN ('MON', 'QRT', 'HLF', 'YR') AND (NVL(y.PERIOD_WK, '') = '' OR NVL(y.PERIOD_DAY, '') = '') THEN
                                                      TO_CHAR(ADD_MONTHS(y.DEFAULT_DATE2, (y.P_DAY * (z.LVL+y.DEFAULT_CHECK))), 'YYYYMMDD')
                                                 ELSE TO_CHAR(y.DEFAULT_DATE2 + (y.P_DAY * (z.LVL+y.DEFAULT_CHECK)), 'YYYYMMDD')
                                            END
               LEFT OUTER JOIN ESFCSCDT s2
                            ON s2.SYS_ID  = y.SYS_ID
                           AND s2.YR_WK  = s1.YR_WK
                           AND s2.IN_YR  = s1.IN_YR
                           --AND s2.IN_YR  = TO_CHAR(SYSDATE, 'YYYY')
                           AND s2.DAY_WK = y.PERIOD_DAY
               LEFT OUTER JOIN ESFCSCDT s3
                            ON s3.SYS_ID  = y.SYS_ID
                           AND s3.IN_YR  = CASE WHEN y.PERIOD_TYP IN ('MON', 'QRT', 'HLF', 'YR') AND y.PERIOD_CRE_RULE = '1' AND y.PERIOD_WK > '' AND y.PERIOD_DAY > '' THEN
                                                     TO_CHAR(ADD_MONTHS(y.DEFAULT_DATE2, (y.P_DAY * (z.LVL+y.DEFAULT_CHECK))), 'YYYY')
                                                ELSE NULL
                                           END
                           AND s3.IN_MM  = TO_CHAR(ADD_MONTHS(y.DEFAULT_DATE2, (y.P_DAY * (z.LVL+y.DEFAULT_CHECK))), 'MM')
                           --AND s3.IN_YR  = TO_CHAR(SYSDATE, 'YYYY')
                           AND s3.DAY_WK = y.PERIOD_DAY
                           AND s3.MM_WK  = y.PERIOD_WK
                         WHERE CASE WHEN y.PERIOD_TYP = 'D' THEN s1.SC_DATE
                                    WHEN y.PERIOD_TYP = 'WK' THEN
                                         CASE WHEN y.PERIOD_CRE_RULE = '1' AND NVL(y.PERIOD_DAY, '') != '' THEN s2.SC_DATE
                                              ELSE s1.SC_DATE
                                         END
                                    ELSE CASE WHEN y.PERIOD_CRE_RULE = '1' AND  NVL(y.PERIOD_DAY, '') != '' AND NVL(y.PERIOD_WK, '') != '' THEN s3.SC_DATE
                                              ELSE s1.SC_DATE
                                    END
                               END IS NOT NULL) t
            INNER JOIN ESFCSCDT u
                    ON u.SYS_ID = 'IIAC'
                   AND u.SC_DATE = t.SC_DATE
                   AND u.SC_DATE >= t.DEFAULT_DATE) q
         
         WHERE '1' = CASE WHEN q.DEFAULT_TYPE = 'A' AND q.CREATE_DT <= TO_CHAR(SYSDATE, 'YYYYMMDD') THEN '1'
                          WHEN q.DEFAULT_TYPE = 'B' AND q.CREATE_DT = TO_CHAR(SYSDATE, 'YYYYMMDD') THEN '1'
                          ELSE '2'
                     END
             
           AND NOT EXISTS (SELECT 1
                             FROM ESFCCKSC s1
                            WHERE s1.SYS_ID = q.SYS_ID
                              AND s1.CK_NO  = q.CK_NO
                              AND s1.FST_PLAN_DATE = q.SC_DATE
                              AND s1.STS IN ('C', 'U'))