Oracle Database 18c の新機能 COLLATION 指定の拡張を試す

Oracle Database 18c の新機能でありる。COLLATE 指定列によるパーティション・テーブルの機能を試しました。

COLLATION 指定

Oracle Database 12c で列単位に COLLATE 句を指定して、文字照合に関する指定を行うことができるようになりました。例えば BINARY_CI を指定すると、大文字／小文字の区別をしない列定義を指定することができます。昨年、Oracle Database 12c の新機能として Qiita に書きました。

SQL> CREATE TABLE collation1 (c1 VARCHAR2(10), c2 VARCHAR2(10) COLLATE BINARY_CI) ;

表が作成されました。

SQL> INSERT INTO collation1 VALUES ('abc', 'abc');

1行が作成されました。

SQL> INSERT INTO collation1 VALUES ('ABC', 'ABC');

1行が作成されました。

SQL> SELECT * FROM collation1 WHERE c1='abc';

C1         C2
---------- ----------
abc        abc

SQL> SELECT * FROM collation1 WHERE c2='abc';

C1         C2
---------- ----------
abc        abc
ABC        ABC

Oracle Database 18c における拡張

Oracle Database 18c では、COLLATE 指定が行われた列をパーティション・キーとして使えるようになりました。

Oracle Database 12c の挙動

Oracle Database 12c では、パーティション・キーとして COLLATE 指定された列を指定するとエラーになります。

SQL> CREATE TABLE part1(c1 VARCHAR2(10) COLLATE BINARY_CI, c2 VARCHAR2(10)) PARTITION BY LIST(c1)
    (
        PARTITION P1 VALUES ('data1'),
        PARTITION P2 VALUES ('data2'),
        PARTITION P3 VALUES ('data3')
    );
CREATE TABLE part1(c1 VARCHAR2(10) COLLATE BINARY_CI, c2 VARCHAR2(10)) PARTITION BY LIST(c1)
                                                                                         *
行1でエラーが発生しました。:
ORA-03001: 実装されていない機能です ORA-00722: 機能"Collation-sensitivity for Partitioning Keys"  

Oracle Database 18c の挙動

Oracle Database 18c では、パーティション・キーとして COLLATE 指定された列を指定してもエラーになりません。

SQL> CREATE TABLE part1(c1 VARCHAR2(10) COLLATE BINARY_CI, c2 VARCHAR2(10)) PARTITION BY LIST(c1)
    (
        PARTITION P1 VALUES ('data1'),
        PARTITION P2 VALUES ('data2'),
        PARTITION P3 VALUES ('data3')
    );

表が作成されました。

COLLATE BINARY_CI をパーティション・キー列に指定したリスト・パーティションで、大文字／小文字だけ異なる値のパーティションを作成してみました。リスト・パーティションで同一値であるというチェックが行われ ORA-14306 エラーが発生します。

SQL> CREATE TABLE part2(c1 VARCHAR2(10) COLLATE BINARY_CI, c2 VARCHAR2(10)) PARTITION BY LIST(c1)
       (
               PARTITION P1 VALUES ('data1'),
               PARTITION P2 VALUES ('Data1'),
               PARTITION P3 VALUES ('DATA1')
       );
                PARTITION P3 VALUES ('DATA1')
                                     *
行5でエラーが発生しました。:
ORA-14306:
リストの値'NLSSORT('Data1','nls_sort=''BINARY_CI''')'がパーティション'P2'、'P3'で2回指定されました。

パーティション・プルーニングの動作

COLLATE BINARY_CI設定が行われている列を使ったパーティション・テーブルで、WHERE 句の値によりパーティション・プルーニングが行われるかを確認しました。

SQL> SELECT * FROM scott.part1 WHERE c1='data1';

C1         C2
---------- ----------
data1      data1

SQL> SET AUTOTRACE ON
SQL> SELECT * FROM scott.part1 WHERE c1='data1';

C1         C2
---------- ----------
data1      data1


実行計画
----------------------------------------------------------
Plan hash value: 1427294587

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    19 |   274   (0)| 00:00:01 |      |       |
|   1 |  PARTITION LIST SINGLE|       |     1 |    19 |   274   (0)| 00:00:01 |    1 |     1 |
|   2 |   TABLE ACCESS FULL   | PART1 |     1 |    19 |   274   (0)| 00:00:01 |    1 |     1 |
-----------------------------------------------------------------------------------------------

統計
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         36  consistent gets
          0  physical reads
          0  redo size
        648  bytes sent via SQL*Net to client
        623  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

SQL> SELECT * FROM scott.part1 WHERE c1='DATA1';

C1         C2
---------- ----------
data1      data1


実行計画
----------------------------------------------------------
Plan hash value: 1427294587

-----------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    19 |   274   (0)| 00:00:01 |      |       |
|   1 |  PARTITION LIST SINGLE|       |     1 |    19 |   274   (0)| 00:00:01 |    1 |     1 |
|   2 |   TABLE ACCESS FULL   | PART1 |     1 |    19 |   274   (0)| 00:00:01 |    1 |     1 |
-----------------------------------------------------------------------------------------------

統計
----------------------------------------------------------
          2  recursive calls
          0  db block gets
         38  consistent gets
          0  physical reads
          0  redo size
        648  bytes sent via SQL*Net to client
        623  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

テーブル定義時の値や、WHERE 句の指定値に関係無く、パーティション・プルーニングが行われることがわかります。