database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
MS-SQL
ㆍDB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
DB2 Q&A 3412 게시물 읽기
No. 3412
쿼리문 실행하면 4분이 걸립니다.
작성자
궁그미
작성일
2015-05-28 08:58
조회수
4,352

원래 안그러던 데이타가 갑자기 그러는건 왜 그럴까요?

쿼리문은 기존에 사용되던 쿼리인데 문제가 뭘까요?

문제는 데이타가 없는 년도를 돌려도 마찬가지로 3분넘게 걸립니다.

 

SELECT
            SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '01'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR01
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '01'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR01RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '02'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR02
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '02'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR02RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '03'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR03
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '03'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR03RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '04'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR04
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '04'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR04RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '05'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR05
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '05'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR05RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '06'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR06
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '06'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR06RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '07'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR07
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '07'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR07RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '08'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR08
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '08'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR08RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '09'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR09
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '09'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR09RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '10'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR10
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '10'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR10RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '11'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR11
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '11'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR11RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '12'
                       AND TEST_ITEM IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS SAR12
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '12'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'SAR'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS SAR12RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '01'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC01
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '01'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC01RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '02'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC02
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '02'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC02RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '03'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC03
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '03'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC03RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '04'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC04
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '04'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC04RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '05'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC05
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '05'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC05RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '06'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC06
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '06'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC06RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '07'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC07
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '07'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC07RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '08'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC08
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '08'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC08RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '09'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC09
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '09'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC09RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '10'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC10
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '10'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC10RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '11'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC11
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '11'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC11RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '12'
                       AND TEST_ITEM NOT IN ('S', 'H'))
                THEN 1 ELSE 0 END) AS EMC12
           ,(SELECT COUNT(1)
             FROM
                 TB_REGULAR_TESTITEM  TRT
                ,TB_REGULAR_MASTER    TRM
                ,TB_EVENT_SCHEDULE    TES
             WHERE 1=1
             AND TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
                                                 = SUBSTR('2015-01-01', 1, 4) || '12'
             AND TRT.REG_NO                   LIKE 'PM%'
             AND TRT.LAB_ID                      = 'EMC'
             AND TRT.REG_NO                      = TRM.REG_NO
             AND TRT.TEST_ITEM                   = TES.TEST_ITEM
             AND TRM.MODEL                      IN TES.MODEL
             AND TRM.OPERATION_FACTORY           = TES.OPERATION_FACTORY
             AND TO_CHAR(TES.SCHEDULE_DATE,'YYYYMM')
                                                 = TO_CHAR(TRM.REQUEST_DATE,'YYYYMM')
             AND TES.EVENT                        = 'M'
             AND TES.EVENT_TYPE                   = 'P'
             AND (SUBSTR(TES.REMARK, 1, 6)        = '계획' OR
                  SUBSTR(TES.REMARK, 1, 12)       = '신규추가')
             AND SUBSTR(TES.REMARK, 1, 12)       != '계획삭제') AS EMC12RESULT
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '01')
                THEN 1 ELSE 0 END) AS TOTAL01
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '02')
                THEN 1 ELSE 0 END) AS TOTAL02
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '03')
                THEN 1 ELSE 0 END) AS TOTAL03
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '04')
                THEN 1 ELSE 0 END) AS TOTAL04
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '05')
                THEN 1 ELSE 0 END) AS TOTAL05
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '06')
                THEN 1 ELSE 0 END) AS TOTAL06
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '07')
                THEN 1 ELSE 0 END) AS TOTAL07
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '08')
                THEN 1 ELSE 0 END) AS TOTAL08
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '09')
                THEN 1 ELSE 0 END) AS TOTAL09
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '10')
                THEN 1 ELSE 0 END) AS TOTAL10
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '11')
                THEN 1 ELSE 0 END) AS TOTAL11
           ,SUM(CASE WHEN (TO_CHAR(S.SCHEDULE_DATE,'YYYYMM') = SUBSTR('2015-01-01', 1, 4) || '12')
                THEN 1 ELSE 0 END) AS TOTAL12
        FROM TB_EVENT_SCHEDULE S
        WHERE 1=1
        AND S.OPERATION_FACTORY             = 'KO_KR'
        AND TO_CHAR(S.SCHEDULE_DATE,'YYYY') = SUBSTR('2015-01-01', 1, 4)
        AND S.EVENT                         = 'M'
        AND S.EVENT_TYPE                    = 'P'
        AND (SUBSTR(S.REMARK, 1, 6)         = '계획' OR
             SUBSTR(S.REMARK, 1, 12)        = '신규추가')
        AND SUBSTR(S.REMARK, 1, 12)        != '계획삭제'

이 글에 대한 댓글이 총 1건 있습니다.

쿼리 플랜을 보셔서 풀스캔이 발생하는 등 비효율적인 구간을 찾아보시는게 좋을 것 같습니다.

정상규(pajama)님이 2015-05-28 15:51에 작성한 댓글입니다.
[Top]
No.
제목
작성자
작성일
조회
3415한글깨짐현상
JJYSDF
2015-06-22
4124
3414DB2 for i Series에서 DB2 for LUW 데이터 마이그레이션 문의
db2
2015-06-16
3835
3413db2 쿼리 문의요 [1]
db2궁금
2015-05-28
5523
3412쿼리문 실행하면 4분이 걸립니다. [1]
궁그미
2015-05-28
4352
3411DB2 쿼리 질문
박상수
2015-04-20
4222
3410서브쿼리 -> 조인으로 변환 질문 [1]
dbghktjr
2015-04-07
4342
3409질문좀 드립니다. [1]
궁금이
2015-03-05
3839
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다