本記事では、Oracle において、JSON に対する

アドホックな構造問い合わせ
全文検索的な問い合わせ

の両方の性能を改善するための JSON 検索索引について軽く触った結果を記載します。

（5/2：最初に利用したツイートデータでは件数が少なすぎたので、1,000万件のデータで追加試験した内容を追記しました）

1. 前置き

JSON を扱う DBMS というとドキュメント指向 DB である MongoDB などが有名ですが、RDBMS においても SQL の標準規格である SQL:2016 で JSON を扱う方法が含まれたことから、Oracle、SQL Server、MySQL、PostgreSQL など主要な RDBMS でも JSON を扱うことができるようになってきました。

もちろん、単に JSON ドキュメントを RDB に格納できるというだけではなく、検索を効率よく行うための索引についても JSON 対応がなされているのですが、こと JSON を対象とすると、索引について以下のような課題に出くわすことがあります。

検索対象のJSON フィールド（テーブルでいう列）が多岐に渡る場合、フィールごとに作成する索引の管理が大変。（テーブルデータでも同様の課題はあるが、JSON のスキーマレスの特徴からより面倒になる傾向がある気がする）
JSON ドキュメントは構造化データだけではなくフリーテキストなどを含むことも多く、それらに対する全文検索のニーズも少なくない。

このような課題は多様で複雑な構造を持つ JSON ドキュメントをアドホックに検索したいようなシステム（例えば製造業における品質管理システムや金融系の情報系システム、小売における商品管理システムなど）では特に顕著になります。（こういう OLPT でも OLAP でもないワークロードって何て言うんでしょうね）

RDBMS 以外ではこうした課題に対して、（ほぼ）全てのフィールドに自動的に索引を作成するというアプローチを採っている以下のようなプロダクトがあります。

Marklogic の Universal Index
ElasticSearch の Dynamic Mapping（あまりよく知らない）
（JSON ではない＆直接使ったことはないですが、XML DB も多くは似た技術を持っています）

OracleDB でも似たようなアプローチとして（通常の関数ベースの索引に加え）JSON 検索索引というものがあるので、それの利用手順を共有したいと思います。

ちなみに、索引ということで検索性能は一応計っていますが、使用したデータが自分の Twitter アーカイブデータ(1.8万ツイート）と少なかったため、あまり性能面では有意な比較ができていませんが、ご了承ください。

2. 準備

今回は Oracle Database 21c (21.0.0.0.0)＠Oracle Linux(x86-64) 上で実施しました。

JSON 検索索引は内部で Oracle Text を利用しているので、事前にOracle Text を有効にしてください。ctxsys というユーザーが存在していればおそらく大丈夫だと思います。有効になっていなければ、$ORACLE_HOME/ctx/admin/catctx.sql を実行して有効化してください。

2-1. データの準備

今回は、Twitter の私のアーカイブデータ（[設定] ⇒ [アカウント] ⇒ [データのアーカイブをダウンロード] からダウンロードしたもの）を利用します。

ダウンロードした圧縮ファイルの中にある tweets.js が全ツイートのデータなのですが、JSON 配列の形になっており、そのままでは Oracle に外部表ロードできないため、まず以下のシェルスクリプトで CSV（とは言っても１レコード１フィールドですが）に変換します。

#!/bin/bash

INFILE=tweet.js
OUTFILE=tweet.csv

tail -n +2 ${INFILE} | head -n -1 \
 | cut -b3- \
 | sed -e 's/    "favorite_count" : "\([0-9]*\)",/    "favorite_count" : \1,/' \
 | sed -e 's/    "retweet_count" : "\([0-9]*\)",/    "retweet_count" : \1,/' \
 | sed -e 's/    "id_str" : "\([0-9]*\)",/    "id_str" : \1,/' \
 | sed -e 's/"/""/g' \
 | sed -e 's/^{/"{/' \
 | sed -e 's/^}.*$/}"/'> ${OUTFILE}

やっていることは、以下です。

先頭１行と末尾１秒を削除（全体を囲む配列の [ と ] を取り除く）
favorite_count、retweet_count、id_str の３フィールドについて、本来は数値として取り扱いたいが、なぜかダブルクォート付きの文字列になっているので、ダブルクォートを取り除く
１ツイートのデータは改行を含むため、１ツイート全体をダブルクォートで囲む（その前にデータ内に含まれるダブルクォートをエスケープする）

2-2. データのロード

まず、JSON データを保持する DB ユーザーを作成します。

ctxapp ロールは JSON 検索索引を作成するために必須です。
SELECT_CATALOG_ROLE は後で統計情報を確認するために付与していますが必須ではないです。

SQL> conn sys/***@pdb01 as sysdba
接続されました。
SQL> create user dbuser01 identified by *** quota unlimited on users;

ユーザーが作成されました。

SQL>  grant create session, create table, ctxapp, select_catalog_role to dbuser01;

権限付与が成功しました。

次に、データロード用のディレクトリを作成します（2-1. で生成した tweets.csv は /home/oracle/search_index 以下に置く想定です）

SQL> conn sys/***@pdb01 as sysdba
接続されました。
SQL> create directory tweets_dir as '/home/oracle/search_index';

ディレクトリが作成されました。

SQL> grant read, write on directory tweets_dir to dbuser01;

権限付与が成功しました。

最後に外部表経由でデータを tweets テーブルにロードします。

SQL> conn dbuser01/***@pdb01
接続されました。
SQL> create table tweets (
  2      tweet_data json not null
  3  );

表が作成されました。

SQL> create table tweets_ext (tweet_data json)
  2  organization external (
  3      type oracle_loader
  4      default directory tweets_dir
  5      access parameters (
  6          fields CSV with embedded record terminators (
  7              tweet_data char(10000)
  8          )
  9      )
 10      location (tweets_dir:'tweet.csv')
 11  );

表が作成されました。

SQL> insert into tweets select * from tweets_ext;

18051行が作成されました。

SQL> commit;

コミットが完了しました。

SQL> exec dbms_stats.gather_table_stats(ownname => 'DBUSER01', tabname => 'TWEETS');

PL/SQLプロシージャが正常に完了しました。

2-3. JSON 検索索引の作成

ここからが本題です。

JSON 検索索引を作成する準備として、レクサーとワードリストのプリファレンスを設定する必要があります。今回は my_lexer と my_wordlist という名前のプリファレンスを指定し、それぞれ JAPANESE_VGRAM_LEXER と BASIC_WORDLIST を設定しています。

SQL> conn dbuser01/***@pdb01
接続されました。
SQL> execute ctx_ddl.create_preference('my_lexer','JAPANESE_VGRAM_LEXER');

PL/SQLプロシージャが正常に完了しました。

SQL> execute ctx_ddl.create_preference('my_wordlist','BASIC_WORDLIST');

PL/SQLプロシージャが正常に完了しました。

以上の準備をすると JSON 検索索引が作成できるようになるので、CREATE SEARCH INDEX ... FOR JSON 文で JSON ドキュメントを含む tweets.tweet_data 列に作成します。

SQL> create search index idx_tweets on tweets(tweet_data)
  2  for json
  3  parameters (
  4      'SEARCH_ON TEXT_VALUE_STRING
  5       LEXER my_lexer
  6       WORDLIST my_wordlist'
  7  );

索引が作成されました。

これで検索ができる準備が整いました。

3. 検索

今回は以下の３パターンの検索を試してみます。

3-1. いいねが100以上のツイートを検索（範囲検索）
3-2. ハッシュタグ（１ツイートに複数あり得る）を含むツイートを検索（複数値の一致検索）
3-3. 特定の期間のあるキーワードが含まれるツイートを検索（全文検索）

ちなみに、いずれのケースもドット表記（SQL 式としてパス式を書ける）を使うともっとシンプルに SQL 文を記述できるのですが、データ型を明示的に指定する方が好みなので、今回は全体を通してドット表記は使いません。

3-1. いいねが100以上のツイートを検索

以下の SQL 文で検索ができます。

select
    json_value(tweet_data, '$.tweet.favorite_count' returning number) favs,
    json_value(tweet_data, '$.tweet.created_at' returning varchar2) time,
    json_value(tweet_data, '$.tweet.full_text' returning varchar2) text
from tweets t
    where json_value(tweet_data, '$.tweet.favorite_count' returning number) >= 100

実行計画は以下になります。作成した JSON 検索索引が利用されていること、>= 100 の条件がアクセス述語として掛かっていることが分かります。（正直、それ以上は分かりませんが）

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |   951 |    33   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |     1 |   951 |    33   (0)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TWEETS     |     1 |   945 |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | IDX_TWEETS |       |       |     4   (0)| 00:00:01 |
|*  4 |   JSONTABLE EVALUATION       |            |       |       |            |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("T"."TWEET_DATA" /*+ LOB_BY_VALUE */
              ,'(sdata(FNUM_771D3FFE0DA084605AC10D8BE46BE738_favorite_count  >= 100 ))')>0)
   4 - filter("P"."C_03$">=100)

ちなみに、JSON 検索索引を使ったケースと使わなかったケースの SQL 経過時間（５回平均、ハードハーズ、I/O 含まず）は 1.6ms と 36.7ms でした。

3-2. ハッシュタグを含むツイートを検索

ハッシュタグは tweet.entities.hashtags というフィールドの下に配列として保持されています。
#sformula というハッシュタグを含むツイートを検索する SQL 文は以下になります。

select
    json_value(tweet_data, '$.tweet.created_at' returning varchar2) time,
    json_value(tweet_data, '$.tweet.full_text' returning varchar2) text
from
    tweets
where
    json_exists(tweet_data, '$?(@.tweet.entities.hashtags.text == "sformula")');

実行計画は以下になります。

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |     1 |   949 |    33   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |     1 |   949 |    33   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| TWEETS     |     1 |   945 |     4   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | IDX_TWEETS |       |       |     4   (0)| 00:00:01 |
|   4 |   JSONTABLE EVALUATION       |            |       |       |            |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(JSON_EXISTS2("TWEET_DATA" /*+ LOB_BY_VALUE */  FORMAT OSON ,
              '$?(@.tweet.entities.hashtags.text == "sformula")' FALSE ON ERROR)=1)
   3 - access("CTXSYS"."CONTAINS"("TWEETS"."TWEET_DATA" /*+ LOB_BY_VALUE */
              ,'(sdata(FVCH_51C0B89D6DD95723EDE82A8AFB722BE0_text  = "sformula" ))')>0)

ちなみに、JSON 検索索引を使ったケースと使わなかったケースの SQL 経過時間（５回平均、ハードハーズ、I/O 含まず）は 1.9ms と 20.1ms でした。

3-3. 特定の期間のあるキーワードが含まれるツイートを検索

2019年の Oracle というキーワードを含むツイートを検索する SQL 文は以下になります。本当はcreated_at フィールドの範囲検索をしたいのですが、Wed Nov 27 12:43:29 +0000 2019 と処理しづらいフォーマットになっているため、2019という文字列を含むという条件の書き方にしています。

select
    json_value(tweet_data, '$.tweet.created_at' returning varchar2) time,
    json_value(tweet_data, '$.tweet.full_text' returning varchar2) text
from
    tweets
where
    json_textcontains(tweet_data, '$.tweet.full_text', 'Oracle')
and json_textcontains(tweet_data, '$.tweet.created_at', '2019');

実行計画は以下になります。

-------------------------------------------------------------------------------------------
| Id  | Operation                    | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |            |  6308K|  5709M| 21292   (1)| 00:00:01 |
|   1 |  NESTED LOOPS                |            |  6308K|  5709M| 21292   (1)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| TWEETS     |   772 |   712K|   297   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | IDX_TWEETS |       |       |     4   (0)| 00:00:01 |
|   4 |   JSONTABLE EVALUATION       |            |       |       |            |          |
-------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("CTXSYS"."CONTAINS"("TWEETS"."TWEET_DATA" /*+ LOB_BY_VALUE */
              ,'(((Oracle) INPATH (/tweet/full_text)) and ((2019) INPATH
              (/tweet/created_at)))')>0)

json_textcontains 関数は JSON 検索索引がないと使えないので、JSON 検索索引を使わない検索としては以下の SQL 文を用いました。索引を使うケース/使わないケースの SQL 経過時間（５回平均、ハードハーズ、I/O 含まず）は 2.6ms と 39.3ms でした。（レクサーの都合で少しだけ結果件数が異なります）

select
    json_value(tweet_data, '$.tweet.created_at' returning varchar2) time,
    json_value(tweet_data, '$.tweet.full_text' returning varchar2) text
from
    tweets_noindex
where
    json_value(tweet_data, '$.tweet.full_text') like '%Oracle%'
and json_value(tweet_data, '$.tweet.created_at') like  '%2019%';

4. まとめ

今回は OracleDB の JSON 検索索引について試してみました。感想としては、

JSON の任意のフィールドに対する検索が１つの索引で賄えるというのはシンプルでいいですね。
ただ、実行計画を読むとなると Oracle Text の理解が必要なので、トラブルシュート or チューニングのハードルは上がるのかな（チューニング余地があるかどうかも分かりませんが）。

性能に関しては、JSON 検索索引使わないケースに比べ速くはなっているのですが（当たり前）、如何せんデータ量が少ないので、いずれもっと大きいデータセットで試したいです（こういう場で使える、ある程度多様な構造を持った、データ量のある JSON オープンデータセットってあんまりないんですよね）。

5. 追加

さすがにツイートデータ 1.8万件は少なすぎたので、自分でテスト JSON データを生成して検索を実施してみました。

今回は以下の Python プログラムで 1,000万件＆約840MB（ファイルサイズ換算）の JSON データを生成しました。

for i in range(10000000):
    f_int = random.randint(0, 1000000) # 0-1,000,000 の乱数
    f_str = ''.join(random.choice(string.ascii_letters + string.digits) for _ in range(10)) # 10文字のランダム文字列
    f_short = random.randint(0, 99) # 0-99 の乱数
    f_char1 = random.choice(string.ascii_letters + string.digits) # 1文字乱数
    f_char2 = random.choice(string.ascii_letters + string.digits) # 1文字乱数

    print('{{"f_int": {}, "f_str": "{}", "f_short": {}, "f_char1": "{}", "f_char2": "{}"}}'.format(f_int, f_str, f_short, f_char1, f_char2))

データの中身はこんな感じ。

{"f_int": 714435, "f_str": "jJVyFVpU6B", "f_short": 20, "f_char1": "R", "f_char2": "T"}
{"f_int": 508322, "f_str": "IJS03l1eXq", "f_short": 78, "f_char1": "w", "f_char2": "P"}
{"f_int": 414063, "f_str": "ibnyykKWFT", "f_short": 97, "f_char1": "c", "f_char2": "W"}
{"f_int": 232977, "f_str": "sqP8jwjI27", "f_short": 11, "f_char1": "o", "f_char2": "x"}
{"f_int": 587322, "f_str": "x69osXJrTk", "f_short": 8, "f_char1": "H", "f_char2": "q"}

生成したデータを json_tblという以下の DDL 文で作成したテーブルにロードします。

create table json_tbl (doc json);

JSON 検索索引の作成方法は 2. とほぼ同じ（JSON データに日本語含まないので、日本語用のレクサーを使う必要はないのですが手抜きで使い回しています）。

create search index idx_json_tbl on json_tbl(doc)
for json
parameters (
    'SEARCH_ON TEXT_VALUE_STRING
     LEXER my_lexer
     WORDLIST my_wordlist'
);

ここから検索していきます。今回は alter system flush shared_pool / buffer_cache で共有プールとデータキャッシュはクリアしてから検索 SQL 文を実行しています。

まず、f_int（数値型フィールド）の一致検索です。JSON 検索索引を使うと 1.32秒、使わないと 17-18秒掛かりました。

SQL>  select * from json_tbl where json_value(doc, '$.f_int' returning number) = 351247;

DOC
--------------------------------------------------------------------------------
{"f_int":351247,"f_str":"t8DxUjB6a9","f_short":89,"f_char1":"z","f_char2":"B"}
{"f_int":351247,"f_str":"QC5IlGE1QI","f_short":5,"f_char1":"V","f_char2":"t"}
{"f_int":351247,"f_str":"dIW3Y9SSXS","f_short":71,"f_char1":"5","f_char2":"g"}
{"f_int":351247,"f_str":"VGlgGh5LO8","f_short":35,"f_char1":"C","f_char2":"M"}
{"f_int":351247,"f_str":"urhWUvi6gb","f_short":5,"f_char1":"d","f_char2":"D"}
{"f_int":351247,"f_str":"LAk2r9Gq2K","f_short":6,"f_char1":"5","f_char2":"s"}
{"f_int":351247,"f_str":"WxWUm4Wou2","f_short":66,"f_char1":"9","f_char2":"Z"}
{"f_int":351247,"f_str":"T0XX2N46tw","f_short":4,"f_char1":"9","f_char2":"y"}

8行が選択されました。

経過: 00:00:01.32

次に、f_str（文字列型フィールド）の一致検索です。JSON 検索索引を使うと 1.01秒、使わないと 17-18秒掛かりました（使わないケースはテーブルフルスキャンがネックなので最初のケースと変わらないですね）。

SQL> select * from json_tbl where json_value(doc, '$.f_str') = 'YqH3HwmNZd';

DOC
--------------------------------------------------------------------------------
{"f_int":58590,"f_str":"YqH3HwmNZd","f_short":55,"f_char1":"g","f_char2":"e"}

経過: 00:00:01.01

次に、f_short（数値型フィールド）と f_char1/2（文字フィールド）の組み合わせ検索です。JSON 検索索引を使うと 3.68秒、使わないと 17-18秒掛かりました。

SQL> select * from json_tbl
  2  where
  3      json_value(doc, '$.f_short' returning number) = 75
  4  and json_value(doc, '$.f_char1') = 'A'
  5  and json_value(doc, '$.f_char2') = 'B';

DOC
--------------------------------------------------------------------------------
{"f_int":306938,"f_str":"nYdgWi6yQ4","f_short":75,"f_char1":"A","f_char2":"B"}
{"f_int":410661,"f_str":"WjPs5fqqyy","f_short":75,"f_char1":"A","f_char2":"B"}
{"f_int":220190,"f_str":"N7N7UjgRkS","f_short":75,"f_char1":"A","f_char2":"B"}
{"f_int":109361,"f_str":"egmukPOrJZ","f_short":75,"f_char1":"A","f_char2":"B"}
{"f_int":361868,"f_str":"7vTlosauqj","f_short":75,"f_char1":"A","f_char2":"B"}
(略)
24行が選択されました。

経過: 00:00:03.68

全文検索も試したかったのですが、文字列をランダム生成したのと、レクサーに日本語用のものを使ってしまったため、今回はスキップです。

ちなみに、1,000万件＆約 840MB（ファイルサイズ換算）を Oracle にロードし、JSON 検索索引を作成すると、データ容量は以下になります。

テーブルサイズ：1540MB
索引サイズ：6740MB

この手の技術は Marklogic を触った時にも感じたのですが、索引サイズが大きくて辛いですね。索引サイズだけではなくバイナリ形式で JSON を保持するテーブルのサイズも大きいのは気になりますが。

また、今回はデータを SSD ではなく HDD に乗せてテストしています。かつ、並列処理は利用していないです。なので、

ディスクをより速いものに代える
パラレルクエリを利用する

という手を打つと JSON 検索索引を使うケースと使わないケースの性能差は縮まる傾向になると思います。1. で触れた「多様で複雑な構造を持つ JSON ドキュメントをアドホックに検索したいようなシステム」はデータ量は多いですが、時間当たりのクエリ数や同時利用ユーザー数が少ないことが多いので、ハードパワーに頼ったアプローチというのも場合によってはアリになります（昔、この手のシステムで特にこだわらずクラウド系 DWH でいいかなと判断したことがあったのを思い出しました）。

ただ、アドホック検索に対してこういうアプローチもあると知っておくことは損ではないかなと思います。