[Oracle] 오라클 프로시저 생성/삭제/실행, 프로시저 최적화, 프로시저 원자성 확인

옵티마이저를 활용하여 쿼리 튜닝을 하기 위해, 이를 위한 더미 데이터 삽입을 하고자 하였다.

 

일일이 데이터를 삽입하는 것은 비효율적이므로, 프로시저를 생성하여 더미데이터 삽입의 효율성을 높여본다.

 

 

1.테이블 생성

-- test_member 테이블 생성
CREATE TABLE test_member(
    mem_no varchar(10) PRIMARY KEY,
    mem_name varchar(10),
    mem_pass varchar(10)
);

-- 테이블 삭제
--DROP TABLE TEST_MEMBER;

-- truncate table TEST_MEMBER;

-- 데이터 삭제
DELETE FROM TEST_MEMBER tm ;

-- 삽입된 데이터 갯수 확인
SELECT count(*) FROM TEST_MEMBER tm ;

 

 

 

2. N부터 M까지를 파라미터로 더미데이터를 insert하는 기본 프로시저 생성

-- 프로시저 생성 기본
CREATE OR REPLACE PROCEDURE insert_test_member(n IN NUMBER, m IN NUMBER) IS
BEGIN
    FOR i IN n..m LOOP
        INSERT INTO test_member (mem_no, mem_name, mem_pass)
        VALUES (LPAD(i, 10, '0'), 'Name_' || i, 'Pass_' || i);
    END LOOP;
    
    COMMIT; -- 변경 내용을 데이터베이스에 저장
END;
/

-- 프로시저 실행기본 n부터 m  ... 
-- 1부터 100만이면 n에 1, m에 100만을 넣으면 된다.
BEGIN
    insert_test_member(n, m);
END;
/


-- 프로시저 삭제
DROP PROCEDURE insert_test_member;

 

그런데 해당 프로시저의 경우 100만건의 대용령 데이터를 넣기 위해 매우 비효율적인 프로시저라고 할 수 있다.

 

왜일까??

 

바로 100만번의 루프를 도는 동안, 100만번의 commit을 해야하기 때문이다.

 

따라서  Batch를 활용하여, 해당 프로시저를 조금 더 효율적으로 개선해보기로 한다.

 

 

 

 

3.Batch를 활용한 프로시저 활용

CREATE OR REPLACE PROCEDURE insert_test_member(n IN NUMBER, m IN NUMBER) IS
    v_commit_interval CONSTANT NUMBER := 10000; -- 커밋 간격
    v_counter NUMBER := 0;
BEGIN
    FOR i IN n..m LOOP
        INSERT INTO test_member (mem_no, mem_name, mem_pass)
        VALUES (LPAD(i, 10, '0'), 'Name_' || i, 'Pass_' || i);

        v_counter := v_counter + 1;

        -- v_commit_interval로 지정된 만번 마다 데이터 커밋
        IF MOD(v_counter, v_commit_interval) = 0 THEN
            COMMIT;
        END IF;
    END LOOP;

    -- 마지막 최종 만개 미만의 데이터 커밋
    COMMIT;
END;
/

-- 100만건의 데이터 삽입
BEGIN
    insert_test_member(1, 1000000);
END;
/

-- 프로시저 삭제
DROP PROCEDURE insert_test_member;

 

하지만 해당 프로시저를 실행할 경우, 9만개의 데이터 삽입 이후로는 오류가 발생한다. 왜냐하면 애초에 mem_no 등이 varchar(10)으로 할당되어 있지만, 더미데이터를 위해 Name_ 혹은 Pass_가 접두어로 붙어 데이터의 자릿수의 범위를 벗어나기 때문이다.

 

이때 9만건의 데이터를 삽입한것이 유지되고, 그 이후의 데이터가 삽입에 실패하는 것은 DB의 기본 원리인 원자성에(atomicty) 위배된다. 따라서 해당 프로시저는 전체 실행 성공이 완료 됐을때만 커밋되고, 아닌 겨우는 rollback 시키는 프로시저로 변경한다.

 

 

3. 프로시저 원자성 설정

CREATE OR REPLACE PROCEDURE insert_test_member(n IN NUMBER, m IN NUMBER) IS
    v_commit_interval CONSTANT NUMBER := 10000; -- 커밋 간격
    v_counter NUMBER := 0;
BEGIN
    FOR i IN n..m LOOP
        INSERT INTO test_member (mem_no, mem_name, mem_pass)
        VALUES (LPAD(i, 10, '0'), 'Name_' || i, 'Pass_' || i);

        v_counter := v_counter + 1;

        -- 일정 간격마다 SAVEPOINT 설정
        IF MOD(v_counter, v_commit_interval) = 0 THEN
            SAVEPOINT sp_insert_batch;
        END IF;
    END LOOP;

    -- 작업 완료 후 커밋
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 오류 발생 시 SAVEPOINT로 롤백
        ROLLBACK TO sp_insert_batch;
        RAISE_APPLICATION_ERROR(-20002, '대량 삽입 중 오류 발생: ' || SQLERRM);
END;

-- 100만건의 데이터 삽입
BEGIN
    insert_test_member(1, 1000000);
END;
/

-- 프로시저 삭제
DROP PROCEDURE insert_test_member;

 

해당 프로시저가 실행된다면 만개의 데이터가 삽입될 동안 원자성이 보장될것이다.

 

하지만 효율성 측면, 그리고 원자성 유지를 위해서 특정 batch간격으로 커밋을 조정하여 롤백 시킬 수도 있다.

 

 

if)  save point를 활용하여 최적화 + 원자성유지

CREATE OR REPLACE PROCEDURE insert_test_member(n IN NUMBER, m IN NUMBER) IS
    v_commit_interval CONSTANT NUMBER := 10000; -- 커밋 간격
    v_counter NUMBER := 0;
BEGIN
    FOR i IN n..m LOOP
        INSERT INTO test_member (mem_no, mem_name, mem_pass)
        VALUES (LPAD(i, 10, '0'), 'Name_' || i, 'Pass_' || i);

        v_counter := v_counter + 1;

        -- 일정 간격마다 SAVEPOINT 설정
        IF MOD(v_counter, v_commit_interval) = 0 THEN
            SAVEPOINT sp_insert_batch;
        END IF;
    END LOOP;

    -- 작업 완료 후 커밋
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        -- 오류 발생 시 SAVEPOINT로 롤백
        ROLLBACK TO sp_insert_batch;
        RAISE_APPLICATION_ERROR(-20002, '대량 삽입 중 오류 발생: ' || SQLERRM);
END;

-- 100만건의 데이터 삽입
BEGIN
    insert_test_member(1, 1000000);
END;
/

-- 프로시저 삭제
DROP PROCEDURE insert_test_member;

 

 

 

+)사실 가장 좋은 것은 처음부터 DB설계를 제대로 하는 것이다!

 

하지만 나의 경우 처음엔 대략적으로 이정도 쯤이면 되겠지, 하고 막연하게 데이터 삽입 프로시저를 실행해보다가 운좋게도 오류가 발생(?)

 

당황했긴 했지만, 덕분에 여러모로 어떻게 하면 DB의 정합성을 맞출 수 있을지, 그리고 얼마나 더 효율적인 프로시저를 생성 할 수 있을지 고민해볼 수 있는 좋은 기회 였던거 같다.

 

다음번에는 옵티마이저를 활용한 쿼리 개선, 그리고 DB의 기본 구조에 대해서도 고민해봐야지!