Oracle Database 12cでは外部テーブル（EXTERNAL TABLE）をパーティション化することができました。Oracle Database 19cでは、パーティションとしてヒープ・データと、外部データを単一のパーティション・テーブル内で同時に構成できるようになりました。ヒープ・パーティションと、CSVファイルの外部パーティションを混在させるHyblid Partitionテーブルを作成してみました。
準備
外部パーティションの保存用にDIRECTORYオブジェクトを作成します。全ユーザーにアクセス権を付与しています。
SQL> CREATE DIRECTORY dir1 AS '/home/oracle/dir1';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dir1 TO PUBLIC;
Grant succeeded.
SQL> CREATE DIRECTORY dir2 AS '/home/oracle/dir2';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dir2 TO PUBLIC;
Grant succeeded.
SQL> CREATE DIRECTORY dir3 AS '/home/oracle/dir3';
Directory created.
SQL> GRANT READ,WRITE ON DIRECTORY dir3 TO PUBLIC;
Grant succeeded.
パーティション・テーブルの作成
パーティション・テーブル part1を作成します。このパーティション・テーブルは、ヒープ・データを持つパーティションp1を持っています。
SQL> CREATE TABLE part1(c1 NUMBER, c2 VARCHAR2(10)) PARTITION BY LIST (c1)
2 (
3 PARTITION p1 VALUES (100)
4 );
Table created.
次にALTER TABLE文により、外部のCSVデータにアクセスするための設定を追加します（ADD EXTERNAL PARTITION ATTRIBUTES）。下記の例ではデフォルトのDIRECTORYオブジェクト、CSVファイルの列セパレータ（,）を指定しています。新しい構文ADD EXTERNAL PARTITION ATTRIBUTES句はALTER TABLE文のマニュアルで簡単に説明されています。
SQL> ALTER TABLE part1
2 ADD EXTERNAL PARTITION ATTRIBUTES
3 (TYPE ORACLE_LOADER
4 DEFAULT DIRECTORY dir1
5 ACCESS PARAMETERS (
6 FIELDS TERMINATED BY ',' (c1, c2)
7 )
8 );
Table altered.
パーティションの追加
下記では外部のCSVをアクセスするパーティションを追加しています。デフォルトのDIRECTORYオブジェクトdir1にアクセスし、p2.csvファイルを指定するパーティションが追加されます。
SQL> ALTER TABLE part1 ADD PARTITION p2 VALUES (200)
2 EXTERNAL LOCATION ('p2.csv');
Table altered.
デフォルト以外のDIRECTORYオブジェクトdir2のCSVを読み込むパーティションを追加する場合には、下記のようにファイル名にDIRECTORYオブジェクト名を指定します。
SQL> ALTER TABLE part1 ADD PARTITION p3 VALUES (300)
2 EXTERNAL LOCATION (dir2:'p3.csv');
Table altered.
異なるセパレータ（:）を持つCSVファイルをDIRECTORYオブジェクトdir3に配置し、パーティションを追加しています。しかし、SELECT文でアクセスするとエラーORA-29913が発生します。パーティション単位のセパレータ指定ができないのか、他の方法があるかは不明です。
SQL> ALTER TABLE part1 ADD PARTITION p4 VALUES (400)
2 EXTERNAL LOCATION (dir3:'p4.csv')
3 ACCESS PARAMETERS (
4 FIELDS TERMINATED BY ':' (c1, c2)
5 );
Table altered.
SQL> SELECT * FROM part1 WHERE c1=400;
SELECT * FROM part1 WHERE c1=400
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
Hyblid Partitionテーブルに対してはインデックスを作成することができません。
SQL> CREATE INDEX idx_part1 ON part1(c1) GLOBAL;
CREATE INDEX idx_part1 ON part1(c1) GLOBAL
*
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table
SQL> CREATE INDEX idx_part1 ON part1(c1) LOCAL;
CREATE INDEX idx_part1 ON part1(c1) LOCAL
*
ERROR at line 1:
ORA-14354: operation not supported for a hybrid-partitioned table
実行計画
全パーティションをアクセスする場合と、単一パーティションにアクセスする場合の実行計画を確認します。全パーティションにアクセスする場合は、実行計画に「TABLE ACCESS HYBRID PART FULL」が出力されます。
SQL> SET AUTOTRACE TRACE
SQL> SELECT * FROM part1;
11 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 2384775083
--------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 24589 | 480K| 83 (0)| 00:00:01 | | |
| 1 | PARTITION LIST ALL | | 24589 | 480K| 83 (0)| 00:00:01 | 1 | 4 |
| 2 | TABLE ACCESS HYBRID PART FULL| PART1 | 24589 | 480K| 83 (0)| 00:00:01 | 1 | 4 |
| 3 | TABLE ACCESS FULL | PART1 | | | | | 1 | 4 |
--------------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
108 recursive calls
4 db block gets
400 consistent gets
0 physical reads
612 redo size
801 bytes sent via SQL*Net to client
381 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
11 rows processed
特定のパーティションにアクセスする場合には、ヒープ・パーティションの場合は「TABLE ACCESS FULL」が、外部パーティションの場合は「EXTERNAL TABLE ACCESS FULL」が出力されるようです。
SQL> SET AUTOTRACE TRACE
SQL> SELECT * FROM part1 WHERE c1=100;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1427294587
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |Pstart| Pstop |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 20 | 2 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE| | 1 | 20 | 2 (0)| 00:00:01 | 1 | 1 |
| 2 | TABLE ACCESS FULL | PART1 | 1 | 20 | 2 (0)| 00:00:01 | 1 | 1 |
-----------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
0 consistent gets
0 physical reads
0 redo size
418 bytes sent via SQL*Net to client
383 bytes received via SQL*Net from client
1 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
0 rows processed
SQL> SELECT * FROM part1 WHERE c1=200;
Execution Plan
----------------------------------------------------------
Plan hash value: 2459659237
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 82 | 1640 | 29 (0)| 00:00:01 | | |
| 1 | PARTITION LIST SINGLE | | 82 | 1640 | 29 (0)| 00:00:01 | 2 | 2 |
| 2 | EXTERNAL TABLE ACCESS FULL| PART1 | 82 | 1640 | 29 (0)| 00:00:01 | 2 | 2 |
-----------------------------------------------------------------------------------------------------
Statistics
----------------------------------------------------------
110 recursive calls
4 db block gets
400 consistent gets
0 physical reads
612 redo size
704 bytes sent via SQL*Net to client
394 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
4 rows processed