2022年04月｜ 2022年05月 ｜2022年06月 ブログトップ

Oracle XML DB 19cの簡易検証　[SQL・DDL]

　ゴールデンウィーク後半は良い天気に恵まれ、行楽地からの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


2022-05-05 20:07  コメント(0) 

Oracle Text 19c簡易検証　[SQL・DDL]

　ロシアのウクライナ侵攻はいまだ収束の道が見えない中、日本はゴールデンウィークである。この休みの中での個人的な宿題の１つに、Oracle Textを使ってみる、というのがあったので、手元のVirtual Box環境19c（19.11）で簡単に試してみた。

１．Oracle Textとは


　Oracle Textとは、全文検索を行うための機能である。例えば%FOO%といった条件で検索する場合、仮にそのカラムにB*Tree索引が張られていても、索引は利用できない。Oracle Textでは、全文検索用のテキスト索引を作成することができ、部分一致検索においても、この索引を利用することにより高速にクエリを返却することができる。
　Oracle Textは、Oracle Database本体のライセンスだけで利用できる機能のため、追加のオプション・ライセンスは必要ない。

２．Oracle Textの検証


　EMP表を作成し、ENAME列に対して、通常のB*Tree索引を付与する場合と、Oracle Textによる索引を付与する場合で、検索性能がどのように変わるかを確認する。

(1)環境準備


　まず環境準備をする。SCOTTユーザにOracle Textを利用するのに必要な権限CTXAPPを付与する。実行はsysdbaで実施する。
grant ctxapp to scott

次にSCOTTユーザでテスト用のEMP表を作成する。
drop table emp cascade constraints;

create table emp(  
  empno    number(7,0),  
  ename    varchar2(11),  
  job      varchar2(9),  
  mgr      number(7,0),  
  hiredate date,  
  sal      number(7,2),  
  comm     number(7,2),  
  deptno   number(7,0),  
  constraint pk_emp primary key (empno),  
  constraint fk_deptno foreign key (deptno) references dept (deptno)  
);

続いて、索引を作成する。まずは通常のB*Tree索引である。ENAME列にEMP_IDX1を付与する。
create index emp_idx1 on emp(ename);

次にテキスト索引を作成する。以下のように利用するレクサをJAPANESE_VGRAM_LEXERとし、
ENAME列にEMP_IDX2を付与する。
exec ctx_ddl.drop_preference('jvl_lexer');
exec ctx_ddl.create_preference('jvl_lexer','JAPANESE_VGRAM_LEXER');
create index emp_idx2 on emp(ename) indextype is ctxsys.context parameters ('lexer jvl_lexer SYNC(ON COMMIT)');

EMP表にデータを100万件インサートし、コミットする。なお、手元の環境でインサートは1分23秒、コミットは16秒かかった。通常のコミットは瞬時に終わるため、このコミットにこれだけ時間がかかったのは、テキスト索引のメンテナンス（作成）のオーバーヘッドと思われる。
insert into emp select
rownum,
dbms_random.string('u',3)||' '||dbms_random.string('u',3)||' '||dbms_random.string('u',3) ename,
dbms_random.string('u',9) job,
dbms_random.value(1,1000000) mgr,
sysdate - dbms_random.value(1,5000) hiredate,
dbms_random.value(3000,9999) sal,
dbms_random.value(3000,9999) comm,
dbms_random.value(1,100000) deptno
from dual connect by level <=1000000;
commit;

ENAME列は、空白をはさんで以下のように3文字ずつのデータになるようにした。
SQL> select empno, ename from emp where rownum < 5;

     EMPNO ENAME
---------- -----------
       495 ROT FHC LUH
       496 XPI BUU LLM
       497 KUL LNM PQI
       498 IAO ICN UWB

以上で準備は完了である。

(2)検証（部分一致検索）


ENAME列に対する部分一致検索を実行する。まずはLIKEで%FOO%のように検索する。結果151行である。
SQL> select empno, ename from emp where ename like '%FOO%' order by ename;

     EMPNO ENAME
---------- -----------
    764193 ACD FOO IEB
    779286 ADX FOO FGT
    629631 AFG SAT FOO
...
    230238 ZTX FOO FUH
     90691 ZVA FOO PNM
    509267 ZYB FOO ALI

151 rows selected.

Elapsed: 00:00:00.15

実行計画は以下の通りB*Tree索引は使われずにフルスキャンになる。
Execution Plan
----------------------------------------------------------
Plan hash value: 150391907

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |   100 |  2000 |  2470   (1)| 00:00:01 |
|   1 |  SORT ORDER BY     |      |   100 |  2000 |  2470   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL| EMP  |   100 |  2000 |  2469   (1)| 00:00:01 |
---------------------------------------------------------------------------

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

   2 - filter("ENAME" IS NOT NULL AND "ENAME" LIKE '%FOO%')

次に、テキスト索引を用いるように、CONTAINS関数を用いて検索する。結果は151件と、LIKEを使った場合と一致していることがわかる。また、処理時間(Elapsed)はテキスト検索の方が確かに速い。
SQL> select empno, ename from emp where contains (ename, 'FOO') >0 order by ename;

     EMPNO ENAME
---------- -----------
    764193 ACD FOO IEB
    779286 ADX FOO FGT
    629631 AFG SAT FOO
...
    230238 ZTX FOO FUH
     90691 ZVA FOO PNM
    509267 ZYB FOO ALI

151 rows selected.

Elapsed: 00:00:00.01

実行計画は以下の通りEMP_IDX2のテキスト索引（DOMAIN INDEXと表示される）が使われていることがわかる。
Execution Plan
----------------------------------------------------------
Plan hash value: 61694877

-----------------------------------------------------------------------------------------
| Id  | Operation                    | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |          |   147 |  4704 |    57   (2)| 00:00:01 |
|   1 |  SORT ORDER BY               |          |   147 |  4704 |    57   (2)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP      |   147 |  4704 |    56   (0)| 00:00:01 |
|*  3 |    DOMAIN INDEX              | EMP_IDX2 |       |       |    25   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------

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

   3 - access("CTXSYS"."CONTAINS"("ENAME",'FOO')>0)

(3)検証（前方・後方一致検索）


通常のLIKEによる検索では以下のように前方一致検ができる。
SQL> select empno, ename from emp where ename like 'FOO%';

     EMPNO ENAME
---------- -----------
    725740 FOO AFL UHM
    221838 FOO BFO VNG
    862953 FOO BSA ENH
...
50 rows selected.

Elapsed: 00:00:00.01

実行計画は以下の通り、EMP_IDX1のB*Tree索引が使われる。実行時間も高速である。
Execution Plan
----------------------------------------------------------
Plan hash value: 3621794058

------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |          |     4 |    80 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| EMP      |     4 |    80 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | EMP_IDX1 |     4 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

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

   2 - access("ENAME" LIKE 'FOO%')
       filter("ENAME" LIKE 'FOO%')

同様に後方一致検索も可能である。

SQL> select empno, ename from emp where ename like '%FOO';

     EMPNO ENAME
---------- -----------
     19344 VOY GXC FOO
     42350 CXU TAB FOO
     53848 IOW ZUV FOO
...
39 rows selected.

Elapsed: 00:00:00.09

実行計画は、フルスキャンになる。このため、索引検索よりは時間はかかっていることがわかる。
Execution Plan
----------------------------------------------------------
Plan hash value: 3956160932

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |   100 |  2000 |  2469   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMP  |   100 |  2000 |  2469   (1)| 00:00:01 |
--------------------------------------------------------------------------

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

   1 - filter("ENAME" IS NOT NULL AND "ENAME" LIKE '%FOO')


同様のことをcontains関数を使ってやろうとしても、いずれもcontains (ename, 'FOO')>0の条件と同じ151件が返ってきてLIKEと同じ結果は得られなかった。
select empno, ename from emp where contains (ename, 'FOO%') >0 order by ename;
select empno, ename from emp where contains (ename, '%FOO') >0 order by ename;

　これは、contains関数は単語単位に文字列を分割されたテキスト索引に対して、文字列比較をするため、今回のテストデータのように、ENAME列が3文字の単語で構成される形だとFOO%や%FOOのような条件指定はFOOと同じ意味になってしまうからと思われる。逆に、LIKEと同じように前方一致や後方一致で検索したい、という要件に対しては、containsでどう実現するのかはわからなかった。
　試しに%FO%という文字列を含む条件で検索したところ、LIKEとcontainsで件数の差分が認められた。LIKEではヒットするが、containsではヒットしない条件があるようだが、今回は時間の都合でその原因の確認まではできなかった。
SQL> select count(*) from emp where ename like '%FO%';

  COUNT(*)
----------
      8753
Elapsed: 00:00:00.10
SQL> select count(*) from emp where contains (ename, '%FO%') >0;

  COUNT(*)
----------
      8588
Elapsed: 00:00:00.03

３．おわりに


　本稿では、Oracle Textによるテキスト索引を用いる例を記載した。手元の環境で100万件のEMP表に対し、ENAME列に対する検索を行い、CONTAINS関数を利用することで、テキスト索引が使われることが確認できた。今回の条件で、フルスキャンに対して、テキスト索引の性能上の効果が認められた。
　以下は、今回いくつか検証していて疑問に思ったことである。今後の備忘として残しておく。
・元表の更新（COMMIT）に伴って索引をメンテナスするオーバーヘッド
・日本語での動作確認（文字列の一部に特定の文字列が含まれるかをcontainsで検索する）
・containsでの前方一致・後方一致検索の方法
・%FO%の件数差分の原因

　なお、今回Oracle Textについて調べるにあたり、参考にした、今後の参考になりそうな資料へのリンクを記載しておく。[1]は製品マニュアル(19c)である。読みにくいが、基本は押さえておく必要がある。[2]は日本オラクルの製品紹介資料である。全体的に製品カットで機能がまとめられているので、マニュアルを読むよりざっくりと機能概要を知ることができる。特に今回は、p.22にcontainsでは英語では%を使っても良いが日本語はNG、という話しが記載されている点、参考になった。[3]はOracleAceのナイル・フィリップ氏のスイスオラクルグループ(SOUG)での講演である。英語だが、動画で動きが確認できる点、参考になると思う。

参考


[1]Oracle Textマニュアル
https://docs.oracle.com/cd/F19136_01/ccref/index.html
[2]Oracle Text 詳細解説
https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf
[3]"Oracle Text : A Powerful Tool for Oracle Developers" by Niall Mc Phillips
https://www.youtube.com/watch?v=rfMhlKuJozI

2022-05-03 11:24  コメント(0) 

2022年04月｜2022年05月 ｜2022年06月 ブログトップ

Kazuhiro

データベーススペシャリスト
Oracle ACE
Oracle Community
Linked-in