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

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

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

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

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

JSON検索索引を作成するときに、パス・サブセット化を指定して、索引付けに含めるフィールドまたは索引付けから除外するフィールドを指定できます。その他のフィールドは索引付けされません。検索索引は、問合せ時に使用されません。これは例30-26および例30-27で説明しています。

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検索索引を作成します。例30-23および例30-24に、これを示します。

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

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

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

JSON検索索引を利用する問合せに全文検索または文字列等価検索のみが含まれ、文字列、数値、一時範囲検索が含まれない場合、パラメータSEARCH_ONにTEXTを指定することで、索引のメンテナンス時間とディスク領域をいくらか節約できます。

逆に、全文検索や文字列等価検索が不要な場合は、特定のデータ型の値範囲をチェックするように索引付けを制限できます。そのためには、パラメータSEARCH_ONにVALUEを指定します。

SEARCH_ONのデフォルト値はTEXT_VALUEです。これは、全文および文字列等価一致(TEXT)の索引、および文字列、数値および一時値の範囲(VALUE)を意味します。

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

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

コミット時に同期。

これは、コミットが頻繁に行われず、コミットされた変更を他の操作(問合せなど)がすぐに参照できることが重要である場合に適しています。(索引が失効していると、コミットされていない変更を参照できないことがあります。)例30-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 ...ではない)を使用します。

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

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

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

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

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

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

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

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

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

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

例30-26 テキスト検索のパス・サブセット化を使用したJSON検索索引の作成

この例では、全文検索および文字列等価検索用の索引を作成しますが、パス$.SpecialInstructionsおよび$.LineItems.Part.Descriptionにあるフィールドのみが索引付けされます。

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (data)
  FOR JSON PARAMETERS ('SEARCH_ON
    TEXT INCLUDE ($.SpecialInstructions,
                  $.LineItems.Part.Description)');

例30-27 テキスト検索と値検索の両方のパス・サブセット化を使用したJSON検索索引の作成

例30-26と同様に、この例では、フィールド$.SpecialInstructionsおよび$.LineItems.Part.Descriptionの全文検索および文字列等価検索用の索引を作成します。ただし、numeric-value範囲の場合はフィールド$.PONumberおよび$.LineItems.Part.UnitPriceも索引付けし、string-value範囲の場合はフィールド$.Reference、$.User、$.ShippingInstructions.nameおよび$.ShippingInstructions.Address.zipCodeも索引付けします。

CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (data)
  FOR JSON PARAMETERS ('SEARCH_ON
    TEXT INCLUDE    ($.SpecialInstructions,
                     $.LineItems.Part.Description)
    VALUE(NUMBER) INCLUDE  ($.PONumber, $.LineItems.Part.UnitPrice)
    VALUE(VARCHAR2) INCLUDE ($.Reference, $.User,
                             $.ShippingInstructions.name,
                             $.ShippingInstructions.Address.zipCode)');

別の方法として、パラメータPATHLISTを使用して同じ索引を作成できます。この値は、次に示すPL/SQLサブプログラムCTX_DDL.create_path_listおよびCTX_DDL.add_pathを使用して作成される、含めるパスの名前付きリストです:

BEGIN
  CTX_DDL.create_path_list('json_pl', CTX_DDL.PATHLIST_JSON, CTX_DDL.PATHLIST_INCLUDE);
  CTX_DDL.add_path('json_pl', 'TEXT',     '$.SpecialInstructions');
  CTX_DDL.add_path('json_pl', 'TEXT',     '$.LineItems.Part.Description');
  CTX_DDL.add_path('json_pl', 'NUMBER',   '$.PONumber');
  CTX_DDL.add_path('json_pl', 'NUMBER',   '$.LineItems.Part.UnitPrice');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.Reference');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.User');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.name');
  CTX_DDL.add_path('json_pl', 'VARCHAR2', '$.ShippingInstructions.Address.zipCode');
END;
/
CREATE SEARCH INDEX po_search_idx ON j_purchaseorder (data)
  FOR JSON PARAMETERS ('PATHLIST json_pl');

JSONデータの非定型問合せ

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

例30-28 いくつかの非定型JSON問合せ

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

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

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

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

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

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