30.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値を想定する問合せに対してこの索引は選択されません。

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

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

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

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

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

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

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

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

ノート:

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

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

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

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

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

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

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

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