Oracle APAC Database..｜Oracle XML DB 19cの簡易.. ブログトップ

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) 

コメント 0

コメントを書く

お名前：
URL：
コメント：
画像認証：
下の画像に表示されている文字を入力してください。

※ブログオーナーが承認したコメントのみ表示されます。
Oracle APAC Database..｜Oracle XML DB 19cの簡易.. ブログトップ

Kazuhiro

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