検索最適化サービス(Search Optimization Service)の使い方

ドキュメントを読んで使ったことが無い機能に詳しくなるシリーズ。
今回は検索最適化サービス(Search Optimization Service)。

他のドキュメントと比較して英語・日本語ともに文体が異なる気がする。
日本語訳が変なのではなく、元の英文の文体が違う。

まず、”クラスタリング”とか”Materialized View”が構造の名称である一方で、
“検索最適化”が目的の名称となっている点に違和感を感じる。
似て非なる索引(index)とは違うのだよ、という何かなのだろうか…

検索最適化サービスの概要

検索最適化サービスは、1つまたは少数の異なる行のみを返す「ポイントルックアップクエリ」。むむ?。
要は、巨大なテーブルに対するSELECT文の結果が数行となるようなケースを高速化する。

例えば、あるテーブルのageというカラムに対する等価条件を設定したクエリを速くしたい場合、
あらかじめ高速化用のデータ構造を構築し、ageに対する等価条件を使ったクエリを高速化する。

等価条件検索、範囲条件検索、部分文字列検索に加え、
プレビュー機能として、正規表現検索、半構造化型値検索、地理空間関数検索 が用意される。

他の高速化技術との比較

高速化機能として、他にクラスタリング、Materialized Viewがあるが、それぞれ機序が異なる。
クラスタリングは、マイクロパーティションの分散と枝刈りを利用した方法であって、
ナチュラルキーよりも高いプルーニング効率が得られるケースに適用する。

Materialized Viewは、同一テーブルに対して複数のクラスタリングキーを透過的に適用する
用途で用いられる。もちろん直アクセスでは遅い場所の事実上のキャッシュとしても使う。

検索最適化サービスは、ある意味、RDBMSの索引チックな使い方ができる。
良く使うカラムと検索述語を「アクセスキー」として登録し、高速検索用の索引を構築する。

検索最適化サービスの構成に必要な権限

以下の権限が必要。

• テーブルの所有権
• テーブルを含むスキーマに対する ADD SEARCH OPTIMIZATION 権限

検索最適化サービスを適用したテーブルにアクセスするには普通にSELECTできれば良い。

検索最適化サービスで未サポートのもの

以下に対して検索最適化サービスを適用することはできない。
(逆に以下以外なら適用できる。)

• 外部テーブル
• マテリアライズドビュー
• COLLATE 句 で定義された列
• 列の連結
• 分析表現
• テーブル列にキャスト（文字列にキャストされる固定小数点数を除く）

Materialized Viewは外部テーブルアクセスを高速化できるが、
検索最適化は外部テーブル、Materialized Viewを対象に出来ない。

何かの事情でCOLLATE句を指定した場合、その列は使用できない。

述語を指定する際に、定数側の明示的キャストはサポートされるが、
列側をキャストすると効かなくなる。例えば以下のような違いがある。
ただし、NUMBERからVARCHARへのキャストだけは機能する。

-- 機能する
SELECT hoge FROM table as t1 WHERE timestamp1 = '2020-01-01'::date;
-- 機能する
SELECT hoge FROM table as t1 WHERE cast(numeric_column as varchar) = '2'

-- 機能しない
SELECT hoge FROM table as t1 WHERE to_date(varchar_column) = '2020-01-01';

(分析表現って何!?)

検索最適化サービスの構成方法

ALTER TABLE と ADD SEARCH OPTIMIZATION を使用する。
テーブル全体(つまり全カラム)に対して適用できるか、カラムを指定することもできる。
カラムを指定する場合、ON句に最適化したい検索述語等を指定する。

例えば以下であればc1,c2列を使用した等価述語が最適化される。
c1列、c2列に対して等価述語(=)を使ったSQLが高速化される。

-- t1テーブルの c1,c2 の等価述語に対して検索最適化を適用
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1,c2);

-- クエリ
SELECT hoge FROM t1 where c1 = 100 and c2 = 200;

検索最適化サービスが適用済みか確認する方法

DESCRIBE により該当テーブルへの検索最適化サービスの適用状況を確認できる。
定義した「検索アクセスパス」の詳細と、有効無効が表示される。

DESCRIBE SEARCH OPTIMIZATION ON t1;

+---------------+----------+--------+------------------+--------+
| expression_id |  method  | target | target_data_type | active |
+---------------+----------+--------+------------------+--------+
| 1             | EQUALITY | C1     | NUMBER(38,0)     | true   |
+---------------+----------+--------+------------------+--------+

テーブル全体に対して検索最適化サービスを適用していた場合、
列の追加により、追加した列に対して自動的に検索最適化サービスが適用される。
逆に列の削除により、削除した列が検索最適化サービスから除外される。

列のデフォルト値を無効にすると、検索最適化サービスが「無効」になる。
一度「無効」になってしまったら、SEARCH OPTIMIZATION を一度 DROP し、
再度 ADD しなければならない。

検索最適化サービスのコスト

以下のコストがかかる。

• ストレージリソース
• コンピューティングリソース

ストレージリソース

検索アクセスパスのためのデータ構造を構築するために使われる。
通常、元テーブルの1/4ぐらいのサイズになるらしい。

ただし、テーブル内の個別の値の数が多ければ多いほどストレージを消費する。
例えば、全カラムを対象としている場合、全カラムの値が全て異なるという状況は最悪で、
元テーブルと同じだけのストレージを消費してしまう。

コンピューティングリソース

検索最適化を維持するためにコンピューティングリソースが使われる。
テーブル内で大量のデータが変更される場合、リソースの消費量が激しくなる。
追加・変更に比例して大きくなる。削除は若干使用する。

ざっくり以下に比例してコストが増加する。

• この機能が有効になっているテーブルの数、およびそれらのテーブル内の個別の値の数。
• これらのテーブルで変更されるデータの量

SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS 関数によりコストを見積もれる。

-- テーブルに検索最適化を追加する場合の推定コスト
select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITHOUT_SEARCH_OPT');

+---------------------------------------------------------------------------+
| SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITHOUT_SEARCH_OPT')     |
|---------------------------------------------------------------------------|
| {                                                                         |
|   "tableName" : "TABLE_WITHOUT_SEARCH_OPT",                               |
|   "searchOptimizationEnabled" : false,                                    |
|   "costPositions" : [ {                                                   |
|     "name" : "BuildCosts",                                                |
|     "costs" : {                                                           |
|       "value" : 11.279,                                                   |
|       "unit" : "Credits"                                                  |
|     },                                                                    |
|     "computationMethod" : "Estimated",                                    |
|     "comment" : "estimated via sampling"                                  |
|   }, {                                                                    |
|     "name" : "StorageCosts",                                              |
|     "costs" : {                                                           |
|       "value" : 0.070493,                                                 |
|       "unit" : "TB"                                                       |
|     },                                                                    |
|     "computationMethod" : "Estimated",                                    |
|     "comment" : "estimated via sampling"                                  |
|   }, {                                                                    |
|     "name" : "MaintenanceCosts",                                          |
|     "costs" : {                                                           |
|       "value" : 30.296,                                                   |
|       "unit" : "Credits",                                                 |
|       "perTimeUnit" : "MONTH"                                             |
|     },                                                                    |
|     "computationMethod" : "Estimated",                                    |
|     "comment" : "Estimated from historic change rate over last ~11 days." |
|   } ]                                                                     |
| }                                                                         |
+---------------------------------------------------------------------------+

-- すでに検索最適化が行われているテーブルに対するこの関数の出力
select SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITH_SEARCH_OPT');
+---------------------------------------------------------------------------+
| SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS('TABLE_WITH_SEARCH_OPT')        |
|---------------------------------------------------------------------------|
| {                                                                         |
|   "tableName" : "TABLE_WITH_SEARCH_OPT",                                  |
|   "searchOptimizationEnabled" : true,                                     |
|   "costPositions" : [ {                                                   |
|     "name" : "BuildCosts",                                                |
|     "computationMethod" : "NotAvailable",                                 |
|     "comment" : "Search optimization is already enabled."                 |
|   }, {                                                                    |
|     "name" : "StorageCosts",                                              |
|     "costs" : {                                                           |
|       "value" : 0.052048,                                                 |
|       "unit" : "TB"                                                       |
|     },                                                                    |
|     "computationMethod" : "Measured"                                      |
|   }, {                                                                    |
|     "name" : "Benefit",                                                   |
|     "computationMethod" : "NotAvailable",                                 |
|     "comment" : "Currently not supported."                                |
|   }, {                                                                    |
|     "name" : "MaintenanceCosts",                                          |
|     "costs" : {                                                           |
|       "value" : 30.248,                                                   |
|       "unit" : "Credits",                                                 |
|       "perTimeUnit" : "MONTH"                                             |
|     },                                                                    |
|     "computationMethod" : "EstimatedUpperBound",                          |
|     "comment" : "Estimated from historic change rate over last ~11 days." |
|   } ]                                                                     |
| }                                                                         |
+---------------------------------------------------------------------------+

まとめ

検索最適化サービスについてドキュメントを読んでいくつか試してみた。
ドキュメントにある「ポイントルックアップクエリ」という言葉が機能を最も適切に表していると思う。
高速化したいクエリが明確な時に使えるのかもしれない。