Hybrid Partitioned 테이블

19.1 버전부터 Hybrid Partitioned 테이블을 생성할 수 있다.

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

-- 1
C:\>type C:\app\ora19c\admin\ora19c\ext1\205001.txt
1,20500101
2,20500102

C:\>type C:\app\ora19c\admin\ora19c\ext2\205002_01.txt
3,20500201

C:\>type C:\app\ora19c\admin\ora19c\ext2\205002_02.txt
4,20500202

SYS 유저로 접속한 세션에서 아래와 같이 디렉터리를 생성하고, TUNA 유저에 전체 권한을 부여하자.

-- 2
CREATE OR REPLACE DIRECTORY dir_ext1 AS 'C:\app\ora19c\admin\ora19c\ext1';
CREATE OR REPLACE DIRECTORY dir_ext2 AS 'C:\app\ora19c\admin\ora19c\ext2';

GRANT ALL ON DIRECTORY dir_ext1 TO tuna;
GRANT ALL ON DIRECTORY dir_ext2 TO tuna;

아래의 구문으로 Hybrid Partitioned 테이블을 생성할 수 있다. p1, p2 파티션은 외부 파일, p3 파티션은 파티션 세그먼트를 조회한다. ORACLE_LOADER 외에도 ORACLE_DATAPUMP, ORACLE_HDFS, ORACLE_HIVE 등의 유형을 사용할 수 있다.

-- 3
DROP TABLE t1 PURGE;

CREATE TABLE t1 (c1 NUMBER, c2 DATE)
EXTERNAL PARTITION ATTRIBUTES (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY dir_ext1
    ACCESS PARAMETERS (
        RECORDS DELIMITED BY NEWLINE
        NOBADFILE NOLOGFILE NODISCARDFILE
        FIELDS TERMINATED BY ','
        OPTIONALLY ENCLOSED BY '"'
        MISSING FIELD VALUES ARE NULL (
            c1
          , c2 CHAR(8) DATE_FORMAT DATE MASK "YYYYMMDD"
        )
    )
)
PARTITION BY RANGE (c2) (
    PARTITION p1 VALUES LESS THAN (DATE '2050-02-01') EXTERNAL LOCATION ('205001.txt')
  , PARTITION p2 VALUES LESS THAN (DATE '2050-03-01') EXTERNAL DEFAULT DIRECTORY dir_ext2
                                                               LOCATION ('205002_01.txt', '205002_02.txt')
  , PARTITION p3 VALUES LESS THAN (MAXVALUE)
);

external 파티션에 DML 작업을 수행하면 에러가 발생한다. internal 파티션은 DML 작업을 수행할 수 있다.

-- 4
INSERT INTO t1 VALUES (7, DATE '2050-02-03');

ORA-14466: 읽기 전용 분할 영역이나 하위 분할 영역의 데이터는 수정할 수 없습니다.

INSERT INTO t1 VALUES (5, DATE '2050-03-01');
INSERT INTO t1 VALUES (6, DATE '2050-03-02');
COMMIT;

아래는 t1 테이블을 조회한 결과다. 5-2번 쿼리에서 파티션 Pruning이 동작하는 것을 확인할 수 있다.

-- 5-1
SELECT * FROM t1;

C1 C2
-- -------------------
 1 2050-01-01 00:00:00
 2 2050-01-02 00:00:00
 3 2050-02-01 00:00:00
 4 2050-02-02 00:00:00
 5 2050-03-01 00:00:00
 6 2050-03-02 00:00:00

6 행이 선택되었습니다.

---------------------------------------------------------------
| Id  | Operation                      | Name | Pstart| Pstop |
---------------------------------------------------------------
|   0 | SELECT STATEMENT               |      |       |       |
|   1 |  PARTITION RANGE ALL           |      |     1 |     3 |
|   2 |   TABLE ACCESS HYBRID PART FULL| T1   |     1 |     3 |
|   3 |    TABLE ACCESS FULL           | T1   |     1 |     3 |
---------------------------------------------------------------

-- 5-2
SELECT * FROM t1 WHERE c2 = DATE '2050-02-01';

C1 C2
-- -------------------
 3 2050-02-01 00:00:00

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

-------------------------------------------------------
| Id  | Operation              | Name | Pstart| Pstop |
-------------------------------------------------------
|   0 | SELECT STATEMENT       |      |       |       |
|   1 |  PARTITION RANGE SINGLE|      |     2 |     2 |
|*  2 |   TABLE ACCESS FULL    | T1   |     2 |     2 |
-------------------------------------------------------

DBMS_STATS.GATHER_TABLE_STATS 프로시저로 테이블의 통계정보를 수집할 수 있다.

-- 6
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');

SELECT table_name, partition_name, object_type, num_rows
  FROM user_tab_statistics
 WHERE table_name = 'T1';

TABLE_NAME PARTITION_NAME OBJECT_TYPE NUM_ROWS
---------- -------------- ----------- --------
T1                        TABLE              6
T1         P1             PARTITION          2
T1         P2             PARTITION          2
T1         P3             PARTITION          2

4 행이 선택되었습니다.

인덱스는 PARTIAL NON-UNIQUE 인덱스만 생성할 수 있다.

-- 7
CREATE INDEX t1_x1 ON t1 (c2) LOCAL;

ORA-14354: 하이브리드 분할 테이블에 대해 지원되지 않는 작업입니다.

CREATE INDEX t1_x1 ON t1 (c2) LOCAL INDEXING PARTIAL;

인덱스가 생성되었습니다.

아래는 INDEX 힌트를 사용한 쿼리다. 8-2번 쿼리는 에러가 발생한다. PARTIAL 인덱스가 생성되지 않은 파티션에 INDEX 힌트를 사용했기 때문이다.

-- 8-1
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c2 = DATE '2050-03-01';

C1 C2
-- -------------------
 5 2050-03-01 00:00:00

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

----------------------------------------------------------------------------
| Id  | Operation                                  | Name  | Pstart| Pstop |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                           |       |       |       |
|   1 |  PARTITION RANGE SINGLE                    |       |     3 |     3 |
|   2 |   TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| T1    |     3 |     3 |
|*  3 |    INDEX RANGE SCAN                        | T1_X1 |     3 |     3 |
----------------------------------------------------------------------------

-- 8-2
SELECT /*+ INDEX(T1) */ * FROM t1 WHERE c2 = DATE '2050-02-01';

ORA-01502: 인덱스 'TUNA.T1_X1'또는 인덱스 분할영역은 사용할 수 없은 상태입니다

아래는 인덱스 생성 시점의 통계정보다.

-- 9
SELECT index_name, partition_name, object_type, num_rows
  FROM user_ind_statistics
 WHERE index_name = 'T1_X1';

INDEX_NAME PARTITION_NAME OBJECT_TYPE NUM_ROWS
---------- -------------- ----------- --------
T1_X1                     INDEX              2
T1_X1      P1             PARTITION          0
T1_X1      P2             PARTITION          0
T1_X1      P3             PARTITION          2

4 행이 선택되었습니다.

아래처럼 데이터를 입력하자.

-- 10
INSERT INTO t1 VALUES (7, DATE '2050-04-01');
INSERT INTO t1 VALUES (8, DATE '2050-04-02');
COMMIT;

테이블 통계정보를 수집하면 에러가 발생한다. 버그로 판단된다. CASCADE 파라미터를 FALSE로 설정해야 테이블 통계정보를 수집할 수 있다.

-- 11
EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1');

ORA-00600: 내부 오류 코드, 인수: [25027], [0], [0], [0], [0], [0], [1], [1], [], [], [], []

EXEC DBMS_STATS.GATHER_TABLE_STATS ('TUNA', 'T1', CASCADE => FALSE);

PL/SQL 처리가 정상적으로 완료되었습니다.

인덱스는 파티션을 지정해도 에러가 발생한다.

-- 12
EXEC DBMS_STATS.GATHER_INDEX_STATS ('TUNA', 'T1_X1');

ORA-00600: 내부 오류 코드, 인수: [25027], [0], [0], [0], [0], [0], [1], [1], [], [], [], []

EXEC DBMS_STATS.GATHER_INDEX_STATS ('TUNA', 'T1_X1', 'P3');

ORA-00600: 내부 오류 코드, 인수: [25027], [0], [0], [0], [0], [0], [1], [1], [], [], [], []

차선책으로 인덱스 REBUILD를 통해 통계정보를 갱신할 수 있습니다.

-- 13
ALTER INDEX t1_x1 REBUILD PARTITION p3 PARALLEL 2 ONLINE;

인덱스가 변경되었습니다.

SELECT index_name, partition_name, object_type, num_rows
  FROM user_ind_statistics
 WHERE index_name = 'T1_X1';

INDEX_NAME PARTITION_NAME OBJECT_TYPE NUM_ROWS
---------- -------------- ----------- --------
T1_X1                     INDEX              4
T1_X1      P1             PARTITION          0
T1_X1      P2             PARTITION          0
T1_X1      P3             PARTITION          4

4 행이 선택되었습니다.