13.2.1 複数の表およびビューを使用したDBMS_SEARCH索引の作成および問合せ

この例では、ユビキタス検索索引の作成方法、複数の表およびビューの追加方法、CONTAINS、JSON_TEXTCONTAINSおよびJSON_EXISTS演算子を使用した索引に対する問合せ方法を確認できます。

  1. ローカル・ユーザーとしてOracle Databaseに接続します。
    1. SQL*PlusにSYSユーザーとしてログインし、SYSDBAとして接続します。
      conn sys/password as sysdba
      CREATE TABLESPACE tbs1
DATAFILE 'tbs5.dbf' SIZE 20G AUTOEXTEND ON
EXTENT MANAGEMENT LOCAL
SEGMENT SPACE MANAGEMENT AUTO;
      SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 10000
SET LONG 10000
    2. ローカル・ユーザー(docuser)を作成し、必要な権限を付与します。
      DROP USER docuser cascade;
      GRANT DB_DEVELOPER_ROLE, DEFAULT TABLESPACE tbs1 quota unlimited on tbs1
TO docuser IDENTIFIED BY password;
    3. ローカル・ユーザー(docuser)として接続します:
      CONN docuser/password
  2. customers表、items表、orders表およびlineitems表を作成して移入します。これらの表は、後でユビキタス検索索引に追加します。
    1. customers:

      ここでは、スキーマ所有者名DOCUSERが明示的に指定されています。 

      CREATE TABLE docuser.customers (
    cust_id number PRIMARY KEY,
    first varchar2(30),
    last varchar2(30));
      insert into customers values (1, 'Robert', 'Smith');
insert into customers values (2, 'John', 'Doe');
insert into customers values (3, 'James', 'Martin');
insert into customers values (5, 'Jane', 'Y');
    2. items:
      CREATE TABLE items (
    item_id number PRIMARY KEY,
    name    varchar2(30),
    price   number(5,2),
    stock_quantity number);
      insert into items values (122, 'Potato Gun', 29.99, 10);
insert into items values (232, 'Rubber Christmas Tree', 65.00, 0);
insert into items values (345, 'Border Patrol Costume', 19.99, 20);
insert into items values (845, 'Meteor Impact Survival Kit', 299.00, 0);
insert into items values (429, 'Air Guitar', 9.99, 14);
    3. orders:
      CREATE TABLE orders (
    order_id number PRIMARY KEY,
    cust_id number REFERENCES customers(cust_id) ON DELETE CASCADE);
      insert into orders values (1, 1);
insert into orders values (2, 1);
insert into orders values (3, 3);
insert into orders values (4, 2);
    4. lineitems:
      CREATE TABLE lineitems (
    order_id number REFERENCES orders(order_id) ON DELETE CASCADE,
    item_id  number REFERENCES items(item_id) ON DELETE CASCADE,
    quantity number,
    PRIMARY KEY(order_id, item_id));
      insert into lineitems values(1, 845, 1);
insert into lineitems values(2, 232, 1);
insert into lineitems values(2, 429, 4);
insert into lineitems values(3, 122, 1);
insert into lineitems values(4, 345, 1);
  3. 作成した表に基づいて、search_viewという名前のビューを作成します。このビューは、後でユビキタス検索索引に追加します。
    CREATE OR REPLACE VIEW search_view(cust_id, business_object,
  CONSTRAINT search_view_pk
    PRIMARY KEY(cust_id)
      RELY DISABLE NOVALIDATE,
  CONSTRAINT search_view_fk
    FOREIGN KEY(cust_id) REFERENCES customers(cust_id)
      DISABLE NOVALIDATE) AS
SELECT c.cust_id, JSON_OBJECT(
          'id' VALUE c.cust_id,
          'name' VALUE (c.first || ' ' || c.last),
          'num_orders' VALUE (
               SELECT COUNT(*)
               FROM orders o
               WHERE o.cust_id = c.cust_id),
          'orders' VALUE (
               SELECT JSON_ARRAYAGG(
                    JSON_OBJECT(
                         'order_id' VALUE o.order_id,
                         'items' VALUE (
                              SELECT JSON_ARRAYAGG (
                                   JSON_OBJECT(
                                        'id' VALUE l.item_id,
                                        'name' VALUE i.name,
                                        'quantity' VALUE l.quantity,
                                        'single_item_price' VALUE i.price,
                                        'total_price' VALUE (i.price * l.quantity)))
                               FROM lineitems l, items i
                               WHERE l.order_id = o.order_id
                               AND i.item_id = l.item_id)))
          FROM orders o
          WHERE o.cust_id = c.cust_id) ABSENT ON NULL) business_object
FROM customers c;
  4. MY_SEARCH_INDEXという名前のユビキタス検索索引を作成します。
    EXEC DBMS_SEARCH.CREATE_INDEX('DOCUSER.MY_SEARCH_INDEX', NULL, 'JSON');

    次のように、tablespaceおよびdatatype (デフォルトはJSON)を省略できます: 

    exec dbms_search.create_index('MY_SEARCH_INDEX');

    次のコマンドを実行して、DOCUSERスキーマに作成される索引の構造を決定します: 

    DESC MY_SEARCH_INDEX;
     Name             Null?      Type
 ---------------- -------- ----------------------------
 METADATA         NOT NULL   JSON
 DATA                        JSON
 OWNER                       VARCHAR2(128)
 SOURCE                      VARCHAR2(128)
 KEY                         VARCHAR2(1024)
  5. CUSTOMERS表をデータ・ソースとしてMY_SEARCH_INDEXに追加します。
    EXEC DBMS_SEARCH.ADD_SOURCE('DOCUSER.MY_SEARCH_INDEX', 'DOCUSER.CUSTOMERS');

    他のすべての表ソースをこの索引に追加できますが、このシナリオ例を完了する必要はありません。

  6. 次のステップに示すように、索引のDATA列およびMETADATA列をDBMS_SEARCHディクショナリ・ビューとともに確認します:
    1. METADATA列を問い合せます:
      SELECT JSON_SERIALIZE(METADATA FORMAT JSON) META
FROM DOCUSER.MY_SEARCH_INDEX
ORDER BY META;

      METADATA列は、DBMS_SEARCH索引で、索引付けされた表またはビューの各行を一意に識別するために役立ちます。METADATA列には、customers表の索引付き行ごとに、次の形式のJSON表現が格納されていることを確認できます: 

      META
--------------------------------------------------------------------------------
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":1}}
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":2}}
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":3}}
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":5}}

4 rows selected.
    2. DATA列を問い合せます:
      SELECT DATA
FROM DOCUSER.MY_SEARCH_INDEX;

      DATA列に実際のデータは格納されないことに注意してください。かわりに、データは元の実表に存在します。この索引は、データ・ソース表を参照して、索引付けされた仮想JSONドキュメントをその場で作成します。データがフェッチおよび索引付けされた後、この列は事実上空になり、重複が回避されます。 

      DATA
--------------------------------------------------------------------------------


4 rows selected.
    3. 索引付けされた仮想ドキュメントを取得し、customers表ソースから抽出された内容を確認します。
      SELECT 
JSON_SERIALIZE(
  DBMS_SEARCH.GET_DOCUMENT('DOCUSER.MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC
FROM DOCUSER.MY_SEARCH_INDEX
ORDER BY JSON_SERIALIZE(METADATA FORMAT JSON);

      このドキュメントには、索引にデータ・ソースとして追加されるcustomers表の索引付き行ごとにJSON表現が含まれています: 

      DOC
--------------------------------------------------------------------------------
{"DOCUSER":{"CUSTOMERS":{"CUST_ID":1,"FIRST":"Robert","LAST":"Smith"}}}
{"DOCUSER":{"CUSTOMERS":{"CUST_ID":2,"FIRST":"John","LAST":"Doe"}}}
{"DOCUSER":{"CUSTOMERS":{"CUST_ID":3,"FIRST":"James","LAST":"Martin"}}}
{"DOCUSER":{"CUSTOMERS":{"CUST_ID":5,"FIRST":"Jane","LAST":"Y"}}}

4 rows selected.
    4. USER_DBMS_SEARCH_INDEXESビューを問い合せて、索引のメタデータ値を表示します。
      SELECT IDX_NAME FROM USER_DBMS_SEARCH_INDEXES ORDER BY IDX_NAME;

      このビューには、ユーザー・スキーマDOCUSERに追加された索引名が表示されます: 

      IDX_NAME
--------------------------------------------------
MY_SEARCH_INDEX

1 row selected.
    5. USER_DBMS_SEARCH_INDEX_SOURCESビューを問い合せて、データ・ソースのメタデータ値を表示します。
      SELECT IDX_NAME,SRC_OWNER,SRC_NAME,SRC_TYPE         
FROM USER_DBMS_SEARCH_INDEX_SOURCES
ORDER BY IDX_NAME, SRC_OWNER,SRC_NAME;

      このビューには、ユーザー・スキーマ(DOCUSER)から、索引に関連付けられているデータ・ソースの詳細が表示されます。ここで、ソース・タイプTは「表」ソースを意味します: 

      IDX_NAME
------------------------------------------------
SRC_OWNER
------------------------------------------------
SRC_NAME
------------------------------------------------
SRC_TYPE
-
MY_SEARCH_INDEX
DOCUSER
CUSTOMERS
T

1 row selected.
  7. 索引にビューを追加し、METADATA列およびディクショナリ・ビューを再度調べて、変更が索引付きデータにどのように反映されるかを比較します。
    1. 作成したビュー(SEARCH_VIEW)をデータ・ソースとして索引に追加します:
      EXEC DBMS_SEARCH.ADD_SOURCE('DOCUSER.MY_SEARCH_INDEX', 'DOCUSER.SEARCH_VIEW');
    2. METADATA列を問い合せます:
      SELECT JSON_SERIALIZE(METADATA FORMAT JSON) META
FROM DOCUSER.MY_SEARCH_INDEX
ORDER BY META;

      METADATA列には、索引付けされているビューの各行が追加で表示されます: 

      META
--------------------------------------------------------------------------------
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":1}}
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":2}}
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":3}}
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":5}}
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":1}}
{"OWNER":"CUSTOMER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":2}}
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":3}}
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":5}}

8 rows selected.
    3. USER_DBMS_SEARCH_INDEXESディクショナリ・ビューを問い合せます。
      SELECT IDX_NAME FROM USER_DBMS_SEARCH_INDEXES 
  ORDER BY IDX_NAME;

      このビューには、ユーザー・スキーマDOCUSERに存在する索引名が表示されます: 

      IDX_NAME
--------------------------------------------------------------------------------
MY_SEARCH_INDEX

1 row selected.
    4. USER_DBMS_SEARCH_INDEX_SOURCESディクショナリ・ビューを問い合せます。
      SELECT IDX_NAME,SRC_OWNER,SRC_NAME,SRC_TYPE
FROM USER_DBMS_SEARCH_INDEX_SOURCES
ORDER BY IDX_NAME, SRC_OWNER,SRC_NAME;

      出力には、データ・ソースとして索引に追加されたSEARCH_VIEWの追加行が表示されます。ここで、ソース・タイプTとVは、それぞれ「表」ソースと「ビュー」ソースを意味します: 

      IDX_NAME
----------------------------------------------------
SRC_OWNER
----------------------------------------------------
SRC_NAME
----------------------------------------------------
SRC_TYPE
-
MY_SEARCH_INDEX
DOCUSER
CUSTOMERS
T

MY_SEARCH_INDEX
DOCUSER
SEARCH_VIEW
V

2 rows selected.
  8. JSON_EXISTS演算子を使用して、索引に対して問合せを実行します。
    1. DATA列にJSON要素$.DOCUSER.SEARCH_VIEWが含まれる索引のビュー・ソースで、ドキュメントを検索します。
      SELECT JSON_SERIALIZE(METADATA FORMAT JSON) META
FROM DOCUSER.MY_SEARCH_INDEX
WHERE JSON_EXISTS(DATA,'$.DOCUSER.SEARCH_VIEW')
ORDER BY META;

      出力では、DOCUSERスキーマから4行が返され、顧客IDが1、2、3および5として結合されます: 

      META
--------------------------------------------------------------------------------
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":1}}
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":2}}
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":3}}
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":5}}

4 rows selected.
    2. DATA列にJSON要素$.DOCUSER.SEARCH_VIEWが含まれる索引のビュー・ソースで、仮想ドキュメントとしてドキュメントを検索します:

      これは、前のステップに示したのと同じ問合せです。ただし、ここでは、すべてのメタデータ値のJSON表現を使用して、索引付けされた仮想ドキュメント全体を表示できます:

      SELECT 
JSON_SERIALIZE(
  DBMS_SEARCH.GET_DOCUMENT('DOCUSER.MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC
FROM DOCUSER.MY_SEARCH_INDEX
WHERE JSON_EXISTS(DATA,'$.DOCUSER.SEARCH_VIEW')
ORDER BY JSON_SERIALIZE(METADATA FORMAT JSON);

      出力では、MY_SEARCH_INDEXで索引付けされたメタデータ値が結合されてJSONドキュメントが返されます: 

      DOC
--------------------------------------------------------------------------------
{"DOCUSER":{"SEARCH_VIEW":{"CUST_ID":1,"BUSINESS_OBJECT":{"id":1,"name":"Robert 
Smith","num_orders":2,"orders":[{"order_id":1,"items":[{"id":845,"name":"Meteor
 Impact Survival Kit","quantity":1,"single_item_price":299,"total_price":299}]},
{"order_id":2,"items":[{"id":232,"name":"Rubber Christmas Tree","quantity":1,
"single_item_price":65,"total_price":65},{"id":429,"name":"Air Guitar","quantity":4
,"single_item_price":9.99,"total_price":39.96}]}]}}}}

{"DOCUSER":{"SEARCH_VIEW":{"CUST_ID":2,"BUSINESS_OBJECT":{"id":2,"name":"John
Doe","num_orders":1,"orders":[{"order_id":4,"items":[{"id":345,"name":"Border
Patrol Costume","quantity":1,"single_item_price":19.99,"total_price":19.99}]
}]}}}}

{"DOCUSER":{"SEARCH_VIEW":{"CUST_ID":3,"BUSINESS_OBJECT":{"id":3,"name":"James 
Martin","num_orders":1,"orders":[{"order_id":3,"items":[{"id":122,"name":"Potato
Gun","quantity":1,"single_item_price":29.99,"total_price":29.99}]}]}}}}

{"DOCUSER":{"SEARCH_VIEW":{"CUST_ID":5,"BUSINESS_OBJECT":{"id":5,"name":"Jane Y",
"num_orders":0}}}}

4 rows selected.
  9. JSON_TEXTCONTAINS演算子を使用して、ターゲット・パスに対してテキスト検索問合せを実行します。
    1. キーワード"Anon or Jane"について、DATA列の$.DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.name JSONパスを問い合せます。
      SELECT 
JSON_SERIALIZE(
  DBMS_SEARCH.GET_DOCUMENT('DOCUSER.MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC
FROM DOCUSER.MY_SEARCH_INDEX
WHERE
JSON_TEXTCONTAINS(DATA,'$.DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.name',
                       'Anon or Jane')
ORDER BY JSON_SERIALIZE(METADATA FORMAT JSON);

      出力では、DOCUSERスキーマのSEARCH_VIEWソースから、顧客IDが5で名前がJane YのJSONドキュメントが返されます: 

      DOC
---------------------------------------------------------------------------------
{"DOCUSER":{"SEARCH_VIEW":{"CUST_ID":5,"BUSINESS_OBJECT":{"id":5,"name":"Jane Y",
"num_orders":0}}}}

1 row selected.
    2. SCORE演算子をJSON_TEXTCONTAINSとともに使用して、検索結果の関連性スコアを取得します。
      SELECT METADATA, score(1) from DOCUSER.MY_SEARCH_INDEX
    WHERE JSON_TEXTCONTAINS(
        DATA,'$.DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.name','Anon or Jane',1);

      出力では、顧客ID 5、Jane Yの一致レコードについて、メタデータ値と関連性スコア5が返されます。 

       METADATA
--------------------------------------------------------------------------------
  SCORE(1)
----------
{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":5}}
         5

1 row selected.
  10. CONTAINS演算子を使用してスキーマ全体を検索します。
    1. 索引を問い合せて、キーワード"Anon or Jane"に一致するレコードを取得します。
      SELECT 
JSON_SERIALIZE(
  DBMS_SEARCH.GET_DOCUMENT('DOCUSER.MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC
FROM DOCUSER.MY_SEARCH_INDEX
WHERE
CONTAINS(DATA, 'Anon or Jane') > 0
ORDER BY JSON_SERIALIZE(METADATA FORMAT JSON);

      出力では、DOCUSERスキーマから2つのJSONオブジェクトが返されます。1つは、CUSTOMERS表ソースからの、顧客IDが5、名前がJane Yのオブジェクトです。もう1つのオブジェクトも、顧客IDが5、名前がJane Yですが、SEARCH_VIEWビュー・ソースから返されます。

      ここで、ビジネス・オブジェクトに関連付けられたオーダーがゼロであることに注意してください。

      DOC
--------------------------------------------------------------------------------
{"DOCUSER":{"CUSTOMERS":{"CUST_ID":5,"FIRST":"Jane","LAST":"Y"}}}
{"DOCUSER":{"SEARCH_VIEW":{"CUST_ID":5,"BUSINESS_OBJECT":{"id":5,"name":"Jane Y",
 "num_orders":0}}}}

2 rows selected.
    2. SCORE演算子をCONTAINSとともに使用して、検索結果の関連性スコアを取得します。
      SELECT METADATA, score(1) as search_score 
    from DOCUSER.MY_SEARCH_INDEX  
    WHERE CONTAINS(DATA, 'Anon or Jane',1)>0;

      ここで、出力では、表ソースとビュー・ソースの両方から、顧客ID 5、Jane Yの一致レコードが返されます。また、両方のレコードの検索スコアが5と表示されます。 

      METADATA
--------------------------------------------------------------------------------
SEARCH_SCORE
------------
{"OWNER":"DOCUSER","SOURCE":"CUSTOMERS","KEY":{"CUST_ID":5}}
           5

{"OWNER":"DOCUSER","SOURCE":"SEARCH_VIEW","KEY":{"CUST_ID":5}}
           5

2 rows selected.
  11. DBMS_SEARCH.FINDプロシージャを使用して、ヒットリストを取得します。また、指定された、例による問合せ(QBE)フィルタ条件に基づいて、JSONドキュメントの集計もファセット化します。
    SELECT JSON_SERIALIZE(DBMS_SEARCH.FIND('DOCUSER.MY_SEARCH_INDEX',
JSON('{
  "$query" : { "DOCUSER.SEARCH_VIEW.*" : { "$contains" : "Gun or patrol costume" }  },
  "$facet" : [
    { "$sum" : "DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.total_price"},
    { "$count" : 
      { "path" : "DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.single_item_price",
        "bucket" : [ { "$lt" : 20  }, { "$gte" : 20 } ] } },
    { "$uniqueCount" : "DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.name"  }
  ]
}')) FORMAT JSON PRETTY) AGG FROM DUAL;

    出力には、JSON形式の集計結果が表示されます。

    ここで、問合せでは、DOCUSER.SEARCH_VIEW内でGun or patrol costumeというフレーズを検索します。$countは、問合せ基準に一致する2つのレコードを示します。$facetキーは、複数の集計結果を個別のバケットにグループ化し、次の集計を実行します:

    • DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.total_price:

      orders.items.total_priceフィールドのすべてのアイテムの合計価格を合計49.98に集計します。

    • DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.single_item_price:

      single_item_priceフィールドの個々のアイテムの価格を集計し、価格範囲(バケット)にグループ化します。

    • DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.name:

      BUSINESS_OBJECT.nameフィールドでの名前(John DoeおよびJames Martin)の一意の出現をカウントします。両方の名前が1回出現します。 

    AGG
--------------------------------------------------------------------------------
{
  "$count" : 2,
  "$facet" :
  [
    {
      "DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.total_price" :
      {
        "$sum" : 49.98
      }
    },
    {
      "DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.orders.items.single_item_price" :
      [
        {
          "bucket" :
          {
            "$gte" : 19.99,
            "$lt" : 20
          },
          "$count" : 1
        },
        {
          "bucket" :
          {
            "$gte" : 20,
            "$lte" : 29.99
          },
          "$count" : 1
        }
      ]
    },
    {
      "DOCUSER.SEARCH_VIEW.BUSINESS_OBJECT.name" :
      [
        {
          "value" : "John Doe",
          "$uniqueCount" : 1
        },
        {
          "value" : "James Martin",
          "$uniqueCount" : 1
        }
      ]
    }
  ]
}

1 row selected.

関連トピック