Oracle Database 12cの外部表パーティションを試す

外部表をパーティション化

Oracle Database 12c Release 2 では外部テーブル（EXTERNAL TABLE）をパーティション化できるようになりました。ここではCSVファイルを使った外部テーブルについて検証しています。
パーティション化外部テーブルの作成方法は従来のテキストファイルを使ったCREATE TABLE文にPARTITION句を指定するだけです。各パーティションの定義には、DIRECTORYオブジェクトの指定（DEFAULT DIRECTORY句）と、ファイルの指定（LOCATION）を指定しています。すべてのパーティションが単一のDIRECTORYを使う場合、パーティション単位のDEFAULT DIRECTORY句は不要です。

作成例

以下に作成例を記述します。

SQL> CREATE TABLE ext_part1 (
  id     NUMBER,
  val    VARCHAR2(10)
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY dir1
  ACCESS PARAMETERS (
    FIELDS CSV WITH EMBEDDED TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
    MISSING FIELD VALUES ARE NULL (
      id,
      val
    )
  )
)
REJECT LIMIT UNLIMITED
PARTITION BY LIST (id) (
  PARTITION id1    VALUES (100) LOCATION ('vala1.txt', 'vala2.txt'),
  PARTITION id2    VALUES (200) DEFAULT DIRECTORY dir2 LOCATION ('valb1.txt')
);

Table created.

ファイルの内容は以下の通りです。

$ cat dir1/vala1.txt
100,    data100
100,    data101
100,    data102
200,    data200
200,    data201
200,    data202
$ cat dir1/vala2.txt
100,    Data100
100,    Data101
100,    Data102
200,    Data200
200,    Data201
200,    Data202
$ cat dir2/valb1.txt
100,    dAta100
100,    dAta101
100,    dAta102
200,    dAta200
200,    dAta201
200,    dAta202
$

実行結果と実行計画を確認します。

SQL> SELECT * FROM ext_part1 WHERE id=100 ;

        ID VAL
---------- ----------
       100 data100
       100 data101
       100 data102
       200 data200
       200 data201
       200 data202
       100 Data100
       100 Data101
       100 Data102
       200 Data200
       200 Data201
       200 Data202

12 rows selected.


PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  5jzd87x6wsrf1, child number 0
-------------------------------------
SELECT * FROM ext_part1 WHERE id=100

Plan hash value: 2398555010

---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |    29 (100)|          |       |       |
|   1 |  PARTITION LIST SINGLE      |           |    82 |  1640 |    29   (0)| 00:00:01 |     1 |     1 |
|   2 |   EXTERNAL TABLE ACCESS FULL| EXT_PART1 |    82 |  1640 |    29   (0)| 00:00:01 |     1 |     1 |
---------------------------------------------------------------------------------------------------------

14 rows selected.

SQL> SELECT * FROM ext_part1 WHERE val = 'data100' ;

        ID VAL
---------- ----------
       100 data100

SQL> SELECT * FROM TABLE(dbms_xplan.display_cursor()) ;

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
SQL_ID  fnn62f1ft01rw, child number 0
-------------------------------------
SELECT * FROM ext_part1 WHERE val = 'data100'

Plan hash value: 3823654654
---------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |       |       |    56 (100)|        |       |       |
|   1 |  PARTITION LIST ALL         |           |   163 |  3260 |    56   (0)| 00:00:01 |     1 |     2 |
|*  2 |   EXTERNAL TABLE ACCESS FULL| EXT_PART1 |   163 |  3260 |    56   (0)| 00:00:01 |     1 |     2 |
---------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("VAL"='data100')

19 rows selected.

検証結果から

SELECT文の実行計画にPARTITION LIST SINGLEが表示されているため、パーティション・プルーニングが行われています。一方で、検索条件として実行計画内にfilterの出力が無く、WHERE id=100と指定したにも関わらず、id=200のレコードが出力されています。
また別のSELECT文であるWHERE val='data100'の文は正しくフィルタリングされていることがわかります。

このことから、外部表のパーティション・テーブルは、パーティション・プルーニングが有効になる場合とならない場合でWHERE句の条件が検査されるかどうかが異なる動作になることがわかります。