13 DBMS_SEARCH APIを使用したユビキタス・データベース検索の実行
Oracle Database 23c以降では、単一の索引で複数のスキーマ・オブジェクトの索引付けにDBMS_SEARCH PL/SQLパッケージを使用できます。
13.1 ユビキタス検索索引について
ユビキタス・データベース検索では、スキーマ全体における複数オブジェクトに対する全文および範囲ベースの検索が可能です。
概要
ユビキタス検索索引(すなわちDBMS_SEARCH索引)は、複数の列、表またはビューに対する全文検索を実行できるようにする、一連のプリファレンスと設定が事前定義されているJSON検索索引です。これらの索引の作成、管理および問合せには、DBMS_SEARCH PL/SQLパッケージを使用します。
指定したスキーマ内の、1つの表の複数の列、または様々な表からの複数の列にDBMS_SEARCH索引を作成できます。データ・ソース(つまり、表およびビュー)をこの索引に追加できます(ビューをマテリアライズする必要はない)。指定したソース内のすべての列が索引付けされ、全文または範囲ベースの検索に使用できるようになります。
DBMS_SEARCH APIを使用すると、次のタスクを実行できます:
-
索引の作成:
DBMS_SEARCH.CREATE_INDEXAPIでは、DBMS_SEARCH索引を作成できます。デフォルトでは、この索引は、ワイルドカード検索を可能にする
BASIC_WORDLISTや、全文および範囲検索の問合せを可能にするSEARCH_ONなど、キー索引プリファレンスを使用して作成されます。これらの索引は事前定義された間隔でバックグラウンドで非同期的にメンテナンスされるため、このような索引に対してSYNC_INDEX操作とOPTIMIZE_INDEX操作を明示的に実行する必要はありません。 -
データ・ソースの管理:
DBMS_SEARCH.ADD_SOURCEAPIでは、データ・ソース(特定の表やビューなど)を様々なスキーマからこの索引に追加できます。DBMS_SEARCH.REMOVE_SOURCEAPIでは、表またはビュー、およびそれに関連するすべてのデータを索引から削除できます。 -
索引付けされた仮想ドキュメントの表示:
DBMS_SEARCH.GET_DOCUMENTAPIでは、索引付けされた仮想JSONドキュメントを表示できます。このドキュメントでは、索引付けされたデータ・ソースの各行について、この索引で索引付けされたメタデータ値が表示されます。 -
複数のオブジェクトの問合せ:
DBMS_SEARCH.FINDAPIでは、指定したフィルタ条件に基づいてすべてのドキュメントのヒットリストを取得できます。同じ索引を使用して、単一のデータ・ソース内または複数のソースにわたり、全文または範囲ベースの問合せを実行できます。
索引表: DATA列およびMETADATA列
DBMS_SEARCH索引では、サポートされているすべてのSQLデータ型(Object Type列を含む)がJSONオブジェクトに格納されます(XMLTYPEおよびLONGデータ型を除く)。表またはビューは、XMLTYPEまたはLONGデータ型の列がある場合は、データ・ソースとして索引に追加できません。
DBMS_SEARCH.CREATE_INDEXプロシージャでは、INDEX_NAMEという名前の索引表が作成されます。この索引表には、次の2つのJSON列があります:
-
DATA列:
DBMS_SEARCH索引(INDEX_NAMEという名前もある)がDATA列に作成されます。なお、索引名は索引表名と一致します。これは空の列であり、
DBMS_SEARCH索引を問い合せるためのプレースホルダです。この列にデータ・ソースを追加します。CONTAINS()、JSON_TEXTCONTAINS()およびJSON_EXISTS演算子を使用して、DATA列に対してPL/SQL問合せを実行できます。ノート:
DBMS_SEARCH索引に追加するすべてのデータ・ソース(表、ビュー、またはビュー定義の各表)には、少なくとも1つのPrimary Key列が含まれている必要があります。外部キーがあるビュー・ソースに含まれている各表には、その表で定義されている関連する主キーを参照するForeign Key制約も必要です。 -
METADATA列:
METADATA列は、DBMS_SEARCH索引で、索引付けされた表またはビューの各行を一意に識別するために役立ちます。METADATA列には、次の形式のエントリが含まれています:{"OWNER":"TABLE_OWNER", "SOURCE":"TABLE_NAME", "KEY":"{PrimaryKey_COLUMN_i":PrimaryKey_VALUE_i}}METADATA列の各JSONキー(つまり、OWNER、SOURCEおよびKEY)は、INDEX_NAME表内の別々の仮想列でもあります。なお、
INDEX_NAME表はOWNERおよびSOURCEによってパーティション化されています。OWNERおよびSOURCEのメタデータ値により、行の選択元となる特定のデータ・ソースを一意に識別します。特定のデータ・ソースを問い合せるときは、OWNERおよびSOURCEの仮想列にWHERE句条件を追加すると、パーティション・プルーニングを使用して問合せ検索をそのソースの特定のパーティションに限定できます。
JSONデータ表現
INDEX_NAME索引では、表またはビューの索引付けされた行ごとに、次の形式のJSON表現が格納されます。{"OWNER":
{
"TABLE_NAME":{"COLUMN1_NAME":"COLUMN1_VALUE",…}
}
}INDEX_NAME表のMETADATA列には、索引付けされた行ごとに、次の形式の対応するJSONエントリが格納されます。{"OWNER":"TABLE_OWNER",
"SOURCE":"TABLE_NAME",
"KEY":"{PrimaryKey_COLUMN_i":PrimaryKey_VALUE_i}}例:
SCOTTという名前のユーザーがEMPLOYEES表を所有している場合:EMPLOYEES(EMPL_ID NUMBER PRIMARY KEY, NAME VARCHAR2(200), ADDRESS JSON)EMPLOYEES表には次の行が含まれています:(1,'Jones Smith','{"STREET":"Oracle Parkway","CITY":"Redwood City"}'){"SCOTT":"EMPLOYEES":
{
"EMPL_ID":1,
"NAME":"Jones Smith",
"ADDRESS":{
"STREET":"Oracle Parkway",
"CITY":"Redwood City"
}
}
}INDEX_NAME表のMETADATA列には、その行の対応するJSONエントリが次のように格納されます:{"OWNER":"SCOTT","SOURCE":"EMPLOYEES","KEY":{"ID": 1}}ここで、OWNERは表の所有者、SOURCEは表名、KEYは主キーを示します。
関連項目
13.2 例: ユビキタス検索索引を使用した複数のオブジェクトの検索
この例では、単純なユビキタス・データベース検索シナリオに関連するステップを示します。
SCOTTとして、ユーザーがPRODUCTS表とCUSTOMERS表にわたりドキュメントを問い合せることができる、テキスト問合せアプリケーションを作成するとします。複数の索引を作成するかUSER_DATASTOREおよびMULTI_COLUMN_DATASTOREプロシージャをマテリアライズド・ビューとともに手動で定義するのではなく、DBMS_SEARCH PL/SQLパッケージを使用して、両方の表のドキュメントをユビキタス検索索引に自動的に合成できます。その後、同じ索引を使用して、PRODUCTS表とCUSTOMERS表にわたり全文および範囲ベースの検索を実行できます。
これを行うには次のステップが必要になります。
-
PRODUCTS表およびCUSTOMERS表を作成します。CREATE TABLE PRODUCTS( id NUMBER primary key, price NUMBER, description VARCHAR2(2000), in_stock BOOLEAN, long_description CLOB); CREATE TABLE CUSTOMERS( id NUMBER primary key, firstname VARCHAR2(200), lastname VARCHAR2(200), middlename VARCHAR2(200), address JSON, added DATE); -
表にデータを入力します。
INSERT INTO PRODUCTS values (1, 10, 'simple widget', FALSE, 'a low-cost, high-quality widget'); INSERT INTO PRODUCTS values (2, 2000, 'shiny thing', TRUE, 'a useful product, but very expensive'); INSERT INTO CUSTOMERS values (1, 'Robert', 'Smith', 'Englebert Toddwangle', '{ "street": "1230 East 32nd Street", "city": "Frogmortem", "state": "CA", "country": "USA", "zip": 12352}', SYSDATE); INSERT INTO CUSTOMERS values (99, 'John', 'Doe', NULL, '{ "street": "123 High Street", "city": "Richmond", "county": "Greater London", "country": "United Kingdom", "postcode": "AB12 6DU"}', SYSDATE); -
DBMS_SEARCH.CREATE_INDEXプロシージャを使用して、MYINDEXという名前のユビキタス検索索引を作成します。exec DBMS_SEARCH.CREATE_INDEX('MYINDEX'); -
DBMS_SEARCH.ADD_SOURCEプロシージャを使用して、PRODUCTS表とCUSTOMERS表をデータ・ソースとして索引に追加します。exec DBMS_SEARCH.ADD_SOURCE('MYINDEX','PRODUCTS'); exec DBMS_SEARCH.ADD_SOURCE('MYINDEX','CUSTOMERS');このプロシージャでは、
PRODUCTS表とCUSTOMERS表のすべての列の内容が、MYINDEXという名前(これは索引名と一致します)の単一の索引表に結合されます。MYINDEX表のMETADATA列には、索引付けされた行ごとに、次の形式のJSON表現が格納されます。METADATA -------------------------------------------------------------------------------- {"OWNER":"SCOTT","SOURCE":"PRODUCTS","KEY":{"ID":1}} {"OWNER":"SCOTT","SOURCE":"PRODUCTS","KEY":{"ID":2}} {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":1}} {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":99}} -
DBMS_SEARCH.GET_DOCUMENTプロシージャを使用して、索引付けされた仮想ドキュメントを表示します:SELECT DBMS_SEARCH.GET_DOCUMENT('MYINDEX',METADATA) from MYINDEX;この出力では、PRODUCTS表とCUSTOMERS表の行ごとに、MYINDEX索引で索引付けされた、メタデータ値が結合されたJSONドキュメントが返されます:DBMS_SEARCH.GET_DOCUMENT('MYINDEX',METADATA) -------------------------------------------------------------------------------- { "SCOTT" : { "PRODUCTS" : { "ID" : 1, "PRICE" : 10, "DESCRIPTION" : "simple widget", "IN_STOCK" : false, "LONG_DESCRIPTION" : "a low-cost, high-quality widget" } } } { "SCOTT" : { "PRODUCTS" : { "ID" : 2, "PRICE" : 2000, "DESCRIPTION" : "shiny thing", "IN_STOCK" : true, "LONG_DESCRIPTION" : "a useful product, but very expensive" } } } { "SCOTT" : { "CUSTOMERS" : { "ID" : 1, "FIRSTNAME" : "Robert", "LASTNAME" : "Smith", "MIDDLENAME" : "Englebert Toddwangle", "ADDRESS" : { "street" : "1230 East 32nd Street", "city" : "Frogmortem", "state" : "CA", "country" : "USA", "zip" : 12352 }, "ADDED" : "2023-08-08T15:29:39" } } } { "SCOTT" : { "CUSTOMERS" : { "ID" : 99, "FIRSTNAME" : "John", "LASTNAME" : "Doe", "MIDDLENAME" : null, "ADDRESS" : { "street" : "123 High Street", "city" : "Richmond", "county" : "Greater London", "country" : "United Kingdom", "postcode" : "AB12 6DU" }, "ADDED" : "2023-08-08T15:29:39" } } } -
次のように、結合索引に対して問合せを実行します。
CONTAINSの使用:
すべてのフィールドを検索するには、SELECT文で、WHERE句にCONTAINS演算子を使用して問合せを指定します。-
特定のソースの検索(パーティション・プルーニングを使用):
SELECT METADATA from MYINDEX WHERE CONTAINS(data,'shiny or street')>0 And OWNER = 'SCOTT' and SOURCE = 'PRODUCTS';出力:METADATA -------------------------------------------------------------------------------- {"OWNER":"SCOTT","SOURCE":"PRODUCTS","KEY":{"ID":2}} -
複数のソースにわたる検索:
SELECT METADATA from MYINDEX WHERE CONTAINS(data,'shiny or street')>0;出力:METADATA -------------------------------------------------------------------------------- {"OWNER":"SCOTT","SOURCE":"PRODUCTS","KEY":{"ID":2}} {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":1}} {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":99}} -
関連性スコアを取得するには、
SCORE演算子をCONTAINSとともに使用します:SELECT METADATA, score(1) as search_score from MYINDEX WHERE CONTAINS(data,'shiny or street',1)>0;出力:METADATA -------------------------------------------------------------------------------- SEARCH_SCORE ------------ {"OWNER":"SCOTT","SOURCE":"PRODUCTS","KEY":{"ID":2}} 4 {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":1}} 3 {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":99}} 3
JSON_TEXTCONTAINSの使用:
JSONデータを検索するには、SELECT文で、WHERE句にJSON_TEXTCONTAINS演算子を使用して問合せを指定します。JSONパス式を指定して、指定したテキストを検索するフィールドを指定します。-
特定のソースを検索:
SELECT METADATA from MYINDEX WHERE JSON_TEXTCONTAINS( data,'$.SCOTT.CUSTOMERS.ADDRESS.county','london');出力:METADATA -------------------------------------------------------------------------------- {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":99}} -
JSONドット表記法を使用して、個々の主キー値をフェッチします:
SELECT p.METADATA.KEY from MYINDEX p WHERE JSON_TEXTCONTAINS( p.data,'$.SCOTT.CUSTOMERS.ADDRESS.county','london');出力:KEY -------------------------------------------------------------------------------- {"ID":99} -
JOINを使用して複数の主キー値をフェッチします:この問合せは、equijoin条件に従ってCUSTOMERS表の行を結合します。SELECT c.firstname, c.lastname from MYINDEX p, CUSTOMERS c WHERE JSON_TEXTCONTAINS( p.data,'$.SCOTT.CUSTOMERS.ADDRESS.county','london') and c.id = p.METADATA.KEY.ID;出力:FIRSTNAME -------------------------------------------------------------------------------- LASTNAME -------------------------------------------------------------------------------- John Doe
JSON_EXISTSの使用:
JSONデータ内の特定の値を検索するには、SELECT文で、WHERE句にJSON_EXISTS演算子を使用して問合せを指定します。-
number型にJSON_EXISTSを使用:SELECT METADATA from MYINDEX Where json_exists(data,'$.SCOTT.PRODUCTS?(@.PRICE <= 100)');出力:METADATA -------------------------------------------------------------------------------- {"OWNER":"SCOTT","SOURCE":"PRODUCTS","KEY":{"ID":1}} -
string型にJSON_EXISTSを使用:SELECT METADATA from MYINDEX Where json_exists(data,'$.SCOTT.CUSTOMERS?(@.FIRSTNAME == "John")');出力:METADATA -------------------------------------------------------------------------------- {"OWNER":"SCOTT","SOURCE":"CUSTOMERS","KEY":{"ID":99}}
-