Edited at

Hyblid Partitionを試す(Oracle Database 19c)

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