Oracle Database 12c Release 2 新機能 列単位Collationを試す

Oracle Database の文字列比較のデフォルト値は初期化パラメーター NLS_SORTとNLS_COMPで決まります。どちらのパラメーターもデフォルト値はBINARYで、WHERE句の比較やソート時にはバイナリ比較（大文字／小文字を異なるものとみなす）が使われます。
ソート順の指定には末尾に _CI を付けると大文字と小文字を区別せず、_AI を付けるアクセント記号も区別しません。

COLLATION指定

Oracle Database 12cではこの設定をテーブル単位、列単位で指定できるようになりました。

テーブル単位の指定

下記の例はcollation1テーブルのデフォルトのCollationをBINARY_CIに指定しています。

SQL> CREATE TABLE collation1 (c1 NUMBER, c2 VARCHAR2(10)) DEFAULT COLLATION BINARY_CI ;

表が作成されました。

列単位の指定

列単位に指定する場合は、列定義にCOLLATE句を指定します。

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

表が作成されました。

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

1行が作成されました。

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

1行が作成されました。

上記の例では、WHERE句の指定方法によって取得できるデータが変わります。最初の例ではCOLLATE BINARYが指定されているので大文字と小文字は区別されます。次の例では COLLATE BINARY_CIが指定されているので大文字と小文字は区別されません。

SQL> SELECT * FROM collation2 WHERE c1='ABC' ;

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

SQL> SELECT * FROM collation2 WHERE c2='ABC' ;

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

実行計画を確認してみる

実行計画を見ると、COLLATE指定された列に対する検索ではNLS_SORT関数が自動的に実行されていることがわかります。

SQL> SELECT * FROM collation2 WHERE c2='ABC' ;

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

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

--------------------------------------------------------------------------------
| Id  | Operation         | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |            |     1 |    14 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| COLLATION2 |     1 |    14 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NLSSORT("C2",'nls_sort=''BINARY_CI''')=HEXTORAW('61626300'))

COLLATION設定が異なる列の結合

COLLATION設定が異なる列では単純に結合できません。

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

表が作成されました。

SQL> CREATE TABLE collation4 (c1 VARCHAR2(10), c2 VARCHAR2(10) COLLATE BINARY_AI) ;

表が作成されました。

SQL> SELECT * FROM collation3  ci INNER JOIN collation4 ai ON ci.c2 = ai.c2 ;
SELECT * FROM collation3  ci INNER JOIN collation4 ai ON ci.c2 = ai.c2
                                                                 *
行1でエラーが発生しました。:
ORA-43915: 照合を決定できません: 引数に競合する照合があります

WHERE句にCOLLATE句を使って同じ照合順序で比較するようにしてください。

SQL> SELECT * FROM collation3  ci INNER JOIN collation4 ai ON ci.c2 = ai.c2 COLLATE BINARY_CI ;
レコードが選択されませんでした。

実行計画
----------------------------------------------------------
Plan hash value: 377530914
---------------------------------------------------------------------------------
| Id  | Operation          | Name       | Rows  | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |            |     1 |    28 |     4   (0)| 00:00:01|
|*  1 |  HASH JOIN         |            |     1 |    28 |     4   (0)| 00:00:01|
|   2 |   TABLE ACCESS FULL| COLLATION3 |     1 |    14 |     2   (0)| 00:00:01|
|   3 |   TABLE ACCESS FULL| COLLATION4 |     1 |    14 |     2   (0)| 00:00:01|
---------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access(NLSSORT("CI"."C2",'nls_sort=''BINARY_CI''')=NLSSORT("AI"."C2" COLLATE "BINARY_CI",'nls_sort=''BINARY_CI'''))