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

はじめに

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

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

＜テキスト・データ＞

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ドキュメント＞

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ドキュメント＞

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