Partitioned External 테이블

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

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

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

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

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

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

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

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

아래의 구문으로 Partitioned External 테이블을 생성할 수 있다. p1 파티션은 dir_ext1 디렉터리의 205001.txt 파일, p2 파티션은 dir_ext2 디렉터리의 205002_01.txt, 205002_02.txt 파일을 조회한다.

-- 3
DROP TABLE t1 PURGE;

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

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

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

4 행이 선택되었습니다.

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

-- 4-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 |   EXTERNAL TABLE ACCESS FULL| T1   |     2 |     2 |
------------------------------------------------------------

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

-- 5
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              4
T1         P1             PARTITION          2
T1         P2             PARTITION          2
T1         P3             PARTITION          0

4 행이 선택되었습니다.