はじめに
前回 の記事では、Oracle Database で JSON データを SQL で扱う方法を紹介しました。テーブルの中に JSON データ格納用の列を用意することで、Oracle Database 上で JSON データが扱えます。
今回は、JSON データに Index を設定する方法を紹介します。JSON データの一部の key に Index を設定することが出来ます。
検証に使った環境
- OCI DBCS 19c
検証用データ
前回 の記事で作成した、JSON データをそのまま使っていきます。
Index 作成(Number型)
JSON データには、price という key で価格を格納しています。実際の INSERT 文を一部抜粋すると、次のオレンジ色の部分です。
この price の部分に Index を設定します。見てわかるとおり、JSON の一部データに対して Index を設定できます。作成方法の詳細は次の Document に書いています。詳細を確認したいときは、合わせてご利用ください。
https://docs.oracle.com/cd/F19136_01/adjsn/indexes-for-json-data.html#GUID-FEE83855-780A-424B-9916-B899BFF2077B
次の構文で Index を作成します。
json_value の部分で、JSONデータのうち、どこに Index を作成するか指定をしています。
テーブル menu には、jdata という名前のJSON用の VARCHAR 列があります。jdata の中にある price key を使う指定となっています。
RETURNING NUMBER ERROR ON ERROR の指定で、数値データを期待していることを宣言しています。Index 作成時点で、数値データ以外のものが格納されていると、Index 作成に失敗します。Index 作成後に、price に数値以外のデータを Insert するとエラーになります。この挙動は、指定方法でコントロールできます。詳細は Document をご利用ください。
Index 作成に失敗する条件
- price フィールドが無い行がある
- price に配列など複数データが格納されている
- price が数値ではない
CREATE INDEX price_index1 ON menu (json_value(jdata, '$.price' RETURNING NUMBER ERROR ON ERROR));
memo : Index の削除
DROP INDEX price_index1;
Index が作成されているか確認します。Index_type が、Function-based Normal となっており、正常に作成できています。
SELECT INDEX_NAME, INDEX_TYPE FROM USER_INDEXES WHERE INDEX_NAME = 'PRICE_INDEX1';
Index を作成したので、実際に Index が使われるか実行計画を確認します。次の Select 文を実行します。WHERE の指定で、price が 500 の行を絞り込んでいます。
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.price == 500)');
実行計画を確認しましょう。
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.price == 500)');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
3行目が INDEX RANGE SCAN となっており、PRICE_INDEX1 を使っていることが分かります。なお、Index が使われていないと、TABLE FULL ACCESS になります。
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 735 | 88200 | 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 735 | 88200 | 31 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MENU | 1 | 116 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PRICE_INDEX1 | 1 | | 1 (0)| 00:00:01 |
| 4 | JSONTABLE EVALUATION | | | | | |
-----------------------------------------------------------------------------------------------------
では、WHERE で price が 500 以上のものを検索してみましょう。
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.price > 500)');
実行計画を出します
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.price > 500)');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
実行計画の内容を見ると、先ほどと同じく3行目が INDEX RANGE SCAN となっており、PRICE_INDEX1 を使っていることが分かります。
-----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3676 | 430K| 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 3676 | 430K| 31 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MENU | 1 | 116 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | PRICE_INDEX1 | 1 | | 1 (0)| 00:00:01 |
| 4 | JSONTABLE EVALUATION | | | | | |
-----------------------------------------------------------------------------------------------------
Index 作成(文字列型)
文字列型の Index は若干特殊な事情があります。結論から書くと、Like のように前方・中間・後方一致で検索したい場合は、Search Index を作成して、json_textcontains 関数でパターンマッチを検索すると良いです。
この結論になる理由を説明するために、まずは、ベーシックな Index から確認していきます。
jdata 列に含まれている、name key に Index を設定してみます。
CREATE INDEX で作成します。name を指定しています。
CREATE INDEX name_index1 ON menu (json_value(jdata, '$.name' RETURNING VARCHAR2 ERROR ON ERROR));
memo : Index の削除
DROP INDEX name_index1;
作成した index が使われるか確認するために、.name == "坂元メロン" と完全一致の検索をしてみます。
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name == "坂元メロン")');
実行計画の表示
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name == "坂元メロン")');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
結果を見ると、INDEX RANGE SCAN となっており、Index が使われています。ここまでは良いですね。
----------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 735 | 88200 | 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 735 | 88200 | 31 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MENU | 1 | 116 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | NAME_INDEX1 | 1 | | 1 (0)| 00:00:01 |
| 4 | JSONTABLE EVALUATION | | | | | |
----------------------------------------------------------------------------------------------------
では次に、完全一致ではなく、like でパターン検索はどうでしょうか？
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name like_regex "坂元.*")');
実行計画の表示
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_exists(m.jdata, '$?(@.name like_regex "坂元.*")');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
実行計画を見ればわかるとおり、Index は使われません。ベーシックな Index を作って、like_regex でパターン検索してもIndex は使われないことが分かりました。
-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 735 | 88200 | 32 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 735 | 88200 | 32 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | MENU | 1 | 116 | 3 (0)| 00:00:01 |
| 3 | JSONTABLE EVALUATION | | | | | |
-------------------------------------------------------------------------------
ではどのようにすればいいかというと、パターン検索をしたいときは、Search Index を作る必要があります。
Search Index 作成(文字列型)
Search Index とは、Oracle Text と呼ばれる全文検索を使うための特別な Index です。JSON データの文字列パターンマッチなどの検索方法で、Oracle Text が利用できます。詳細は次の Document をご利用ください。
https://docs.oracle.com/cd/F19136_01/adjsn/indexes-for-json-data.html#GUID-D7A604E1-F617-4C92-A9D9-00B6AB78EFD8
https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/SQL-JSON-Conditions.html#GUID-DEF7941B-1267-44E7-8514-5CD448503179
Search Index を作成します。CREATE SEARCH INDEX で作成できます。jdata 列をまるまる指定しています。
CREATE SEARCH INDEX name_search_index1 ON menu (jdata) FOR JSON;
memo : Index の削除
DROP INDEX name_search_index1;
作成した Search Index を使って、JSON データから特定の文字列パターンを検索します。json_textcontains 句で検索をします。
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_textcontains(jdata, '$.name', '坂元%');
実行結果です。坂元で前方一致検索が出来ています。
実行計画をみてみましょう。
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_textcontains(jdata, '$.name', '坂元%');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
結果をみると、DOMAIN INDEX となっており、作成した Search Index で検索していることがわかります。
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 116 | 4 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| MENU | 1 | 116 | 4 (0)| 00:00:01 |
|* 2 | DOMAIN INDEX | NAME_SEARCH_INDEX1 | | | 4 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
中間一致検索ではどうなるでしょうか？
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_textcontains(jdata, '$.name', '%まん%');
実行例です。まんで中間一致検索が出来ています。
実行計画をみてみましょう。
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_textcontains(jdata, '$.name', '%まん%');
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
実行計画を見ると、DOMAIN INDEX となっており、作成した Search Index で検索していることがわかります。
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 37 | 4440 | 33 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 37 | 4440 | 33 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| MENU | 1 | 116 | 4 (0)| 00:00:01 |
|* 3 | DOMAIN INDEX | NAME_SEARCH_INDEX1 | | | 4 (0)| 00:00:01 |
| 4 | JSONTABLE EVALUATION | | | | | |
---------------------------------------------------------------------------------------------------
複合インデックス
JSON のなかで、2つ以上のデータを使って複合インデックスも可能です。
2つ以上のデータを使う複合インデックスの場合は、JSON データの一部を使って、仮想列を作る必要があります。詳細 Document はこちらにあります。
https://docs.oracle.com/cd/F19136_01/adjsn/indexes-for-json-data.html#GUID-47CEF6AA-0FCF-40D2-90E0-C57C0B0E816F
具体的に見て行きましょう。JSON の name と price の2つを使って、仮想列を2個作ります。
ALTER TABLE menu ADD (name_virtual_column VARCHAR2(100) GENERATED ALWAYS AS (json_value(jdata, '$.name' RETURNING VARCHAR2(100))));
ALTER TABLE menu ADD (price_virtual_column NUMBER(20) GENERATED ALWAYS AS (json_value(jdata, '$.price' RETURNING NUMBER(20))));
仮想列2個の確認です。name_virtual_column と price_virtual_column の2つの列が追加されています。JSON データと連動しています。
仮想列2個を使って、複合インデックスを作ります。
CREATE INDEX name_price_composite_index on menu(name_virtual_column, price_virtual_column);
複合インデックスを仮想列に対して検索します。文字列の前方一致と、1500 以上の price を検索する内容です。
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE name_virtual_column like '坂元%' AND price_virtual_column > 1500;
実行計画表示
EXPLAIN PLAN FOR SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE name_virtual_column like '坂元%' AND price_virtual_column > 1500;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY());
結果を見ると、複合 Index が使われています。
-------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 184 | 22080 | 31 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 184 | 22080 | 31 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID BATCHED| MENU | 1 | 116 | 2 (0)| 00:00:01 |
|* 3 | INDEX RANGE SCAN | NAME_PRICE_COMPOSITE_INDEX | 1 | | 1 (0)| 00:00:01 |
| 4 | JSONTABLE EVALUATION | | | | | |
-------------------------------------------------------------------------------------------------------------------
また、仮想列ではなく、JSON データに直接 SELECT クエリーを投げても、Index が使えます。が、一部例外もあります。
文字列をLike 検索をするときには、JSON データに直接 SELECT クエリーを投げると Index が使われません。そういう場合は、仮想列を使えば回避できます。
# Like検索
↓Index 使う
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE name_virtual_column like '坂元%' AND price_virtual_column > 1500;
↓Index 使わない
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_value(m.jdata, '$.name') like '坂元%' AND json_value(m.jdata, '$.price') > 1500;
# 固定文字列
↓Index 使う
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE name_virtual_column = '坂元イチゴ' AND price_virtual_column > 1500;
↓Index 使う
SELECT /*+ MONITOR */ m.id, m.jdata.name, m.jdata.price FROM menu m WHERE json_value(m.jdata, '$.name') = '坂元イチゴ' AND json_value(m.jdata, '$.price') > 1500;
参考URL
JSON開発者ガイド
https://docs.oracle.com/cd/F19136_01/adjsn/index.html
JSON用のパフォーマンス・チューニング
https://docs.oracle.com/cd/F19136_01/adjsn/performance-tuning-for-json-data.html#GUID-D3A3F17B-8372-43A7-83A3-E37C22E9011E
JSON のクエリーサンプルが多くある
https://livesql.oracle.com/apex/livesql/file/content_EUJBKKNUU6R37CCQA28XZ44XR.html