はじめに

前回の記事では、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

仮想列
https://www.oqiita.com/?p=677

[Oracle Cloud] Oracle Database で JSON データに Index を設定する

はじめに

検証に使った環境

検証用データ

Index 作成(Number型)

Index 作成(文字列型)

Search Index 作成(文字列型)

複合インデックス

参考URL