파티션 확장 절

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

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

1 2 3 4 5 6 7 8 9 10 11 12

-- 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]

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

--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 행이 선택되었습니다.

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

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

-- 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; /

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

-- 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 이전 버전에서 값으로 파티션을 조회하려면 아래와 같은 방식을 사용해야 했다.

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

-- 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 버전부터 파티션 확장 절을 통해 값으로 파티션을 조회할 수 있다.

1 2 3 4 5 6 7 8 9

-- 6 SELECT * FROM t1 PARTITION FOR ('201401');   C1     C2 ------ -- 201401  1 201401  0   2 행이 선택되었습니다.

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

1 2 3 4 5 6

-- 7 VARIABLE v_c1 VARCHAR2(6) = '201401';   SELECT * FROM t1 PARTITION FOR (:v_c1);   ORA-14763: FOR VALUES 절을 분할 영역 번호로 분석할 수 없습니다.

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