概要
SQL の JSON 関数 JSON_TEXTCONTAINS を用いると、任意の文字列が含まれている JSON データを検索することができます。
SELECT COUNT(*)
FROM wiki_en
WHERE JSON_TEXTCONTAINS(content_json, '$.text', 'データベース');
Oracle Database の場合には、次のような簡単な構文で JSON 列に対する全文索引を作成することができます。
CREATE SEARCH INDEX wiki_search_idx ON wiki (jsondata);
この全文索引が利用できると、RDBMS で大きなテキストデータを含む JSON を扱いやすくなるため便利ですが、一方で、データ量に対して性能や索引サイズが十分かわかっていないため、日本語 Wikipedia のデータを使って簡単に検証したいと思います。
日本語 Wikipedia の JSON データを作成して、データベースにロードするためのスクリプトとその解説は こちらの記事 に記載しています。
Oracle Database の準備
無料版の Oracle Database 23ai Free を利用しようと考えましたが、全文索引のサイズを含めるとユーザーデータの合計が 12GB の制限を超えてしまいました。そのため、以下の Oracle Cloud 環境を使用しました。
- Oracle Base Database Service
- DB Version: 23.4.1.24.06 (23ai)
- Edition: Standard Edition
- Shape: VM.Standard.E5.Flex
- CPU Core Count: 1
sys でログインして、一般ユーザー myuser を作成します。
sqlplus sys/<password>@<ip_address>:1521/<service_name> as sysdba
CREATE USER myuser IDENTIFIED BY <password> QUOTA UNLIMITED ON users;
GRANT CONNECT, RESOURCE TO myuser;
myuser でログインできることを確かめておきます。
sqlplus myuser/<password>@<ip_address>:1521/<service_name>
Wikipedia データのインポート
今回用いる wiki 表のダンプ（約 3.8 GB）をこちらに公開しています。2024年5月1日時点の日本語 Wikipedia データ（2,289,280 件）を JSON 列に格納しています。
OS ディレクトリを作成しておきます。
sudo mkdir /u01/dir01
sudo chown oracle:oinstall /u01/dir01/
ダンプファイルをダウンロードします。
sudo su - oracle
cd /u01/dir01/
wget https://objectstorage.ap-osaka-1.oraclecloud.com/n/ax2juefahle0/b/download/o/wiki.dmp
sys でログインして、myuser にディレクトリ作成権限を付与します。
GRANT create any directory, drop any directory TO myuser;
myuser でログインして、ディレクトリを作成します。
CREATE DIRECTORY dir01 AS '/u01/dir01';
インポート用のコマンド impdp を使って表をインポートします。
impdp myuser/<password>@<ip_address>:1521/<service_name> \
tables=myuser.wiki directory=dir01 dumpfile=wiki.dmp logfile=wiki.log
Processing object type TABLE_EXPORT/TABLE/TABLE_DATA
. . imported "MYUSER"."WIKI" 3.8 GB 2289280 rows
表には JSON データを格納する列のみが含まれています。
SELECT * FROM wiki FETCH FIRST 3 ROWS ONLY;
JSONDATA
--------------------------------------------------------------------------------
{"id":"60","revid":"2160598","url":"https://ja.wikipedia.org/wiki?curid=60","tit
{"id":"62","revid":"1788533","url":"https://ja.wikipedia.org/wiki?curid=62","tit
{"id":"63","revid":"516649","url":"https://ja.wikipedia.org/wiki?curid=63","titl
全文索引の作成
全文索引の作成には一時表領域を使用するため、一時表領域が十分に拡張できるよう、データファイルを追加しておきます。
sys でログインして、データファイルをひとつ追加します。
ALTER TABLESPACE temp ADD TEMPFILE '+DATA';
myuser でログインして、全文索引を作成します。この環境では 15 分程度かかりました。
CREATE SEARCH INDEX wiki_search_idx ON wiki (jsondata);
INDEX WIKI_SEARCH_IDX created.
Elapsed: 00:15:08.640
sys でログインして、セグメントのサイズを確認します。
SELECT
SEGMENT_NAME
, SEGMENT_TYPE
, BYTES/1024/1024 AS MB
FROM DBA_SEGMENTS
WHERE segment_name LIKE '%WIKI%'
ORDER BY mb DESC;
SEGMENT_NAME SEGMENT_TYPE MB
------------------------- ------------ ------
DR$WIKI_SEARCH_IDX$I TABLE 8758
DR$WIKI_SEARCH_IDX$X INDEX 5991
WIKI TABLE 3232
DR$WIKI_SEARCH_IDX$SN TABLE 316
DR$WIKI_SEARCH_IDX$SNI INDEX 216
DR$WIKI_SEARCH_IDX$KR INDEX 72
DR$WIKI_SEARCH_IDX$KD INDEX 72
DR$WIKI_SEARCH_IDX$K TABLE 54
DR$WIKI_SEARCH_IDX$H INDEX .0625
DR$WIKI_SEARCH_IDX$STI INDEX .0625
DR$WIKI_SEARCH_IDX$UI INDEX .0625
DR$WIKI_SEARCH_IDX$NI INDEX .0625
DR$WIKI_SEARCH_IDX$ST TABLE .0625
DR$WIKI_SEARCH_IDX$B TABLE .0625
DR$WIKI_SEARCH_IDX$C TABLE .0625
DR$WIKI_SEARCH_IDX$Q TABLE .0625
DR$WIKI_SEARCH_IDX$U TABLE .0625
DR$WIKI_SEARCH_IDX$N TABLE .0625
DR$WIKI_SEARCH_IDX$G TABLE .0625
DR$WIKI_SERACH_IDX$.. という名前の表と索引が内部的に作成されていて、3.2 GB 程度の wiki 表に対して、数倍のデータ容量が必要になることがわかります。
JSON 内の全文検索
JSON_TEXTCONTAINS 関数を用いて、本文に「データベース」という単語を含む Wikipedia ページの数をカウントしてみると、0.1 秒以内に実行できることがわかります。
SELECT COUNT(*)
FROM wiki
WHERE JSON_TEXTCONTAINS(jsondata, '$.text', 'データベース');
COUNT(*)
----------
288
Elapsed: 00:00:00.028
試しに索引を使わないようにヒントをつけてみると実行時間は 26 秒になりました。
SELECT /*+ NO_INDEX(wiki wiki_search_idx) */ COUNT(*)
FROM wiki
WHERE JSON_TEXTCONTAINS(jsondata, '$.text', 'データベース');
COUNT(*)
----------
288
Elapsed: 00:00:26.320
JSON の値の内容を出力するときには JSON_VALUE 関数を用います。text 要素に含まれる長い文字列は CLOB にキャストします。
SELECT JSON_VALUE(jsondata, '$.id') AS id
, JSON_VALUE(jsondata, '$.title') AS title
, JSON_VALUE(jsondata, '$.text' RETURNING CLOB) AS text
FROM wiki
WHERE JSON_TEXTCONTAINS(jsondata, '$.text', 'データベース')
FETCH FIRST 3 ROWS ONLY;
ID TITLE TEXT
------- --------------------- -------------------------------------------------------------------------------------------------------------------------
63 情報工学 情報工学（じょうほうこうがく、）とは、「計算機による情報処理に関連する科学技術の一分野」を指す言葉であり、「情報科学」や「計算機科学」ともいう。なお英語の は
1297 パーソナルコンピュータ パーソナルコンピュータ（）は、個人によって占有されて使用されるコンピュータのことである。略称はパソコンまたはPC（ピーシー）。ただし「PC」という略称は、特に
1244 バイオインフォマティクス バイオインフォマティクス（英語：bioinformatics）とは、生命科学と情報科学の融合分野のひとつであり、DNAやRNA、タンパク質をはじめとする、生命が
Elapsed: 00:00:00.051
以上の通り、200 万件以上の日本語 JSON データに対して、全文索引が有効であることを確認できました。索引のデータサイズは大きいため注意が必要ですが、JSON の内容と表のメタデータを用いて検索する場合などに便利そうです。