C SQL操作の照合導出および決定ルール
この付録では、SQL操作の照合導出および決定ルールについて説明します。この付録の内容は次のとおりです。
C.1 照合導出
SQL操作の文字結果の照合を決定するプロセスを照合導出と言います。このような操作として、演算子、列参照、文字リテラル、バインド変数参照、関数コール、CASE式または問合せ句があります。
SQL式の各文字値には、導出された照合と導出された照合順序強制性レベルがあります。
次の表に、基本的な式の導出された照合と照合順序強制性レベルを示します。
表C-1 様々な式タイプの導出された照合と導出された照合順序強制性レベル
式のタイプ | 導出された照合 | 導出された照合順序強制性レベル |
---|---|---|
|
|
0 |
表、ビューまたはマテリアライズド・ビューの列参照などのデータ・コンテナ参照 |
データ・コンテナの宣言された名前付き照合または擬似照合 |
2 |
PL/SQL関数コールまたはユーザー定義演算子の結果 |
|
2 |
文字リテラル |
最上位の文に含まれている場合は |
4 |
|
|
4 |
|
IDが属性値として渡される照合 |
0 |
ノート:
-
照合順序強制性レベル1は、割り当てられた照合がないことです
-
照合順序強制性レベル3は、将来の使用のために予約されています
操作結果の導出照合と照合順序強制性レベルは、操作の引数の照合および照合順序強制性レベルに基づきます。操作の導出関連の文字引数とは、操作結果の照合を導出するために使用される引数です。1つの演算子には、0個以上の導出関連の文字引数と、0個以上のその他の文字引数(たとえば、フラグまたは導出関連の引数と直接対話しないその他の制御情報など)が含まれます。引数の値が変換後に、または変換なしで結果に含まれる場合、その引数は導出関連であるとみなされます。
書式モデル、パターン、フラグ文字列、またはシステム情報の仮想表へのキーである引数は、導出関連の引数とはみなされません。たとえば、組込み関数TO_CHAR(arg1,arg2,arg3)
は、メイン引数arg1
が文字データ型の引数ではないため、導出関連の引数はありません。2つの文字引数arg2
およびarg3
は、メイン引数arg1
の変換の書式およびパラメータを定義するだけであるため、導出関連の引数ではありません。
導出関連の引数を持たない操作の結果の導出された照合と照合順序強制性レベルは、その式に操作のかわりに文字リテラルを指定した場合と同じになります。
次に、文字値を戻す、導出関連の引数を持つ操作に対する照合導出ルールを示します。これらのルールは、式ツリーに再帰的に適用されます。これらのルールは、SQL標準バージョンISO/IEC 9075-2:1999に基づいています。
導出関連の文字引数arg1, arg2, …, argnを持つ操作の結果の導出照合は、次のようになります。
-
少なくとも1つの引数の照合順序強制性レベルが0である場合、照合順序強制性レベルが0であるすべての引数には同じ照合、つまり、結果の導出照合が割り当てられる必要があります。結果の照合順序強制性レベルは0です。照合順序強制性レベルが0である2つの引数に異なる照合が指定されると、エラーがレポートされます。
-
そうではなく、少なくとも1つの引数の照合順序強制性レベルが1である場合は、式結果は照合順序強制性レベル1になり、照合は割り当てられません。
-
そうでない場合、LCLが引数の数値的に最も小さい照合順序強制性レベルである場合は、次のようになります。
-
LCLが指定されたすべての引数に同じ照合が指定されている場合、この照合は、結果の導出された照合であり、結果の照合順序強制性レベルはLCLになります。
-
そうでない場合、式の結果の照合順序強制性レベルは1になり、照合は割り当てられません。
-
ノート:
set演算子には、式リストである引数があります。set演算子については、照合導出は、式リスト内の各引数の対応する要素に対して個別に実行されます。たとえば、次の問合せがあるとします。
SELECT expr1, expr2 FROM t1
UNION
SELECT expr3, expr4 FROM t2
照合は、結果セットの最初の列と2番目の列に対して個別に導出されます。最初の列については、照合導出ルールはexpr1
およびexpr3
に適用されます。2番目の列については、照合導出ルールはexpr2
およびexpr4
に適用されます。
関連項目:
バインド変数参照の照合導出
OCIでは、OCI_ATTR_COLLATION_ID
属性の値を使用して、問合せまたはDML文でバインド変数の照合を渡すことができます。バインド変数ハンドルで、OCIAttrSet()
関数を使用してOCI_ATTR_COLLATION_ID
属性をサポートされる照合IDのいずれかに設定できます。名前付き照合および疑似照合の両方のIDが許可されます。この場合、バインド変数参照の導出される照合順序強制性レベルは0です。
バインド変数ハンドルでOCI_ATTR_COLLATION_ID
属性値がOCI_COLLATION_NONE
(デフォルト値)に設定されている場合、バインド変数の照合はUSING_NLS_COMP
で、バインド変数参照の導出される照合順序強制性レベルは4です。
OCIでは、バインド変数の特定のデータ型に対して照合が有効かどうかはチェックされません。OCI_ATTR_COLLATION_ID
属性値が非文字データ型変数に対して設定されている場合、データベース・サーバーによって無視されます。
バインド変数の照合は、現在PL/SQL式では無視されます。上位互換性のために、変数がSQL文でのみ参照される場合を除いて、OCI_ATTR_COLLATION_ID
属性を匿名PL/SQLブロックに渡されるバインド変数に対して設定しないでください。
関連項目:
OCI_ATTR_COLLATION_ID
属性の詳細は、Oracle Call Interfaceプログラマーズ・ガイドを参照してください。
C.2 照合決定
照合決定とは、照合依存操作の実行中に、適用する正しい照合を選択するプロセスです。照合依存操作には、SQL演算子、条件、組込み関数コール、CASE式または問合せ句などがあります。
Oracle Databaseリリース12.2以前では、操作で適用される照合はNLS_SORT
およびNLS_COMP
セッション・パラメータによってのみ決定されていました。
ノート:
NLS_UPPER、NLS_LOWER、NLS_INITCAP
およびNLSSORT
のオプションの2つ目のパラメータは例外です。
Oracle Databaseリリース12.2以降では、操作で適用される照合は、その引数のデータ・バインドされた導出照合によって決定されます。使用する照合が擬似照合であると決定されると、適用する実際の名前付き照合を指定するためにNLS_SORT
およびNLS_COMP
セッション・パラメータがチェックされます。
ノート:
照合決定は、照合導出が適用される操作と同じ操作には適用する必要はありません。たとえば、TO_CHAR
関数は照合依存ではないため、照合決定は必要ありません。ただし、TO_CHAR
は照合宣言を必要とする文字結果を戻すため、照合導出が適用されます。これに対し、INSTR
関数は文字を照合する必要があり、この照合操作のために照合を決定する必要があります。ただし、INSTR
関数の結果は数値であるため、照合導出は必要ありません。
操作の決定関連の文字引数とは、操作によって使用される照合を決定するために使用される引数です。照合依存操作には、1つ以上の決定関連の文字引数と、0個以上のその他の文字引数(たとえば、フラグまたは決定関連の引数と直接対話しないその他の制御情報など)があります。
引数は、操作の評価中に値が比較される場合、決定関連であるとみなされます。書式モデル、フラグ文字列、またはシステム情報の仮想表へのキーである引数は、決定関連の引数とはみなされません。ただし、パターン引数は決定関連の引数である場合があります。たとえば、LIKE
述語の3つの引数のうちの2つ、つまりargumentとpatternは、決定関連の引数です。3つ目の引数であるエスケープ文字は、決定関連の引数とはみなされません。別の例として、4つの引数source_char、pattern、positionおよび match_paramを持つ組込み関数REGEXP_COUNT
があります。決定関連の引数は、比較対象の文字を含むsource_charおよびpatternです。決定関連でない文字引数は、数値であるpositionと、照合操作のパラメータを指定するmatch_paramです。
決定関連の文字引数arg1, arg2, …, argnを持つ操作に使用する照合を決定するための照合決定ルールは、次のとおりです。これらのルールは、SQL標準バージョンISO/IEC 9075-2:1999に基づいています。
-
操作が等価条件であり、外部キー制約を適用するために使用される場合、使用する照合は、参照先の主キーまたは一意キー列の宣言された照合です。この宣言された照合は、外部キー列の宣言された照合と同じである必要があります。
-
そうではなく、少なくとも1つの引数の導出された照合順序強制性レベルが0である場合、照合順序強制性レベルが0であるすべての引数には同じ照合が割り当てられる必要があり、この照合が操作で使用されます。照合順序強制性レベルが0である2つの引数に異なる照合が指定されると、エラーがレポートされます。
-
そうではなく、少なくとも1つの引数の導出された照合順序強制性レベルが1である場合は、エラーがレポートされます。
-
そうでない場合、LCLが引数の数値的に最も小さい照合順序強制性レベルである場合は、次のようになります。
-
LCLが指定されたすべての引数に同じ照合が指定されている場合、その照合が操作で使用されます。
-
指定しないとエラーがレポートされます。
-
決定された照合が擬似照合である場合、適用する実際の名前付き照合を指定するために、影響を受ける操作はセッションまたはデータベース設定NLS_SORT
またはNLS_COMP
、あるいはこれら両方を参照する必要があります。データベース設定は、仮想列、CHECK
制約およびファイングレイン監査(FGA)規則の式で使用されます。
CLOB
またはNCLOB
データ型が関連する操作の照合決定ルールでは、結果として擬似照合USING_NLS_COMP
が指定される必要があり、そうでない場合はエラーがレポートされます。
ノート:
いくつかの条件、set演算子および問合せ句には、式リストである引数があります。この場合、照合決定は、式リスト内の各引数の対応する比較対象要素で実行されます。たとえば、次の条件があるとします。
(expr1, expr2) IN (SELECT expr3, expr4 FROM t1)
照合は、比較される要素のペアに対して個別に決定されます。最初に、照合決定ルールはexpr1
およびexpr3
に適用されます。次に、expr2
およびexpr4
にルールが適用されます。条件が評価されるときに、最初に決定された照合を使用して、expr1
の値がexpr3
の値と比較され、2番目に決定された照合を使用して、expr2
の値がexpr4
の値と比較されます。同様に、次の問合せがあるとします。
SELECT expr1, expr2 FROM t1
MINUS
SELECT expr3, expr4 FROM t2
照合決定ルールは、最初にexpr1
およびexpr3
に適用され、その後expr2
およびexpr4
に適用されます。MINUS
演算子が評価されるときに、最初に決定された照合を使用して、expr1
の値がexpr3
の値と比較され、2番目に決定された照合を使用して、expr2
の値がexpr4
の値と比較されます。
次の問合せがあるとします。
SELECT * FROM t1 ORDER BY expr1, expr2, expr3
行は、最初に、導出照合expr1
を使用してexpr1
の値でソートされ、次に、同順位の場合、導出照合expr2
を使用してexpr2
の値でソートされ、また次に、導出照合expr3
を使用してexpr3
の値でソートされます。ORDER BY
リスト内の各位置は、行の値に対する個別の比較演算子のように扱われます。
関連項目:
C.3 SQL操作とその導出および決定関連の引数
次の表に、文字値を戻すSQL操作、照合依存であるSQL操作、あるいはその両方であるSQL操作のすべてを示します。文字値を戻す各操作については、操作の導出関連の引数を示します。操作にこのような引数がない場合は、かわりに操作結果の固定された照合を示します。リテラル照合という用語は、操作の結果に対して導出された照合が、式に操作のかわりに指定された文字リテラルの照合、つまり、最上位のSQL文のUSING_NLS_COMP
またはビュー、マテリアライズド・ビューまたはソース式を含むPL/SQLストアド・ユニットのデフォルト照合であることを意味します。各照合依存操作について、次の表に操作の決定関連引数を示します。
表C-2 SQL操作の導出関連および決定関連引数
操作タイプ | 操作名 | 操作の説明 | 導出関連引数または固定照合 | 決定関連引数 |
---|---|---|---|---|
擬似列 |
|
フラッシュバック・バージョン問合せの操作タイプ |
リテラル照合 |
— |
擬似列 |
|
文字データ型のネストした表要素の値 |
USING_NLS_COMP |
— |
演算子 |
|
文字値の連結 |
a1, a2 |
— |
演算子 |
|
階層問合せの親値 |
a1 |
— |
演算子 |
|
階層問合せのルート値 |
a1 |
— |
演算子 |
|
2つの行セットの重複のある和集合 |
結果の行セットの各列に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に導出されます。 特殊なケース: 引数 |
— |
演算子 |
|
2つの行セットの重複のない和集合 |
結果の行セットの各列に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に導出されます。 |
引数行セットの各列の比較に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に決定されます。 |
演算子 |
|
2つの行セットの重複のない積集合 |
結果の行セットの各列に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に導出されます。 |
引数行セットの各列の比較に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に決定されます。 |
演算子 |
|
行セットの重複のない差集合 |
結果の行セットの各列に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に導出されます。 |
引数行セットの各列の比較に使用する照合は、2つの引数行セットそれぞれからの列の照合を組み合せて個別に決定されます。 |
式 |
|
検索CASE式 |
|
各条件 |
式 |
|
単純CASE式。次と同じになります。
|
|
v の照合は次の照合より優先されないとします。 この場合、単純CASE式は内部的に検索CASE式に変換されます。 |
式 |
オブジェクト・アクセス式 |
オブジェクト・メソッドへの参照 |
USING_NLS_COMP |
— |
式 |
:name |
バインド変数参照 |
リテラル照合 |
— |
式 |
|
式リスト |
各リスト要素には、それぞれ個別に独立して導出された照合があります。 |
2つのリストが比較される場合、照合決定は、両方のリストの同じ索引で2つの文字データ型要素それぞれに対して個別に独立して実行されます。 |
条件 |
|
単純比較条件 |
— |
|
条件 |
( |
リスト比較条件。次と同じです。
|
— |
照合は、各ペアに対して個別に決定されます。 |
条件 |
( |
問合せ比較条件 |
— |
a1, a2
|
条件 |
|
|
— |
|
条件 |
|
|
— |
|
条件 |
|
範囲比較。次と同じです。
|
— |
照合は、各比較に対して個別に決定されます。 |
条件 |
|
メンバーシップ比較。次と同じです。
|
— |
前述の「=ANY」 を参照
|
関数 |
|
結果セット内の |
— |
a1 |
関数 |
|
Unicodeエスケープを使用して |
a1 |
— |
関数 |
|
値 |
|
— |
関数 |
|
数値コード |
リテラル照合 |
— |
関数 |
|
|
|
— |
関数 |
|
|
リテラル照合 |
— |
関数 |
|
ネストした表に集計します |
— |
|
関数 |
|
|
a1 |
— |
関数 |
|
文字列 |
a1, a2 |
— |
関数 |
|
|
a1 |
— |
関数 |
|
結果セット内の |
— |
a1 |
関数 |
|
Kendallの順位相関係数(Kendall's tau) |
— |
照合は、各引数に対して独立して決定されます。 |
関数 |
|
Spearmanの順位相関係数(Spearman's rho) |
— |
照合は、各引数に対して独立して決定されます。 |
関数 |
|
リレーショナル表へのOLAPキューブまたは階層 |
(生成された表内の文字データ型の各列に対する)リテラル照合 |
— |
関数 |
|
MODEL句内の現在のディメンション値 |
CV()コールが対応するディメンション列の照合、 |
— |
関数 |
|
文字列としてのデータベース・タイム・ゾーン |
リテラル照合 |
— |
関数 |
|
値の選択 |
|
|
関数 |
|
Unicode正規化(NFD、NFKD)。 |
a1 |
— |
関数 |
|
値グループ内の値の稠密ランク |
— |
ランキングは、関数の |
関数 |
|
フォーマット |
リテラル照合 |
— |
関数 |
EMPTY_CLOB |
空の |
USING_NLS_COMP |
— |
関数 |
|
日時値 |
リテラル照合 |
— |
関数 |
|
|
リテラル照合 |
— |
関数 |
|
行セットからの |
a1 |
— |
関数 |
|
|
|
|
関数 |
|
|
a1 |
— |
関数 |
|
位置 |
— |
|
関数 |
|
JSONパス式 |
リテラル照合 |
— |
関数 |
|
JSONパス式 |
(生成された表内の文字データ型の各列に対する)リテラル照合 |
— |
関数 |
|
JSONパス式 |
リテラル照合 |
— |
関数 |
|
行オフセット |
a1 |
— |
関数 |
|
行セットからの |
a1 |
— |
関数 |
|
行オフセット |
a1 |
— |
関数 |
|
|
|
|
関数 |
|
複数の行から |
|
— |
関数 |
|
|
a1 |
— |
関数 |
|
表示長が |
a1 |
— |
関数 |
|
文字列 |
a1 |
a1 |
関数 |
|
結果セット内の |
a1 |
|
関数 |
|
結果セット内の |
a1 |
a1 |
関数 |
|
数値コード |
リテラル照合 |
— |
関数 |
|
ID |
リテラル照合 |
— |
関数 |
|
ID |
リテラル照合 |
— |
関数 |
|
オプションで |
a1 |
|
関数 |
|
オプションで |
a1 |
|
関数 |
|
オプションで |
a1 |
|
関数 |
|
オプションで |
— |
|
関数 |
|
行セットからの |
a1 |
— |
関数 |
|
これは、次の操作に相当します。
|
a1 |
|
関数 |
|
|
a1, a2 |
— |
関数 |
|
|
a2, a3 |
— |
関数 |
|
起動ユーザー名 |
リテラル照合 |
— |
関数 |
|
リソースへのパス。 |
リテラル照合 |
— |
関数 |
|
値グループ内の値のパーセント・ランク |
— |
ランキングは、関数の |
関数 |
|
データ・マイニングの予測 |
リテラル照合 |
— |
関数 |
|
セル参照 |
a2, a3 |
— |
関数 |
|
セル参照 |
a2, a3 |
— |
関数 |
|
MODEL句での反復の最初にあるセル参照a1の値 |
a1 |
— |
関数 |
|
値グループ内の値のランク |
— |
ランキングは、関数の |
関数 |
|
RAW値 |
リテラル照合 |
— |
関数 |
|
|
リテラル照合 |
— |
関数 |
|
フラグ |
— |
|
関数 |
|
フラグ |
— |
|
関数 |
|
フラグ |
a1 |
|
関数 |
|
フラグ |
a1 |
|
関数 |
|
|
a1 |
|
関数 |
|
行ID |
リテラル照合 |
— |
関数 |
|
行ID |
リテラル照合 |
— |
関数 |
|
表示長が |
a1 |
— |
関数 |
|
文字列 |
a1 |
|
関数 |
|
文字列としてのデータベース・タイム・ゾーン |
リテラル照合 |
— |
関数 |
|
|
a1 |
— |
関数 |
|
|
— |
|
関数 |
|
|
— |
照合は、各引数に対して独立して決定されます。 |
関数 |
|
|
— |
|
関数 |
|
コルモゴロフ-スミルノフ関数 |
— |
照合は、各引数に対して独立して決定されます。 |
関数 |
|
結果セット内の |
a1 |
|
関数 |
|
Mann-Whitney検定 |
— |
照合は、各引数に対して独立して決定されます。 |
関数 |
|
一元配置分散分析 |
— |
|
関数 |
|
同じ分散を持つ独立したグループのt検定 |
— |
|
関数 |
|
分散が等しくない独立したグループのt検定 |
— |
|
関数 |
|
位置 |
a1 |
— |
関数 |
|
列の値が文字 |
a1 |
— |
関数 |
|
ネームスペース |
リテラル照合 |
— |
関数 |
/*character*/ |
|
a1 |
— |
関数 |
/*datetime*/ |
オプションの書式 |
リテラル照合 |
— |
関数 |
/*number*/ |
オプションの書式 |
リテラル照合 |
— |
関数 |
|
|
(結果は |
— |
関数 |
/*long*/ |
|
(結果は |
— |
関数 |
|
|
a1 |
— |
関数 |
/*character*/ |
|
a1 |
— |
関数 |
/*datetime*/ |
オプションの書式 |
リテラル照合 |
— |
関数 |
/*number*/ |
オプションの書式 |
リテラル照合 |
— |
関数 |
|
|
a1 (結果は |
— |
関数 |
|
|
a1 |
— |
関数 |
|
|
a1 |
|
関数 |
|
( |
a1 |
— |
関数 |
|
|
a2 |
|
関数 |
|
タイム・ゾーン |
リテラル照合 |
— |
関数 |
|
Unicodeエスケープを解釈して文字列 |
a1 |
— |
関数 |
|
文字列 |
a1 |
— |
関数 |
|
ログイン・ユーザー名 |
リテラル照合 |
— |
関数 |
|
|
リテラル照合 |
— |
関数 |
|
|
リテラル照合 |
— |
関数 |
|
XML文書 |
リテラル照合 |
— |
関数 |
|
XMLオブジェクト内容を仮想リレーショナル表として表します |
(生成された表内の文字データ型の各列に対する)リテラル照合 |
— |
句 |
|
分析句のパーティション化 |
— |
照合は、句内の各文字引数について個別に決定されます。 |
句 |
|
分析句の順序付け |
— |
照合は、句内の各文字引数について個別に決定されます。 |
句 |
|
集計関数の順序付け |
— |
照合は、句内の各文字引数について個別に決定されます。 |
句 |
|
問合せ結果の順序付け |
— |
照合は、句内の各文字引数について個別に決定されます。 |
句 |
|
問合せ行のグループ化 |
— |
照合は、句内の各文字引数について個別に決定されます。 |