Oracle XML DB 19cの簡易検証

　ゴールデンウィーク後半は良い天気に恵まれ、行楽地からのUターンラッシュが激しい様子を見ると、コロナのことなど忘れてしまいそうである。今回は、以前から気になっていたXML-Typeを使って手元の環境で簡単な検証を行ってみた。恥ずかしながら今までOracleでXMLを使ったことがないので、具体的に何ができるのか実機でさわりの動作を確認したレベルである。

１．XML DBとは

　Oracle XML DBは、XMLデータの格納、生成、アクセス、検索、検証、変換、拡張および索引付けなどの高パフォーマンスの処理に関連する一連のOracleの技術を指す。XML DBを使うために、XMLをOracleに格納するが、その際に使うカラム型をXML-Typeという。今回はXML DBのチュートリアル（参考[4]のpurchase orderのモデル）を参考に、手元の環境（19.11）で簡単なXML-Type型を持つ表を作成し、検索、索引付与をして、実行計画を確認してみた。

２．検証準備

　検証準備として、表の作成、データの準備等を行う。

(1)表の作成

　表の作成はpurchaseorder表のpo_detailカラム型にXMLTYPEを指定し、STORE ASでBINARY XMLを指定した。ここでは深く考えずにチュートリアルに従うこととする。実際に作成された型を見るとSYS.XMLTYPE STORAGE BINARYとなっている。

CREATE TABLE purchaseorder (
po_number NUMBER,
po_details XMLTYPE
)
XMLTYPE COLUMN po_details STORE AS BINARY XML ;

SQL> desc purchaseorder
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 PO_NUMBER                                          NUMBER
 PO_DETAILS                                         SYS.XMLTYPE STORAGE BINARY

実はこの状態でuser_tab_colsで確認すると、内部的には隠しカラムとしてSYS_NC00003$のような名前のBLOBが作成されているため、おそらく実体としてはこのBLOBにXMLが格納され、表に出ているカラムはBLOBへのロケータなのかもしれない。

SQL> select table_name, column_name, data_type, hidden_column from user_tab_cols where table_name = 'PURCHASEORDER' order by column_id;

TABLE_NAME           COLUMN_NAME          DATA_TYPE            HID
-------------------- -------------------- -------------------- ---
PURCHASEORDER        PO_NUMBER            NUMBER               NO
PURCHASEORDER        SYS_NC00003$         BLOB                 YES
PURCHASEORDER        PO_DETAILS           XMLTYPE              NO

(2)データの準備

以下のPL/SQLを用いて初期データを作成する。po_detailsカラムはPurchaseOrderをルートとするXMLをPL/SQLで生成して格納する。データ件数は100件である。統計情報も取得しておく。なお、このPL/SQLは、参考[4]のチュートリアルの初期データ作成のサンプルを少し修正したものである。

BEGIN
    FOR i IN 1..100 LOOP
        INSERT INTO purchaseorder
        VALUES      (i,'<PurchaseOrder><Reference>ACABRIO-'
                     ||i
                     ||'PDT</Reference><Actions><Action><User>ACABRIO-'
                     ||i
                     ||
        '</User></Action></Actions><Rejection/><Requestor>A. Cabrio '
                     || i
                     ||'</Requestor><User>ACABRIO-'
                     ||i
                     ||'</User><CostCenter>A'
                     ||i
                     ||'</CostCenter><ShippingInstructions><name>A. Cabrio '
                     ||i
                     ||'</name><Address><street>'
                     ||i
                     ||' Sporting Green Centre, Science Club, building '
                     ||i
                     ||', Magdalen</street><city>SFO-'
                     ||i
                     ||
'</city><state>CA</state><zipCode>99236</zipCode><country>United States of America</country></Address><telephone>269-'
||i
||
'-4036</telephone></ShippingInstructions><SpecialInstructions>Priority Overnight</SpecialInstructions><LineItems><LineItem ItemNumber="1"><Part Description="Face to Face: First Seven Years" UnitPrice="19.95">'
||i
||'</Part><Quantity>'
||i
||
'</Quantity></LineItem><LineItem ItemNumber="2"><Part Description="Runaway" UnitPrice="27.95">'
||i
||'</Part><Quantity>'
||i
||
'</Quantity></LineItem><LineItem ItemNumber="3"><Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">'
||i
||'</Part><Quantity>'
||i
||'</Quantity></LineItem></LineItems></PurchaseOrder>');
END LOOP;
COMMIT;
END;
/  
-- gathering statistics
exec dbms_stats.gather_table_stats('SCOTT','PURCHASEORDER');

以上で基本的な準備は完了である。

３．XML問い合わせ動作検証

(1)検索～XMLドキュメント数確認

　XMLドキュメント数を確認するSQLはいくつかあるが、以下はその一つである。通常はselect count(*) from purchaseorderとするところだが、ここではXML-Typeに格納されたXMLのPurchaseOrderの構造の数をカウントしているようだ。実際にテストはしていないが、おそらく1カラムに複数のPurchaseOrderが含まれている場合はその分も計上するものと思われる。

SELECT Count(*)
FROM   purchaseorder p,
       XMLTABLE('for $r in /PurchaseOrder return $r' passing p.po_details) t;  

  COUNT(*)
----------
       100

　実行計画は以下の通りフルスキャンである。SQLの形から2回フルスキャンしてしまうのか？と思ったが、1回で大丈夫のようだ。良く見ると、Id:2のfilter条件は隠しカラムであるSYS_NC0003$に対して行われているので、XMLの実体はこちらに格納されているのは間違いなさそうである。何となく、BLOBカラムに格納されているXMLからSYS_MAKEXML関数でPurchaseOrderの構造を取り出し、EXISTNODE関数の結果が1となるカラムを条件としてフィルタしているように見える。

Execution Plan
----------------------------------------------------------
Plan hash value: 3002203368

------------------------------------------------------------------------------------
| Id  | Operation          | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |               |     1 |  2002 |     6   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE    |               |     1 |  2002 |            |          |
|*  2 |   TABLE ACCESS FULL| PURCHASEORDER |     1 |  2002 |     6   (0)| 00:00:01 |
------------------------------------------------------------------------------------

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

   2 - filter(EXISTSNODE(SYS_MAKEXML(0,"P"."SYS_NC00003$" /*+ LOB_BY_VALUE
              */ ),'/PurchaseOrder')=1)

(2)検索～XMLExistsによるフィルター操作

　ReferenceがACABRIO-1PDTを満たす行のXMLをXMLExistsで抽出する。Passing句でXMLTypeカラムをバインド変数として渡すことができる。

SELECT p.po_number,p.po_details "XML"
FROM purchaseorder p
WHERE XMLExists (
 '$PO/PurchaseOrder[Reference=$REF]'
 PASSING p.po_details as "PO", 'ACABRIO-1PDT' as "REF");

 PO_NUMBER XML
---------- ------------------------------------------------------------------------------------------
         1 <PurchaseOrder>
             <Reference>ACABRIO-1PDT</Reference>
             <Actions>
               <Action>
                 <User>ACABRIO-1</User>
               </Action>
             </Actions>
             <Rejection/>
             <Requestor>A. Cabrio 1</Requestor>
             <User>ACABRIO-1</User>
             <CostCenter>A1</CostCenter>
             <ShippingInstructions>
               <name>A. Cabrio 1</name>
               <Address>
                 <street>1 Sporting Green Centre, Science Club, building 1, Magdalen</street>
                 <city>SFO-1</city>
                 <state>CA</state>
                 <zipCode>99236</zipCode>
                 <country>United States of America</country>
               </Address>
               <telephone>269-1-4036</telephone>
             </ShippingInstructions>
             <SpecialInstructions>Priority Overnight</SpecialInstructions>
             <LineItems>
               <LineItem ItemNumber="1">
                 <Part Description="Face to Face: First Seven Years" UnitPrice="19.95">1</Part>
                 <Quantity>1</Quantity>
               </LineItem>
               <LineItem ItemNumber="2">
                 <Part Description="Runaway" UnitPrice="27.95">1</Part>
                 <Quantity>1</Quantity>
               </LineItem>
               <LineItem ItemNumber="3">
                 <Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">1</Part>
                 <Quantity>1</Quantity>
               </LineItem>
             </LineItems>
           </PurchaseOrder>

実行計画を見ると表のフルスキャンの後にNL SEMIでXPATH EVALUATIONが走っていることがわかる。正直これが具体的にどのような動作をしているのかは想像できないが、Predicate InformationからId:5のfilterでXMLExisitsのReferenceに対するフィルタ処理をしているように見える。カラムC_01$は内部的にReferenceを意味するカラムなのだろう。Id:4のXPATH EVALUATIONは何をしているのかわからないが、もしかするとXMLの構造からこのC_01$を含む表構造を作り出す処理なのかもしれないが、想像の域を出ない。また、Id:1のfilterについては、Id:5でフィルタできればそれ以上フィルタは不要ではないかと思うのだが、これも理解できない。

Execution Plan
----------------------------------------------------------
Plan hash value: 144453041

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |    32 | 64480 |    21   (0)| 00:00:01 |
|*  1 |  FILTER             |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL | PURCHASEORDER |   100 |   196K|     5   (0)| 00:00:01 |
|   3 |   NESTED LOOPS SEMI |               |     2 |     8 |     5   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |               |       |       |            |          |
|*  5 |    XPATH EVALUATION |               |       |       |            |          |
-------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ )
   5 - filter("P1"."C_01$"='ACABRIO-1PDT')

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

(3)検索～XMLQueryによる部分抽出操作

　(2)の条件同様にReferenceがACABRIO-1PDTを満たす行のXMLから、さらにXMLQueryでShippingInstructionsのパスを抽出する。問い合わせは以下のように、得られた行のpo_detailsに対してXMLQueryを用いてShippingInstructionsを取り出している。

SELECT p.po_number, XMLQuery (
 '$PO/PurchaseOrder/ShippingInstructions'
 PASSING p.po_details as "PO"
 returning content) XML
FROM purchaseorder p
WHERE XMLExists (
 '$PO/PurchaseOrder[Reference=$REF]'
 PASSING p.po_details as "PO", 'ACABRIO-1PDT' as "REF");

 PO_NUMBER XML
---------- ------------------------------------------------------------------------------------------
         1 <ShippingInstructions>
             <name>A. Cabrio 1</name>
             <Address>
               <street>1 Sporting Green Centre, Science Club, building 1, Magdalen</street>
               <city>SFO-1</city>
               <state>CA</state>
               <zipCode>99236</zipCode>
               <country>United States of America</country>
             </Address>
             <telephone>269-1-4036</telephone>
           </ShippingInstructions>

実行計画は以下の通り、上記(2)と全く同じである。当たり前といえばそうかもしれないが、得られた結果セットに対するXMLQueryは特段実行計画に影響は与えないようである。

Execution Plan
----------------------------------------------------------
Plan hash value: 144453041

-------------------------------------------------------------------------------------
| Id  | Operation           | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |               |    32 | 64480 |    21   (0)| 00:00:01 |
|*  1 |  FILTER             |               |       |       |            |          |
|   2 |   TABLE ACCESS FULL | PURCHASEORDER |   100 |   196K|     5   (0)| 00:00:01 |
|   3 |   NESTED LOOPS SEMI |               |     2 |     8 |     5   (0)| 00:00:01 |
|   4 |    XPATH EVALUATION |               |       |       |            |          |
|*  5 |    XPATH EVALUATION |               |       |       |            |          |
-------------------------------------------------------------------------------------

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

   1 - filter( EXISTS (SELECT /*+ )
   5 - filter("P1"."C_01$"='ACABRIO-1PDT')

Note
-----
   - Unoptimized XML construct detected (enable XMLOptimizationCheck for more information)

４．XML索引付与

これまではXMLへの問い合わせは全てフルスキャンで行っていた。今回のようにデータが100件であればいざ知らず、実際は数百万件のレコードにたいして問い合わせを行うような状況は容易に想定される。このため、索引を用いた検索ができることは非常に重要である。ここでは実際に索引を付与して、その索引がXMLの問い合わせにて使われることを確認してみよう。

全文検索用の準備

索引を作成する前に、セクショングループとストレージプリファレンスを作成する。ここはOracle Textによる全文検索機能を利用しているため、それぞれの詳細はOracle Textのマニュアルを確認する必要があるが、作成方法は参考[1]の「6.4.1 XML検索索引の作成と使用」を参考にした。特筆すべきは、セクショングループにPATH_SECTION_GROUPを指定し、XML_ENAMBEをＴ(True)にすることである。AUTO_SECTION_GROUPやXML_SECTION_GROUPを指定した場合、索引は作成できるが、実際に問い合わせを行うと、ORA-18177: XQuery full text expression ...のエラーが出てしまう。

-- create a section group and a preference
BEGIN
  CTX_DDL.create_section_group('XQFT', 'PATH_SECTION_GROUP');
  CTX_DDL.set_sec_grp_attr('XQFT', 'XML_ENABLE', 'T');

  CTX_DDL.create_preference('STORAGE_PREFS', 'BASIC_STORAGE');
  CTX_DDL.set_attribute('STORAGE_PREFS',
                        'D_TABLE_CLAUSE',
                        'TABLESPACE USERS
                         LOB(DOC) STORE AS SECUREFILE 
                         (TABLESPACE USERS NOCOMPRESS CACHE)');
  CTX_DDL.set_attribute('STORAGE_PREFS',
                        'I_TABLE_CLAUSE',
                        'TABLESPACE USERS
                         LOB(TOKEN_INFO) STORE AS SECUREFILE
                         (TABLESPACE USERS NOCOMPRESS CACHE)');
END;
/

次に、XML Typeカラムに対し索引を付与する。実際は日本語を扱う場合はlexerや、更新に伴い索引のメンテを行うSYNC(ON COMMIT)等の考慮が必要ではないかと思うが、ここではチュートリアルの例に従い索引を作成する。

CREATE INDEX purchaseorder_xqft_idx
ON purchaseorder (po_details)
INDEXTYPE IS CTXSYS.CONTEXT
PARAMETERS (
'storage STORAGE_PREFS section group XQFT'
) ;

以上でテキスト索引の付与は終わりである。

(1)テキスト索引を用いた検証

XML列から、住所の通り(street)に"Magdalen"と"80"がAND条件で含まれる列を抽出する。ftandは全文検索用の演算子でFull Text ANDの意味である。結果を見ると確かにstreetに、80 Sporting Green Centre, Science Club, building 80, Magdalenが入っていることが確認できる。

SELECT p.po_number,po_details
FROM purchaseorder p
WHERE XMLExists(
 '$P/PurchaseOrder/ShippingInstructions/Address/street[. contains text "Magdalen" ftand "80" ]'
 PASSING p.po_details as "P");

 PO_NUMBER PO_DETAILS
---------- ------------------------------------------------------------------------------------------
        80 <PurchaseOrder>
             <Reference>ACABRIO-80PDT</Reference>
             <Actions>
               <Action>
                 <User>ACABRIO-80</User>
               </Action>
             </Actions>
             <Rejection/>
             <Requestor>A. Cabrio 80</Requestor>
             <User>ACABRIO-80</User>
             <CostCenter>A80</CostCenter>
             <ShippingInstructions>
               <name>A. Cabrio 80</name>
               <Address>
                 <street>80 Sporting Green Centre, Science Club, building 80, Magdalen</street>
                 <city>SFO-80</city>
                 <state>CA</state>
                 <zipCode>99236</zipCode>
                 <country>United States of America</country>
               </Address>
               <telephone>269-80-4036</telephone>
             </ShippingInstructions>
             <SpecialInstructions>Priority Overnight</SpecialInstructions>
             <LineItems>
               <LineItem ItemNumber="1">
                 <Part Description="Face to Face: First Seven Years" UnitPrice="19.95">80</Part>
                 <Quantity>80</Quantity>
               </LineItem>
               <LineItem ItemNumber="2">
                 <Part Description="Runaway" UnitPrice="27.95">80</Part>
                 <Quantity>80</Quantity>
               </LineItem>
               <LineItem ItemNumber="3">
                 <Part Description="Founding Fathers: Men Who Shaped" UnitPrice="19.95">80</Part>
                 <Quantity>80</Quantity>
               </LineItem>
             </LineItems>
           </PurchaseOrder>

実行計画は以下の通り、テキスト索引（DOMAIN INDEX）が使われていることがわかる。Id:2のPredicate Informationを見ると、accessとなっており索引が使われていることがわかる。また、ここでCONTAINS関数によって、SYS_MAKEXMLで隠しカラムであるBLOBのSYS_NC00003$からstreetパスを取り出し、検索条件としたキーワードが含まれるかを>0で確認している様子がうかがえる。

Execution Plan
----------------------------------------------------------
Plan hash value: 4054188112

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |     1 |   482 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PURCHASEORDER          |     1 |   482 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | PURCHASEORDER_XQFT_IDX |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML(0,"SYS_NC00003$"),'( ( {Magdalen} ) and ( {80} ) ) INPATH
              (/PurchaseOrder/ShippingInstructions/Address/street)')>0)

なお、上記のテキスト索引がある状態であっても、３－(2)のXMLExistsによる問い合わせはそのままではフルスキャンになってしまう。テキスト索引を使うためには、以下のようにcontainsを用いた形に修正する必要がある。

SELECT p.po_number,p.po_details "XML"
FROM purchaseorder p
WHERE XMLExists (
 '$PO/PurchaseOrder/Reference[. contains text "ACABRIO-1PDT" ]'
 PASSING p.po_details as "PO");

Execution Plan
----------------------------------------------------------
Plan hash value: 4054188112

------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                   | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                        |     1 |   482 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| PURCHASEORDER          |     1 |   482 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | PURCHASEORDER_XQFT_IDX |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------

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

   2 - access("CTXSYS"."CONTAINS"(SYS_MAKEXML(0,"SYS_NC00003$"),'{ACABRIO-1PDT} INPATH
              (/PurchaseOrder/Reference)')>0)

５．おわりに

　本稿では、簡単なXML-Typeテーブルを作成し、検索、索引付与をして、実行計画を確認した。
　XML-Type型のカラムにXMLデータを格納すると、XMLExists関数で行をフィルタリングしたり、XMLQuery関数で特定のXMLの構造（パス）を取り出せることが確認できた。また、テキスト索引を付与することで、containsを用いた全文検索を複数の文字列をANDで含むなどで抽出することができることが確認できた。
　今回はデータ件数が100件と多くないため、性能の観点では確認していない。XMLデータ作成のPL/SQLのデータ件数を変更することで、テキスト索引の性能的な効果を確認することができるだろうが、このあたりは今後の課題としたい。

参考

[1]Oracle XML DB 開発者ガイド 19c
https://docs.oracle.com/cd/F19136_01/adxdb/index.html

[2]Oracle XML DB (OTN)
https://www.oracle.com/database/technologies/appdev/xmldb.html

[3]An Introduction to Oracle XML DB in Oracle Database 19c and 21c Technical Overview
https://www.oracle.com/a/tech/docs/technical-resources/xmldb-tech-overview-2022.pdf

[4]Oracle XML DB : Storing and Processing XML Documents
https://livesql.oracle.com/apex/livesql/file/tutorial_HE5NRRMNBOHLLKRLZJU0VNRCB.html