DB링크 통해 기존DB데이터 조회하기
SELECT * FROM DB링크명.CDW.테이블명CLOB데이터 SELECT~INSERT방법의 한계
- CLOB컬럼은 DBLINK를 통해서 SELECT할 수 없다.
- 단, CTAS나 INSERT INTO ... SELECT 문을 통해서 TARGET 쪽 DB에 복제한 후 사용해야 한다.
- DBLINK를 통해서 CLOB컬럼이 있는 테이블의 INSERT ALL은 불가능하다.
CLOB데이터 SELECT~INSERT 속도개선
기존 DB의 데이터를 새 DB에 붙이는 작업을 하기 전..
DB링크를 통해 기존 데이터들을 다 옮길 수 있는지 몇 가지 테이블을 대상으로 테스트를 해 보았다.
그런데, CLOB컬럼의 경우 SELECT ~ INSERT속도가 현저히 느려질 수 있다고 한다
우리 CLOB컬럼의 데이터가 몇십만, 많으면 몇 억건씩 나와서 속도 문제를 무조건 고려해야 했다...
그러던 중 어느 블로그에 4000자 이하/이상 데이터를 구분해 적재하면 속도가 향상된다는 내용이 나와있었다.
INSERT /+ APPEND / TOBE_BBS SELECT BBS_NO, TITLE||' '||CONT FROM ASIS_BBS;-- 2200초 수행
그런데, 아래와 같이 CONT 컬럼을 가공(CONCATENATE)하지 않으면 260초만에 완료된다.
INSERT /+ APPEND / TOBE_BBS SELECT BBS_NO, CONT FROM ASIS_BBS; -- 260초 수행
위와 같은 차이의 원인은 CLOB에 대한 가공으로 생각되며 CLOB 컬럼에 REPLACE, SUBSTR 등의 함수를 붙여도 유사한 성능 저하를 경험하게 된다.
이 현상은 오라클의 내부적인 처리 문제가 아닌가 예측하면서,이런 저런 곳에서 해결책을 찾아 보았지만 특별한 방법을 찾지 못하던 중, CLOB 컬럼 이라 하더라도 대부분의 데이터는 4000바이트 이하라는 점, LENGTH 함수는 빠르게 수행된다는 점에서 아이디어가 떠올라 다음과 같이 해결하게 되었다.
아래와 같이 4000바이트 이하인 경우와 초과하는 경우로 나누어 수행하면 어느 정도의 성능향상을 볼 수 있다. (단, 성능 향상의 전제 조건은 CLOB 컬럼이라 하더라도 4000 바이트 이하인 ROW가 많아야 한다.)
INSERT /+ APPEND / ALLWHEN LENGTHB(TITLE) + LENGTHB(CONT) <=4000 THEN INTO TOBE_BBS VALUES (BBS_NO, TITLE||' '||TO_CHAR(CONT) )WHEN LENGTHB(TITLE) + LENGTHB(CONT)>4000THEN INTO TOBE_BBS VALUES (BBS_NO, TITLE||' '||CONT)SELECT * FROM ASIS_BBS;-- 400초
결과를 보면 260초 까지 줄지는 않았지만, 2200초에서 400초 정도로 개선됨을 알 수 있다.(물론 4000 바이트 이하 데이터의 비율에 따라 효과는 달라진다.)
워낙 심플한 방법이니 더 이상 설명을 추가할 필요도 없어 보이며, 다음과 같이 분석용 SQL에도 적용할 수 있다.
SELECT SUM(DECODE(INSTR(GDAS_CONT,'상품'),0,0,1)) FROMTOBE_BBS;-- 160초
SELECT SUM(CASE WHEN LENGTHB(GDAS_CONT) <= 4000 THEN DECODE(INSTR(TO_CHAR(GDAS_CONT),'상품'),0,0,1)
ELSE DECODE(INSTR(GDAS_CONT,'상품'),0,0,1) END) FROMTOBE_BBS;-- 100초우리 회사 데이터같은 경우는 대부분 조회밖에 하지 않는데, 데이터에서 KEY값을 바탕으로 대량의 데이터를 조회하는 방식이다. 그래서 4000자가 넘는 데이터와 그렇지 않은 데이터를 떨어뜨려서 테이블에 INSERT해도 되는지는 DBA에게 확인받은 후 작업해야하지만, 이런 방법을 사용할 수 있을 경우 속도가 빨라짐을 기대해보자!
https://dataonair.or.kr/db-tech-reference/d-lounge/technical-data/?mod=document&uid=236146
