Oracle Database 23c Free - Developer ReleaseでCREATE SEARCH INDEXを試してみた

# はじめに

Oracle Database 23cから、CREATE SEARCH INDEX文を使用して、JSONドキュメント、テキスト・データ、XMLドキュメントに対するサーチ・インデックスを共通の構文で作成できるようになったので、早速試してみました。

# 1. 各データ・タイプ毎のCREATE SEARCH INDEXの構文

＜テキスト・データ＞
```sql
CREATE SEARCH INDEX [schema.]index ON [schema.]table(txt_column)
[ONLINE]
[FILTER BY filter_column[, filter_column]...]
[ORDER BY oby_column[desc|asc][, oby_column[desc|asc]]...]
[LOCAL [PARTITION [partition] ]
[, PARTITION [partition] ])]
[PARAMETERS(paramstring)]
[PARALLEL n]
[UNUSABLE];
```

＜JSONドキュメント＞
```sql
CREATE SEARCH INDEX [schema.]index ON [schema.]table(json_column) FOR JSON 
[LOCAL]
PARAMETERS(
  [DATAGUIDE ON [CHANGE (ADD_VC | function_name)] | OFF]
  [STORAGE storage_pref]
  [SEARCH_ON (NONE | TEXT | TEXT_VALUE[(data_types)] | VALUE[(data_types)] | TEXT_VALUE_STRING)]
  [MEMORY memsize]
  [SYNC (MANUAL | EVERY "interval-string" | ON COMMIT)]
  [MAINTENANCE AUTO | MAINTENANCE MANUAL]
  [OPTIMIZE (MANUAL | EVERY "interval-string" | AUTO_DAILY)]
)
[PARALLEL n]
[UNUSABLE];
```

＜XMLドキュメント＞
```sql
CREATE SEARCH INDEX [schema.]index ON [schema.]table(xml_column) FOR XML 
[LOCAL]
PARAMETERS(
  SEARCH_ON (TEXT | TEXT_VALUE(data_types) | VALUE(data_types))
  [STORAGE storage_pref]
  [PREFIX_NS (prefix_ns_mapping)]
  [MEMORY memsize]
  [SYNC (MANUAL | EVERY "interval-string" | ON COMMIT)]
  [MAINTENANCE AUTO | MAINTENANCE MANUAL]
  [OPTIMIZE (MANUAL | EVERY "interval-string" | AUTO_DAILY)]
)
[PARALLEL n]
[UNUSABLE];
```

# 2. CREATE SEARCH INDEXの動作ルール

CREATE SEARCH INDEXの動作ルールは以下のようになっています。

・INDEXを作成する列がJSON型で、ユーザーがFOR JSON句を省略すると、FOR JSON設定が自動的に選択されます。

・INDEXを作成する列にIS JSONチェック制約があり、ユーザーがFOR JSON句を省略すると、FOR JSON設定が自動的に選択されます。

・INDEXを作成する列がTRANSPORTABLE BINARY XMLのXMLTypeで、ユーザーがFOR XML句を省略すると、FOR XML設定が自動的に選択されます。

・INDEXを作成する列がTRANSPORTABLE BINARY XMLのXMLType、JSON型またはIS JSONチェック制約がある列のいずれかの場合、FOR TEXT句を追加することでユーザーは設定をオーバーライドし、「テキスト索引」設定を指定できます。
(つまり、全文索引を作成します。)

・INDEXを作成する列が上記以外の場合、FOR TEXT設定が自動的に選択されます。
（FOR TEXTの指定は任意）

# 3. CREATE SEARCH INDEXを使用したサーチ・インデックスの作成例
＜テキスト・サーチ・インデックス＞
この例では、test_table_text表のtext_data列（VARCHAR2型）にテキスト・サーチ・インデックスを作成します。
```
SQL> CREATE TABLE test_table_text (
  2    id        NUMBER,
  3    text_data VARCHAR2(1000)
  4  );

Table created.

SQL> CREATE SEARCH INDEX search_idx_text ON test_table_text(text_data);

Index created.

SQL> 
```
＜JSONサーチ・インデックス＞
この例では、test_table_json表のjson_data列（JSON型）にJSONサーチ・インデックスを作成します。
```
SQL> CREATE TABLE test_table_json (
  2    id        NUMBER,
  3    json_data JSON
  4  );

Table created.

SQL> CREATE SEARCH INDEX search_idx_json ON test_table_json(json_data) FOR JSON;

Index created.

SQL> 
```
＜XMLサーチ・インデックス＞
この例では、test_table_xml表のxml_data列（XMLType型）にXMLサーチ・インデックスを作成します。
```
SQL> CREATE TABLE test_table_xml (
  2    id       NUMBER, 
  3    xml_data XMLType
  4  )
  5  XMLTYPE xml_data STORE AS TRANSPORTABLE BINARY XML;

Table created.

SQL> CREATE SEARCH INDEX search_idx_xml ON test_table_xml(xml_data) FOR XML
  2  PARAMETERS ('SEARCH_ON TEXT');

Index created.

SQL>
```

# 参考情報
・[Oracle Text SQL Statements and Operators:CREATE SEARCH INDEX](https://docs.oracle.com/en/database/oracle/oracle-database/23/ccref/oracle-text-SQL-statements-and-operators.html#GUID-10013D4C-AB6A-44EB-B8B3-AD318EDE33A9)