[Oracle] PL/SQL 성능을 위한 짤막팁

가능하면 묵시적 커서로.

묵시적 커서 별거 없다.

DECLARE

	VAR_SOME VARCHAR2(123);

BEGIN

	SELECT '뭐왜뭐' INTO VAR_SOME FROM DUAL;

END;

니들이 PL/SQL 쓰면서 조회하기 위해 SELECT SQL문 넣는 것 자체가 묵시적 커서다.

반복문의 경우,

CREATE OR REPLACE FUNCTION FN_GETROWS() RETURN YOUR_TABLE_TYPE PIPELINED IS

	VAR_ROW YOUR_ROW_TYPE;

BEGIN

	FOR VAR_ROW IN (SELECT SOME, THING FROM YOUR_TABLE) LOOP
    
		PIPE ROW (VAR_ROW);
    
	END LOOP;

END;

대충 반복문에 복잡한 거 아니거나 쿼리 간단하면 굳이 커서 안 써도 된다.
가독성 구리면 커서를 써도 되는데 이건 아래에 다루겠다.
특히 일괄 INSERT 및 UPDATE 라면 FORALL 이라는 멋진 반복문이 있는데,
안타깝게도 FOR I IN 1..10 식으로 인덱스 숫자만 지원하므로 (INDICES OF 및 VALUES OF 다 의미없음)
명시적 커서를 써야 하니 아래 석션♂을 참고하라.

명시적 커서 성능 팁

명시적 커서 반복문에 딱히 할 거 없다면, 위와 같이 FOR IN 문을 사용한다.
쿼리의 가독성이 떨어져 커서를 쓴다 해도 좋다. 커서도 FOR 문이 먹힌다는 게 넘나 편리하다는 것.

CREATE OR REPLACE FUNCTION FN_GETROWS() RETURN YOUR_TABLE_TYPE PIPELINED IS

	VAR_ROW YOUR_ROW_TYPE;
    CURSOR CUR_SOME IS SELECT SOME, THING FROM YOUR_TABLE;

BEGIN

	FOR VAR_ROW IN CUR_SOME LOOP
    
		PIPE ROW (VAR_ROW);
    
	END LOOP;

END;

참고로 위 구문을 쓰면 오라클에서는 아래와 같은 구문으로 해석해 실행한다. (Oracle 10g 이상)

CREATE OR REPLACE FUNCTION FN_GETROWS() RETURN YOUR_TABLE_TYPE PIPELINED IS

	VAR_ROW YOUR_ROW_TYPE;
    CURSOR CUR_SOME IS SELECT SOME, THING FROM YOUR_TABLE;
    
    -- 오라클에서 생성하는 임시 테이블 타입과 해당 변수를 선언한다고 보면 된다.
    -- 실제로 이렇게 동작하는 건 아니고 가상 예시다.
    TYPE __ORA_GEN_BULK_TBL__ IS TABLE OF YOUR_ROW_TYPE;
    __ORA_GEN_BULK_VAR__ __ORA_GEN_BULK_TBL__;

BEGIN

	OPEN CUR_SOME; -- 무조건 루프 시작 전 열고
	LOOP
		-- 오라클 공식에서 FOR IN CURSOR 문은 아래와 같이 100건씩 가져와 반복문을 처리한다고 한다.
		FETCH CUR_SOME BULK COLLECT INTO __ORA_GEN_BULK_VAR__ LIMIT 100;
		EXIT WHEN CUR_SOME%NOTFOUND;
		FOR I IN 1..__ORA_GEN_BULK_VAR__.COUNT() LOOP
			VAR_ROW := __ORA_GEN_BULK_VAR__(I);
			PIPE ROW (VAR_ROW);
		END LOOP;
	END LOOP;
    CLOSE CUR_SOME; -- 무조건 루프 종료 후 닫는다.

END;

따라서 그냥 반복할 개수가 그리 많지 않다면 이게 성능 상 유리하지만, 만약 반복할 개수가 많다면 직접 위 커서 반복문을 직접 작성하는 것이 좋다. 그 이유는 LIMIT 조절을 위해서다. 그건 뭐 검색하면 질리도록 나오니 여기까지.

FOR IN 문과 달리 FORALL 문은 해석 안 하고 그냥 있는 그대로 동작한다.
따라서 반복 횟수가 수십만번이라면 수십만번 한꺼번에 수행한다고 보면 된다.
만약 이걸 무작정 방치할 경우 불필요한 트랜잭션 로그가 엄청나게 쌓여 DBA가 울먹이며 로그를 정리해야 하고 결국 화살은 쿼리 작성자에게 날아오게 되어 있다.
그러니 가능하면 FETCH LIMIT 문을 사용하여 단위를 쪼개고, 가능하면 작성 후 COMMIT 문으로 최적화를 하는 게 좋다.

CREATE OR REPLACE PROCEDURE PR_SAVEYOURS() IS

	VAR_ROW YOUR_ROW_TYPE;
	VAR_TBL YOUR_TABLE_TYPE;
	CURSOR CUR_SOME IS SELECT SOME, THING FROM YOUR_TABLE;

BEGIN

	OPEN CUR_SOME;
	LOOP
		-- LIMIT 뒤에 숫자는 알아서 예상 수량에 따라 쪼갤 수를 조절하도록 하라.
		FETCH CUR_SOME BULK COLLECT INTO VAR_TBL LIMIT 1000;
		EXIT WHEN CUR_SOME%NOTFOUND;
		FORALL I IN 1..VAR_TBL.COUNT()
		INSERT INTO OTHER_TABLE (SAME, THO) VALUES (VAR_TBL(I).SOME, VAR_TBL(I).THING);
		COMMIT;
	END LOOP;
    CLOSE CUR_SOME;

END;

끗.