[Oracle Cloud] Object Storage上のドキュメントをOracle Textで全文検索してみた (2023/01/08)

Oracle Cloud Infrastructure（OCI）のOracle Databaseサービスの１つ、Autonoous DatabaseでOCI Object Storageバケット内のファイルに対してOracle Textの全文検索索引を作成し、全文検索してみた。

# 前提条件
- OCI Object Storageバケットに対象ファイルを配置
- Autonomous Databaseでクレデンシャルの作成
    - OCIチュートリアルの [クレデンシャル情報の登録](https://oracle-japan.github.io/ocitutorials/database/adb202-dataload-dbms-cloud/)参照

# Object StorageファイルにOracle Text索引を作成

Object Storage内のファイルにOracle Text索引を作成するためにDBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEX 使用します。

- パラメータcredential_name：クレデンシャルを指定します。
- パラメータlocation_uri：指定された URI にあるObject StorageファイルにOracle Text索引を作成します。
- パラメータindex_name；索引名を指定します。
- パラメータformatのrefresh_rateオプション：索引更新間隔(分)を指定します。

```
begin
    dbms_cloud.create_external_text_index (
        credential_name => 'DEF_CRED_NAME',
        location_uri    => 'https://objectstorage.ap-tokyo-1.oraclecloud.com/n/<namespace>/b/<bucket名>/o/',
        index_name      => 'mydocs',
        format          => JSON_OBJECT('refresh_rate' value '10')
    );
end;
/
```

これにより、東京リージョンの `namespace`の`bucket名`のバケットに対してOracle Text索引が作成されます。
Database Object(Oracle Text索引参照表)としては、mydocs$TXTIDX（`index_name`$TXTIDX)テーブルが作成され、CONTAINSを使用して検索を実行できます。
このOracle Text索引は10分間隔でリフレッシュされます。

# Oracle Text索引参照表

DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXを実行することで作成されるOracle Text索引参照表(`Index_name`$TXTIDX)には次の列があります。

- mtime：Object Storageファイルの最終変更タイムスタンプです。これは、DBMS_CLOUDが最後にファイルにアクセスした時刻です。
- object_name：Object Storage上のファイル名です。
- object_path：Object Storage上のファイルを含むObject Storageバケットの URI です。


# 全文検索の実施

### 索引付けが完了したオブジェクト(ファイル)の確認

Oracle Text索引参照表：mydocs$TXTIDXテーブル を検索すると 索引付けが完了したファイルを確認できます。


```
select * from mydocs$txtidx
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/74740ece-4027-453b-dc5d-2b831bf1b82d.png)

### 全文検索

「日本語」という文字列を含むファイルの検索

```
select * from mydocs$txtidx where contains (object_name, '日本語') > 0;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/1e4337e9-1771-3ac8-88c7-30597d89700c.png)

「ホワイト」という文字列を含むファイルの検索

```
select * from mydocs$txtidx where contains (object_name, 'ホワイト') > 0;
```
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/c1ebfde8-45e1-3586-0339-1396f1bb92ca.png)



# 作成したOracle Text索引の削除

Object Storageファイルに対するOracle Text索引を削除するには DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEXを使用します。

- パラメータindex_name：索引名を指定します。



```
BEGIN 
DBMS_CLOUD.DROP_EXTERNAL_TEXT_INDEX (
        index_name => 'mydocs'
);
END;
/
```


# Oracle Text索引作成の監視

DBMS_CLOUD.CREATE_EXTERNAL_TEXT_INDEXでOracle Text索引を作成すると、ALL_SCHEDULER_JOB_RUN_DETAILSビューにログが記録されます。
ALL_SCHEDULER_JOB_RUN_DETAILSビューを照会して、索引作成ジョブによって報告されたステータスとエラーを取得できます。

DBMS_SCHEDULERジョブの名前は、`index_name`$JOBです。

```
SELECT status, additional_info 
   FROM all_scheduler_job_run_details WHERE LOWER(job_name) = LOWER('mydocs$JOB');

STATUS    ADDITIONAL_INFO 
--------- --------------- 
SUCCEEDED                 
SUCCEEDED                 
SUCCEEDED                 
SUCCEEDED                 
SUCCEEDED                 

5行が選択されました。
```

Database Actions の`スケジュール`でも確認できます。
![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/602ae2f8-a12f-fbf8-f72a-d318956a5cbe.png)

![image.png](https://qiita-image-store.s3.ap-northeast-1.amazonaws.com/0/108635/3afd3d40-b3b9-a184-e62b-b56f6663ff73.png)


# おわりに

Autonomous DatabaseからObject Storageのファイルに対してOracle Textを使った全文検索ができました。
記事執筆時点(2023/01/08)ではAutonomous Database以外のOracle Database 19c の DBMS_CLOIDパッケージには CREATE_EXTERNAL_TEXT_INDEX プロシージャがありませんでした。

## 参考情報

- [Indexing Text in Object Storage](https://blogs.oracle.com/datawarehousing/post/indexing-text-object-storage)
- [Query Text in Object Storage](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/query-oracle-text-index.html)
- [DBMS_CLOUD Subprograms and REST APIs](https://docs.oracle.com/en/cloud/paas/autonomous-database/adbsa/dbms-cloud-subprograms.html)
- [Oracle Textリファレンス](https://docs.oracle.com/cd/F19136_01/ccref/index.html)
- [Oracle Textアプリケーション開発者ガイド ](https://docs.oracle.com/cd/F19136_01/ccapp/index.html)
- Oracle LiveLabs [Full-Text Indexing in Oracle Database](https://apexapps.oracle.com/pls/apex/r/dbpm/livelabs/view-workshop?wid=3286&clear=RR,180&session=109774578817407)