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

# はじめに

[前回](https://qiita.com/sugimount/items/064a1301b461df482f00) の記事では、Oracle Database で JSON データを SQL で扱う方法を紹介しました。テーブルの中に JSON データ格納用の列を用意することで、Oracle Database 上で JSON データが扱えます。
今回は、JSON データに Index を設定する方法を紹介します。JSON データの一部の key に Index を設定することが出来ます。



# 検証に使った環境

- OCI DBCS 19c



# 検証用データ

[前回](https://qiita.com/sugimount/items/064a1301b461df482f00#insert) の記事で作成した、JSON データをそのまま使っていきます。



# Index 作成(Number型)

JSON データには、price という key で価格を格納しています。実際の INSERT 文を一部抜粋すると、次のオレンジ色の部分です。

![1598470968371.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/9e54e615-9305-8975-40a7-471f69f1dd31.png)



この 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';
```

![1598364276220.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/92a91fd4-f4e2-a43b-7eb9-57f5fd7524a6.png)



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 を設定してみます。

![1598473008224.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/278dc4e4-7465-7df8-9344-317fb083ddf1.png)



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', '坂元%');
```



実行結果です。坂元で前方一致検索が出来ています。

![1598375321269.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/be3e50ba-b48a-23a4-fda5-b64bf93f1d6b.png)



実行計画をみてみましょう。

```
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', '%まん%');
```



実行例です。`まん`で中間一致検索が出来ています。

![1598375412361.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/518e9fc2-6e64-998d-e24e-66d8587e7d98.png)



実行計画をみてみましょう。

```
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 データと連動しています。

![1598376664552.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/104247/5aecbd41-0ca1-9e1d-18c9-7ad997de7f1f.png)



仮想列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