1. JSON開発者ガイド
  2. JSON用のパフォーマンス・チューニング
  3. JSONデータの索引

29 JSONデータの索引

関数ベースの索引を使用すると、JSONデータのスカラー値を索引付けできます。また、JSON検索索引を定義できます。これは、非定型の構造的問合せと全文問合せの両方に便利です。

  • JSONデータの索引付けの概要
    関数ベースの索引を使用して、JSONデータ内の特定のスカラー値を索引付けできます。非定型の構造的問合せおよび全文問合せ用に、JSON検索索引を使用して通常の方法でJSONデータを索引付けできます。
  • JSONデータに対して関数ベースの索引が選択されるかどうかを確認する方法
    特定の問合せに対して特定の索引が選択されるかどうかは、オプティマイザによって決定されます。特定の問合せで特定の関数ベースの索引が選択されるかどうかを確認するには、問合せの実行計画内で索引名を検索します。
  • JSON_VALUEのためのビットマップ索引の作成
    SQL/JSONファンクションjson_valueのためにビットマップ索引を作成できます。ビットマップ索引は、問合せが小さいセットのJSON値のみを対象にしている場合に適しています。
  • JSON_VALUEのためのBツリー索引の作成
    SQL/JSONファンクションjson_valueのために、Bツリーの関数ベースの索引を作成できます。これを行うには、標準構文を使用してjson_valueを明示的に指定するか、項目メソッドを持つドット表記法構文を使用できます。これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。
  • JSON_VALUE関数ベースの索引とJSON_TABLE問合せの使用
    ERROR ON ERRORを指定したjson_valueを使用して作成された索引は、json_tableを含む問合せに使用できます。この場合、この索引が索引付きパスに対する制約として機能することで、JSONデータ内の項目ごとに必ず1つの(非null)スカラーJSON値のみが投影されるようになります。
  • JSON_VALUE関数ベースの索引とJSON_EXISTS問合せの使用
    ERROR ON ERRORを指定したSQL/JSONファンクションjson_valueを使用して作成された索引は、SQL/JSON条件json_existsを含む問合せに使用できます。
  • JSON_VALUEの索引付けおよび問合せに関するデータ型の考慮事項
    SQL/JSONファンクションjson_valueを使用して作成されたファンクション・ベースの索引では、問合せでjson_valueによって返されたデータ型が索引で指定された型と一致する必要があります。
  • JSON_EXISTSの複数値関数ベースの索引の作成
    JSONデータ型として格納されるJSONデータの場合、SQL/JSON条件json_existsに複数値関数ベースの索引を使用できます。このような索引は、スカラーJSON値を個別に、またはJSON配列内で対象とします。
  • 複数値関数ベースの索引の使用
    WHERE句のjson_exists問合せでは、対象とするデータが索引に指定されたスカラー型と一致する場合(かつその場合にかぎり)、複数値関数ベースの索引を選択できます。
  • コンポジットBツリー索引を使用した複数のJSONフィールドの索引付け
    JSONオブジェクトの複数のフィールドに索引を付けるには、SQL/JSONファンクションjson_valueまたはドット表記法構文で複数のパス式を使用してコンポジットBツリー索引を作成できます。
  • 非定型の問合せおよび全文検索のためのJSON検索索引
    JSON検索索引は、一般的な索引です。この検索索引によって、(1)非定型の構造的問合せ、つまり、定期的には予測または使用されない可能性のある問合せと、(2)全文検索の両方のパフォーマンスを向上させることができます。これは、JSONデータでの使用に特化して設計されたOracle Text索引です。

親トピック: JSON用のパフォーマンス・チューニング

29.1 JSONデータの索引付けの概要

関数ベースの索引を使用して、JSONデータ内の特定のスカラー値を索引付けできます。非定型の構造的問合せおよび全文問合せ用に、JSON検索索引を使用して通常の方法でJSONデータを索引付けできます。

同様に、関数ベースの索引付けは、特定の関数を対象とする問合せに適していますが、これは、SQL/JSONファンクションのコンテキストでは特定のSQL/JSONパス式を意味します。この索引付けは、非定型(つまり、任意)の問合せの場合はあまり役に立ちません。関数ベースの索引を定義するのは、特定のパス式を何度も問い合せることがわかっている場合にしてください。

JSONデータの格納に使用するSQLデータ型に関係なく、SQL/JSONファンクションjson_value問合せにBツリーまたはビットマップ関数ベースの索引を使用できます。このような索引は、単一のスカラーJSON値を対象とします。関数で想定される値の数が少ない場合は、常にビットマップ索引が適しています。たとえば、対象となる値が少数であると予想される場合は、json_valueのためにビットマップ索引を使用できます。

JSON型として格納されるJSONデータの場合、SQL/JSON条件json_existsに複数値関数ベースの索引を使用できます。このような索引は、スカラーJSON値を個別に、または(特に)JSON配列の要素として対象にします。

複数値索引は単一のスカラー値に索引付けできますが、このような値を対象とするパス式が予想される場合は、Bツリー索引またはビットマップ索引を使用する方がパフォーマンスが高くなります。特に、スカラー値の配列を対象にする予定のパス式に索引を付けるには、複数値索引を使用します。

フィルタ式が含まれたSQL/JSONパス式は、ファンクション・ベースの索引を選択する問合せで使用できます。ただし、ファンクション・ベースの索引を定義するために使用するパス式には述語を含めることができません。

非定型方式で問い合せる場合は、JSON検索索引を定義してください。これは、一般的索引であり、特定のパス式の対象ではありません。これは、構造問合せ(特定の値を持つJSONフィールドを検索する場合など)や、Oracle SQL条件json_textcontainsを使用した全文問合せ(様々な文字列値から特定の単語を検索する場合など)に適しています。

同じJSON列に対して関数ベースの索引とJSON検索索引を定義することもできます。

JSON検索索引は、JSONデータでの使用に特化して設計されたOracle Text (全文)索引です。

ノート:

データベース文字セットとしてAL32UTF8を使用することをお薦めします。索引の作成または適用時に、文字セットの自動変換が行われる場合があります。このような変換では情報が失われる可能性があります。これは、問合せで戻されることが予測されるデータが、戻されない場合があることを意味します。JSONデータの文字セットおよび文字エンコーディングを参照してください。

静的ディクショナリ・ビューDBA_JSON_INDEXES、ALL_JSON_INDEXESおよびUSER_JSON_INDEXESでは、それぞれ、データベース内のJSONデータに対するすべての索引、それらのうち現在のユーザーがアクセスできるすべて、およびそれらのうち現在のユーザーが所有しているすべてが示されます。

コンポジット索引の場合、静的ディクショナリ・ビューDBA_TABLE_VIRTUAL_COLUMNS、ALL_TABLE_VIRTUAL_COLUMNSおよびUSER_TABLE_VIRTUAL_COLUMNSが、索引付け用に自動的に作成される仮想列に関する情報を提供します。*_JSON_INDEXESビューを補足します。

関連項目

親トピック: JSONデータの索引

29.2 JSONデータに対して関数ベースの索引が選択されるかどうかを確認する方法

指定された問合せに対して特定の索引が選択されるかどうかは、オプティマイザによって決定されます。特定の問合せで特定の関数ベースの索引が選択されるかどうかを確認するには、問合せの実行計画内で索引名を検索します。

たとえば:

  • 例29-3で定義した索引が指定されると、例29-5、例29-6、例29-7、例29-8および例29-10の例の各問合せの実行計画は、索引po_num_id1によって索引スキャンを参照します。

  • 例29-14で定義した索引が指定されると、例29-17および例29-18の問合せの実行計画は、索引mvi_1によって索引スキャンを参照します。

複数値索引が選択された場合、索引範囲スキャンの実行計画には(MULTI VALUE)も表示され、計画に使用されるフィルタはJSON_EXISTS2ではなくJSON_QUERYです。実行計画に、指定されたjson_exists問合せに対する複数値索引が使用されない場合、フィルタはJSON_EXISTS2になります。

関連項目

親トピック: JSONデータの索引

29.3 JSON_VALUEのためのビットマップ索引の作成

SQL/JSONファンクションjson_valueに対してビットマップ索引を作成できます。ビットマップ索引は、問合せが小さいセットのJSON値のみを対象にしている場合に適しています。

例29-1 JSON_VALUEのビットマップ索引の作成

データ内のフィールドCostCenterに対して想定される値が少ない場合、これは作成に適した索引です。 

CREATE BITMAP INDEX cost_ctr_idx ON j_purchaseorder
  (json_value(po_document, '$.CostCenter'));

親トピック: JSONデータの索引

29.4 JSON_VALUEのためのBツリー索引の作成

SQL/JSONファンクションjson_valueのために、Bツリーの関数ベースの索引を作成できます。これを行うには、標準構文を使用してjson_valueを明示的に指定するか、項目メソッドを持つドット表記法構文を使用できます。これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。

例29-3では、表j_purchaseorderの列po_document内にあるオブジェクトのフィールドPONumberに対するjson_valueのファンクション・ベースの索引を作成しています。オブジェクトはパス式のコンテキスト項目として渡されます。

ここでERROR ON ERRORを使用することは、フィールドPONumberがないレコード、複数のPONumberフィールドを持つレコードまたは非数値の値を持つPONumberフィールドがあるレコードがデータに含まれる場合、索引の作成が失敗することを意味します。また、索引が存在する場合、このようなレコードを挿入しようとしても失敗します。

代替の方法としては、JSONデータに対する単純なドット表記法アクセスで説明されているように、ドット表記法構文を使用して対象となるデータに項目メソッドを適用して索引を作成する方法があります。例29-2に、これを示します。

例29-3と例29-2の両方で作成した索引は、ドット表記法構文を使用する問合せとjson_valueを使用する問合せのいずれかに対して選択できます。

json_value式の対象のフィールドで欠落している可能性のあるデータの索引付けを許可する場合は、NULL ON EMPTY句をERROR ON ERROR句と一緒に使用します。例29-4に、これを示します。

次のいずれかの形式を使用してjson_valueに対して関数ベースの索引を作成することをお薦めします。どちらの場合も、指定されたJSONデータ型のスカラーの結果となるドット表記法およびjson_value問合せの両方で、索引を使用できます。

  • 項目メソッドが索引付けされる値に適用されるドット表記法構文。索引付けされる値は、項目メソッドで指定されたデータ型のスカラーのみです。

  • RETURNINGのデータ型を指定するjson_value式。必要に応じて、ERROR ON ERRORおよびNULL ON EMPTYを使用できます。索引付けされる値は、RETURNING句で指定されたデータ型のスカラーのみです。

このように、これらいずれかの方法で作成された索引は、ドット表記法問合せとjson_value問合せのどちらとも一緒に使用できます。

関連項目:

Oracle Database SQL言語リファレンスのCREATE INDEX

例29-2 JSONフィールドに対するファンクション・ベースの索引の作成: ドット表記法

項目メソッドnumber()を使用すると、数値型の索引が作成されます。ドット表記法を使用して関数ベースの索引を作成する場合は、対象データに項目メソッドを常に適用します。 

CREATE UNIQUE INDEX po_num_idx1 ON j_purchaseorder po
  (po.po_document.PONumber.number());

ノート:

デフォルトでは、関数ベースの索引にはNULL値は含まれません。索引で使用されるjson_value式がNULLを返す場合、デフォルトでは、一致するドキュメントの取得時に索引は使用されません。これは、NULLがフィルタ述語として使用されている場合(たとえば、json_value ... IS NULL)、またはORDER BY句でjson_valueが使用されている場合、デフォルトで関数ベースの索引が使用されないことを意味します。

NULL値を索引付けしてORDER BY句でjson_valueを使用できるようにするには、索引作成文に定数値(任意の値)を追加する必要があります。 

CREATE INDEX po_num_idx1 ON j_purchaseorder po
  (po.po_document.PONumber.number(), 42);

ただし、索引のサイズは大きくなります。

例29-3 JSONフィールドに対するファンクション・ベースの索引の作成: JSON_VALUE

項目メソッドnumber()を使用すると、数値型の索引が作成されます。かわりに、RETURNING NUMBER句を使用することもできます。 

CREATE UNIQUE INDEX po_num_idx2 ON j_purchaseorder
  (json_value(po_document, '$.PONumber.number()' 
              ERROR ON ERROR));

例29-4 JSON_VALUEファンクション・ベースの索引に対するNULL ON EMPTYの指定

RETURNING VARCHAR2(200)句を指定すると、索引は最大長200文字のSQL文字列になります。かわりにパス式に項目メソッドstring()を使用できますが、その場合は、デフォルトの戻り型VARCHAR2(4000)が使用されます。

句NULL ON EMPTYが指定されているため、索引po_ref_idx1は、ReferenceフィールドがないJSON文書にも索引を付けることができます。 

CREATE UNIQUE INDEX po_ref_idx1 ON j_purchaseorder
  (json_value(po_document, '$.Reference'
              RETURNING VARCHAR2(200) ERROR ON ERROR
              NULL ON EMPTY));

関連項目

親トピック: JSONデータの索引

29.5 JSON_VALUE関数ベースの索引とJSON_TABLE問合せの使用

ERROR ON ERRORを指定したjson_valueを使用して作成された索引は、json_tableを含む問合せに使用できます。この場合、この索引が索引付きパスに対する制約として機能することで、JSONデータ内の項目ごとに必ず1つの(非null)スカラーJSON値のみが投影されるようになります。

この方法で索引を使用する場合は、次の各条件が満たされている必要があります。

  • 問合せのWHERE句は、json_tableによって投影される列を参照する。

  • その列のデータ型は、索引定義に使用されたデータ型と一致する。

  • その列を対象とする有効なSQL/JSONパスが、索引付けされたパス式と一致する。

そのため、例29-5の問合せでは、例29-3で作成した索引を使用しています。

ノート:

json_value式またはドット表記法を使用して作成された関数ベースの索引を、問合せでWHERE句が出現するたびに取得できるのは、この発生がSQLの比較条件(>=など)で使用されている場合のみです。特に、これは、条件IS NULLまたはIS NOT NULLで使用される存在としては選択されません。

SQL比較条件の詳細は、Oracle Database SQL言語リファレンスを参照してください。

例29-5 JSON_VALUEファンクション・ベースの索引とJSON_TABLE問合せの使用

列のSQL型NUMBER(5)が索引に使用されている型と一致するため、索引を選択できます。 

SELECT jt.*
  FROM j_purchaseorder po,
       json_table(po.po_document, '$'
         COLUMNS po_number  NUMBER(5) PATH '$.PONumber',
                 reference  VARCHAR2(30 CHAR) PATH '$.Reference',
                 requestor  VARCHAR2(32 CHAR) PATH '$.Requestor',
                 userid     VARCHAR2(10 CHAR) PATH '$.User',
                 costcenter VARCHAR2(16 CHAR) PATH '$.CostCenter') jt
  WHERE po_number = 1600;

親トピック: JSONデータの索引

29.6 JSON_VALUE関数ベースの索引とJSON_EXISTS問合せの使用

ERROR ON ERRORを指定したSQL/JSONファンクションjson_valueを使用して作成された索引は、SQL/JSON条件json_existsを含む問合せに使用できます。

問合せの比較のいずれかでjson_value関数ベースの索引が選択されるためには、その比較の型が、索引のSQL戻りデータ型と同じである必要があります。使用されるSQLデータ型は、double()、float()、number()、string()、timestamp()、date()、dateWithTime()、dsInterval()およびymInterval()の項目メソッドで言及されたデータ型です(SQL/JSONパス式の項目メソッドを参照)。

たとえば、索引で数値が戻される場合は、比較の型も数値である必要があります。問合せのフィルタ式に、json_value索引と一致する複数の比較が含まれる場合は、これらの索引の1つがオプティマイザによって選択されます。

比較の型は、次のように決まります。

  1. 2つの比較語(比較の両側)のSQLデータ型が異なる場合、比較の型は不明になり、索引は選択されません。両方の型が同じ場合は、その型が比較の型になります。

  2. 1つの比較語のSQLデータ型が文字列(テキスト・リテラル)である場合、比較の型は、もう一方の比較語の型になります。

  3. 1つの比較語が、項目メソッドによってSQLの一致型が強制される関数ステップを持つパス式の場合、その型も比較語の型になります。SQLの一致型が強制される項目メソッドは、double()、float()、number()、string()、timestamp()、date()、dateWithTime(), dsInterval()およびymInterval()です。

  4. 1つの比較語が、前述のような関数ステップを持たないパス式の場合、その型はSQL文字列(テキスト・リテラル)になります。

例29-3では、フィールドPONumberのjson_valueに対するファンクション・ベースの索引を作成します。この索引は、NUMBER値に索引付けします。

例29-6、例29-7および例29-8のそれぞれの問合せでは、json_exists条件を評価するときに、この索引を使用できます。これらの各問合せで、絶対パス式$.PONumberに相対的な単純なパス式に関連する比較が使用されます。それぞれの場合の単純な相対パス式は、カレント・フィルタ項目(@)を対象としますが、例29-8の場合は、照合データをSQLデータ型NUMBERに変換(キャスト)します。

例29-6 リテラル数値と比較したフィールドを対象としたJSON_EXISTS問合せ

この問合せでは、次の理由から索引が利用されます。

  1. 1つの比較語が、関数ステップを持たないパス式であるため、その型はSQL文字列(テキスト・リテラル)である。

  2. 1つの比較語が文字列型であるため、比較の型はもう一方の語の型になり、その型が数値である(もう一方の語は数字)。

  3. (唯一の)比較の型が、索引で戻される型(数値)と同じである。 

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@ > 1500)');

例29-7 変数値と比較したフィールドを対象としたJSON_EXISTS問合せ

この問合せでは、次の理由から索引を利用できます。

  1. 1つの比較語が、関数ステップを持たないパス式であるため、その型はSQL文字列(テキスト・リテラル)である。

  2. 1つの比較語が文字列型であるため、比較はもう一方の語の型を持つことになり、この型が数値である(もう一方の語は、数値にバインドされた変数)。

  3. (唯一の)比較の型が、索引で戻される型(数値)と同じである。 

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@ > $d)'
                    PASSING 1500 AS "d");

例29-8 変数値と比較して数値にキャストされるフィールドを対象としたJSON_EXISTS問合せ

この問合せでは、次の理由から索引を利用できます。

  1. 1つの比較語が、項目メソッド(number())によって照合データが数値に変換される関数ステップを持つパス式であるため、比較語の型はSQL数値である。

  2. もう一方の比較語が、SQL型数値を持つ数値である。比較語の型が一致しているため、比較も同じ型である数値になる。

  3. (唯一の)比較の型が、索引で戻される型(数値)と同じである。 

SELECT count(*) FROM j_purchaseorder 
  WHERE json_exists(po_document, '$.PONumber?(@.number() > $d)'
                    PASSING 1500 AS "d");

例29-9 フィールド比較の論理積を対象としたJSON_EXISTS問合せ

例29-6と同様に、この問合せではフィールドPONumberの索引を利用できます。json_value索引がフィールドReferenceに対しても定義されている場合、オプティマイザがこの問合せに対してどちらの索引を使用するかを選択します。 

SELECT count(*) FROM j_purchaseorder
  WHERE json_exists(po_document,
                    '$?(@.PONumber > 1500
                        && @.Reference == "ABULL-20140421")');

関連項目

親トピック: JSONデータの索引

29.7 JSON_VALUEの索引付けおよび問合せに関するデータ型の考慮事項

SQL/JSONファンクションjson_valueを使用して作成されたファンクション・ベースの索引では、問合せでjson_valueによって返されたデータ型が索引で指定された型と一致する必要があります。

RETURNING DATEがjson_valueとともに使用される場合、索引が選択されるためには、同じ時間処理動作(切捨てまたは保存)を索引と問合せの両方で使用する必要があります。つまり、両方でRETURNING DATE PRESERVE TIMEを使用するか、両方でRETURNING DATE TRUNCATE TIME(または、切捨てがデフォルトの動作であるためRETURNING DATE)を使用する必要があります。

デフォルトでは、SQL/JSONファンクションjson_valueはVARCHAR2値を戻します。json_valueを使用して関数ベースの索引を作成する場合、RETURNING句または項目メソッドを使用して異なる戻りデータ型を指定しないかぎり、非VARCHAR2値を想定する問合せに対してこの索引は選択されません。

たとえば、例29-10の問合せの場合は、json_valueでRETURNING NUMBERが使用されています。この問合せに対して例29-3で作成した索引を選択できますが、これは、索引付けされたjson_value式で戻り型としてNUMBERが指定されているためです。索引にキーワードRETURNING NUMBERが指定されていない場合、指定される戻り型はVARCHAR2(4000) (デフォルト)になり、そのような問合せに対して索引は選択されません。

同様に、例29-2で作成した索引は、戻り型NUMBERが強制される項目メソッドnumber()を使用しているため、この問合せに対して選択できます。

ここで、戻される値の型がVARCHAR2になるようにRETURNING句なしでjson_valueを使用している例29-11と例29-12の問合せについて検討してみます。

例29-11では、SQLファンクションto_numberにより、json_valueによって戻されるVARCHAR2値を明示的に数値に変換しています。同様に、例29-12では、比較条件> (より大きい)により、暗黙的に値を数値に変換しています。

例29-3と例29-2の索引のいずれも、これらの問合せに対して選択されません。これらの問合せでは、いずれの事例でも型キャストのために正しい結果が戻される可能性がありますが、これらの索引を使用してこれらの問合せを評価することはできません。

また、一部のデータを特定のデータ型に変換できない場合に何が起きるかについても検討してください。たとえば、例29-10、例29-11および例29-12の問合せが実行された場合、"alpha"などのPONumber値はどうなるでしょうか。

例29-11および例29-12の場合、値を数値にキャストしようとするために問合せはエラーで停止します。ただし、例29-10の場合、デフォルトのエラー処理動作がNULL ON ERRORであるため、非数値の"alpha"は単純にフィルタで除外されます。値には索引が付けられますが、問合せに対しては無視されます。

同様に、たとえばDEFAULT '1000' ON ERRORが問合せで使用されたとすると、つまり、数値のデフォルト値が指定されたとすると、値"alpha"に対してエラーは発生しません。この場合、デフォルト値の1000が使用されます。

ノート:

指定された問合せに対してSQL/JSONファンクションjson_valueに基づくファンクション・ベースの索引が選択されるためには、索引と問合せの両方で同じ戻りデータ型および処理方法(エラー、空、不一致)を使用する必要があります。

つまり、問合せの戻り型または処理方法を変更して、索引に指定されたものと一致しないようにする場合は、問合せパターンに依存する永続オブジェクトを再構築する必要があります。(同じことが、マテリアライズド・ビュー、パーティション、チェック制約およびそのパターンに依存するPL/SQLサブプログラムにも当てはまります。)

例29-10 JSON_VALUE問合せと明示的なRETURNING NUMBER

SELECT count(*) FROM j_purchaseorder po
  WHERE json_value(po_document, '$.PONumber' RETURNING NUMBER) > 1500;

例29-11 JSON_VALUE問合せと明示的な数値変換

SELECT count(*) FROM j_purchaseorder po
  WHERE to_number(json_value(po_document, '$.PONumber')) > 1500;

例29-12 JSON_VALUE問合せと暗黙的な数値変換

SELECT count(*) FROM j_purchaseorder po
  WHERE json_value(po_document, '$.PONumber') > 1500;

親トピック: JSONデータの索引

29.8 JSON_EXISTSの複数値関数ベースの索引の作成

JSONデータ型として格納されるJSONデータの場合、SQL/JSON条件json_existsに複数値関数ベースの索引を使用できます。このような索引は、スカラーJSON値を個別に、またはJSON配列内で対象とします。

複数値索引の主な用途は、配列内のスカラー値の索引付けです。これにはスカラー配列要素が含まれますが、オブジェクト配列要素のスカラー・フィールド値も含まれます。

複数値索引では単一のスカラー値に索引付けすることもできますが、単一の値を対象とする問合せでは、一般に、Bツリー索引またはビットマップ索引を使用する方がパフォーマンスが高くなります。

問合せでは、SELECT文のWHERE句でjson_existsを使用します。条件json_existsは、対象となるデータが問合せのSQL/JSONパス式(または同等の単純なドット表記法構文)と一致する場合にtrueを返します。それ以外の場合、falseを返します。通常は、パス式にフィルタ式を含めます。つまり、照合では対象データがフィルタを満たす必要があります。

複数値索引を作成するには、キーワードMULTIVALUEを指定したCREATE INDEXを使用し、SQL/JSONファンクションjson_tableの構文または索引付きデータへのパスを指定する問合せで使用するのと同じ単純なドット表記法構文を使用します。(ただし、SQL NESTED句をjson_tableのかわりに使用することはできません。使用するとコンパイル時にエラーが発生します。)

コンポジット関数ベースの索引を作成して、複数の仮想列(複数のJSONフィールド)に索引付けできます。コンポジット索引は、関数ベースの索引のセットのように動作します。問合せに使用する場合、ファンクションjson_tableを使用して、指定したJSONフィールド値をSQLスカラー値の仮想列として投影します。同様に、索引の定義に使用する場合、json_tableで指定したフィールド値はコンポジット関数ベースの索引として索引付けされます。

json_table構文を使用して複数値索引を作成する場合は、エラー処理句ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCHを使用する必要があります。そうしないと、問合せのコンパイル時エラーが発生します。json_tableを使用せずに単純なドット表記法構文を使用する場合、これらの句の動作は暗黙的に指定されます。

json_table構文を使用する場合、FOR ORDINALITY句を使用すると、特定の配列位置を対象とする問合せに対して索引を使用できるようになります。(SQL/JSONファンクションJSON_TABLEのCOLUMNS句を参照。)

複数値索引を問合せによって選択するには、索引付けするデータのSQL型を索引で指定する必要があり、問合せ結果のSQL型は索引で指定された型と一致する必要があります。

非コンポジット複数地索引を作成する場合、つまりjson_table構文を使用しない場合、索引指定には(binary()およびdateWithTime()以外の)データ型変換項目メソッドを含めてSQLデータ型を示す必要があります。データ型変換項目メソッドの詳細は、SQL/JSONパス式の項目メソッドを参照してください。

索引の名前に"only"を含む項目メソッドが使用されている場合は、その同じ項目メソッドを使用している問合せのみが索引を選択できます。それ以外の("only"が含まれていないメソッドを使用しているか、メソッドを使用していない)場合、項目メソッドで指定された型に変換できるスカラー値を(おそらく配列要素として)対象とする問合せで索引を選択できます。

たとえば、項目メソッドnumberOnly()を使用する複数値索引は、numberOnly()も使用する問合せに対してのみ選択できます。ただし、number()を使用する索引または項目メソッドを使用していない索引は、数値に変換可能な任意のスカラー(文字列"3.14"など)に一致する問合せに対して選択できます。

コンポジット複数値索引を作成する場合、json_tableの仮想列型では使用するSQL型を指定します。つまり、指定したSQL型に変換できるデータの問合せでは、索引を選択できます。

ただし、非コンポジット索引の場合と同様に、名前に"only"を含むデータ型変換項目メソッドを使用して、指定された列型をオーバーライド(さらに制約)できます。項目メソッドは列パス式で使用します。

たとえば、列型がNUMBERと指定されている場合、数値に変換可能なデータ(文字列"3.14"など)に一致する問合せでは、索引を選択できます。ただし、列パス式で項目メソッドnumberOnly()を使用している場合は、同様にnumberOnly()を使用している問合せのみが索引を選択できます。

特定のターゲットに複数の複数値索引を作成できます。たとえば、フィールドmonthに対して項目メソッドnumber()を使用する索引を作成し、同じフィールドに対して項目メソッドstring()を使用する別の索引を作成できます。

複数値索引を作成する方法として、次のことはできません。

  • コンポジット複数値索引の作成に使用されるjson_table式で、兄弟のネストされた配列を指定できません。しようとすると、エラーが発生します。複数の配列に索引付けできますが、兄弟にすることはできません。つまり、同じ親フィールドを持つことはできません。

  • SQL NESTED句の使用(JSON_TABLEの代替のSQL NESTED句を参照)。

json_table仮想列のスカラーJSON値の型とそれに対応するスカラーSQLデータ型の間の型エラー不一致は、表17-2で示されている型の非互換性、またはSQLデータ型の制約が厳しすぎること(データを格納するには小さすぎる)が原因である可能性があります。

エラー処理ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCHでは、1つ目の種類の不一致に対してSQL NULLを返しますが、2つ目の種類に対してエラーが発生します。たとえば、JSON文字列データに対してSQL型NUMBERの索引を作成する際に型の非互換性が許容されますが、JSON文字列値が"hello"のデータに対してSQL型VARCHAR(2)を使用して索引を作成しようとするとエラーが発生します。これは、データが2文字を超えるためです。

例29-13 表PARTS_TAB (複数値索引の例)

JSONデータ型列jpartsが含まれる表parts_tabが、この複数値索引の例で使用されています。JSONデータには、値がスカラー要素を含む配列であるフィールドsubpartsが含まれます。 

CREATE TABLE parts_tab (id NUMBER, jparts JSON);

INSERT INTO parts_tab VALUES
  (1, '{"parts" : [{"partno"   : 3, "subparts" : [510, 580, 520]},
                   {"partno"   : 4, "subparts" : 730}]}');

INSERT INTO parts_tab VALUES
  (2, '{"parts" : [{"partno"   : 7, "subparts" : [410, 420, 410]},
                   {"partno"   : 4, "subparts" : [710, 730, 730]}]}');

例29-14 JSON_EXISTSの複数値索引の作成

ここで作成された複数値索引は、フィールドsubpartsの値を索引付けします。単純なドット表記法構文を使用する際は、表の別名(この場合はt)が必要です。

問合せの対象となるsubparts値が配列の場合、数値である配列要素の索引を選択できます。値がスカラーの場合、スカラーが数値であれば索引を選択できます。

表parts_tabのデータを考えた場合、最初の行(id 1)の配列partsの各オブジェクトのsubpartsフィールドが索引付けされます。1つ目のオブジェクトのフィールドは、配列値に数値(510、580および520)の要素があるため、2つ目のオブジェクトのフィールドは、値が数値(730)であるためです。

項目メソッドnumber()がnumberOnly()のかわりに索引定義で使用された場合、数値に変換可能な数値以外のスカラー値(文字列"730"など)も索引付けされます。 

CREATE MULTIVALUE INDEX mvi ON parts_tab t
  (t.jparts.parts.subparts.numberOnly());

例29-15 JSON_EXISTSのコンポジット複数値索引の作成

この例では、フィールドpartnoとフィールドsubpartsの両方を対象とするコンポジット複数値索引を作成する2つの同等の方法を示します。コンポジット索引は、これらの2つのフィールドを対象とする2つの関数ベースの索引のセットのように動作します。

1つ目の問合せでは、json_table構文をSQL/JSONパス式とともに行パターン$.parts[*]に使用します。2つ目では、単純なドット表記法を行パターンに使用します。その他の点では、コードはどちらも同じです。json_tableを使用した複数値索引の作成では常にそうであるように、エラー処理はERROR ON ERROR NULL ON EMPTY NULL ON MISMATCHと指定します。

ここでは、列PARTNUMにSQLデータ型NUMBER(10)が指定されています。つまり、フィールドpartnoを対象とする問合せに索引を使用するには、そのフィールドの値がそのデータ型に変換できる必要があります。

  • 表17-2で示されているように、型が一般的に互換性がないために型変換できない場合は、NULL ON MISMATCHエラー・ハンドラによってSQL NULLが返されます。この例として、型NUMBER(10)のSQL partNum列に対するpartno文字列値"hello"があります。

  • 一方、SQLデータ型の記憶域の制約が厳しすぎる場合は、エラーが発生します。つまり、索引は作成されません。この例として、"1234567890123"など、10文字を超えるpartno文字列があります。 

CREATE MULTIVALUE INDEX cmvi_1 ON parts_tab
  (json_table(jparts, '$.parts[*]'
     ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
     COLUMNS (partNum NUMBER(10) PATH '$.partno',
       NESTED
         PATH '$.subparts[*]'
         COLUMNS (subpartNum NUMBER(20) PATH '$'))));

CREATE MULTIVALUE INDEX cmvi_1 ON parts_tab t
  (t.jparts.parts[*]
  ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
  COLUMNS (partNum NUMBER(10) PATH '$.partno',
    NESTED subparts[*]
      COLUMNS (subpartNum NUMBER(20) PATH '$'))));

例29-16 配列位置を対象にできるコンポジット複数値索引の作成

この例のコードは例29-15に似ていますが、このコードでは、さらに順序性のための仮想列SEQを指定しています。つまり、その直前の列(SUBPARTNUM)の値には、配列subparts内の(1ベースの)位置を使用してアクセスできます。(FOR ORDINALITY列のSQLデータ型は、常にNUMBERです。)

常に、COLUMNS句内の多くても1つのエントリでは、列名の後に、生成された行番号の列(SQLデータ型NUMBER)を指定する、1から始まるFOR ORDINALITYを続けることができます。そうしないと、索引の作成時にエラーが発生します。

json_table構文のこの一般ルールに加えて、

  • json_tableを使用して複数値索引を作成する場合、FOR ORDINALITY列はjson_tableの最後の列である必要があります。(json_tableが問合せで使用される場合、これは必須ではありません。索引の作成にのみ適用されます。)

  • json_tableを使用して作成した複数値索引を特定の問合せに対して選択するには、その問合せではjson_table式の最初の仮想列に対応するJSONフィールドにフィルタ式を適用する必要があります。

位置で配列要素を対象とする問合せで配列位置に対して複数値索引を選択するには、それらの配列要素の索引列がFOR ORDINALITY列の直前の列である必要があります

(ここでのコードでは、単純なドット表記法を行パターンに使用します。かわりにSQL/JSONパス式を行パターンに使用した場合、残りのコードは同じになります。)

CREATE MULTIVALUE INDEX cmvi_2 ON parts_tab t
  (t.jparts.parts[*]
  ERROR ON ERROR NULL ON EMPTY NULL ON MISMATCH
  COLUMNS (partNum NUMBER(10) PATH '$.partno',
    NESTED subparts[*]
      COLUMNS (subpartNum NUMBER(20) PATH '$',
               seq FOR ORDINALITY))));

関連項目

親トピック: JSONデータの索引

29.9 複数値関数ベースの索引の使用

WHERE句のjson_exists問合せでは、対象とするデータが索引に指定されたスカラー型と一致する場合(かつその場合にかぎり)、複数値関数ベースの索引を選択できます。

SQL/JSON条件json_existsの複数値関数ベースの索引は、個別にまたはJSON配列の要素としてスカラーJSON値を対象とします。複数値索引は、JSONデータ型として格納されているJSONデータに対してのみ定義できます。

条件json_existsは、対象となるデータが問合せのSQL/JSONパス式(または同等の単純なドット表記法構文)と一致する場合にtrueを返します。それ以外の場合、falseを返します。通常は、パス式にフィルタ式を含めます。つまり、照合では対象データがフィルタを満たす必要があります。

名前に"only"を含むデータ型変換項目メソッド(numberOnly()など)を使用して定義される複数値索引は、同様に同じ項目メソッドを使用するjson_exists問合せによってのみ選択できます。つまり、問合せでは同じ項目メソッドを明示的に使用する必要があります。詳細は、JSON_EXISTSの複数値関数ベースの索引の作成を参照してください。

項目メソッドを使用せずに、または名前に"only"が含まれていないデータ型変換項目メソッド(number()など)を使用して定義された複数値索引は、項目メソッドで指定された型に変換できるスカラー値を(おそらく配列要素として)対象とする問合せで選択できます。データ型変換項目メソッドの詳細は、SQL/JSONパス式の項目メソッドを参照してください。

この例では、WHERE句でSQL/JSON条件json_existsを使用して、730に一致するsubpartsフィールド値がないかチェックします。これらは、複数値索引mvi、cmvi_1およびcmvi_2 (JSON_EXISTSの複数値関数ベースの索引の作成で定義)を選択できるどうかの観点で説明されています。JSONスカラー値のSQLスカラー値への変換は、表17-2を参照してください。

例29-17 項目メソッドnumberOnly()を使用したJSON_EXISTS問合せ

この例では、WHERE句で項目メソッドnumberOnly()を使用します。パス式が数値のsubparts値730 (たとえばsubparts : 730)または1つ以上の数値の要素730が含まれる配列のsubparts値(たとえばsubparts:[630, 730, 690, 730])を対象とする場合、問合せでは索引mviを選択できます。対象となる文字列値"730" (たとえばsubparts:"730"またはsubparts:["630", "730", 690, "730"])に対しては、索引mviを選択できません。

かわりに、項目メソッドnumber()を使用して索引mviが定義されている場合、この問合せでは、数値のsubparts値730、文字列のsubparts値"730"または数値要素730または文字列要素"730"が含まれる配列のsubparts値に対して索引を選択できます。 

SELECT count(*) FROM parts_tab
  WHERE json_exists(jparts, '$.parts.subparts?(@.numberOnly() == 730)');

例29-18 項目メソッドnumberOnly()を使用しないJSON_EXISTS問合せ

これらの2つの問合せでは、項目メソッドnumberOnly()を使用しません。1つ目はメソッドnumber()を使用します。このメソッドは対象データを数値に変換します(可能な場合)。2つ目は対象データを型変換しません。

索引mviは、対象データが数値730であっても、どちらの問合せでも選択できません。索引はnumberOnly()を使用して定義されているため、索引を選択するには、問合せでnumberOnly()を使用する必要があります。 

SELECT count(*) FROM parts_tab t
  WHERE json_exists(jparts, '$.parts.subparts?(@.number() == 730)');

SELECT count(*) FROM parts_tab t
  WHERE json_exists(jparts, '$.parts.subparts?(@ == 730)');

例29-19 複数フィールドをチェックするJSON_EXISTS問合せ

この問合せのフィルタ式では、SQL NUMBER値4と一致するpartnoフィールド(おそらくJSON文字列からの変換によるもの)および数値730と一致するフィールドsubpartsの有無を指定します。

この問合せでは、索引cmvi_1またはcmvi_2のいずれかを選択できます。各行に数値4と一致するparts.partno値と、数値730と一致するparts.subparts値があるため、データのどちらの行もこれらの索引と一致します。subparts一致の場合、1行目にはsubparts値730が、2行目には値730が含まれる配列であるsubparts値があります。 

SELECT a FROM parts_tab
  WHERE json_exists(jparts,'$.parts[*]?(@.partno == 4 &&
                                        @.subparts == 730)');

例29-20 配列要素の位置をチェックするJSON_EXISTS問合せ

この例は例29-19に似ていますが、フィールドpartnoが数値4と一致する必要があることに加えて、ここでのフィルタ式では、フィールドsubpartsの値が少なくとも2つの要素の配列と一致し、配列の2つ目の要素が数値730と一致している必要があります。

この問合せでは、位置述語[1]に対しても含め、索引cmvi_2を選択できます。索引cmvi_2では、仮想列subpartNumを指定します。これは、最終列FOR ORDINALITYの直前、最後から2番目の列としてJSONフィールドsubpartsに対応します。

この問合せでは索引cmvi_1も選択できますが、その索引にFOR ORDINALITY列がないため、使用するには、配列の位置条件[1]を評価するために追加ステップが必要になります。索引cmvi_2の使用には、このような追加ステップは必要ないため、このような問合せのパフォーマンスは向上します。 

SELECT a FROM parts_tab
  WHERE json_exists(jparts,'$.parts[*]?(@.partno == 4 &&
                                        @.subparts[1] == 730)');

関連項目

親トピック: JSONデータの索引

29.10 コンポジットBツリー索引を使用した複数のJSONフィールドの索引付け

JSONオブジェクトの複数のフィールドに索引を付けるには、SQL/JSONファンクションjson_valueまたはドット表記法構文で複数のパス式を使用してコンポジットBツリー索引を作成できます。

例29-21に、これを示します。対応するJSONデータ(オブジェクト・フィールド)を参照するSQL問合せでは、コンポジット索引が選択されます。例29-22に、これを示します。

または、索引付けするJSONオブジェクトのフィールドの仮想列を作成し、それらの仮想列にコンポジットBツリー索引を作成できます。その場合、仮想列または対応するJSONデータ(オブジェクト・フィールド)を参照するSQL問合せでは、コンポジット索引が選択されます。問合せパフォーマンスはどちらの場合も同じです。

問合せのパフォーマンスを向上させるために実装された索引に、データが論理的に依存することはありません。この実装からの独立をコードに反映するには、(仮想列ではなく)データを直接問い合せます。このようにすることにより、問合せは索引の有無とは関係なく同様に機能します。この場合、索引はパフォーマンスの向上に特化して機能します。

例29-21 JSONオブジェクト・フィールドのコンポジットBツリー索引の作成

CREATE INDEX user_cost_ctr_idx ON
  j_purchaseorder(json_value(po_document, '$.User'
                             RETURNING VARCHAR2(20),
                  json_value(po_document, '$.CostCenter'
                             RETURNING VARCHAR2(6)));

例29-22 コンポジットBツリー索引を使用して索引付けされたJSONデータの問合せ

SELECT po_document FROM j_purchaseorder
  WHERE json_value(po_document, '$.User')       = 'ABULL'
    AND json_value(po_document, '$.CostCenter') = 'A50';

関連項目

親トピック: JSONデータの索引

29.11 非定型の問合せおよび全文検索のためのJSON検索索引

JSON検索索引は、一般的な索引です。この検索索引によって、(1)非定型の構造的問合せ、つまり、定期的には予測または使用されない可能性のある問合せと、(2)全文検索の両方のパフォーマンスを向上させることができます。これは、JSONデータでの使用に特化して設計されたOracle Text索引です。

JSONデータの全文問合せについては、全文検索問合せを参照してください。このトピックでは、全文検索に必要であり、アドホック問合せにも役立つJSON検索索引の作成および保守について説明します。JSON検索索引でサポートされているアドホック問合せの例を次に示します。

全文検索が含まれる問合せについて、JSON検索索引を作成します。特に想定されていない、つまり定期的に使用されない問合せ(アドホック問合せ)についてもJSON検索索引を作成します。ただし、前もって問合せパターンがわかっている問合せを索引付けするには、通常、そのような特定のパターンを対象とする関数ベースの索引を使用することをお薦めします。関数ベースの索引とJSON検索索引の両方が特定の問合せに適用可能な場合、使用されるのは関数ベースの索引です。

JSON型として格納されているJSONデータの場合、JSON検索索引を作成して保守する代替方法として、インメモリー列ストア(IM列ストア)にJSON列を移入する方法があります(インメモリーJSONデータを参照)。

ノート:

Oracle Database 12cリリース1 (12.1.0.2)を使用してJSON検索索引を作成した場合は、その索引を削除し、以降のリリースで使用するために、ここで説明するCREATE SEARCH INDEXを使用して検索索引を新しく作成することをお薦めします。

ノート:

名前が64バイトを超えるオブジェクト・フィールドが含まれるJSONデータを索引付けする場合、Oracle Database 18cより前に作成された任意のJSON検索索引およびOracle Text索引を再構築する必要があります。そのようにしないと、そのようなフィールドは再索引付けされるまで検索可能にならない可能性があります。詳細は、『Oracle Databaseアップグレード・ガイド』を参照してください。

CREATE SEARCH INDEXにキーワードFOR JSONを指定して、JSON検索索引を作成します。例29-23および例29-24に、これを示します。

JSON検索索引を作成する列は、JSON、VARCHAR2、CLOBまたはBLOBデータ型の列です。整形式のJSONデータのみが含まれていることが認識されている必要があります(つまり、JSON型であるか、is jsonチェック制約がある)。列にJSONデータが含まれていることを認識できない場合、CREATE SEARCH INDEXでエラーが発生します。

問合せの実行計画内にJSON検索索引の名前が存在する場合は、索引が実際に問合せに対して選択されていることがわかります。例29-25に示すものと似た行が表示されます。

特定の構成可能なオプションのデフォルト設定をオーバーライドする場合は、検索索引の作成時にPARAMETERS句を指定できます。デフォルト(PARAMETERS句なし)では、索引は自動的にメンテナンスされ(バックグラウンドで同期され)、テキストと数値の両方の範囲が索引付けされます。

JSON検索索引を使用する問合せにフルテキスト検索または文字列等価検索のみが含まれ、文字列範囲検索、数値検索および時間検索(等価または範囲)が含まれない場合は、パラメータSEARCH_ONにTEXTを指定することで、索引のメンテナンス時間とディスク領域をいくらか節約できます。SEARCH_ONのデフォルト値はTEXT_VALUEです。これは、テキストと同様に索引の数値の範囲も意味します。

デフォルトでは、JSON検索索引は非同期的にメンテナンスされます。これにより、同期によってDML操作にもたらされる可能性がある悪影響が少なくなります。(索引は、同期されるまでデータの追加または変更が反映されません。ただし、同期されていない場合でも、削除はただちに反映されます。)

デフォルトでは、JSON検索索引はバックグラウンドで自動的に同期されます。この動作は、様々なユースケースで索引の同期設定を変更することでオーバーライドできます。

  • コミット時に同期。

    これは、コミットが頻繁に行われず、コミットされた変更を他の操作(問合せなど)がすぐに参照できることが重要である場合に適しています。(索引が失効していると、コミットされていない変更を参照できないことがあります。)例29-24では、コミット時に同期される検索索引を作成します。

  • オンデマンドで同期(たとえば、データベースの負荷が減少したとき)。

    通常、これは頻繁に実行しません。コミット時の同期または間隔での同期よりも少ない頻度で、索引が同期されます。DMLのパフォーマンスが特に重要な場合は、この方法が一般的に適しています。

パッケージCTX_DDLのCTX_DDL.sync_indexなどのプロシージャを起動して索引を手動で同期化する必要がある場合は、権限CTXAPPが必要です。

静的ディクショナリ・ビューCTX_USER_INDEXESには、JSON検索索引を含め、既存のOracle Text索引に関する情報が含まれています。たとえば、次の問合せでは、すべてのOracle Text索引の同期タイプおよびメンテナンス・タイプがリストされます。 

SELECT IDX_NAME, IDX_SYNC_TYPE, IDX_MAINTENANCE_TYPE FROM CTX_USER_INDEXES;

ノート:

JSON検索索引j_s_idxを変更するには、ALTER INDEX j_s_idx REBUILD ...(ALTER SEARCH INDEX j_s_idx ...ではない)を使用します。

例29-23 デフォルトの動作によるJSON検索索引の作成

この例では、デフォルトの動作をするJSON検索索引を作成します。索引は自動的にメンテナンスされ(バックグラウンドで同期され)、テキストと数値の両方の範囲が索引付けされます。

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON;

このコードは、同等のものです。PARAMETERS句を使用して、自動メンテナンスを明示的に指定します。

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('MAINTENANCE AUTO');

例29-24 コミット時に同期されるJSON検索索引の作成

この例では、PARAMETERS句を使用して、COMMIT時に新しいデータを同期する索引を作成します。 

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (po_document)
  FOR JSON PARAMETERS ('SYNC (ON COMMIT)');

例29-25 JSON検索索引が使用されていることを示す実行計画

|* 2|   DOMAIN INDEX     | PO_SEARCH_IDX |     |     |     4 (0)

JSONデータの非定型問合せ

例29-26に、例29-24で作成したJSON検索索引も使用するJSONデータの非全文問合せを示します。

例29-26 いくつかの非定型JSON問合せ

この問合せでは、船積み依頼書の住所に国が含まれる文書を選択します。

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document,
                    '$.ShippingInstructions.Address.country');

この問合せでは、ユーザーAKHOOが含まれる文書で、注文された項目が8個より多いものを選択します。ここでは、数値範囲の索引付けが利用されます。 

SELECT po_document FROM j_purchaseorder
  WHERE json_exists(po_document, '$?(@.User == "AKHOO"
                                     && @.LineItems.Quantity > 8)');

この問合せでは、ユーザーがAKHOOの文書が選択されます。ここでは、WHERE句で、json_existsのかわりにjson_valueが使用されます。 

SELECT po_document FROM j_purchaseorder
  WHERE json_value(po_document, '$.User') = 'AKHOO';

関連項目

関連項目:

親トピック: JSONデータの索引