파티션 확장 절

11.1 버전에 추가된 파티션 확장 절(partition extension clause)을 살펴보자.

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

-- 1
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1, c2) PARTITION BY RANGE(c1) (
    PARTITION p_201401   VALUES LESS THAN (201402)
  , PARTITION p_201402   VALUES LESS THAN (201403)
  , PARTITION p_201403   VALUES LESS THAN (201404)
  , PARTITION p_maxvalue VALUES LESS THAN (MAXVALUE)) AS
SELECT     TO_CHAR (ADD_MONTHS (DATE '2014-01-01', CEIL (LEVEL / 2) - 1), 'YYYYMM')
         , MOD (LEVEL, 2)
      FROM DUAL
CONNECT BY LEVEL <= 8;

TBL$OR$IDX$PART$NUM 함수를 사용하면 값 또는 ROWID를 통해 파티션 번호를 알아낼 수 있다.^[각주:1]

--2
SELECT c1, c2
     , TBL$OR$IDX$PART$NUM (tuna.t1, 0, 0, 0, c1) AS pp1
     , TBL$OR$IDX$PART$NUM (tuna.t1, 0, 0, 0, ROWID) AS pp2
  FROM t1;
 
C1     C2 PP1 PP2
------ -- --- ---
201401  1   1   1
201401  0   1   1
201402  1   2   2
201402  0   2   2
201403  1   3   3
201403  0   3   3
201404  1   4   4
201404  0   4   4

8 행이 선택되었습니다.

아래와 같이 파티션 명을 반환하는 함수를 생성하자.

-- 3
CREATE OR REPLACE FUNCTION fnc_get_partition_name (
    i_table_owner   IN    VARCHAR2
  , i_table_name    IN    VARCHAR2
  , i_value         IN    VARCHAR2
)
    RETURN VARCHAR2
IS
    l_sql_text          VARCHAR2 (4000);
    l_partition_name    VARCHAR2 (30);
BEGIN
    l_sql_text := l_sql_text || q'[SELECT partition_name]'                                 || CHR (10);
    l_sql_text := l_sql_text || q'[  FROM all_tab_partitions]'                             || CHR (10);
    l_sql_text := l_sql_text || q'[ WHERE table_owner = UPPER (:i_table_owner)]'           || CHR (10);
    l_sql_text := l_sql_text || q'[   AND table_name = UPPER (:i_table_name)]'             || CHR (10);
    l_sql_text := l_sql_text || q'[   AND partition_position = ]'                          || CHR (10);
    l_sql_text := l_sql_text || q'[       TBL$OR$IDX$PART$NUM (%TBL%, 0, 0, 0, :i_value)]' || CHR (10);
   
    l_sql_text := REPLACE (l_sql_text, '%TBL%', i_table_owner || '.' || i_table_name);

    EXECUTE IMMEDIATE l_sql_text
                 INTO l_partition_name
                USING i_table_owner, i_table_name, i_value;

    RETURN l_partition_name;
END fnc_get_partition_name;
/

생성한 함수를 사용하면 파티션 명을 출력할 수 있다.

-- 4
SELECT c1
     , c2
     , TBL$OR$IDX$PART$NUM (tuna.t1, 0, 0, 0, c1) AS pp1
     , TBL$OR$IDX$PART$NUM (tuna.t1, 0, 0, 0, ROWID) AS pp2
     , fnc_get_partition_name ('TUNA', 'T1', c1) AS partition_name
  FROM t1;
 
C1     C2 PP1 PP2 PARTITION_NAME
------ -- --- --- --------------
201401  1   1   1 P_201401
201401  0   1   1 P_201401
201402  1   2   2 P_201402
201402  0   2   2 P_201402
201403  1   3   3 P_201403
201403  0   3   3 P_201403
201404  1   4   4 P_MAXVALUE
201404  0   4   4 P_MAXVALUE

8 행이 선택되었습니다.

11.1 이전 버전에서 값으로 파티션을 조회하려면 아래와 같은 방식을 사용해야 했다.

-- 5
COLUMN partition_name NEW_VALUE partition_name

SELECT fnc_get_partition_name ('TUNA', 'T1', '201401') AS partition_name FROM DUAL;

PARTITION_NAME
--------------
P_201401

1개의 행이 선택되었습니다.

SELECT * FROM t1 PARTITION (&partition_name);

C1     C2
------ --
201401  1
201401  0

2 행이 선택되었습니다.

11.1 버전부터 파티션 확장 절을 통해 값으로 파티션을 조회할 수 있다.

-- 6
SELECT * FROM t1 PARTITION FOR ('201401');

C1     C2
------ --
201401  1
201401  0

2 행이 선택되었습니다.

파티션 확장 절은 바인드 변수를 사용할 수 없다. 파싱시 조회할 파티션을 결정해야 하기 때문이다.

-- 7
VARIABLE v_c1 VARCHAR2(6) = '201401';

SELECT * FROM t1 PARTITION FOR (:v_c1);

ORA-14763: FOR VALUES 절을 분할 영역 번호로 분석할 수 없습니다.

1. 해당 함수는 내부적으로 Subquery Pruning에 사용된다. [본문으로]