가변 IN 조건 값 처리 #2 - 임시 테이블

지난 글에서 문자열 분리 PIPELINED 함수로 가변 IN 조건 값을 처리하는 내용을 살펴봤다. IN 조건 값을 재사용해야하는 경우 PIPELINED 함수 대신 임시 테이블을 사용할 수도 있다.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT LPAD (ROWNUM, 3, '0') AS c1, LPAD (ROWNUM, 3, '0') AS c2 FROM XMLTABLE ('1 to 100');
CREATE INDEX t1_x1 ON t1 (c1);

아래는 IN 조건 값을 저장할 임시 테이블이다. 테이블을 IOT로 생성했고, 통계 정보의 행수를 1000으로 설정했다. 사용 환경에 따라 테이블 구조와 통계 정보를 변경할 필요가 있다.

-- 2-1
DROP TABLE gtt1 PURGE;

CREATE GLOBAL TEMPORARY TABLE gtt1 (
    column_name    VARCHAR2(30)
  , value          VARCHAR2(4000)
  , CONSTRAINT gtt1_pk PRIMARY KEY (column_name, value));

EXEC DBMS_STATS.SET_TABLE_STATS  ('TUNA', 'T1', numrows => 1000, force => TRUE);
EXEC DBMS_STATS.LOCK_TABLE_STATS ('TUNA', 'T1');

아래는 임시 테이블에 IN 조건 값을 저장하는 프로시저다. 문자열 분리 PIPELINED 함수와 동작 방식이 동일하다.

-- 2-2
CREATE OR REPLACE PROCEDURE p1 (
    i_column_name    IN    VARCHAR2
  , i_val            IN    CLOB
  , i_del            IN    VARCHAR2 DEFAULT ','
)
IS
    TYPE taa_varchar2 IS TABLE OF VARCHAR2 (32747) INDEX BY PLS_INTEGER;
    l_tmp_aa    taa_varchar2;
    l_val_aa    taa_varchar2;
    l_tmp       VARCHAR2 (32747);
    l_pos       PLS_INTEGER;
    l_idx       PLS_INTEGER := 1;
    l_row       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;
            l_val_aa (l_row) := SUBSTR (l_tmp, 1, l_pos - 1);
            l_row := l_row + 1;
            l_tmp := SUBSTR (l_tmp, l_pos + 1);
        END LOOP;

        l_idx := l_tmp_aa.NEXT (l_idx);
    END LOOP;

    FORALL i IN l_val_aa.FIRST .. l_val_aa.LAST
        INSERT
          INTO gtt1
               (column_name, value)
        VALUES (i_column_name, l_val_aa (i));
EXCEPTION
    WHEN OTHERS THEN NULL;
END p1;
/

아래와 같이 프로시저로 임시 테이블에 값을 저장하고, IN 조건에 서브 쿼리를 기술하면 된다. 프로시저를 수행하기 앞서 COMMIT 문을 수행하여 임시 테이블을 초기화해야 한다.

-- 3-1
COMMIT;
EXEC p1 ('C1', '001,002,003,004,005');
EXEC p1 ('C2', '001,002,003,004,005');

-- 3-2
SELECT COUNT (*) AS cn
  FROM t1 a
 WHERE a.c1 IN (SELECT x.value FROM gtt1 x WHERE x.column_name = 'C1')
   AND a.c2 IN (SELECT x.value FROM gtt1 x WHERE x.column_name = 'C1');

------------------------------------------------------------------------------
| Id  | Operation                      | Name    | Starts | A-Rows | Buffers |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |         |      1 |      1 |       6 |
|   1 |  SORT AGGREGATE                |         |      1 |      1 |       6 |
|   2 |   NESTED LOOPS                 |         |      1 |      5 |       6 |
|   3 |    NESTED LOOPS                |         |      1 |      5 |       4 |
|*  4 |     INDEX RANGE SCAN           | GTT1_PK |      1 |      5 |       1 |
|   5 |     TABLE ACCESS BY INDEX ROWID| T1      |      5 |      5 |       3 |
|*  6 |      INDEX RANGE SCAN          | T1_X1   |      5 |      5 |       2 |
|*  7 |    INDEX UNIQUE SCAN           | GTT1_PK |      5 |      5 |       2 |
------------------------------------------------------------------------------