※ 本記事は、Ulrike Schwinnによる”New full-text search in 23c: Ubiquitous Database Search“を翻訳したものです。
2023年7月3日
Oracle Databaseでの検索方法を知っていますか。Oracle Databaseに完全に統合された全文検索を可能にする、Oracle Textと呼ばれる機能があります。Oracle Database 23cには、ユビキタス・データベース検索という新機能があります。これは、様々なデータ・ソースで検索する必要がある場合に役立ちます。自己管理索引を作成するには1つのコマンドのみ、別のソースを追加するには別のコマンドが必要です。追加のPL/SQLコードを記述せずに、検索に異なる表データ・ソースを含めることができます。
以下の投稿は次のように細分化されています。
- Oracle Textの短いレビュー
- ステップ1: ユビキタス・データベース検索の索引作成
- ステップ2: データ・ソースの追加
- ステップ3: 仮想ドキュメントの確認
- ステップ4: 索引に対する問合せの実行
- データ・ソースの追加
- モニタリング
Oracle Textのレビュー
Oracle Textはすべてのデータベース・エディションに含まれており、追加のインストールなしで直接使用できます。通常のデータベース・スキーマですぐに起動できます。最も単純なユース・ケースは、単一列内で全文検索を実行することです。ただし、さらに複雑なケースがサポートされています。同じデータ・ソース(MULTI_COLUMN_DATASTORE)の複数の列内で検索します。また、複数のデータ・ソース内の検索もサポートされています(USER_DATASTORE)。この場合、索引作成時にドキュメントを合成するための特別なシグネチャを持つストアド・プロシージャが必要です。すべてのシナリオで、すべての種類の全文検索基準を満たすには、1つの索引のみが必要です。例として、あいまい、健全性、近似などの演算子があります。
ユビキタス・データベース検索による23cの新機能と特長は何ですか? 複数のデータ・ソースが参照される複雑なユース・ケースでは、Oracle Textの使用がはるかに容易になりました。DBMS_SEARCHという新しい単純なPL/SQLインタフェースがあり、複数のオブジェクトにまたがる索引を作成したり、索引にデータ・ソース(特定の表またはビュー)を簡単に追加したり、それらを削除したりできます。これにより、索引付けタスクが簡略化されます。たとえば、複雑なUSER_DATASTOREプロシージャを定義する必要はありません。これにより、開発者の生産性が大幅に向上します。さらに、索引の同期は自動的に維持されます。
ここでは、簡単な例を使用して、それがどのように機能するかを理解します。
ステップ1: ユビキタス・データベース検索の索引作成
新しいインタフェースDBMS_SEARCHでは、単一の索引で複数のスキーマ・オブジェクトの索引付けが可能です。詳細は、『Oracle Textリファレンス: DBMS_SEARCH』のドキュメントを参照してください。
開始する前に、githubからOracle Databaseサンプル・スキーマをインストールできます。この例では、スキーマ “customer orders” (ユーザーCO)の表を使用して、機能を示します。スキーマ統計を収集すると、次のようになります。:
SQL> select table_name, num_rows from user_tables; TABLE_NAME NUM_ROWS ------------------------------ ---------- CUSTOMERS 392 STORES 23 PRODUCTS 46 ORDERS 1950 SHIPMENTS 1892 ORDER_ITEMS 3914 INVENTORY 566
SEARCH_PRODUCTSという名前のユビキタス索引を作成します。CREATE_INDEXプロシージャを使用して、ユビキタス検索索引を作成します。いくつかのデフォルトの索引付けプリファレンスでJSON検索索引テクノロジを使用します。
execute DBMS_SEARCH.CREATE_INDEX(index_name=>'SEARCH_PRODUCTS');
2つのJSON列DATAおよびMETADATAを含む表SEARCH_PRODUCTSを含むOracle Textのインフラストラクチャを作成します。METADATA列は、OWNER、SOURCEおよびKEY (ROWID) JSON値を使用して、特定のデータ・ソースの各行を一意に識別します。
SQL> desc SEARCH_PRODUCTS Name Null? Type ----------------------------------------- -------- ---------------------------- METADATA NOT NULL JSON DATA JSON OWNER VARCHAR2(128) SOURCE VARCHAR2(128) KEY VARCHAR2(1024)
最初に索引を削除する必要がある場合は、これを使用します。
execute DBMS_SEARCH.DROP_INDEX('SEARCH_PRODUCTS');
ステップ2: データ・ソースの追加
この索引には、表、外部表またはビューのセットをデータ・ソースとして追加できます。指定したソースのすべての列が索引付けされ、その後に全文検索に使用できます。この場合は、SHIPMENTS表とSTORES表を追加します。ADD_SOURCEプロシージャは、表をデータ・ソースとしてDBMS_SEARCH索引に追加します。
ノート: 各表に主キー列を定義する必要があります。
ここでは、2つの表に主キーが定義されていることを確認しました。
execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'SHIPMENTS'); execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'STORES');
ステップ3: 仮想ドキュメントの確認
検索できる内容を確認するには、GET_DOCUMENTプロシージャを実行します。GET_DOCUMENTプロシージャは、複数の列、表またはビューを移入した後に作成された仮想索引付きドキュメントを返します。次のように、DBMS_SEARCH索引に対する問合せを実行する前に、このドキュメントを確認できます。
ノート: ユーザー・データ・ストアを使用して、JSONドキュメントをその場でアセンブルします。
SQL> set long 1000 longc 500
SQL> select DBMS_SEARCH.GET_DOCUMENT (INDEX_NAME=>'SEARCH_PRODUCTS', DOCUMENT_METADATA=>METADATA) output
from SEARCH_PRODUCTS;
OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"CO":{"STORES":{"LOGO_CHARSET":null,"LOGO_FILENAME":null,"LONGITUDE":34.862138,"STORE_ID":23,"LOGO":null,"LOGO_MIME_TYPE":null,"LATITUDE":32.100664,"STORE_NAME":"Tel Aviv","LOGO_LAST_UPDATED":null,"WEB_ADDRESS":null,"PHYSICAL_ADDRESS":"B, Aharon Bart St 18, Petah Tikva, 4951400, Israel"}}}
...
{"CO":{"SHIPMENTS":{"SHIPMENT_ID":1956,"STORE_ID":1,"CUSTOMER_ID":376,"DELIVERY_ADDRESS":"Pecos, NM 87552 USA","SHIPMENT_STATUS":"IN-TRANSIT"}}}
1915 rows selected.
ステップ4: 索引に対する問合せの実行
JSON_TEXTCONTAINSおよびCONTAINS演算子を使用して問合せを実行できます。
まず、演算子FUZZYでCONTAINSを使用します。FUZZY演算子は、指定された用語と同様のスペルが付けられた単語を含むように問合せを展開します。SHIPMENTS表およびSTORES表からの結果は、次のようになります。両方の表からの結果が含まれます。
SQL> SELECT METADATA output from SEARCH_PRODUCTS
WHERE CONTAINS(data,'fuzzy(Los)')>0;
OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"STORES","KEY":{"STORE_ID":10}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":976}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":469}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":470}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1484}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":545}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":546}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1806}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1807}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":854}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":855}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1833}}
{"OWNER":"CO","SOURCE":"SHIPMENTS","KEY":{"SHIPMENT_ID":1834}}
-- let's check
SQL> select PHYSICAL_ADDRESS from stores where store_id=10;
PHYSICAL_ADDRESS
--------------------------------------------------------------------------------
C/ Jos?? Echegaray 6B
Las Rozas -- here fuzzy(Los) finds the term 'Las'
28230 Madrid
SQL> select DELIVERY_ADDRESS from shipments where shipment_id=976;
DELIVERY_ADDRESS
--------------------------------------------------------------------------------
Los Angeles, CA 90026 USA
JSON_TEXTCONTAINSを使用して問合せを試行します。JSON_TEXTCONTAINSは、指定された文字列がJSONプロパティ値に存在するかどうかをチェックします。
SQL> SELECT metadata from SEARCH_PRODUCTS
WHERE JSON_TEXTCONTAINS(data,'$.CO.STORES.PHYSICAL_ADDRESS','fuzzy(LOS)');
METADATA
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"STORES","KEY":{"STORE_ID":10}}
データ・ソースの追加
次に、別のソースを追加して、そのソースを検索に含めることができるかどうかを確認します。CUSTOMERS表を追加します。
execute DBMS_SEARCH.ADD_SOURCE(index_name =>'SEARCH_PRODUCTS', source_name => 'CUSTOMERS');
次に、問い合せましょう。「jon」という用語(より正確には「fuzzy(jon)」)を使用して、CUSTOMERS表に結果を検索します。
SQL> SELECT METADATA output from SEARCH_PRODUCTS WHERE CONTAINS(data,'fuzzy(jon)')>0;
OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":66}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":368}}
2 rows selected.
-- Let's check
SQL> select full_name from customers where customer_id=66;
FULL_NAME
--------------------------------------------------------------------------------
John Venzl
CUSTOMERS表に行を追加し、再度検索を元に戻します。
SQL> insert into customers values (1000,'john.johnson@oracle.com','John Johnson');
1 row created.
SQL> SELECT METADATA output from SEARCH_PRODUCTS WHERE CONTAINS(data,'fuzzy(jon)')>0;
OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":66}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":368}}
2 rows selected.
まだ2行しかありません。変更をコミットすると、新しい行が表示されます。これを実現するために、JSON検索索引では、デフォルトでsync on commitと呼ばれるメソッドを使用します。つまり、索引はコミットの一部として自動的に同期されます。
SQL> commit;
Commit complete.
SQL> SELECT METADATA output from SEARCH_PRODUCTS WHERE CONTAINS(data,'fuzzy(jon)')>0;
OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------------------------------
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":66}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":368}}
{"OWNER":"CO","SOURCE":"CUSTOMERS","KEY":{"CUSTOMER_ID":1000}}
3 rows selected.
モニタリング
Oracle Textの経験がある場合は、Oracleテキスト索引をモニターおよび管理するための特別なデータ・ディクショナリ・ビューがあることがわかっている場合があります。
USER_INDEXESから開始して、作成された索引に関する基本情報を取得します。
SQL> select INDEX_TYPE,TABLE_OWNER, TABLE_NAME, TABLE_TYPE, STATUS, ITYP_OWNER
from user_indexes where index_name='SEARCH_PRODUCTS';
INDEX_TYPE TABLE_OWNER TABLE_NAME TABLE_TYPE STATUS ITYP_OWNER
--------------------------- --------------- -------------------- ----------- -------- ----------
DOMAIN CO SEARCH_PRODUCTS TABLE VALID CTXSYS
技術的な詳細に関心のあるユーザーについては、CTX_INDEX_VALUESなどを問い合せてください。索引で使用される各オブジェクトの属性値が表示されます。このビューは、ユーザーCTXSYSのみが問合せ可能なことに注意してください。
SQL> select IXV_CLASS, IXV_OBJECT, IXV_ATTRIBUTE, IXV_VALUE from ctxsys.CTX_INDEX_VALUES where IXV_INDEX_OWNER='CO'; IXV_CLASS IXV_OBJECT IXV_ATTRIBUTE IXV_VALUE ------------------------------ ------------------------- ------------------------- ---------------------------------------- DATASTORE USER_DATASTORE PROCEDURE "CTXSYS"."DRVXMD"."GET_DOCUMENT" DATASTORE USER_DATASTORE OUTPUT_TYPE JSON SECTION_GROUP PATH_SECTION_GROUP JSON_ENABLE YES SECTION_GROUP PATH_SECTION_GROUP DATAGUIDE ON ... WORDLIST BASIC_WORDLIST WILDCARD_INDEX YES WORDLIST BASIC_WORDLIST WILDCARD_INDEX_K 3 STORAGE BASIC_STORAGE R_TABLE_CLAUSE lob (data) store as (cache) STORAGE BASIC_STORAGE I_INDEX_CLAUSE compress 2 STORAGE BASIC_STORAGE STAGE_ITAB YES STORAGE BASIC_STORAGE STAGE_ITAB_MAX_ROWS 10000
簡単に説明します。索引タイプは、通常、JSONドキュメントの索引付けおよび問合せに使用されるJSON検索索引です。予想どおり、出力JSONでUSER_DATASTOREプリファレンスを使用します。セクション・グループは、JSONを含むセクション内の問合せが有効かどうか、およびドキュメント・セクションの定義方法を示します。Dataguideは、これらのドキュメントで使用されるフィールドに関するメタデータを記録する一連のJSONドキュメントに含まれる構造およびタイプの情報のサマリーです。JSON検索索引DATAGUIDEの一部として永続dataguide情報を作成するには、specified.The BASIC_WORDLISTを使用すると、ステミングおよびファジー・マッチングが可能になります。WILDCARD_INDEXが有効です。つまり、すべてのワイルドカード式に対する高速で効率的なワイルドカード検索が可能です。
索引記憶域に関する情報もあります。重要なのは、STAGE_ITABオプションが有効になっていることです。STAGE_ITAB索引オプションを有効にすると、新しいドキュメントは$I表ではなく、一時的に$Gステージング表に格納されます。この記憶域によって、$I表が断片化されていないことが確認され、問合せのパフォーマンスが低下することはありません。Oracle Databaseリリース21c以降、$Gにデフォルトで10Kを超える行がある場合、$Gの内容は索引同期中に自動的に$Iに移動されます。この値は、STORAGEプリファレンスのSTAGE_ITAB_MAX_ROWS属性によって制御されます。詳細は、ドキュメントを参照してください。
続きを読む
アプリケーション開発者ガイド: ユビキタス・データベース検索の使用
Oracle Textリファレンス: DBMS_SEARCH