오라클 INSTR 속도 개선 0 5 196

by sw5716 [SQL Query] 튜닝 속도 INSTR WITH절 [2019.04.11 09:19:42]


안녕하세요 아래 쿼리와 같이 DATE_K값을 읽어서 같은 데이터를 가져오는 쿼리인데 속도가...너무 느려서 문의 드립니다.

A 테이블에 ID_T 데이터는
12

123|3DSFA|FFF|TTT|UTEKCCC 

3DSFA@@FFF@@WTQERQ

123

이런식으로 들어가있습니다.

WITH DATA_K 데이터는 

123

FFF

12

이렇게 유일한 값으로 들어가있습니다 

조인을 할수 없어서 INSRT로 문자열 찾아서 하는데 속도가 너무 많이 걸립니다. 

A 테이블 데이터건수는 88,000 정도 이고 WITH DATA_K는 300건정도 됩니다.

어떻게 풀어야 성능이 향상이 될까요??

GROUP BY 를 한이유는 A 테이블에 ID_T 데이터가 중복데이터가 있어서 GROUP BY를 한겁니다.

WITH DATA_K AS

  SELECT ID_T FROM T_TABLE1 UNION ALL
  SELECT ID_T FROM T_TABLE2 UNION ALL
  SELECT ID_T FROM T_TABLE3 UNION ALL
  SELECT ID_T FROM T_TABLE4 UNION ALL
  SELECT ID_T FROM T_TABLE5 UNION ALL
)
SELECT A.ID_T
     , A.NAME_T
     , A.TITME_T
     , A.CODE_T
     , A.DT_T
  FROM TARGET_T A 
     , DATE_K B 
 WHERE A.CODE_T ='TTT'
   AND INSTR (A.ID_T, B.ID_T) > 0
 GROUP BY    
       A.ID_T
     , A.NAME_T
     , A.TITME_T
     , A.CODE_T 
 ORDER BY A.DT_T

by 마농 [2019.04.11 09:37:17]

구분자가 제각각 인가요? | 와 @@ 말고 다른 구분자도 있나요?
한 컬럼에 최대 몇개까지 연결되나요?
A.CODE_T 에 인덱스는 있나요?
A.CODE_T = 'TTT' 인 데이터는 전체 몇건 중 몇건인가요?
B.ID_T 에 인덱스는 있겠죠?
B 는 몇건인가요? B 안의 테이블들은 각각 몇건인가요?
GROUP BY 이유가 중복제거인데?
원본 테이블 A 에 중복이 있는 건가요? B 랑 조인하다보니 중복이 생긴건가요?


by sw5716 [2019.04.11 14:47:52]

구분자는 다행이도 | 와 @@ 두개 뿐입니다.

한컬럼에 최대 9개정도 있습니다.

CODE_T인덱스 있습니다.

28만건중 8만건입니다.

B.ID_T에 인덱스 있습니다.

B는 총 300건입니다. 각각테이블은 1건부터 16건 200건 등등 1건은 무조건 있습니다.

A중에 중복이 있어서 B랑 조인해야 하는데 GROUP BY를 할수 밖에 없는 구조입니다.


by 마농 [2019.04.11 15:15:44]
-- 조인 후 Distinct --
SELECT DISTINCT
       a.id_t
     , a.name_t
     , a.time_t
     , a.code_t
  FROM target_t a
     , data_k   b
 WHERE a.code_t = 'TTT'
   AND b.id_t IN ( REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 1)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 2)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 3)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 4)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 5)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 6)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 7)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 8)
                 , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 9)
                 )
;
-- Distinct 후 Exists --
SELECT *
  FROM (SELECT DISTINCT
               a.id_t
             , a.name_t
             , a.time_t
             , a.code_t
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 1) id_1
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 2) id_2
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 3) id_3
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 4) id_4
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 5) id_5
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 6) id_6
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 7) id_7
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 8) id_8
             , REGEXP_SUBSTR(a.id_t, '[^|@]+', 1, 9) id_9
          FROM target_t a
         WHERE a.code_t = 'TTT'
        ) a
 WHERE EXISTS (SELECT 1
                 FROM data_k b
                WHERE b.id_t IN ( a.id_1
                                , a.id_2
                                , a.id_3
                                , a.id_4
                                , a.id_5
                                , a.id_6
                                , a.id_7
                                , a.id_8
                                , a.id_9
                                )
               )
;

 


by sw5716 [2019.04.11 16:12:19]

넵 감사합니다~~~~~

응용해서 한번 해보겠습니다~~~~


by 마농 [2019.04.11 16:27:35]

기존 INSTR 조건은 논리적인 오류가 있습니다. '12' 를 검색할 때 '123' 이 딸려올 수 있는 문제.
- 오류 : INSTR(a.id_t, b.id_t) > 0
- 수정 : INSTR(','||a.id_t||',', ','||b.id_t||',') > 0

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입