※ 本記事は、Roger Fordによる”Using JSON documents and don’t know what you’re looking for? 23c Search Indexes to the rescue“を翻訳したものです。
2023年6月5日
概要
Oracleには、JSONを処理するための強力な機能があります。また、キーワード検索、フレーズ検索、近接検索など、全文検索のための柔軟な機能もあります。すべてのJSONドキュメントに対して最適化された方法で全文検索の強力な機能を提供するために、これらの機能がJSON検索索引でどのように満たされているかを確認します。
テキスト索引とは何ですか?
基本形式では、テキスト索引を使用すると、データベース内のテキスト・フィールドに単語ベースの索引を作成できます。これにより、特定の単語やフレーズを含むフィールドをテーブルで検索できます。
従業員の詳細が記載されたテーブルempがあるとします。:
create table emp(name varchar2(40), salary number, qualifications varchar2(200));
insert into emp values ('John', 1500, 'PhD in physics, Msc Math');
commit;
23cでは、次を使用して、その表にテキスト索引を作成します。:
create search index emp_qual on emp(qualifications);
以前のバージョンでは、次のことを行います。:
create index emp_qual on emp(qualifications) indextype is ctxsys.context;
テキスト索引を作成した後、次のようなCONTAINS問合せを使用して検索できます。:
select * from emp where contains(qualifications, 'physics') > 0;
これは、テキスト索引の使用方法の最も簡単な例です。この検索では、qualificationsフィールドのどこかで「physics」という語が検索されます。問合せはより複雑になる場合があります。JSON_TEXTCONTAINSを少し見ると、さらに高度な例がいくつか表示されます。
検索インデックスに関する特長は何ですか?
その一方で、「physics」という単語を検索するために、「qualifications」フィールドで部分文字列検索を実行できると考えられます。ただし、部分文字列検索はかなり制限されています。
- 索引を使用できない
- 大/小文字が区別
- 句読点と間隔は検索に影響
- AND、OR、NOTなどのブール検索は、部分文字列検索自体では実行できません。非常に非効率になる複数の検索を実行する必要があります。
検索索引を持つことは、テキスト内の各単語に独自の索引エントリがあることを意味するため、元のテキストをスキャンしなくても、個々の単語やフレーズ、または語句のブール組合せへの参照を迅速に検索できます。
JSON索引とは何ですか?
たとえば、従業員データはJSONとして格納され、給与範囲を使用して検索する必要があるとします。少数の行の場合、特に21c/23cでバイナリJSONデータ型を使用している場合、JSONの全体スキャンは十分に高速です。
create table empj(empdata json);
insert into empj values ('{ "name":"john", "salary":1500, "qualifications": "PhD in physics, Msc Math"}');
insert into empj values ('{ "name":"bobby", "salary":900,
"qualifications": "Msc Math", "hobbies": "physics" }');
commit;
select * from empj e where e.empdata.salary.number() > 1000;
しかし、JSON表に行数が非常に多い場合はどうなるでしょうか? その場合は、JSONのsalary要素に索引を作成します。:
create index emp_salary on empj e(e.empdata.salary.number());
しかし、検索する必要があるフィールドがわからない場合はどうなるでしょうか? あるいは、これらのフィールドで単語ベースの検索を実行しますか。最終的に、JSONデータは柔軟性があり、以前はリレーショナル表のような静的で適合性に優れたスキーマ定義はありません。
これらの問題はどちらもJSON検索索引で解決されています。JSON検索索引は、JSONオブジェクト内のすべてのデータを索引付けします。データ型を事前に宣言したり、ドキュメント内の属性を知る必要もありません。すべてJSONです。JSONドキュメントを最も効率的な方法で索引付けするだけでなく、この索引で全文検索機能を提供します。
OK … JSON検索索引が必要です。どのようにして作成しますか?
JSON検索索引の作成は、次のように簡単です。:
create search index emp_search on empj(empdata) for json;
このJSON検索索引を使用すると、一石二鳥です。まず、実行計画に示すように、以前の給与例のような通常のJSON検索の索引があります。:
explain plan for select * from empj e where e.empdata.salary.number() > 1000; select * from table(dbms_xplan.display); ------------------------------------------------------------------------------------------ | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ------------------------------------------------------------------------------------------ | 0 | SELECT STATEMENT | | 1 | 4114 | 4 (0)| 00:00:01 | |* 1 | TABLE ACCESS BY INDEX ROWID| EMPJ | 1 | 4114 | 4 (0)| 00:00:01 | |* 2 | DOMAIN INDEX | EMP_SEARCH | | | 4 (0)| 00:00:01 | ------------------------------------------------------------------------------------------
Id=2の行は、検索にドメイン索引を使用していることを示しています。これは、the2給与フィールドへのアクセスを高速化するために使用されているJSON検索索引です。ただし、そのフィールドに特に索引付けするように指定していなくてもかまいません。
次に、JSON検索索引では、JSON内のすべての文字列フィールドに対する全文検索も可能です。これは、JSON_TEXTCONTAINS演算子を使用して行います。この演算子は、列名、検索場所のためのJSONパス、および引数として全文検索式を取ります。次に例を示します。:
select e.empdata.name from empj e where json_textcontains(empdata, '$', 'physics'); NAME -------------------------------------------------------------------------------- "john" "bobby"
そこにある「$」は、ルートまたはJSONドキュメントのベース(詳細を参照する場合は「JSONパス式」です)を表し、JSONドキュメントの任意の場所で「physics」という語を検索する必要があることを意味します。ドキュメントの特定の部分を検索する場合は、「qualifications」のように、その部分をパスとして表現できます。:
select e.empdata.name from empj e
where json_textcontains(empdata, ‘$.qualifications’, ‘physics’);
NAME -------------------------------------------------------------------------------- "john"
これはJSON検索索引のパワーです。「検索スペース」に応じて、ドキュメント全体または特定の属性 – JSON検索インデックスによりリクエストが高速化されます。
単純なJSON等価演算子といくつかのワイルドカードを使用してこれを行うことができましたが、大規模なコレクションでは高速ではありませんでした。次のような複雑な検索は実行できませんでした。:
select e.empdata.name, e.empdata.qualifications from empj e where json_textcontains(empdata, '$.qualifications', 'physics AND msc math'); NAME QUALIFICATIONS _________ _____________________________ "john" "PhD in physics, Msc Math" Meaning "the qualifications field contains the single word "physics" and the contiguous phrase "msc math". Nor could we do a 'fuzzy' search such as: select e.empdata.name, e.empdata.qualifications from empj e where json_textcontains(empdata, '$.qualifications', 'fuzzy(phisiks')); NAME QUALIFICATIONS _________ _____________________________ "john" "PhD in physics, Msc Math" Very useful if you're unsure of your spelling, or that of whoever created the JSON in the first place. The JSON search index comes to the rescue and you’ll find what you’re looking for!
23cでは、JSON_TEXTCONTAINSを使用して関連性ランキングを行うこともできます。さらにJSONドキュメントを次の表に追加します。:
insert into empj values ('{ "name":"bill", "salary":1000, "qualifications": "Math professor"}');
insert into empj values ('{ "name":"mike", "salary":2000, "qualifications": "Physics student"}');
commit;
COMMITを発行し、索引が更新されるまで2、3秒待つ必要があるため、SCOREを使用して問合せを実行できます。JSON_TEXTCONTAINSの追加の最終引数に注意してください。これは、SCORE()関数をこの特定のJSON_TEXTCONTAINSに関連付ける数値です。
select score(1), e.empdata.name, e.empdata.qualifications from empj e where json_textcontains(empdata, '$.qualifications', 'math ACCUM physics', 1) order by score(1) desc;
ACCUM演算子は、両方の用語が見つかった場合、レコードが1つの用語のみが見つかった場合よりも高いスコアであることを保証します。2つ以上の用語が存在し、検出された用語の数が多いほど、常にスコアが高くなります。したがって、前述の問合せでは次のことが示されます。:
SCORE(1) NAME QUALIFICATIONS
___________ __________ _____________________________
52 "john" "PhD in physics, Msc Math"
2 "bobby" "Msc Math"
2 "bill" "Math professor"
2 "mike" "Physics student"
スコアの絶対値はそれほど重要ではなく、通常はユーザーに表示するものではありません。かわりに、ここと同様に、結果をその関連性に従って順序付けるために使用されます。JSON検索インデックスを使用して、探しているものを簡単に見つけます。
そして、それは始まりにすぎません。Oracle JSON検索索引(および一般的なOracle Text索引)では、さらに多くの検出があります。JSON_TEXTCONTAINSで使用される検索問合せ構文は、Oracle Text CONTAINS演算子と同じであるため、ここで説明した内容の詳細を確認し、さらに多くのテキスト問合せ演算子については、『Oracle Textリファレンス』を参照してください。