[Oracle 11gR2] 패키지와 함께 사용하면 좋은 TABLE( ) 함수

이번에 패키지 소스를 분석하면서 TABLE( ) 함수에 대해 알게 되었습니다. TABLE( )함수는 Function( )이 리턴한 ‘배열 데이터(리스트)’를 SQL이 조인하거나 필터링할 수 있는 가상 테이블 형태로 변환합니다. 데이터 변경은 자주 발생하지 않으면서 많은 화면이나 쿼리의 서브쿼리에 사용되는 다음과 같은 경우에 사용합니다.

• 동적 권한 필터링: 로그인한 사용자 사번에 따라 접근 가능한 공정(Process)이나 부서 리스트를 가져와 IN 연산자와 함께 사용해야 할 때.
• 배열 데이터를 SQL로 처리: 프로그램(Java, C# 등)에서 넘겨준 리스트 데이터를 SQL의 IN 절이나 JOIN 대상처럼 쓰고 싶을 때.

패키지와 함께 사용하면 좋은 TABLE( ) 함수를 사용하는 방법을 알아보겠습니다. 사번으로 여러 공정의 접근권한을 체크하는 로직을 구현하는 시나리오입니다.

1. TABLE( ) 함수를 사용할 수 있는지 파라미터를 확인합니다.

   -- RESULT_CACHE_MODE 파라미터값이 MANUAL이어야 합니다.
   SQL> show parameter RESULT_CACHE_MODE;
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   result_cache_mode                    string      MANUAL 
   
   -- V$RESULT_CACHE_STATISTICS뷰의 Create Count Failure 값이 0보다 크다면 RESULT_CACHE_MAX_SIZE 파라미터 값을 늘려주는 것을 검토해야 합니다.
   SQL> show parameter RESULT_CACHE_MAX_SIZE;
   NAME                                 TYPE        VALUE
   ------------------------------------ ----------- ------------------------------
   result_cache_max_size                big integer 83904K
   

2. 패키지 헤더를 선언합니다.

   CREATE OR REPLACE PACKAGE PKG_MES IS
       -- 1. 한 행(Row)의 모양 정의
       TYPE PROC_REC_TYPE IS RECORD (
           BIZ_ID    VARCHAR2(20),
           PROC_CODE VARCHAR2(20)
       );
       
       -- 2. 위 레코드를 담을 바구니(Table Type) 정의
       TYPE PROC_TAB_TYPE IS TABLE OF PROC_REC_TYPE;
   
       -- 3. 위 바구니를 리턴하는 함수 선언 (PIPELINED 권장)
       FUNCTION GET_PROC(IN_USER_ID IN VARCHAR2) 
       RETURN PROC_TAB_TYPE PIPELINED
       RESULT_CACHE;
   END PKG_MES;
   

   • TABLE: TABLE 타입으로 선언해야 Function에서 반환된 값을 TABLE( )함수가 행과 열로 변환할 수 있습니다.
   • PIPELINED: 일반 함수는 데이터 1,000건을 다 만들 때까지 기다렸다가 한 번에 반환하지만, PIPELINED를 쓰면 만들어지는 즉시 한 줄씩 보냅니다. 또한 메모리 사용량이 적고, 대량 데이터 조회 시 첫 번째 행이 화면에 뜨는 속도가 매우 빠릅니다.
   • RESULT_CACHE: 자주 쓰이는 쿼리나 함수의 결과값을 메모리에 저장해 두었다가, 똑같은 요청이 오면 계산 없이 즉시 돌려줍니다.
3. 패키지 바디를 구현합니다.

   CREATE OR REPLACE PACKAGE BODY PKG_MES IS
       FUNCTION GET_PROC(IN_USER_ID IN VARCHAR2) 
       RETURN PROC_TAB_TYPE PIPELINED 
       RESULT_CACHE 
       RELIES_ON (TB_MC_BASE_CODE)
       IS
           --패키지 헤더에서 선언한 레코드 타입을 변수로 선언
           PROC_REC    PROC_REC_TYPE;
       BEGIN
           FOR REC IN (SELECT BIZ_ID, PROC_CODE FROM TB_MC_BASE_CODE ...) LOOP
               PROC_REC.BIZ_ID     := REC.BIZ_ID;
               PROC_REC.PROC_CODE  := REC.PROC_CODE;
   
               -- 
               PIPE ROW(PROC_REC);
           END LOOP;
       END GET_PROC;
   END PKG_MES;
   

   • RELIES_ON( ): “해당 테이블들이 바뀌면 캐시를 비워라”라는 명령을 수행합니다. 11gR2버전 이후에는 자동으로 관리되나, 가독성을 위해 이 함수는 선언된 테이블에 의존함을 명시하고, “,”로 구분해서 여러 개의 테이블을 등록할 수 있습니다.
   • PIPE ROW( ): Pipelined Table Function에서만 사용하는 특수 명령어로, 함수가 끝나기도 한 줄씩 화면에 출력하거나 다음 조인(Join) 단계로 넘깁니다.
4. 아래와 같이 FROM과 함께 TABLE( )를 사용합니다. TABLE( ) 함수를 다른 대용량 테이블과 JOIN하여 성능이 저하될 경우, /*+ CARDINALITY(별칭 데이터수) */와 같은 오라클 힌트를 사용해야 할 수도 있습니다.

   ....
   WHERE PROC_CODE IN (SELECT PROC_CODE FROM TABLE(PKG_MES.GET_PROC('user_id')))