Oracle Text 復習・データストア

前回に引き続き、Oracle Text の復習です。
環境は変わらず Oracle Autonomous AI Database 26ai (Always Free) です。

複数列に対する全文検索

前回は単一列 (songs表の lyrics列) に対してテキスト索引を作成し全文検索しましたが、複数列に対して全文検索したいケースがあると思います。

しかし、CREATE INDEXで素直に複数列指定すると ORA-29851 でエラーとなります。

SQL> drop index songs_idx1;

Index SONGS_IDX1 dropped.

SQL>
SQL> create index songs_idx1 on songs(title, lyrics)  ★
  2  indextype is ctxsys.context
  3* parameters('LEXER MYLEXER1');

Error starting at line : 1 in command -
create index songs_idx1 on songs(title, lyrics)
indextype is ctxsys.context
parameters('LEXER MYLEXER1')
Error report -
ORA-29851: cannot build a domain index on more than one column  ★

https://docs.oracle.com/error-help/db/ora-29851/
29851. 00000 -  "cannot build a domain index on more than one column"  ★
*Cause:    User attempted to build a domain index on more than one column.
*Action:   Build the domain index only on a single column.
SQL>

エラー文に記載の通り、複数列でドメイン索引を作成することはできません。
(ドメイン索引…空間処理やイメージ処理など、特化されたドメイン向けに設計された索引)

複数列に対して全文検索するには、MULTI_COLUMN_DATASTOREというデータストア型を使用します。

Oracle Text リファレンス > 2.3 データストア型

SQL> --MULTI_COLUMN_DATASTORE を使用してプリファレンスを作成
SQL> exec ctx_ddl.create_preference('MYDS1', 'MULTI_COLUMN_DATASTORE');

PL/SQL procedure successfully completed.

SQL> --対象列を複数指定
SQL> exec ctx_ddl.set_attribute('MYDS1', 'columns', 'title, lyrics');

PL/SQL procedure successfully completed.

SQL> select * from ctx_preferences where pre_owner='ADMIN';

PRE_OWNER    PRE_NAME    PRE_CLASS    PRE_OBJECT
____________ ___________ ____________ _________________________
ADMIN        MYLEXER1    LEXER        JAPANESE_VGRAM_LEXER
ADMIN        MYDS1       DATASTORE    MULTI_COLUMN_DATASTORE

SQL> select * from ctx_preference_values where prv_owner='ADMIN';

PRV_OWNER    PRV_PREFERENCE    PRV_ATTRIBUTE    PRV_VALUE
____________ _________________ ________________ ________________
ADMIN        MYDS1             COLUMNS          title, lyrics
ADMIN        MYLEXER1          BIGRAM           YES

SQL>

作成したMYDS1をparameters句で指定してテキスト索引を作成します。
この時CREATE INDEXで指定する列は、プリファレンスで指定した列のいずれか (今回は title または lyrics) を指定します。

SQL> create index songs_idx1 on songs(lyrics)  ★title か lyrics を指定
  2  indextype is ctxsys.context
  3* parameters('DATASTORE MYDS1 LEXER MYLEXER1');  ★

Index SONGS_IDX1 created.

SQL>

全文検索すると、キーワードの「海」が title列にしか登場しない ID = 1 も検索結果として表示されます。

SQL> select id, title, lyrics from songs where contains(lyrics, '海') > 0;

   ID TITLE     LYRICS
_____ _________ _________________________________________________________
    1 海         1番：うみはひろいな おおきいな 月がのぼるし 日がしずむ
                2番：うみはおおなみ あおいなみ ふねがうかぶし かもめがとぶ
                3番：うみはよぶな ひとよぶな 日本と外国 むすぶふね
   11 われは海の子    1番：我は海の子 白浪の さわぐいそべの松原に 煙たなびくとまやこそ 我がなつかしき住家なれ        
                2番：生まれて潮に浴しては 波を子守の歌と聞き 千里寄せくる海の気を 吸いてわらべとなりにけり
                3番：高く鼻つくいその香に 不断の花のふきぬきは はやみどりなる松の葉に 海月星影（みづきほしかげ）さや か也
   14 椰子の実      1番：名も知らぬ遠き島より 流れ寄る椰子の実一つ
                2番：故郷の岸を離れて なれはそも波に幾月
                3番：旧（もと）の木は生（お）いや茂れる 枝はなお影をやなせる
                4番：われもまた渚を枕 孤身（ひとりみ）の浮寝の旅ぞ
                5番：実をとりて胸にあつれば 新（あらた）なる愁（うれい）の湧く
                6番：海の日の沈むを見れば 激（たぎ）り落つ異郷の涙
                7番：思いやる八重の汐々（しおじお） いずれの日にか国に帰らん
   15 砂山        1番：海は荒海 向こうは佐渡よ すずめなけなけ もう日はくれた みんな呼べ呼べ お星さま出たぞ
                2番：暮れりゃ砂山 汐鳴りばかり すずめちりぢり また風荒れる みんな散り散り もう誰も見えぬ
                3番：かえろかえろよ 茱萸（ぐみ）原わけて すずめさよなら さよならあした 海よさよなら さよならあした   

SQL>

なお、containsで指定する列はCREATE INDEXで指定した列でないとエラーになるので注意しましょう。

SQL> drop index songs_idx1;

Index SONGS_IDX1 dropped.

SQL> create index songs_idx1 on songs(title)  ★title を指定
  2  indextype is ctxsys.context
  3* parameters('DATASTORE MYDS1 LEXER MYLEXER1');

Index SONGS_IDX1 created.

SQL> select id, title, lyrics from songs where contains(lyrics, '海') > 0;  ★lyrics を指定するとエラー

Error starting at line : 1 in command -
select id, title, lyrics from songs where contains(lyrics, '海') > 0
Error report -
ORA-12801: error signaled in parallel query server P000, instance 2
ORA-30600: Oracle Text error
DRG-10599: column is not indexed

https://docs.oracle.com/error-help/db/ora-12801/

More Details :
https://docs.oracle.com/error-help/db/ora-12801/
https://docs.oracle.com/error-help/db/ora-30600/
https://docs.oracle.com/error-help/db/drg-10599/
SQL> select id, title, lyrics from songs where contains(title, '海') > 0;  ★title を指定すると正常終了

   ID TITLE     LYRICS
_____ _________ _________________________________________________________
    1 海         1番：うみはひろいな おおきいな 月がのぼるし 日がしずむ
                2番：うみはおおなみ あおいなみ ふねがうかぶし かもめがとぶ
                3番：うみはよぶな ひとよぶな 日本と外国 むすぶふね
   11 われは海の子    1番：我は海の子 白浪の さわぐいそべの松原に 煙たなびくとまやこそ 我がなつかしき住家なれ        
                2番：生まれて潮に浴しては 波を子守の歌と聞き 千里寄せくる海の気を 吸いてわらべとなりにけり
                3番：高く鼻つくいその香に 不断の花のふきぬきは はやみどりなる松の葉に 海月星影（みづきほしかげ）さや か也
   14 椰子の実      1番：名も知らぬ遠き島より 流れ寄る椰子の実一つ
                2番：故郷の岸を離れて なれはそも波に幾月
                3番：旧（もと）の木は生（お）いや茂れる 枝はなお影をやなせる
                4番：われもまた渚を枕 孤身（ひとりみ）の浮寝の旅ぞ
                5番：実をとりて胸にあつれば 新（あらた）なる愁（うれい）の湧く
                6番：海の日の沈むを見れば 激（たぎ）り落つ異郷の涙
                7番：思いやる八重の汐々（しおじお） いずれの日にか国に帰らん
   15 砂山        1番：海は荒海 向こうは佐渡よ すずめなけなけ もう日はくれた みんな呼べ呼べ お星さま出たぞ
                2番：暮れりゃ砂山 汐鳴りばかり すずめちりぢり また風荒れる みんな散り散り もう誰も見えぬ
                3番：かえろかえろよ 茱萸（ぐみ）原わけて すずめさよなら さよならあした 海よさよなら さよならあした   

SQL>

ファイルに対する全文検索

ファイルに対して全文検索するには、DIRECTORY_DATASTOREというデータストア型を使用します。
まずはディレクトリオブジェクトを作成し、全文検索したいファイルを格納します。(ここでは既存のディレクトリオブジェクトtempに 5つのテキストファイルを格納)

SQL> BEGIN
  2    DBMS_CLOUD.GET_OBJECT(
  3      credential_name => 'OCI_CRED',
  4      object_uri      => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxx/song_hamabenouta.txt',
  5      directory_name  => 'temp',
  6      file_name       => 'song_hamabenouta.txt'
  7    );
  8  END;
  9* /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_CLOUD.GET_OBJECT(
  3      credential_name => 'OCI_CRED',
  4      object_uri      => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxx/song_hotarukoi.txt',
  5      directory_name  => 'temp',
  6      file_name       => 'song_hotarukoi.txt'
  7    );
  8  END;
  9* /

PL/SQL procedure successfully completed.

SQL>
SQL> BEGIN
  2    DBMS_CLOUD.GET_OBJECT(
  3      credential_name => 'OCI_CRED',
  4      object_uri      => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxx/song_kamomenosuiheisan.txt',
  5      directory_name  => 'temp',
  6      file_name       => 'song_kamomenosuiheisan.txt'
  7    );
  8  END;
  9* /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_CLOUD.GET_OBJECT(
  3      credential_name => 'OCI_CRED',
  4      object_uri      => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxx/song_warehauminoko.txt',
  5      directory_name  => 'temp',
  6      file_name       => 'song_warehauminoko.txt'
  7    );
  8  END;
  9* /

PL/SQL procedure successfully completed.

SQL> BEGIN
  2    DBMS_CLOUD.GET_OBJECT(
  3      credential_name => 'OCI_CRED',
  4      object_uri      => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/xxx/song_yashinomi.txt',
  5      directory_name  => 'temp',
  6      file_name       => 'song_yashinomi.txt'
  7    );
  8  END;
  9* /

PL/SQL procedure successfully completed.

SQL> select object_name from table(dbms_cloud.list_files('TEMP'));

OBJECT_NAME
_____________________________
song_hamabenouta.txt
song_hotarukoi.txt
song_kamomenosuiheisan.txt
song_warehauminoko.txt
song_yashinomi.txt

SQL>

DIRECTORY_DATASTOREを使用してプリファレンスを作成します。
作成したプリファレンスに対してディレクトリオブジェクトを指定します。

SQL> --DIRECTORY_DATASTORE を使用してプリファレンスを作成
SQL> exec ctx_ddl.create_preference('MYDS2', 'DIRECTORY_DATASTORE');

PL/SQL procedure successfully completed.

SQL> --ディレクトリオブジェクトを指定 (TEMP)
SQL> exec ctx_ddl.set_attribute('MYDS2', 'DIRECTORY', 'TEMP');

PL/SQL procedure successfully completed.

SQL> select * from ctx_preferences where pre_owner='ADMIN';

PRE_OWNER    PRE_NAME    PRE_CLASS    PRE_OBJECT
____________ ___________ ____________ _________________________
ADMIN        MYLEXER1    LEXER        JAPANESE_VGRAM_LEXER
ADMIN        MYDS2       DATASTORE    DIRECTORY_DATASTORE
ADMIN        MYDS1       DATASTORE    MULTI_COLUMN_DATASTORE

SQL> select * from ctx_preference_values where prv_owner='ADMIN';

PRV_OWNER    PRV_PREFERENCE    PRV_ATTRIBUTE    PRV_VALUE
____________ _________________ ________________ ________________
ADMIN        MYDS1             COLUMNS          title, lyrics
ADMIN        MYDS2             DIRECTORY        TEMP
ADMIN        MYLEXER1          BIGRAM           YES

SQL>

ファイル名の情報を持つ song_files表を作成します。

SQL> create table song_files(id number, file_name varchar2(2000));

Table SONG_FILES created.

SQL>
SQL>
SQL> insert into song_files values (1,'song_hamabenouta.txt');

1 row inserted.

SQL> insert into song_files values (2,'song_hotarukoi.txt');

1 row inserted.

SQL> insert into song_files values (3,'song_kamomenosuiheisan.txt');

1 row inserted.

SQL> insert into song_files values (4,'song_warehauminoko.txt');

1 row inserted.

SQL> insert into song_files values (5,'song_yashinomi.txt');

1 row inserted.

SQL> commit;

Commit complete.

SQL> select * from song_files;

   ID FILE_NAME
_____ _____________________________
    1 song_hamabenouta.txt
    2 song_hotarukoi.txt
    3 song_kamomenosuiheisan.txt
    4 song_warehauminoko.txt
    5 song_yashinomi.txt

SQL>

song_files表の file_name列に対してテキスト索引を作成します。
全文検索すると、キーワードの「海」がファイル内で登場する ID = 4, 5 が検索結果として表示されます。

SQL> create index song_files_idx1 on song_files(file_name)
  2  indextype is ctxsys.context
  3* parameters('DATASTORE MYDS2 LEXER MYLEXER1');

Index SONG_FILES_IDX1 created.

SQL> select id, file_name from song_files where contains(file_name, '海') > 0;

   ID FILE_NAME
_____ _________________________
    4 song_warehauminoko.txt
    5 song_yashinomi.txt

SQL>

song_hamabenouta.txt

1番：あした浜辺を さまよえば 昔のことぞ しのばるる 風の音よ 雲のさまよ 寄する波も 貝の色も
2番：ゆうべ浜辺を もとおれば 昔の人ぞ しのばるる 寄する波よ 返す波よ 月の色も 星の影も

song_hotarukoi.txt

ほ ほ ほたるこい あっちの水は にがいぞ
こっちの水は あまいぞ ほ ほ ほたるこい
ほ ほ 山へいろ あかりのつくまで 飛んでゆけ

song_kamomenosuiheisan.txt

1番：かもめの水兵さん ならんだ水兵さん 白い帽子 白いシャツ 白い服 波にチャップチャップ 浮かんでる
2番：かもめの水兵さん かけあし水兵さん 白い帽子 白いシャツ 白い服 波をチャップチャップ 越えてゆく
3番：かもめの水兵さん ずぶぬれ水兵さん 白い帽子 白いシャツ 白い服 波にチャップチャップ お洗濯
4番：かもめの水兵さん なかよし水兵さん 白い帽子 白いシャツ 白い服 波にチャップチャップ 揺れている

song_warehauminoko.txt

1番：我は海の子 白浪の さわぐいそべの松原に 煙たなびくとまやこそ 我がなつかしき住家なれ
2番：生まれて潮に浴しては 波を子守の歌と聞き 千里寄せくる海の気を 吸いてわらべとなりにけり
3番：高く鼻つくいその香に 不断の花のふきぬきは はやみどりなる松の葉に 海月星影（みづきほしかげ）さやか也

song_yashinomi.txt

1番：名も知らぬ遠き島より 流れ寄る椰子の実一つ
2番：故郷の岸を離れて なれはそも波に幾月
3番：旧（もと）の木は生（お）いや茂れる 枝はなお影をやなせる
4番：われもまた渚を枕 孤身（ひとりみ）の浮寝の旅ぞ
5番：実をとりて胸にあつれば 新（あらた）なる愁（うれい）の湧く
6番：海の日の沈むを見れば 激（たぎ）り落つ異郷の涙
7番：思いやる八重の汐々（しおじお） いずれの日にか国に帰らん