안녕하세요.
데이타 건수가 2천만건 정도의 테이블이 존재하고,
5,600건 정도의 데이타를 트리구조로 변환하는데 25초 정도 소요됩니다.
변환 속도를 줄이고 싶은데요.
CHNL_SEQ,UP_CHNL_SEQ 컬럼이 각각 인덱스로 생성이 않되어 있고,결합 인덱스로도 생성이
않되어 있습니다.
****_CHNL_CONF_04 INDEX(EQP_ID,SLOT_SEQ,CRD_SEQ,PORT_SEQ,UP_CHNL_SEQ,CIR_ID)
다름 컬럼들과 같이 결합 인덱스로 선언이 되어 있습니다.
다른 인덱스에 결합 인덱스로 CHNL_SEQ가 선언이 되어 있지만 아래 Plan에는 해당 인덱스가 사용이
않되어 있네요.
제가  CHNL_SEQ,UP_CHNL_SEQ 컬럼을 각각 인덱스 또는 결합 인덱스로 만들자고 했지만 담당자는
다른 프로그램에서 해당 테이블을 많이 사용하고 있어서 영향을 미칠수 있으니 인덱스를 만들고 싶다면
그에 따른 근거를 제시하라고 하네요.
고수님들 많은 조언 부탁드립니다.
감사합니다.

Plan hash value: 3338746805
 
------------------------------------------------------------------------------------------------------
| Id  | Operation   | Name     | Rows  | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    | | 1 |    35 | 2   (0)| 00:00:01 |
|*  1 |  CONNECT BY WITH FILTERING | |    |    |     |   |
|   2 |   NESTED LOOPS | | 1 |    53 | 2   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID | ****_CHNL_CONF    | 1 |    43 | 1   (0)| 00:00:01 |
|*  4 | INDEX RANGE SCAN    | ****_CHNL_CONF_04 | 1 |    | 1   (0)| 00:00:01 |
|   5 |    TABLE ACCESS BY INDEX ROWID | ****_SIGN_CLYR    | 1 |    10 | 1   (0)| 00:00:01 |
|*  6 | INDEX UNIQUE SCAN   | ****_SIGN_CLYR    | 1 |    | 1   (0)| 00:00:01 |
|   7 |   NESTED LOOPS | | 1 |    35 | 2   (0)| 00:00:01 |
|   8 |    NESTED LOOPS     | |    |    |     |   |
|   9 | CONNECT BY PUMP     | |    |    |     |   |
|  10 | TABLE ACCESS BY INDEX ROWID| ****_CHNL_CONF    | 1 |    28 | 1   (0)| 00:00:01 |
|* 11 |   INDEX RANGE SCAN   | ****_CHNL_CONF_04 | 1 |    | 1   (0)| 00:00:01 |
|  12 |    TABLE ACCESS BY INDEX ROWID | ****_SIGN_CLYR    | 1 | 7 | 1   (0)| 00:00:01 |
|* 13 | INDEX UNIQUE SCAN   | ****_SIGN_CLYR    | 1 |    | 1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
 
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
 
   1 - SEL$42296F85
   2 - SEL$BC2A2465
   3 - SEL$BC2A2465 / A@SEL$BC2A2465
   4 - SEL$BC2A2465 / A@SEL$BC2A2465
   5 - SEL$BC2A2465 / B@SEL$BC2A2465
   6 - SEL$BC2A2465 / B@SEL$BC2A2465
  10 - SEL$42296F85 / A@SEL$4
  11 - SEL$42296F85 / A@SEL$4
  12 - SEL$42296F85 / B@SEL$4
  13 - SEL$42296F85 / B@SEL$4
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   1 - access("A"."UP_CHNL_SEQ"=PRIOR "A"."CHNL_SEQ")
   4 - access("A"."EQP_ID"=4893924 AND "A"."SLOT_SEQ"=12 AND "A"."CRD_SEQ"=1 AND
  "A"."PORT_SEQ"=1 AND "A"."UP_CHNL_SEQ"=0)
   6 - access("A"."SIGN_CLYR_CD"="B"."SIGN_CLYR_CD")
  11 - access("A"."EQP_ID"=4893924 AND "A"."SLOT_SEQ"=12 AND "A"."CRD_SEQ"=1 AND
  "A"."PORT_SEQ"=1 AND "A"."UP_CHNL_SEQ"=PRIOR "A"."CHNL_SEQ")
  13 - access("A"."SIGN_CLYR_CD"="B"."SIGN_CLYR_CD")
 
Column Projection Information (identified by operation id):
-----------------------------------------------------------
 
   1 - "A"."UP_CHNL_SEQ"[NUMBER,22], "A"."CHNL_SEQ"[NUMBER,22], "A"."EQP_ID"[NUMBER,22],
   "A"."SLOT_SEQ"[NUMBER,22], "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22],
   "B"."SIGN_CLYR_CD"[NUMBER,22], "B"."RPST_SIGN_CLYR_NM"[VARCHAR2,50],
   "LNNO_OPRT_DV_CD"[VARCHAR2,10], "A"."SIGN_CLYR_CD"[NUMBER,22], LEVEL[4]
   2 - (#keys=0) "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."CHNL_SEQ"[NUMBER,22],
   "A"."UP_CHNL_SEQ"[NUMBER,22], "LNNO_OPRT_DV_CD"[VARCHAR2,10], "A"."SIGN_CLYR_CD"[NUMBER,22],
   "B".ROWID[ROWID,10], "B"."SIGN_CLYR_CD"[NUMBER,22], "B"."RPST_SIGN_CLYR_NM"[VARCHAR2,50]
   3 - "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."CHNL_SEQ"[NUMBER,22],
   "A"."UP_CHNL_SEQ"[NUMBER,22], "LNNO_OPRT_DV_CD"[VARCHAR2,10], "A"."SIGN_CLYR_CD"[NUMBER,22]
   4 - "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."UP_CHNL_SEQ"[NUMBER,22]
   5 - "B".ROWID[ROWID,10], "B"."SIGN_CLYR_CD"[NUMBER,22],
   "B"."RPST_SIGN_CLYR_NM"[VARCHAR2,50]
   6 - "B".ROWID[ROWID,10], "B"."SIGN_CLYR_CD"[NUMBER,22]
   7 - (#keys=0) "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."CHNL_SEQ"[NUMBER,22],
   "A"."UP_CHNL_SEQ"[NUMBER,22], "LNNO_OPRT_DV_CD"[VARCHAR2,10], "A"."SIGN_CLYR_CD"[NUMBER,22],
   PRIOR NULL[22], "B".ROWID[ROWID,10], "B"."SIGN_CLYR_CD"[NUMBER,22],
   "B"."RPST_SIGN_CLYR_NM"[VARCHAR2,50]
   8 - (#keys=0) "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."CHNL_SEQ"[NUMBER,22],
   "A"."UP_CHNL_SEQ"[NUMBER,22], "LNNO_OPRT_DV_CD"[VARCHAR2,10], "A"."SIGN_CLYR_CD"[NUMBER,22],
   PRIOR NULL[22]
   9 - PRIOR NULL[22]
  10 - "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."CHNL_SEQ"[NUMBER,22],
   "A"."UP_CHNL_SEQ"[NUMBER,22], "LNNO_OPRT_DV_CD"[VARCHAR2,10], "A"."SIGN_CLYR_CD"[NUMBER,22]
  11 - "A".ROWID[ROWID,10], "A"."EQP_ID"[NUMBER,22], "A"."SLOT_SEQ"[NUMBER,22],
   "A"."CRD_SEQ"[NUMBER,22], "A"."PORT_SEQ"[NUMBER,22], "A"."UP_CHNL_SEQ"[NUMBER,22]
  12 - "B".ROWID[ROWID,10], "B"."SIGN_CLYR_CD"[NUMBER,22],
   "B"."RPST_SIGN_CLYR_NM"[VARCHAR2,50]
  13 - "B".ROWID[ROWID,10], "B"."SIGN_CLYR_CD"[NUMBER,22]