30 JSON_TABLEに対してマテリアライズド・ビューを使用するためのJSONクエリー・リライト

SQL/JSONファンクションjson_tableを使用して定義されたデータに対してマテリアライズド・ビューを作成および索引付けすることで、特定のJSONフィールドにアクセスする問合せのパフォーマンスを向上させることができます。

例20-10に、ファンクションjson_tableを使用してJSONデータに対してマテリアライズド・ビューを作成する方法を示します。この例では、データに含まれることが予想されるJSONフィールドごとに仮想列を作成します。

かわりに、頻繁に問い合せる特定のフィールドのみを投影するマテリアライズド・ビューを作成できます。これを実行し、次のすべての条件が満たされている場合、投影されるフィールドの列のデータ型に一致する問合せをマテリアライズド・ビューにアクセスするように自動的にリライトできます。

• マテリアライズド・ビューは、REFRESH FAST ON STATEMENTを使用して作成されます。

• マテリアライズド・ビュー定義には、WITH PRIMARY KEYまたはWITH ROWIDのいずれかが含まれます(主キーがない場合)。

• マテリアライズド・ビューは、マスター表と、json_tableで定義された1つの仮想表だけを結合します。

• json_tableによって投影された列では、ERROR ON ERRORが使用されます。

自動クエリー・リライトは、これらの条件が満たされている場合にサポートされます。ビュー定義でENABLE QUERY REWRITEを指定する必要はありません。リライトは、単純なドット表記法、条件json_existsまたはファンクションjson_valueのいずれかがWHERE句で使用されている問合せに適用されます。

ERROR ON ERRORを指定しない列も許可されますが、問合せはこれらの列を使用するようにリライトされません。json_table行パターンにERROR ON ERRORを使用すると、各列にERROR ON ERRORを指定した場合と同じ結果になります。

一部のJSONデータに特定の投影フィールドがない場合、NULL ON EMPTYを使用すると、そのフィールドが存在する場合は選択され、存在しない場合はエラーが発生しません。

マテリアライズド・ビューを使用するための自動クエリー・リライトにより、パフォーマンスを向上させることができます。マテリアライズド・ビューに索引も作成すると、パフォーマンスをさらに向上させることができます。

例30-1では、このようなマテリアライズド・ビューを作成します。例30-2では、その索引を作成します。

例30-1 クエリー・リライトをサポートするためのJSONデータのマテリアライズド・ビューの作成

この例では、複数のJSONフィールドをリレーショナル列に投影するマテリアライズド・ビューmv_for_query_rewriteを作成します。単純なドット表記法、条件json_existsまたはファンクションjson_valueを使用してWHERE句のこれらのフィールドにアクセスする問合せは、対応するビュー列にかわりにアクセスするように自動的にリライトできます。

このような問合せの例が例17-5の問合せで、フィールドUser、UPCCodeおよびQuantityの比較を含んでいます。これらの比較はすべて、マテリアライズド・ビューを使用するようにリライトされます。

マテリアライズド・ビューを問合せの特定の比較に使用するには、その比較の型が、対応するビュー列のSQLデータ型と同じである必要があります。比較の型の詳細は、JSON_VALUE関数ベースの索引とJSON_EXISTS問合せの使用を参照してください。

たとえば、ビューmv_for_query_rewriteは、フィールドUPCCodeに数値85391628927があるかどうかをチェックする問合せに使用できます。これは、そのフィールドから投影されるビュー列にSQL型NUMBERがあるためです。ただし、このビューは、そのフィールドに文字列値"85391628927"があるかどうかをチェックする問合せには使用できません。

CREATE MATERIALIZED VIEW mv_for_query_rewrite
  BUILD IMMEDIATE
  REFRESH FAST ON STATEMENT WITH PRIMARY KEY
  AS SELECT po.id, jt.*
       FROM j_purchaseorder po,
            json_table(po.po_document, '$' ERROR ON ERROR NULL ON EMPTY
              COLUMNS (
                po_number       NUMBER         PATH '$.PONumber',
                userid          VARCHAR2(10)   PATH '$.User',
                NESTED PATH '$.LineItems[*]'
                  COLUMNS (
                    itemno      NUMBER         PATH '$.ItemNumber',
                    description VARCHAR2(256)  PATH '$.Part.Description',
                    upc_code    NUMBER         PATH '$.Part.UPCCode',
                    quantity    NUMBER         PATH '$.Quantity',
                    unitprice   NUMBER         PATH '$.Part.UnitPrice'))) jt;

実行計画を調べることで、マテリアライズド・ビューが特定の問合せに使用されるかどうかを確認できます。該当する場合は、計画がmv_for_query_rewriteを参照します。たとえば:

|* 4| MAT_VIEW ACCESS FULL | MV_FOR_QUERY_REWRITE |1|51|3(0)|00:00:01|

例30-2 JSONデータのマテリアライズド・ビューに対する索引の作成

この例では、例30-1で作成したマテリアライズド・ビューmv_for_query_rewriteの列userid、upc_codeおよびquantityにコンポジット・リレーショナル索引mv_idxを作成します。

CREATE INDEX mv_idx ON mv_for_query_rewrite(userid, upc_code, quantity);

例30-1の実行計画スニペットは、マテリアライズド・ビューの全表スキャン(MAT_VIEW ACCESS FULL)を示しています。索引mv_idxを定義すると、問合せの計画が向上する可能性があります。これは、INDEX RANGE SCAN (および索引の名前、MV_IDX、マテリアルビューMV_FOR_QUERY_REWRITE)が存在することで示されます。

|  4| MAT_VIEW ACCESS BY INDEX ROWID BATCHED | MV_FOR_QUERY_REWRITE |1|51|2(0)|00:00:01|

|* 5|                       INDEX RANGE SCAN | MV_IDX               |1|  |1(0)|00:00:01|