안녕하세요.

작업중인 쿼리 속도 문제로 질문드립니다.

이것도 나름대로 연구하여 속도를 줄인다고 줄여봤지만,

여전히 속도문제가 있어서 더 좋은 방법을 알고 싶어서 조언을 구합니다.

데이터는 총 10000건 정도 됩니다.

아래 1번 테이블이 조회시 속도가 나오지 않는 쿼리입니다.

혹시 몰라서 View도 같이 적어뒀습니다.

감사합니다.

//------------------------------------------------------------------------------------------------------------------------------------------------------

1. 사용중인 조회쿼리

SELECT
        c.COMPANY_IDX
        ,c.NAME
        , c.COMPANY_NUM1
        , c.COMPANY_NUM2
        , c.CEO_NAME
        , (SELECT CODE_VALUE FROM V_CODE_ACTIVE V1 WHERE V1.CODE_IDX = (SELECT PARENT_CODE_IDX FROM V_CODE_ACTIVE V2 WHERE V2.CODE_VALUE = c.CLUSTER_VALUE))AS CLUSTER_VALUE1
        , c.CLUSTER_VALUE AS CLUSTER_VALUE2
        , c.FOUNDATION_DATE
        , c.OFFICE_ZIP_CODE
        , c.OFFICE_ADDRESS1
        , c.OFFICE_ADDRESS2
        , c.OFFICE_PHONE
        , c.OFFICE_FAX
        , c.CENTER_IDX
        , (SELECT LOCATION FROM TBL_CENTER WHERE c.CENTER_IDX = CENTER_IDX) as LOCATION
        , c.INDUSTRY
        , c.PRODUCT
        , c.INDUSTRY_DEPART_NAME1
        , c.INDUSTRY_DEPART_NAME2
        , c.INDUSTRY_DEPART_NAME3
        , c.INDUSTRY_DEPART_NAME4
        , 
          (SELECT CODE_TEXT FROM TBL_CODE WHERE CODE_VALUE = c.INDUSTRY_DEPART_NAME4) as industry_complex
        , c.HOMEPAGE
        , TO_CHAR(c.REGIST_DATE, 'yyyy-mm-dd') as REGIST_DATE
        , c.REGIST_MEMBER_IDX
                , (SELECT name FROM TBL_MEMBER m WHERE c.REGIST_MEMBER_IDX = m.MEMBER_IDX)AS REGIST_MEMBER_NAME
        , TO_CHAR(c.UPDATE_DATE, 'yyyy-mm-dd') as UPDATE_DATE
        , c.UPDATE_MEMBER_IDX
        , c.IS_DEL
        , c.PM_DOCTOR_MEMBER_IDX
        , c.MANAGEMENT_DOCTOR_MEMBER_IDX
        , c.FINANCE_DOCTOR_MEMBER_IDX
        , c.TECHNOLOGY_DOCTOR_MEMBER_IDX
        , ( (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.PM_DOCTOR_MEMBER_IDX) || ', ' ||
            (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.MANAGEMENT_DOCTOR_MEMBER_IDX) || ', ' ||
            (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.FINANCE_DOCTOR_MEMBER_IDX) || ', ' ||
            (SELECT NAME FROM TBL_MEMBER WHERE MEMBER_IDX = c.TECHNOLOGY_DOCTOR_MEMBER_IDX) ) AS DOCTOR_NAME
            
        , (SELECT name FROM TBL_MEMBER m WHERE c.PM_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS PM_DOCTOR_MEMBER_NAME
        , (SELECT name FROM TBL_MEMBER m WHERE c.MANAGEMENT_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS MANAGEMENT_DOCTOR_MEMBER_NAME
        , (SELECT name FROM TBL_MEMBER m WHERE c.FINANCE_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS FINANCE_DOCTOR_MEMBER_NAME
        , (SELECT name FROM TBL_MEMBER m WHERE c.TECHNOLOGY_DOCTOR_MEMBER_IDX = m.MEMBER_IDX)AS TECHNOLOGY_DOCTOR_MEMBER_NAME
        
        , c.COMPANY_SCALE_TYPE
        , c.COMPANY_TYPE
        , (SELECT CODE_VALUE FROM V_CODE_ACTIVE V1 WHERE V1.CODE_IDX = (SELECT PARENT_CODE_IDX FROM V_CODE_ACTIVE V2 WHERE V2.CODE_VALUE = c.INDUSTRY_VALUE))AS INDUSTRY_VALUE1
        , c.INDUSTRY_VALUE AS INDUSTRY_VALUE2
        , c.INDUSTRY_ETC_VALUE
        , (SELECT TO_DATE(MAX(CHOICE_DATE), 'yyyy-mm-dd')
              FROM TBL_COMPANY_PROMOTE_HIST
             WHERE PROMOTE_TYPE = 'IA'
               AND COMPANY_IDX = c.COMPANY_IDX
             GROUP BY COMPANY_IDX) as PROMOTE_DATE
        , (SELECT ADD_MONTHS(MAX(CHOICE_DATE), 36) -1
              FROM TBL_COMPANY_PROMOTE_HIST
             WHERE PROMOTE_TYPE = 'IA'
               AND COMPANY_IDX = c.COMPANY_IDX
             GROUP BY COMPANY_IDX)as SCHEDULED_END_PROMOTE_DATE
        , (SELECT TO_DATE(MAX(CHOICE_DATE), 'yyyy-mm-dd')
              FROM TBL_COMPANY_PROMOTE_HIST
             WHERE PROMOTE_TYPE IN ('GA')
               AND COMPANY_IDX = c.COMPANY_IDX
             GROUP BY COMPANY_IDX)AS GRADUATE_DATE
        , i.SELL_1_YEAR
        , i.SELL_2_YEAR
        , i.SELL_3_YEAR
        , i.SELL_1_PRICE
        , i.SELL_2_PRICE
        , i.SELL_3_PRICE
        , i.MAKE_1_YEAR
        , i.MAKE_2_YEAR
        , i.MAKE_3_YEAR
        , i.MAKE_1_PRICE
        , i.MAKE_2_PRICE
        , i.MAKE_3_PRICE
        , i.OUT_1_YEAR
        , i.OUT_2_YEAR
        , i.OUT_3_YEAR
        , i.OUT_1_PRICE
        , i.OUT_2_PRICE
        , i.OUT_3_PRICE
        , i.EMP_1_YEAR
        , i.EMP_2_YEAR
        , i.EMP_3_YEAR
        , i.EMP_1_NUMBER
        , i.EMP_2_NUMBER
        , i.EMP_3_NUMBER
        , i.PROFIT_1_YEAR
        , i.PROFIT_2_YEAR
        , i.PROFIT_3_YEAR
        , i.PROFIT_1_PRICE
        , i.PROFIT_2_PRICE
        , i.PROFIT_3_PRICE
        , i.PATENT_1_YEAR
        , i.PATENT_2_YEAR
        , i.PATENT_3_YEAR
        , i.PATENT_1_NUMBER
        , i.PATENT_2_NUMBER
        , i.PATENT_3_NUMBER
        , i.RESEARCH_1_YEAR
        , i.RESEARCH_2_YEAR
        , i.RESEARCH_3_YEAR
        , i.RESEARCH_1_NUMBER
        , i.RESEARCH_2_NUMBER
        , i.RESEARCH_3_NUMBER
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type1'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as LONG_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type2'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as MISSION_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type3'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as CONSULT_CNT
        , (SELECT COUNT(*)
            FROM TBL_PROJECTMANAGER
            WHERE PROJECT_TYPE = 'promote_type4'
            AND RUN_STATE = 0
            AND COMPANY_IDX = c.COMPANY_IDX) as SHORT_CNT

    , (SELECT
        MAX(CHOICE_DATE) CHOICE_DATE
      from TBL_company_promote_hist cph
      where promote_type = 'GA'
        AND cph.company_idx = c.company_idx
        AND NOT EXISTS ( SELECT
                              1
                          FROM TBL_COMPANY_PROMOTE_HIST
                          WHERE cph.company_idx = company_idx
                            and promote_type = 'GU')
        GROUP BY COMPANY_IDX, CHOICE_DATE) as CHOICE_DATE
FROM tbl_company c
   , TBL_COMPANY_ADD_PRICE_INFO i
WHERE c.COMPANY_IDX = i.COMPANY_IDX(+)
;


2. 참조하는 View

CREATE OR REPLACE FORCE VIEW V_CODE_ACTIVE
( CODE_IDX, PARENT_CODE_IDX, CODE_VALUE, CODE_TEXT, 
  REG_DATE, IS_DEL, DISPLAY_ORDER_BY, USE_YN )
AS
SELECT "CODE_IDX","PARENT_CODE_IDX","CODE_VALUE","CODE_TEXT","REG_DATE","IS_DEL","DISPLAY_ORDER_BY","USE_YN" FROM tbl_code
WHERE IS_DEL = 0
;