오라클 대량 업데이트 : UPDATE

오라클 대량 업데이트 : UPDATE

수백만 건의 데이터를 일괄 UPDATE 해야 하는 일이 생겼습니다. 수백만건의 데이터는 수십만 건으로 분할 되어 있고 각 분할되어 있는 데이타에게는 구분해주는 값이 있어서 그 구분자 값만 잡아 일부분 처리 할 수 있다고 생각했습니다. 그 중 가장 적은 갯수인 2만여건의 데이타를 먼저 업데이를 해보려고 하는데 업데이트 해야 하는 내용은 다음과 같습니다.

구분	컬럼1	컬럼2	컬럼3	전체 내용
1	A	B	C	A B C
2	A	D	C	A D C

위의 표 내용처럼 전체 내용 란이 공백으로 데이타가 공백으로 들어가 있었고 UPDATE 구문을 통해 전체 내용란에 컬럼1, 컬럼2, 컬럼3의 합한 내용을 UPDATE 칠 생각이었습니다.

가장 단순하게 UPDATE 구문을 작성해 보았습니다.

UPDATE 테이블 A       SET 전체내용 = (                           SELECT 컬럼1 || ' ' || 컬럼2 || ' ' || 컬럼3                              FROM 테이블                             WHERE 테이블.유니크컬럼 = A.유니크컬럼 )  WHERE (2만건에 해당 되는 구분자 조건)

위처럼 작성해서 돌리니 끝날 줄 모릅니다. 위와 같이 하면 하나의 데이타를 찾고 FULL SCAN 하고 하나 데이타 찾고 또 FULL SCAN 하는 형태가 되나 봅니다. 그래서 찾아보았습니다. 어떻게 처리 할 수 있을지. 방법은 2가지로 나타났습니다. 첫번째 방법은 서브쿼리를 이용하는 방법이고 두번째 방법은 UPDATABLE JOIN VIEW 방법이었습니다.

첫번째 서브쿼리를 이용하는 방법은 

UPDATE 테이블 A       SET 전체내용 = (                           SELECT 컬럼1 || ' ' || 컬럼2 || ' ' || 컬럼3                              FROM 테이블                             WHERE (테이블.유니크컬럼 = A.유니크컬럼 )                               )  WHERE (2만건에 해당 되는 구분자 조건)        AND EXISTS ( SELECT 1                                FROM 테이블                              WHERE 테이블.유니크컬럼 = A.유니크컬럼 );

방법으로 처음에 작성한 내용과는 크게 다르지 않았습니다. 속도차이도 2만여건 40분 진행중 취소하였습니다.

두번째 UPDATABLE JOIN VIEW 방법은

UPDATE /*+ BYPASS_JUVC */              (                   SELECT 전체내용, 컬럼1 || ' ' || 컬럼2 || ' ' || 컬럼3 AS NEW전체내용                      FROM 테이블                   WHERE (2만건에 해당 되는 구분자 조건)               ) SET 전체내용 = NEW전체내용

위와 같이 테이블을 VIEW 형태로 만들고 그 VIEW 형태에서 만들어진 전체내용의 복제물을 실제 전체내용 컬럼에 넣는 방법입니다. 위와 같은 방법으로 2만여건을 3분만에 처리하는 속도를 보았습니다. 위의 내용에서 /*+ bypass_ujvc */ 라는 힌트 구문을 이용하여 놀라운 처리 속도를 보여주었다고 합니다. 힌트 명령어에 대해서는 다음에 알아보도록 하고 대량 업데이트를 빠르게 처리할 수 있게 되어 정말 기쁩니다.

!!두번째 방법을 시도하다 ORA-01732 : 뷰에 대한 데이터 조작이 부적합합니다. 라는 경고문과 함께 더 이상 진행이 안될 때는 아래와 같이 MERGE INTO 방법을 시도 봅니다. 테이블 A과 테이블 B의 조인 조건은 ON에 입력하고 그 결과물이 참이면 WHEN MATCHED THEN 의 내용이 처리됩니다.

MERGE INTO 테이블 A USING           (              SELECT 컬럼1 || ' ' || 컬럼2 || ' ' || 컬럼3 AS NEW전체내용                FROM 테이블 B          ) ON       (          A.유니크컬럼 = B.유니크컬럼        ) WHEN MATCHED THEN         UPDATE SET 전체내용 = NEW전체내용