Oracle Textを始めるメモ

## 説明
Oracle Textを試してみたときのメモです。
ファイルをテーブルに登録して検索できるかやってみました。

## 環境
| モノ     | バージョン                        |
|:-----|:-----------------------|
| OS   |Windows10 Home（64bit） | 
| Oracle Database  | 21c Express Edition  | 

## 流れ
1. テーブルにデータを入れる
1. プリファレンスを作成する
1. CONTEXT索引を作成する
1. SELECT文を発行して全文検索する


### テーブルにデータを入れる
```SQL
CREATE TABLE MYFILE( 
    ID NUMBER PRIMARY KEY
    , FILE_NAME VARCHAR2(256)
    , FILE_DATA BLOB
);
```
BLOB項目を持つテーブルを作ります。
今回はお試しなのでPDFファイルを登録します。PDFは以下のものを使いました。

| モノ     | 入手先                        |
|:-----|:-----------------------|
| Oracle Text 詳細解説.pdf   |[リンク](https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf) | 
| Spring Boot 入門 - リファレンスドキュメント.pdf  | [このサイト](https://spring.pleiades.io/spring-boot/docs/current/reference/html/getting-started.html)をChormeでPDF化したもの  | DX白書2021 付録 第1部 AI技術（PDF形式）.pdf |[リンク](https://www.ipa.go.jp/ikc/publish/dx_hakusho.html)|

:::note warn
データの登録はOracle SQL Developerで行いました。
他のアプリケーションでもデータ(BLOB)を登録するところまではできるのですが、
なぜか全文検索がうまくできませんでした。
:::


### プリファレンスを作成する
```SQL
CALL CTX_DDL.CREATE_PREFERENCE('jvl','JAPANESE_VGRAM_LEXER');

--- 削除するとき
--- CTX_DDL.DROP_PREFERENCE('jvl');

```
プリファレンス (翻訳)-> Preferences (翻訳)-> 環境設定のことのようです。
ここでは、「レクサー・プリファレンス」として、索引付けするテキストの言語として、日本語を設定しております。

### CONTEXT索引を作成する
```SQL
CREATE INDEX MYFILEIDX ON MYFILE(FILE_DATA)
     INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER jvl')
```

#### $I表を見てみる
```SQL
SELECT * FROM DR$MYFILEIDX$I
```
CONTEXT索引は4つの表とB*TREE索引から構成されているそうです。
＄K表、＄N表、＄I表(トークン表)、＄R表、＄X索引の５つだそうです。
＄I表をSELECTして中を見ると、
日本語の文字列が分割されて、トークンとして登録されているのが確認できます。

### SELECT文を発行して全文検索する
```SQL
--- お試し１
SELECT ID, FILE_NAME FROM MYFILE WHERE CONTAINS(FILE_DATA, '重み付け演算子によって') > 0
-- ID FILE_NAME                                                                                                                                                                                                                                                       
-- 1 Oracle Text 詳細解説.pdf        

--- お試し２
SELECT ID, FILE_NAME FROM MYFILE WHERE CONTAINS(FILE_DATA, 'Spring プラットフォーム') > 0

-- ID FILE_NAME                                                                                                                                                                                                                                                       
-- 2 Spring Boot 入門 - リファレンスドキュメント.pdf      

--- お試し３
SELECT ID, FILE_NAME FROM MYFILE WHERE CONTAINS(FILE_DATA, '検索') > 0
-- ID FILE_NAME                                                                                                                                                                                                                                                       
-- 1 Oracle Text 詳細解説.pdf        
```

お試し３の検索では、「DX白書2021 付録 第1部 AI技術（PDF形式）.pdf」の内容が
検索できるはずだったのですが出てきませんでした。

:::note info
登録したファイルを見てみると、PDFファイルにセキュリティの設定がされていました。
もしかすると、[B.1.4 PDFドキュメントおよびセキュリティ設定でのフィルタ処理](https://docs.oracle.com/cd/F19136_01/ccref/oracle-text-supported-document-formats.html#GUID-4BB2A31D-4797-4EBC-AFD3-8F3AEBA953AE)　のあたりの仕様に該当して、文字列が抽出できなかったのではないかと想像しています。
:::

#### データを追加する
PDFからは文字が抽出できることが確認できたので、
Sheet1とSheet2に適当なニュースの文章をコピペしたエクセルファイルをテーブルに登録しました。

#### CONTEXT索引を同期する
```SQL
CALL CTX_DDL.SYNC_INDEX('MYFILEIDX');

--- 同期前
SELECT COUNT(*) FROM DR$MYFILEIDX$I
> 5406
---
--- 同期後
SELECT COUNT(*) FROM DR$MYFILEIDX$I
> 5808 増えた！！

```
同期を行わないと検索してもヒットしませんでした。
手動で同期せずに、CONTEXT索引の作成時のパラメータに「SYNC(ON COMMIT)」をつけることで、
コミット時に自動的に同期する設定もできるようで、その方法でも確認できました。

```SQL 
CREATE INDEX MYFILEIDX ON MYFILE(FILE_DATA)
     INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS ('LEXER jvl これ SYNC(ON COMMIT)')
```

#### CONTEXT索引を最適化する。
```SQL
CALL CTX_DDL.OPTIMIZE_INDEX('MYFILEIDX','FULL');
```
断片化するようなので最適化のコマンドもありますね。

#### その他
```SQL
--- プリファレンスの確認
SELECT * FROM CTXSYS.CTX_USER_PREFERENCES
```


### 参考にさせてもらったサイト！
[Oracle Textリファレンス](https://docs.oracle.com/cd/F32587_01/ccref/index.html)
[Oracle Textアプリケーション開発者ガイド](https://docs.oracle.com/cd/F19136_01/ccapp/index.html)
[Oracle Text 詳細解説](https://www.oracle.com/jp/a/tech/docs/technical-resources/oracletext-ver12.pdf)
[SQL文でできる！Oracle Databaseの全文検索機能「Oracle Text」の活用法](http://www.oracle.com/technetwork/jp/ondemand/db-technique/c-10-oracletext-1448393-ja.pdf)

## 最後に
思ったよりも手軽に全文検索ができたのというメモでした。