안녕하세요 오라클 정규식 쿼리 조언부탁드립니다 0 9 888

by 기린기린 [SQL Query] [2019.02.26 18:49:40]



with temp as  (
       select CLOB2 TXT from TB_TEMP_BLOB
     )
SELECT DISTINCT TO_CHAR(trim(regexp_substr(STR, '[가-힣]+', 1, level)))
  FROM (SELECT TXT STR FROM temp) t
CONNECT BY trim(regexp_substr(str, '[가-힣]+', 1, level)) IS NOT NULL

 

안녕하세요.

특정 장문 텍스트 로부터 한글만 추출하려고 하는데요.
한글을 모두 추출하고자 하는데
위 처럼 하니까 퍼포먼스가 너무 안나옵니다. 그렇게 긴 데이터도 아닌데요. level이 5800안쪽이 끝인데
LEVEL이 점점 올라가면서 처음부터 계속 개수를 셈해서 공간복잡도 때문에 그런가 싶었는데
그런데 제가 직접 regexp_substr 4번째 파라미터에 5000 이런식으로 값을 임의로 주면
0.3초도 안돼서 결과값이 나오는거보니 그러한 로직으로 조회되는 건 아닌 것 같습니다.

사이즈가 큰 텍스트파일도 빠르게 regexp_substr를 loop 돌릴 수 있는 좋은 방법 없을까요?

감사합니다.

by 마농 [2019.02.27 08:18:15]

여러건의 테이블에 직접 Connect By Levle 방식을 사용하면 안됩니다.
단건 dual 에 적용 후 조인하는 방식으로 사용하세요.
http://gurubee.net/article/55635
언어셋이 어떻게 되나요?
MSWIN949 의 경우 완성형(KSC5601)에 조합형을 중간중간 끼워넣은 형태라 '힣' 보다는 '힝' 으로 바꾸는게 더 정확합니다.
UTF 의 경우엔 그대로 '힣' 사용하시면 되구요.
다만, '가-힣' 으로는 'ㅋㅋ ㅎㅎ ㅠㅠ' 등은 못잡아냅니다.


by 기린기린 [2019.02.27 09:36:21]

정말 감사합니다.

TB_TEMP_BLOB 테이블에 추출하고자 하는 CLOB데이터가 있는 레코드 하나만 있습니다.
오라클 캐릭터셋은 UTF8 입니다.

with temp as  (
       select CLOB2 TXT from TB_TEMP_BLOB
     )
 SELECT STR 
   FROM ( SELECT TO_CHAR(trim(regexp_substr(STR, '[가-힣]+', 1, level)))
            FROM (SELECT TXT STR FROM temp) t
         CONNECT BY level < 5800)
  GROUP BY STR

이렇게 바꾸니까 9분 40초로 많이 줄었습니다.

by 신이만든지기 [2019.02.27 11:57:03]
WITH
    TEMP AS
        (SELECT 'asdf 한글, ASDFAS 숫자, 123123, #!@##!@ ㄱㄴㄷㄹㅁ aaaa ㅏㅑㅓㅕtest ' TXT
           FROM DUAL)
SELECT REGEXP_SUBSTR(TXT, '[^ ]+', 1, NO) WORD
  FROM (
           SELECT TRIM(REGEXP_REPLACE(TXT, '([^ㄱ-힣]+)', ' ')) TXT
             FROM TEMP
       ) A
     , (    SELECT LEVEL NO
              FROM DUAL
        CONNECT BY LEVEL <= (SELECT LENGTH(TXT) - LENGTH(REPLACE(TXT, ' ', '')) + 1
                               FROM (
                                        SELECT TRIM(REGEXP_REPLACE(TXT, '([^ㄱ-힣]+)', ' ')) TXT
                                          FROM TEMP
                                    ))) B

이렇게 한번 해보세요.


by 기린기린 [2019.02.27 16:25:26]
WITH
    TEMP AS
        (select CLOB2 TXT from TB_TEMP_BLOB)
SELECT DISTINCT TO_CHAR(REGEXP_SUBSTR(TXT, '[^ ]+', 1, NO)) WORD
  FROM (
           SELECT TRIM(REGEXP_REPLACE(TXT, '([^ㄱ-힣]+)', ' ')) TXT
             FROM TEMP
       ) A
     , (    SELECT LEVEL NO
              FROM DUAL
        CONNECT BY LEVEL <= (SELECT LENGTH(TXT) - LENGTH(REPLACE(TXT, ' ', '')) + 1
                               FROM (
                                        SELECT TRIM(REGEXP_REPLACE(TXT, '([^ㄱ-힣]+)', ' ')) TXT
                                          FROM TEMP
                                    ))) B

47분 46초 걸렸습니다.
결과 개수는 NULL값은 안나오니 빼고 347개로 동일하게 나온걸로 보입니다.
감사합니다.


by 마농 [2019.02.27 12:24:48]

너무 오래 걸리네요. 이상합니다.


by 마농 [2019.02.27 13:38:57]
SELECT lv
     , REGEXP_SUBSTR(str, '[가-힣]+', 1, lv) x
  FROM (SELECT /*+ NO_MERGE */
               clob2 str
             , REGEXP_COUNT(clob2, '[가-힣]+') cnt
          FROM tb_temp_blob
         WHERE ROWNUM = 1
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 6000)
 WHERE lv <= cnt
;

 


by 기린기린 [2019.02.27 16:47:50]
SELECT DISTINCT TO_CHAR(REGEXP_SUBSTR(str, '[가-힣]+', 1, lv)) x
  FROM (SELECT /*+ NO_MERGE */
               clob2 str
             , REGEXP_COUNT(clob2, '[가-힣]+') cnt
          FROM TB_TEMP_BLOB
         WHERE ROWNUM = 1
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL < 6000)
 WHERE lv <= cnt


결과: 347개
9분 31초 걸렸습니다 감사합니다.
 


by 마농 [2019.02.28 10:56:17]

느린 이유가 정규식이 너무 많이 반복 수행되어 서 그런 듯 합니다.
정규식은 최초 한번만 수행하도록 하고,
split 을 하는 반복구문에서는 정규식을 배제했습니다.
 

SELECT DISTINCT
       TO_CHAR(SUBSTR( x
       , INSTR(' '||x, ' ', 1, lv)
       , INSTR(x||' ', ' ', 1, lv)
       - INSTR(' '||x, ' ', 1, lv)
       ) ) x
  FROM (SELECT /*+ no_merge */
               TRIM(REGEXP_REPLACE(REGEXP_REPLACE(clob2, '[^가-힣]+', ' '), ' +', ' ')) x
             , REGEXP_COUNT(clob2, '[가-힣]+') cnt
          FROM tb_temp_blob
         WHERE ROWNUM = 1
        )
     , (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 6000)
 WHERE lv <= cnt
;

 


by 기린기린 [2019.02.28 11:52:57]

결과: 347개
3.635초 걸렸습니다
정말 감사히 배웁니다.

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