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文の[マニュアル](https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/ALTER-TABLE.html#GUID-552E7373-BF93-477D-9DA3-B2C9386F2877)で簡単に説明されています。

```
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:
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:
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
```