13.2.2 DBMS_SEARCH索引でのJSON二面性ビューの使用

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

データの問合せ中に、指定したSYNC設定に応じて、同期操作が完了するまで待機する必要がある場合があります。
  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. JSON二面性ビューを実装するための表を準備します。
    1. employees表およびdepartments表を作成します:
      CREATE TABLE employees
        ( employee_id    NUMBER(6) primary key, 
          first_name     varchar2(4000), 
          last_name      varchar2(4000), 
          department_id  NUMBER(4)
        );
      CREATE TABLE departments
        ( department_id    NUMBER(5) primary key, 
          department_name  VARCHAR2(30), 
          manager_id       NUMBER(6)
        );
    2. employees表に外部キー制約(emp_dept_fkey)が含まれるように変更します。

      ここでは、employees表のdepartment_id列が、departments表のdepartment_id列の外部キーを使用するように指定します: 

      ALTER TABLE employees
  ADD (
       CONSTRAINT  emp_dept_fkey
       FOREIGN KEY (department_id)
       REFERENCES departments
);
    3. employees表およびdepartments表を移入します:
      INSERT INTO departments VALUES(10, 'Administration', 100);
INSERT INTO employees VALUES(100, 'Robert', 'Smith', 10);
INSERT INTO employees VALUES(101, 'James', 'Martin' ,10);
INSERT INTO employees VALUES(102, 'John', 'Doe', 10);

commit;
  3. MY_SEARCH_INDEXという名前のDBMS_SEARCH索引を作成します。
    exec dbms_search.create_index('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)
  4. 作成した表に対してJSON二面性ビューを定義し、そのビューをMY_SEARCH_INDEXに追加します。
    1. MY_EMP_VIEWという名前の従業員中心のJSON二面性ビューを作成します。
      CREATE or replace JSON relational duality VIEW MY_EMP_VIEW
      AS
    select JSON {
    'EMPLOYEE_ID' is emp.EMPLOYEE_ID,
    'FIRST_NAME'  is emp.FIRST_NAME,
    'LAST_NAME' is emp.last_name,
    'department_info' is
    (
      select JSON
     {
     'DEPARTMENT_ID' is dept.department_id ,
     'departmentname' is dept.department_name WITH(UPDATE)
     }
     from departments dept WITH(UPDATE,CHECK ETAG)
     where dept.department_id = emp.department_id
   )
}
from employees emp WITH(INSERT,UPDATE,DELETE);
    2. 二面性ビュー(MY_EMP_VIEW)をデータ・ソースとしてMY_SEARCH_INDEXに追加します:
      exec dbms_search.add_source('MY_SEARCH_INDEX','MY_EMP_VIEW');
  5. MY_SEARCH_INDEXで索引付けされている内容を確認します:
    1. METADATA列を問い合せて、表の抽出元のソース情報を確認します。
      select JSON_SERIALIZE(METADATA FORMAT JSON) META from MY_SEARCH_INDEX
order by owner,source,key;

      索引表のMETADATA列には、索引付けされた行ごとに、次の形式のJSON表現が格納されます: 

      META
--------------------------------------------------------------------------------
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":100}}
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":101}}
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":102}}
    2. DATA列で索引付けされているメタデータ値を調べます。
      select data from MY_EMP_VIEW
order by 1;
      DATA
--------------------------------------------------------------------------------
{"_metadata":{"etag":"77AACDD6860BE5D14FCEB2A8633336D7","asof":"0000000000000000
{"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA","asof":"0000000000000000
{"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638","asof":"0000000000000000
    3. 索引付けされた仮想ドキュメントを表示して、索引付けされている実際のフィールドを確認します。
      select JSON_SERIALIZE(
  DBMS_SEARCH.GET_DOCUMENT('MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC 
from MY_SEARCH_INDEX
order by owner,source,key;

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

      DOC
-----------------------------------------------------------------------------------------
{"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"77AACDD6860BE5D14FCEB2A8633336D7",
"asof":"0000000000000000"},"EMPLOYEE_ID":100,"FIRST_NAME":"Robert","LAST_NAME":"Smith",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}}}}

{"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA",
"asof":"0000000000000000"},"EMPLOYEE_ID":101,"FIRST_NAME":"James","LAST_NAME":"Martin",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}}}}

{"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638",
"asof":"0000000000000000"},"EMPLOYEE_ID":102,"FIRST_NAME":"John","LAST_NAME":"Doe",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}}}}
  6. JSON_EXISTS演算子を使用して二面性ビューを問い合せます。

    • この文は、MY_SEARCH_INDEX表からEMPLOYEE_IDを取得する実行計画を生成し、FIRST_NAMEがRobertでLAST_NAMEがSmithであるエントリのJSONデータをフィルタ処理します: 

      explain plan for 
select 
t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
from MY_SEARCH_INDEX t
where 
json_exists(data,
  '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "Robert" && @.LAST_NAME == "Smith")');

SELECT PLAN_TABLE_OUTPUT FROM 
  TABLE(DBMS_XPLAN.DISPLAY(NULL,NULL,'BASIC PREDICATE PROJECTION'));

      出力は次のようになります。

      PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1710711056

--------------------------------------------------------------
| Id  | Operation                          | Name            |
--------------------------------------------------------------
|   0 | SELECT STATEMENT                   |                 |
|   1 |  PARTITION LIST ALL                |                 |
|*  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| MY_SEARCH_INDEX |
|*  3 |    DOMAIN INDEX                    | MY_SEARCH_INDEX |
--------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter(JSON_EXISTS2("DBMS_SEARCH"."GET_DOCUMENT"('"DOCUSER"."MY_SEARCH_INDEX"',
              "T"."METADATA" /*+ LOB_BY_VALUE */ ) FORMAT OSON,
              '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "Robert" && @.LAST_NAME ==
              "Smith")' /* json_path_str  $.DOCUSER.MY_EMP_VIEW.DATA?((@.FIRST_NAME.string() 
              == "Robert") && (@.LAST_NAME.string() == "Smith"))  */  FALSE ON ERROR TYPE(LAX) )=1)

   3 - access("CTXSYS"."CONTAINS"("T"."DATA" /*+ LOB_BY_VALUE */
              ,'(sdata(FVCH_DFE32BED91ED02414AB59BAEC23126D1_FIRST_NAME  = "Robert" )
              and sdata(FVCH_9A8FA0A86CCA96ADF45C533C7C92EFF7_LAST_NAME  = "Smith"
              ))')>0)

Column Projection Information (identified by operation id):
-----------------------------------------------------------

   1 - "T"."METADATA" /*+ LOB_BY_VALUE */ [JSON,8200]
   2 - "T"."METADATA" /*+ LOB_BY_VALUE */ [JSON,8200]
   3 - "T".ROWID[ROWID,10]

31 rows selected.

    • この文は、MY_SEARCH_INDEX表からEMPLOYEE_IDフィールドを検索して、MY_EMP_VIEWに定義された主キーを取得します。データ列のJSONデータには、FIRST_NAMEとしてRobert、LAST_NAMEとしてSmithが含まれます。 

      select 
t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
from MY_SEARCH_INDEX t
where 
json_exists(data,
  '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "Robert" && @.LAST_NAME == "Smith")');

    出力では、EMPLOYEE_IDが100の行が返されます: 

     EMPLOYEE_ID
-----------
 100
  7. 二面性ビューの一部のフィールドを直接更新し、索引を再度問い合せて変更を分析します。
    1. 従業員IDが100の従業員の新しい名前を追加します。
      update my_emp_view 
set data = 
  '{"EMPLOYEE_ID":100,"FIRST_NAME":"new_name",
    "LAST_NAME":"new_lastname",
    "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}'
  where json_value(data,'$.EMPLOYEE_ID') = 100;

commit;
    2. 索引のDATA列を問い合せる元の実表から抽出されたすべての内容を表示します。
      select data from my_emp_view order by 1;

      DATA列では、この索引にデータ・ソースとして追加されるMY_EMP_VIEWの索引付き行ごとに、次の形式のJSON表現が作成されます: 

      DATA
--------------------------------------------------------------------------------------
{"_metadata":{"etag":"B7CAD96918892950C1FBA12E58AC198E","asof":"0000000000000000"},
"EMPLOYEE_ID":100,"FIRST_NAME":"new_name","LAST_NAME":"new_lastname","department_info":
{"DEPARTMENT_ID":10,"departmentname":"Administration"}}

{"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA","asof":"0000000000000000"},
"EMPLOYEE_ID":101,"FIRST_NAME":"James","LAST_NAME":"Martin","department_info":
{"DEPARTMENT_ID":10,"departmentname":"Administration"}}

{"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638","asof":"0000000000000000"},
"EMPLOYEE_ID":102,"FIRST_NAME":"John","LAST_NAME":"Doe","department_info":
{"DEPARTMENT_ID":10,"departmentname":"Administration"}}

{"_metadata":{"etag":"3F8A0577A0E8F8AAF38B088D1CD3EAE6","asof":"0000000000000000"},
"EMPLOYEE_ID":103,"FIRST_NAME":"new_name2","LAST_NAME":"new_lastname2","department_info":
{"DEPARTMENT_ID":10,"departmentname":"Administration"}}


4 rows selected.
    3. 新しい名前を更新した従業員を問い合せます:
      select 
t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
from MY_SEARCH_INDEX t
where json_exists(data,
  '$.DOCUSER.MY_EMP_VIEW.DATA?(@.FIRST_NAME == "new_name" && @.LAST_NAME == "new_lastname")');

      出力では、EMPLOYEE_IDが100の行が返されます: 

       EMPLOYEE_ID
-----------
 100

1 row selected.
    4. 新しい従業員をAdministration部門に追加します。
      insert into my_emp_view values('
  {"EMPLOYEE_ID":103,"FIRST_NAME":"new_name2",
   "LAST_NAME":"new_lastname2",
   "department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}');
    5. HRという名前の新しい部門を追加します。
      INSERT INTO departments VALUES
  ( 20, 'HR', 103);
    6. 新しい従業員をHR部門に追加します。
      insert into  my_emp_view values('
  {"EMPLOYEE_ID":104,"FIRST_NAME":"new_name3",
   "LAST_NAME":"new_lastname3",
   "department_info":{"DEPARTMENT_ID":20,"departmentname":"HR"}}');

commit;
    7. 索引のDATA列を再度問い合せて、更新された索引付きデータを比較します。
      select data from my_emp_view 
  order by 1; 
      DATA
--------------------------------------------------------------------------------
{"_metadata":{"etag":"B7CAD96918892950C1FBA12E58AC198E","asof":"0000000000000000
"},"EMPLOYEE_ID":100,"FIRST_NAME":"new_name","LAST_NAME":"new_lastname",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}

{"_metadata":{"etag":"4B233BD8A51C3B905A357F8446FBABDA","asof":"0000000000000000
"},"EMPLOYEE_ID":101,"FIRST_NAME":"James","LAST_NAME":"Martin",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}

{"_metadata":{"etag":"5669EAC90AB697DA6C13D2045D6C6638","asof":"0000000000000000
"},"EMPLOYEE_ID":102,"FIRST_NAME":"John","LAST_NAME":"Doe",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}

{"_metadata":{"etag":"3F8A0577A0E8F8AAF38B088D1CD3EAE6","asof":"0000000000000000
"},"EMPLOYEE_ID":103,"FIRST_NAME":"new_name2","LAST_NAME":"new_lastname2",
"department_info":{"DEPARTMENT_ID":10,"departmentname":"Administration"}}


4 rows selected.

      更新した索引のMETADATA列を表示します。 

      select JSON_SERIALIZE(METADATA FORMAT JSON) META from MY_SEARCH_INDEX 
  order by owner,source,key;

      METADATA列には、索引付けされた行ごとに、次の形式のJSON表現が格納されます: 

      META
--------------------------------------------------------------------------------
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":100}}
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":101}}
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":102}}
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":103}}
{"OWNER":"DOCUSER","SOURCE":"MY_EMP_VIEW","KEY":{"EMPLOYEE_ID":104}}
      JSONドキュメントを戻す仮想ドキュメントと、各行が索引付けされ結合されたメタデータ値を比較します。
      select JSON_SERIALIZE(
  DBMS_SEARCH.GET_DOCUMENT('MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC
from MY_SEARCH_INDEX
order by owner,source,key;
  8. JSON_TEXT_CONTAINS演算子を使用して、索引に対して問合せを実行します。
    1. 名前に"new name"が含まれる最近更新されたすべての従業員を検索します:
      select t.metadata.KEY."EMPLOYEE_ID".number() as employee_id  
from MY_SEARCH_INDEX t
where json_textcontains(data,'$.DOCUSER.MY_EMP_VIEW.DATA.FIRST_NAME','new name%')
order by employee_id;

      出力では、EMPLOYEE_IDが100、103および104の3行が返されます: 

       EMPLOYEE_ID
-----------
  100
  103
  104
    2. HR部門から従業員を検索します:
      select t.metadata.KEY."EMPLOYEE_ID".number() as employee_id 
from MY_SEARCH_INDEX t
where json_exists(data,'$.DOCUSER.MY_EMP_VIEW.DATA?(@.department_info.departmentname == "HR")');

      出力では、EMPLOYEE_IDが104の行が返されます: 

       EMPLOYEE_ID
-----------
  104
  9. CONTAINS演算子を使用して、スキーマ全体の索引に対して実行します。
    select JSON_SERIALIZE(           
  DBMS_SEARCH.GET_DOCUMENT('MY_SEARCH_INDEX', METADATA) FORMAT JSON) DOC 
from MY_SEARCH_INDEX
where contains(data,'Robert or HR')>0
order by owner,source,key;

    問合せでは、メタデータ値を含む索引付けされた仮想ドキュメントをMY_SEARCH_INDEXから取得します。DATA列には、キーワードRobertおよびHRが含まれます: 

    DOC
--------------------------------------------------------------------------------------------------
{"DOCUSER":{"MY_EMP_VIEW":{"DATA":{"_metadata":{"etag":"7CB51D7BF53FD85174B3A1FC72EEECDB",
"asof":"0000000000000000"},"EMPLOYEE_ID":104,"FIRST_NAME":"new_name3","LAST_NAME":"new_lastname3",
"department_info":{"DEPARTMENT_ID":20,"departmentname":"HR"}}}}}

関連トピック