SELECT
                           A.p_code
                         , A.c_code    
                          ,NVL(A.cnt1, 0) cnt1
                         , COUNT(CASE WHEN VSC.SCH_SEQ2 > 0 AND TO_CHAR(VSC.WORK_DATE   , 'WW') + 0 <= ( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL ) AND TO_CHAR(VSC.WORK_DATE   , 'YYYY')<= ( SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL ) THEN 1 END) cnt2
                         , COUNT(CASE WHEN VSC.SCH_SEQ2 > 0 AND TO_CHAR(VSC.WORK_DATE   , 'WW') + 0 =( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL )  AND TO_CHAR(VSC.WORK_DATE   , 'YYYY')= ( SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL ) THEN 1 END) cnt3
                         , COUNT(CASE WHEN VSC.SCH_SEQ3 > '0' AND TO_CHAR(VSC.GAE_DATE, 'WW') + 0 <=( SELECT TO_CHAR(SYSDATE,'WW')-1 FROM DUAL )  AND TO_CHAR(VSC.GAETONG_DATE   , 'YYYY')<= ( SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL ) THEN 1 END) cnt5
                         , COUNT(CASE WHEN VSC.SCH_SEQ3 > '0' AND TO_CHAR(VSC.GAE_DATE, 'WW') + 0 =( SELECT TO_CHAR(SYSDATE,'WW')+0 FROM DUAL ) AND TO_CHAR(VSC.GAETONG_DATE   , 'YYYY')= ( SELECT TO_CHAR(SYSDATE,'YYYY') FROM DUAL ) THEN 1 END) cnt6
                    FROM (
                                 SELECT 
                                            SUBSTR( P_CODE ,0,4) P_CODE,
                                             F_CODE(SUBSTR( P_CODE ,0,4)) pCodeNm,
                                            C_CODE_NM, 
                                             C_CODE,
                                             SUM(cnt) cnt1
                                    FROM T_CODE 
                                        LEFT OUTER JOIN V_CON
                                           ON INST_L || '10' || SUBSTR(INST_S, 7, 2) = C_CODE            
                                    WHERE  P_CODE IN ('HW1010','HW2010','HW3010','HW4010')
                                    AND USE_YN='Y'
                                    GROUP BY P_CODE,  F_CODE(SUBSTR( P_CODE ,0,4)),C_CODE_NM,C_CODE
                        )A
                    LEFT OUTER JOIN V_WORK VSC
                       ON VSC.INST_L || '10' || SUBSTR(VSC.INST_S, 7, 2) = A.C_CODE
                     GROUP BY A.P_CODE,A.pCodeNm,A.C_CODE_NM,A.C_CODE,A.cnt1

100만건 이상인데이터 V_WORK 뷰

SELECT 'D' DIV, KN_DEPT1,KN_DEPT2, DEPT1, DEPT2, BUSI_DIV, INST_L, INST_M,  INST_S ,WORK_DATE, SCH_SEQ, SCH_SEQ2, SCH_SEQ3 , DCU_ID INST_ID,GAETONG_DATE FROM T_SCHEDULE_DC WHERE USE_YN ='Y' 
UNION ALL 
SELECT 'M' DIV, KN_DEPT1,KN_DEPT2, DEPT1, DEPT2, MIN(BUSI_DIV), REPLACE(INST_L ,'HW40','HW30') INST_L, MIN(REPLACE(INST_M,'HW40','HW30')) INST_M, MIN(REPLACE( INST_S,'HW40','HW30')) INST_S
,MIN(WORK_DATE),MIN(SCH_SEQ),MIN(SCH_SEQ2),MIN(SCH_SEQ3) ,MAC_MO,INST_ID,MIN(GAETONG_DATE)   FROM T_SCHEDULE_MODEM  WHERE USE_YN ='Y' AND MODEM_DIV='마스터' AND MAC_MO >'0' GROUP BY KN_DEPT1,KN_DEPT2, DEPT1, DEPT2,INST_L ,MAC_MO
UNION ALL
SELECT 'E' DIV, KN_DEPT1,KN_DEPT2, DEPT1, DEPT2, BUSI_DIV, INST_L, INST_M,  INST_S ,WORK_DATE, SCH_SEQ, SCH_SEQ2, SCH_SEQ3 ,INSTR_NUM  INST_ID,GAETONG_DATE FROM T_SCHEDULE_MO  WHERE USE_YN ='Y'

보통 4초이상 넘어가면안되는데 현재 14초 이상걸립니다. 뷰에 인덱스를 못거니까  해당 뷰에사용되는  T_SCHEDULE_DC ,T_SCHEDULE_MO 걸려고 하는데 어떻게 걸어야할지 ㅠ

LEFT OUTER JOIN V_WORK 조인걸때 조건기준으로 걸면되는걸로 아는데  예:  VSC.INST_L || '10' || SUBSTR(VSC.INST_S, 7, 2)   위에있는 이 조건으로요  그런데 안먹네요 ㅠ 왜그럴까요? 한번에  조건을 안걸고 INST_L,INST_S 하나로 걸어도 마찬가지로  풀스캔으로 조회해버리는데 ㅠ