가변 IN 조건 값 처리 #1 - PIPELINED 함수

가변 IN 조건 값을 처리하기 위해 PIPELINED 함수를 활용할 수 있다. IN 조건 값이 다수인 경우 CLOB 타입을 사용할 수 있는데, 이런 경우 과도한 블록 I/O가 발생할 수 있다.

테스트를 위해 아래와 같이 함수를 생성하자. f1 함수는 VARCHAR2, f2 함수는 CLOB 값을 입력받는다. f3 함수도 CLOB 값을 입력받지만, 입력 값을 VARCHAR2 연관 배열에 저장하여 처리한다.

CREATE OR REPLACE TYPE tnt_varchar2 IS TABLE OF VARCHAR2(4000);
/

-- 1-1
CREATE OR REPLACE FUNCTION f1 (i_val IN VARCHAR2, i_del IN VARCHAR2 DEFAULT ',')
    RETURN tnt_varchar2 PIPELINED
IS
    l_tmp    VARCHAR2 (32747) := i_val || i_del;
    l_pos    PLS_INTEGER;
BEGIN
    LOOP
        l_pos := INSTR (l_tmp, i_del);
        EXIT WHEN NVL (l_pos, 0) = 0;
        PIPE ROW (SUBSTR (l_tmp, 1, l_pos - 1));
        l_tmp := SUBSTR (l_tmp, l_pos + 1);
    END LOOP;
END f1;
/

-- 1-2
CREATE OR REPLACE FUNCTION f2 (i_val IN CLOB, i_del IN VARCHAR2 DEFAULT ',')
    RETURN tnt_varchar2 PIPELINED
IS
    l_tmp    CLOB := i_val || i_del;
    l_pos    PLS_INTEGER;
BEGIN
    LOOP
        l_pos := INSTR (l_tmp, i_del);
        EXIT WHEN NVL (l_pos, 0) = 0;
        PIPE ROW (SUBSTR (l_tmp, 1, l_pos - 1));
        l_tmp := SUBSTR (l_tmp, l_pos + 1);
    END LOOP;
END f2;
/

-- 1-3
CREATE OR REPLACE FUNCTION f3 (i_val IN CLOB, i_del IN VARCHAR2 DEFAULT ',')
    RETURN tnt_varchar2 PIPELINED
IS
    TYPE taa_varchar2 IS TABLE OF VARCHAR2 (32747) INDEX BY PLS_INTEGER;
    l_tmp_aa    taa_varchar2;
    l_tmp       VARCHAR2 (32747);
    l_pos       PLS_INTEGER;
    l_idx       PLS_INTEGER      := 1;
BEGIN
    LOOP
        l_tmp := SUBSTR (i_val, (10000 * (l_idx - 1)) + 1, 10000);
        EXIT WHEN l_tmp IS NULL;
        l_tmp_aa (l_idx) := l_tmp;
        l_idx := l_idx + 1;
    END LOOP;

    l_tmp_aa (l_tmp_aa.LAST) := l_tmp_aa (l_tmp_aa.LAST) || i_del;
    l_idx := l_tmp_aa.FIRST;
    l_tmp := NULL;

    WHILE l_idx IS NOT NULL
    LOOP
        l_tmp := l_tmp || l_tmp_aa (l_idx);

        LOOP
            l_pos := INSTR (l_tmp, i_del);
            EXIT WHEN NVL (l_pos, 0) = 0;
            PIPE ROW (SUBSTR (l_tmp, 1, l_pos - 1));
            l_tmp := SUBSTR (l_tmp, l_pos + 1);
        END LOOP;

        l_idx := l_tmp_aa.NEXT (l_idx);
    END LOOP;
EXCEPTION
    WHEN OTHERS THEN NULL;
END f3;
/

아래 테스트에서 f1 함수는 에러가 발생한다. f2 함수는 에러는 발생하지 않지만 과도한 블록 I/O가 발생한 것을 확인할 수 있다.^[각주:1] 아울러 CLOB 값을 탐색하는 과정에서 비교적 많은 시간이 소요되었다. f3 함수는 VARCHAR2 연관 배열을 사용했기 때문에 f2 함수와 같은 문제가 발생하지 않는다.

VAR v1 CLOB;

BEGIN
    :v1 := TO_CLOB (RPAD ('AAAAAAAAA,', 4000, 'AAAAAAAAA,')) || RPAD ('BBBBBBBBB,', 4000, 'BBBBBBBBB,')
                 || RPAD ('CCCCCCCCC,', 4000, 'CCCCCCCCC,')  || RPAD ('DDDDDDDDD,', 4000, 'DDDDDDDDD,')
                 || RPAD ('EEEEEEEEE,', 4000, 'EEEEEEEEE,')  || RPAD ('FFFFFFFFF,', 4000, 'FFFFFFFFF,')
                 || RPAD ('GGGGGGGGG,', 4000, 'GGGGGGGGG,')  || RPAD ('HHHHHHHHH,', 4000, 'HHHHHHHHH,')
                 || RPAD ('IIIIIIIII,', 4000, 'IIIIIIIII,')  || RPAD ('JJJJJJJJJ,', 3999, 'JJJJJJJJJ,');
END;
/

-- 2-1
SELECT COUNT (*) AS cn FROM TABLE (f1 (:v1));

ORA-22835: 버퍼가 너무 작아 CLOB를 CHAR 또는 BLOB에서 RAW로 변환할 수 없습니다(실제: 39999, 최대: 4000).

-- 2-2
SELECT COUNT (*) AS cn FROM TABLE (f2 (:v1));

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |      1 |      1 |00:00:01.05 |     308K|
|   1 |  SORT AGGREGATE                    |      |      1 |      1 |00:00:01.05 |     308K|
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F2   |      1 |   4000 |00:00:01.05 |     308K|
--------------------------------------------------------------------------------------------

-- 2-3
SELECT COUNT (*) AS cn FROM TABLE (f3 (:v1));

--------------------------------------------------------------------------------------------
| Id  | Operation                          | Name | Starts | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                   |      |      1 |      1 |00:00:00.01 |     158 |
|   1 |  SORT AGGREGATE                    |      |      1 |      1 |00:00:00.01 |     158 |
|   2 |   COLLECTION ITERATOR PICKLER FETCH| F3   |      1 |   4000 |00:00:00.01 |     158 |
--------------------------------------------------------------------------------------------

1. Large Pool에 저장한 CLOB 값을 액세스한 과정에서 블록 I/O가 발생한 것으로 보인다. [본문으로]