데이터베이스 질문과 답변

REGEXP_SUBSTR 함수를 사용 쿼리가 너무 느리네요 ㅠ 1 4 2,896

by 에모토 [Oracle Tuning] REGEXP_SUBSTR [2023.02.11 08:07:56]

?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
인터넷을 찾아보니 한 컬럼에 ,(콤마)나 |(버티컬) 등의 구분자로 묶여져 있는 값을
 
REGEXP_SUBSTR 함수를 사용하여 행으로 분리할 수 있다는 걸 알앗습니다.
 
예) COL1 값 : APPLE|ORANGE|GRAPE|BANANA
 
SELECT REGEXP_SUBSTR(COL1, '[^|]+', 1, LEVEL) AS RSLT
FROM   (SELECT 'APPLE|ORANGE|GRAPE|BANANA' AS COL1
           FROM DUAL) X
CONNECT BY REGEXP_SUBSTR(COL1, '[^|]+', 1, LEVEL) IS NOT NULL;
 
RSLT
----------
APPLE
ORANGE
GRAPE
BANANA
 
이렇게 결과가 나오는데요...
이 쿼리문은 한 컬럼에만 적용되는 듯 하더라고요..
 
제가 궁금한 것은 여러 컬럼에도 적용되는가 하는 것입니다
 
만약, NO, COL1, COL2, COL3 컬럼에 각각 이렇게 들어있다고 가정했을 때,
NO   COL1            COL2            COL3
------------------------------------------------
1    APPLE|BANANA    ORANGE|GRAPE    PEACH|KIWI
 
이것을 아래와 같이 나오게 쿼리문을 작성할 수 있을까요?
 
NO   COL1       COL2       COL3
------------------------------------
 1   APPLE      GRAPE      KIWI
 1   APPLE      GRAPE      PEACH
 1   APPLE      ORANGE     KIWI
 1   APPLE      ORANGE     PEACH
 1   BANANA     GRAPE      KIWI
 1   BANANA     GRAPE      PEACH
 1   BANANA     ORANGE     KIWI
 1   BANANA     ORANGE     PEACH
 
아래 쿼리는 제가 고민(?)한 끝에 만들어 본 쿼리입니다.
결과는 제대로 나오는데, 실 데이터에 적용해 보면 속도가 너무 느리네요 ㅠ
무언가 개선이 필요해 보이는데, 어떻게 접근해야 좋을지 감이 안 잡힙니다.
 
SELECT   REGEXP_SUBSTR(COL1, '[^|]+', 1, B.LV) AS RSLT1
       , RSLT2
       , RSLT3
FROM    (SELECT   COL1
                , REGEXP_SUBSTR(COL2, '[^|]+', 1, B.LV) AS RSLT2
                , RSLT3
         FROM    (SELECT   COL1
                         , COL2
                         , REGEXP_SUBSTR(COL3, '[^|]+', 1, B.LV) AS RSLT3
                  FROM   (SELECT   'APPLE|BANANA' AS COL1
                                 , 'ORANGE|GRAPE' AS COL2
                                 , 'PEACH|KIWI' AS COL3
                          FROM DUAL
                         )X1
                        ,(SELECT   LEVEL AS LV
                           FROM     DUAL
                           CONNECT BY LEVEL <= 2
                         )B
                  WHERE    REGEXP_SUBSTR(COL3, '[^|]+', 1, B.LV) IS NOT NULL
                 )X2
                ,(SELECT   LEVEL AS LV
                  FROM     DUAL
                  CONNECT BY LEVEL <= 2
                 )B
         WHERE    REGEXP_SUBSTR(COL2, '[^|]+', 1, B.LV) IS NOT NULL
        )X3
       ,(SELECT   LEVEL AS LV
         FROM     DUAL
         CONNECT BY LEVEL <= 2
        )B
WHERE    REGEXP_SUBSTR(COL1, '[^|]+', 1, B.LV) IS NOT NULL
ORDER BY 1, 2, 3 -- 결과를 보기 좋게 하기 위해 사용하였으며 굳이 없어도 되는 부분
;

제가 봐도 너무 절차식으로 짠 쿼리로 보이네요 ㅠ

도움주실 분에게 미리 감사의 말씀 드립니다...

by 마농 [2023.02.13 09:00:07]

느리다고 하셨는데?
그대로 복사해 실행해 보면 느리지 않습니다.
dual 을 이용한 1건짜리 자료에 대한 쿼리가 느릴리가 없습니다.
만약 느리다면? 실제 수행쿼리가 이게 아닐 듯 한데요?
dual 을 이용한 1건 처리가 아닌 여러건의 테이블에 수행하는게 아닐런지요?
실 사용 쿼리(느린 쿼리)를 보여주세요.

by 마농 [2023.02.13 09:05:05]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
WITH t AS
(
SELECT 1 no, 'APPLE|BANANA' col1, 'ORANGE|GRAPE' col2, 'PEACH|KIWI' col3 FROM dual
UNION ALL SELECT 2, 'APPLE|BANANA', 'ORANGE|GRAPE', 'PEACH|KIWI' FROM dual
)
SELECT no
     , REGEXP_SUBSTR(col1, '[^|]+', 1, x) c1
     , REGEXP_SUBSTR(col2, '[^|]+', 1, y) c2
     , REGEXP_SUBSTR(col3, '[^|]+', 1, z) c3
  FROM t
     , (SELECT LEVEL lv
             , CEIL(LEVEL / 4) x
             , MOD(CEIL(LEVEL / 2)-1, 2) + 1 y
             , MOD(LEVEL-1, 2) + 1 z
          FROM dual
         CONNECT BY LEVEL <= 8
        )
 ORDER BY no, lv
;

 

by 에모토 [2023.02.13 14:42:18]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
감사합니다 마농님
 
덕분에 잘 해결되었습니다.
 
우선은 실 사용 쿼리를 올리지 않은 상태로 질문을 남겨서 죄송합니다.
 
데이터도 그렇고 실 쿼리를 올리기 좀 그런 사정이 있어서
 
개념만 파악하고자 제가 만든 쿼리를 올렸습니다.
 
우선은
 
(SELECT LEVEL lv
      , CEIL(LEVEL / 4) x
      , MOD(CEIL(LEVEL / 2)-1, 2) + 1 y
      , MOD(LEVEL-1, 2) + 1 z
FROM dual
CONNECT BY LEVEL <= 8
)
 
이 부분을 생각하지 못했는데, 좋은 방법을 배워갑니다.
 
다시 한 번 더 감사합니다^^

 

by 마농 [2023.02.13 14:48:30]

빠른 쿼리로 질문 올리면서 느려요 하시면 안됩니다.
올려 주시는 쿼리가 실제 쿼리가 아니더라도 실제 쿼리를 대변할 수 있는 쿼리를 올려주셔야 합니다.
올려주신 쿼리만 보고 그대로 개선하긴 했는데.
실제 데이터도 각 2개씩 정확하게 들어가는지?
1개 이거나 3개 이상인 자료는 혹시 없는지?

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