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

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

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73

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 함수와 같은 문제가 발생하지 않는다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37

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가 발생한 것으로 보인다. [본문으로]