検索最適化サービスの使用¶
検索最適化サービスは、フィルタリングに広範な述語セットを使用する特定の型のルックアップおよび分析クエリのパフォーマンスを大幅に向上させることができます。
検索最適化を使用した場合と使用しない場合の実行時間を比較するチュートリアルを開始するには、 検索最適化を始めるにあたり に移動します。
このトピックの内容:
検索最適化サービスについて¶
検索最適化サービスは、テーブルに対する次を含む特定の型のクエリパフォーマンスを大幅に向上させることを目的としています。
テーブルに対する選択的なポイントルックアップクエリ。ポイントルックアップクエリは、1つまたは少数の異なる行のみを返します。使用例は次のとおりです。
非常に選択的なフィルターを備えた重要なダッシュボードにおいて、高速な応答時間を必要とするビジネスユーザー。
大量のデータを探索し、データの特定のサブセットを探しているデータサイエンティスト。
フィルタリング述語の広範なセットに基づいて、結果の小さなセットを取得するデータアプリケーション。
VARIANT、 OBJECT、および ARRAY 列のフィールドで、特定の型の述語を使用するクエリ。
等価述語。
IN述語。
ARRAY_CONTAINS を使用する述語。
ARRAYS_OVERLAP を使用する述語。
部分文字列および正規表現述語。
NULL 値をチェックする述語。
GEOGRAPHY 値を持つ選択された地理空間関数を使用するクエリ。
検索最適化サービスを利用できるクエリを特定したら、それらのクエリで使用される列とテーブルの検索最適化を構成できます。
検索最適化サービスが機能する方法¶
ポイントルックアップのパフォーマンスを向上させるために、検索最適化サービスは、 検索アクセスパス と呼ばれる永続的なデータ構造を作成および維持します。検索アクセスパスは、テーブルの各列のどの値が、その マイクロパーティション で見つかるかを追跡し、テーブルをスキャンするときに一部をスキップできるようにします。
メンテナンスサービスは、検索アクセスパスの作成と維持を担当します。
テーブルに検索最適化を構成すると、メンテナンスサービスにより、検索を実行するために必要なデータが検索アクセスパスに作成され、入力されます。
テーブルのサイズによっては、データの入力プロセスに非常に多くの時間が必要になる場合があります。メンテナンスサービスはこの作業をバックグラウンドで実行し、テーブルにおけるいずれの同時操作も阻害しません。
テーブル内のデータが更新されると(たとえば、新しいデータセットのロードや DML 操作によって)、メンテナンスサービスは自動的に検索アクセスパスを更新し、データへの変更を反映します。
検索アクセスパスがまだ更新されていないときにクエリを実行すると、クエリの実行が遅くなる可能性がありますが、それでも最新の結果が返されます。
各テーブルのメンテナンスサービスの進行状況は、 SHOW TABLES の出力の search_optimization_progress
列に表示されます。新しく最適化されたテーブルでの検索最適化のパフォーマンス向上を測定する前に、テーブルは完全に最適化済みであるとこの列に表示されることを確認します。
この検索アクセスパスとメンテナンスサービスは、ユーザーに対して透過的です。検索アクセスパスを維持するサービスのウェアハウスを作成する必要はありません。
ただし、このサービスのストレージおよびコンピューティングリソースにはコストがかかることに注意してください。詳細については、 検索最適化サービスのコストの管理 (このトピック内)をご参照ください。
クエリのパフォーマンスを最適化するための他のソリューションの検討¶
検索最適化サービスは、クエリのパフォーマンスを最適化するさまざまな方法の1つです。関連するテクニックは次のとおりです。
クエリアクセラレーション。
テーブルのクラスタリング。
1つ以上のマテリアライズドビュー(クラスター化または非クラスター化)を作成します。
次のテーブルに示すように、これらにはそれぞれ異なる利点があります。
機能 |
サポートされているクエリ型 |
メモ |
---|---|---|
検索最適化サービス |
検索最適化サービスは、 サポートされているデータ型 のこれらの型を検索するパフォーマンスを向上させることができます。 |
|
フィルターまたは集計のあるクエリ。クエリに LIMIT が含まれる場合、クエリには ORDER BY も含まれる必要があります。
フィルターは高度に選択的である必要があり、 ORDER BY 句のカーディナリティは低くする必要があります。
クエリアクセラレーションは、アドホック分析、予測不可能なデータ量のクエリ、
また、大規模なスキャンと選択的フィルターを使用したクエリとの作業に適しています。
|
クエリアクセラレーションと検索最適化は補完的です。どちらも同じクエリを高速化できます。 クエリアクセラレーションとの互換性 をご参照ください。 |
|
注: パフォーマンスを向上させることができるのは、マテリアライズドビューに含まれる行と列のサブセットについてのみです。 |
マテリアライズドビューを使用して、同じソーステーブル(またはそのテーブルのサブセット)に異なるクラスタリングキーを定義するか、フラット化された JSON またはバリアントデータを格納して、フラット化が1回だけで済むようにすることもできます。 |
|
注: テーブルは、1つ以上の列または式を含むことができる単一のキーでのみクラスター化できます。 |
次の表は、これらの3つの最適化のうちどれがストレージまたはコンピューティングコストを持つかを示しています。
ストレージコスト |
コンピューティングコスト |
|
---|---|---|
検索最適化サービス |
✔ |
✔ |
Query Acceleration Service |
✔ |
|
マテリアライズドビュー |
✔ |
✔ |
テーブルのクラスタリング |
✔ 1 |
✔ |
- 1
再クラスタリングのプロセスでは、既存のパーティションが新しいパーティションに書き換えられるため、 Fail-safe ストレージのサイズが増加する可能性があります。(このプロセスでは、新しい行は導入されないことに注意してください。これは既存の行を再編成するだけです。)詳細については、 再クラスタリングのクレジットおよびストレージへの影響 をご参照ください。
検索最適化サービスに必要なアクセス制御権限¶
テーブルの検索最適化を追加、構成、または削除するには、次の権限が必要です。
テーブルに対する OWNERSHIP 権限が必要。
テーブルを含むスキーマに対する ADD SEARCH OPTIMIZATION 権限が必要。
GRANT ADD SEARCH OPTIMIZATION ON SCHEMA <schema_name> TO ROLE <role>
クエリに検索最適化サービスを使用するには、テーブルに対する SELECT 権限が必要。
その他の追加の権限は必要ありません。SEARCH OPTIMIZATION はテーブルプロパティであるため、テーブルのクエリ時に自動的に検出され、(適切な場合は)使用されます。
検索最適化から恩恵を受けるテーブルおよび列の特定¶
検索最適化によりクエリのパフォーマンスが最も向上するのは、プライマリクラスターキー以外の列でテーブルが頻繁にクエリされる場合です。
検索最適化サービスは現在、次のデータ型に対する 特定の型のクエリ をサポートしています。
固定小数点数(例: INTEGER、NUMERIC)
DATE、 TIME、 TIMESTAMP
VARCHAR
BINARY
VARIANT、 OBJECT、および ARRAY。
GEOGRAPHY。
現在、検索最適化サービスは、浮動小数点データ型、GEOMETRY、または上記以外のデータ型をサポートしていません。Snowflakeには、将来さらに多くのデータ型のサポートが追加される可能性があります。
検索最適化サービスも照合をサポートしていません。
検索最適化によるメリットを受けるクエリの特定¶
検索最適化によりクエリのパフォーマンスが最も向上するのは、次の型のクエリです。
通常、数秒以上実行されるクエリ(検索最適化の適用前)。ほとんどの場合、検索最適化によって、実行時間が1秒未満のクエリのパフォーマンスが大幅に向上することはありません。
クエリフィルター操作を介してアクセスされる列の少なくとも1つに、少なくとも100,000から200,000個の個別の値があるクエリ。
個別の値の数を決定するには、次のいずれかを使用できます。
APPROX_COUNT_DISTINCT
を使用して、個別の値の概算数を取得します。select approx_count_distinct(column1) from table1;
COUNT(DISTINCT <列名>)
を使用して、個別の値における実際の数を取得します。select count(distinct c1), count (distinct c2) from test_table;
個別の値の概算数のみが必要なため、
COUNT(DISTINCT <列名>)
より高速で安価なAPPROX_COUNT_DISTINCT
の使用を検討してください。
検索最適化により、これらの型のクエリパフォーマンスを向上させることができます
部分文字列および正規表現¶
検索最適化サービスにより、テキストまたは半構造化データで部分文字列を検索する述語や正規表現を使用する述語を使用して、クエリのパフォーマンスを向上させることができます。(半構造化データにおける部分文字列検索のしくみの詳細については、 VARIANT 型の部分文字列検索 をご参照ください。)
この機能には、次を使用する述語が含まれます。
SPLIT_PART (等価述語内)
注釈
ON 句を使用した ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで特定の列に対してこの機能を有効にする必要があります。(テーブルレベルでの検索最適化を有効にしても、部分文字列検索は最適化されません。)例:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(mycol);
ON 句を省略しても、部分文字列検索は最適化されません。
検索最適化サービスにより、5文字以上の長さの部分文字列を検索する際のパフォーマンスを向上させることができます。(部分文字列をより選択的にすると、パフォーマンスが向上する可能性があります。)検索最適化サービスは、次の述語に対して検索アクセスパスを使用 しません。部分文字列が5文字より短いためです。
LIKE '%TEST%'
次の述語の場合、検索最適化サービスにより、検索アクセスパスを使用して SEARCH
と OPTIMIZED
の部分文字列を検索し、このクエリを最適化できます。ただし、部分文字列が5文字より短いため、 IS
には検索アクセスパスは使用されません。
LIKE '%SEARCH%IS%OPTIMIZED%'
テキストに対して RLIKE、 REGEXP、および REGEXP_LIKE を使用するクエリの場合、
subject
引数は、検索最適化が有効になっているテーブルの TEXT 列にする必要があります。pattern
引数は文字列定数にする必要があります。
正規表現の場合、検索最適化サービスは次のときに最適に機能します。
パターンには、5文字以上の部分文字列リテラルが少なくとも1つ含まれている。
パターンは、部分文字列が少なくとも1回出現する必要があることを指定する。
たとえば、次のパターンは、 string
が件名に1回以上出現する必要があることを指定します。
RLIKE '(string)+'
検索最適化サービスにより、次のパターンを使用するクエリのパフォーマンスを向上させることができます。これは、各述語が、5文字以上の部分文字列が少なくとも1回出現する必要があることを指定しているためです。(最初の例では、バックスラッシュ文字のエスケープを避けるために、 ドル引用符の文字列定数 を使用していることに注意してください。)
RLIKE $$.*email=[\w\.]+@snowflake\.com.*$$RLIKE '.*country=(Germany|France|Spain).*'RLIKE '.*phone=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
対照的に、検索最適化サービスは、次のパターンのクエリに対して検索アクセスパスを使用しません。
部分文字列のないパターン:
RLIKE '.*[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
5文字より短い部分文字列のみを含むパターン:
RLIKE '.*tel=[0-9]{3}-?[0-9]{3}-?[0-9]{4}.*'
1つのオプションが5文字より短い部分文字列である代替演算子を使用するパターン:
RLIKE '.*(option1|option2|opt3).*'
部分文字列がオプションであるパターン:
RLIKE '.*[a-zA-z]+(string)?[0-9]+.*'
部分文字列リテラルが5文字より短くても、正規表現を展開すると5文字以上の部分文字列リテラルが生成される場合には、検索最適化サービスによりクエリのパフォーマンスを向上させることができます。
たとえば、次のパターンを考慮します。
.*st=(CA|AZ|NV).*(-->){2,4}.*
この例では、
部分文字列リテラル(例:
st=
、CA
など)は5文字より短いものの、検索最適化サービスは、部分文字列st=CA
、st=AZ
、またはst=NV
(それぞれ5文字の長さ)がテキストに出現する必要があることを認識します。同様に、部分文字列リテラル
-->
が5文字より短い場合でも、検索最適化サービスは、部分文字列-->-->
(5文字より長い)をテキストに出現する必要があると判断します。
検索最適化サービスは、検索アクセスパスを使用してこれらの部分文字列を照合できるため、クエリのパフォーマンスを向上させることができます。
VARIANT 列のフィールド¶
検索最適化サービスにより、Snowflakeテーブルの半構造化データ(VARIANT、 OBJECT、および ARRAY 列 のデータ)に対するポイントルックアップと部分文字列のクエリのパフォーマンスを向上させることができます。
検索最適化サービスの VARIANT サポートがテーブル内の列に対して構成されている場合、検索最適化サービスは自動的に VARIANT、 OBJECT、および ARRAY 列を検索アクセスパスに含めます。これは、構造が深くネストされ、構造が頻繁に変更される列にも当てはまります。また、半構造化列内の特定のフィールドに対する検索最適化を有効にすることもできます。
注釈
ON 句を使用した ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで 特定の列に対してこの機能を明示的に有効にする必要があります。(テーブルレベルで検索最適化を有効にしても、 VARIANT 列では有効になりません。)例:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON EQUALITY(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON SUBSTRING(myvariantcol);
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON SUBSTRING(c4:user.uuid);
ON 句を省略すると、 VARIANT 列に対するクエリは最適化されません。
次のセクションでは、このサポートについて詳しく説明します。
VARIANT 型の述語の定数およびキャストでサポートされるデータ型¶
検索最適化サービスにより、次の型が定数に使用され、要素に 暗黙的または明示的なキャスト が使用される 半構造化データのポイントルックアップ のパフォーマンスを向上させることができます。
FIXED (有効な精度とスケールを指定するキャストを含む)
INTEGER
TEXT
DATE (スケールを指定するキャストを含む)
TIME (スケールを指定するキャストを含む)
TIMESTAMP、 TIMESTAMP_LTZ、 TIMESTAMP_NTZ、 TIMESTAMP_TZ (スケールを指定するキャストを含む)
検索最適化サービスは、以下を使用した型のキャストをサポートしています。
VARIANT 値キャストを TEXT としてサポート¶
検索最適化サービスにより、 VARIANT 列が TEXT にキャストされ、 TEXT にキャストされる定数と比較されるポイントルックアップのパフォーマンスも向上させることができます。
たとえば、 src
が、 VARIANT に変換されたブール値、日付、時刻の値を含む VARIANT 列であるとします。
create or replace TABLE test_table
(
ID INTEGER,
SRC VARIANT
);
insert into test_table select 1, to_variant('true'::boolean); -- BOOLEAN
insert into test_table select 2, to_variant('2020-01-09'::date); -- DATE
insert into test_table select 3, to_variant('01:02:03.899213'::time); -- TIME
このテーブルの場合は、検索最適化サービスにより、 VARIANT 列を TEXT にキャストし、列を文字列定数と比較する次のクエリを向上させることができます。
select * from test_table where src::TEXT = 'true';
select * from test_table where src::TEXT = '2020-01-09';
select * from test_table where src::TEXT = '01:02:03.899213';
VARIANT 型のポイントルックアップでサポートされる述語¶
検索最適化サービスにより、以下にリストされている述語の型を使用してポイントルックアップクエリを向上させることができます。以下の例では、 src
は VARIANT 列であり、 path_to_variant_field
は VARIANT 列 のフィールドへのパスです。
次の形式の等価述語:
where path_to_variant_field[::target_data_type] = constant
target_data_type
(指定されている場合)、およびconstant
のデータ型は、 上記 のサポートされている型のいずれかでなければなりません。::
は、 値を特定の型にキャストするためのサポートされている方法 の1つの例にすぎないことに注意してください。たとえば、検索最適化サービスは以下をサポートしています。
要素を明示的にキャストせずに、要素を NUMBER 定数と一致させる。
where src:person.age = 42;
指定された精度とスケールで、要素を NUMBER に明示的にキャストする。
where src:location.temperature::NUMBER(8, 6) = 23.456789;
要素を明示的にキャストせずに、要素を TEXT 定数と一致させる。
where src:sender_info.ip_address = '123.123.123.123';
要素を TEXT に明示的にキャストする。
where src:salesperson.name::TEXT = 'John Appleseed';
要素を DATE に明示的にキャストする。
where src:events.date::DATE = '2021-03-26';
指定されたスケールで要素を TIME に明示的にキャストする。
where src:events.time_info::TIME(6) = '01:02:03.456789';
指定されたスケールで要素を TIMESTAMP に明示的にキャストする。
where src:event_logs.exceptions.timestamp_info(3) = '2021-03-26 15:00:00.123 -0800';
次のような ARRAY 関数を使用する述語:
where ARRAY_CONTAINS(constant::VARIANT, path_to_variant_field)
constant
は NULL であってはならず、constant
のデータ型は、 上記 のサポートされている型のいずれかでなければなりません。::
は、 値を特定の型にキャストするためのサポートされている方法 の1つの例にすぎないことに注意してください。例:
where ARRAY_CONTAINS('77.146.211.88'::VARIANT, src:logs.ip_addresses)
where ARRAYS_OVERLAP(ARRAY_CONSTRUCT(constant_1, constant_2, .., constant_N), path_to_variant_field)
各定数のデータ型(
constant_1
、constant_2
など)は、 上記 のサポートされている型のいずれかでなければなりません。構築された ARRAY には、NULL 定数を含めることができます。例:
where ARRAYS_OVERLAP( ARRAY_CONSTRUCT('122.63.45.75', '89.206.83.107'), src:senders.ip_addresses)
NULL 値をチェックする次の述語:
where IS_NULL_VALUE(path_to_variant_field)
IS_NULL_VALUE は JSON null値に適用され、 SQL NULL 値には適用されないことに注意してください。
where path_to_variant_field IS NOT NULL
where variant_column IS NULL
variant_column
は、半構造化データ内の要素へのパスではなく、列を参照します。たとえば、検索最適化サービスは VARIANT 列
src
の使用をサポートしていますが、その VARIANT 列にあるフィールドsrc:person.age
へのパスはサポートしていません。
VARIANT 型の部分文字列検索¶
検索最適化サービスは、 半構造化列 (ARRAY、 OBJECT、および VARIANT 列を含む)、またはそのような列のフィールドで、 ワイルドカードまたは正規表現検索 を最適化することができます。これには、以下を使用する述部が含まれます。
SPLIT_PART (等価述語内)
列または列内の複数のフィールドに対して部分文字列検索の最適化を有効にすることができます。たとえば、以下のステートメントは、列内のネストされたフィールドの部分文字列検索の最適化を有効にします。
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
検索アクセスパスが構築されると、以下のクエリを最適化することができます。
SELECT * FROM test_table WHERE col2:data.search LIKE '%optimization%';
ただし、以下のクエリは、検索最適化を有効にする際に指定したフィールドにないため、最適化されません。
SELECT * FROM test_table WHERE col2:name LIKE '%john%doe%';
SELECT * FROM test_table WHERE col2 LIKE '%hello%world%';
最適化するフィールドは複数指定できます。ここでは、列 col2
にある2つの特定のフィールドに対して検索最適化が有効になっています。
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:name);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
指定したフィールドの検索最適化を有効にすると、すべてのサブフィールドで検索最適化が有効になります。最初のステートメントは、ネストされた search
フィールドを含む、 data
フィールド全体の検索最適化を有効にするため、以下の2番目の ALTER TABLE ステートメントは冗長です。
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data);
ALTER TABLE test_table ADD SEARCH OPTIMIZATION ON SUBSTRING(col2:data.search);
同様に、列全体の検索最適化を有効にすると、その列のすべての部分文字列検索が最適化されます。
VARIANT 部分文字列検索における定数の評価方法
クエリ内の定数文字列(例: LIKE 'constant_string'
)を評価する際、検索最適化サービスは以下の文字を区切り文字として文字列をトークンに分割します。
角括弧(
[
および]
)。中括弧(
{
および}
)。コロン(
:
)。コンマ(
,
)。二重引用符(
"
)。
文字列をトークンに分割した後、検索最適化サービスは5文字以上のトークンだけを考慮します。
述語の例 |
検索最適化サービスがクエリを処理する方法 |
---|---|
|
検索最適化サービスは、次の述語に対して検索アクセスパスを 使用しません。部分文字列が5文字より短いためです。 |
|
検索最適化サービスはこのクエリを最適化することができます。検索アクセスパスを使用して、 |
|
検索最適化サービスはこのクエリを最適化することができます。検索アクセスパスを使用して |
|
検索最適化サービスはこの文字列を |
|
検索最適化サービスはこれを |
|
検索最適化サービスはこの文字列を |
VARIANT 型のサポートにおける現在の制限事項¶
現在、検索最適化サービスにおける VARIANT 型のサポートには、次の制限があります。
XMLGET を使用する述語はサポートされていません。
形式
variant_field IS NULL
の述語はサポートされていません。定数がスカラーサブクエリの結果である述語はサポートされていません。
サブ要素を含む要素へのパスを指定する述語はサポートされていません。
検索最適化サービスの現在の制限 もこの機能に適用されます。
地理空間関数¶
検索最適化サービスにより、 GEOGRAPHY オブジェクトで地理空間関数を使用する述語を使用するクエリのパフォーマンスを向上させることができます。
注釈
ON 句を使用した ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで特定の列に対してこの機能を有効にする必要があります。例:
ALTER TABLE mytable ADD SEARCH OPTIMIZATION ON GEO(mygeocol);
ON 句を省略した場合、地理空間関数を使用するクエリは改善されません。
次のセクションで詳細を説明します。
注釈
GEOMETRY オブジェクトはまだサポートされていません。
地理空間関数でサポートされている述語¶
次の関数を使用する述語を含むクエリの場合、
検索最適化サービスにより、次の場合にパフォーマンスを向上させることができます。
1つの入力式がテーブルの GEOGRAPHY 列であり、
もう1つの入力式が GEOGRAPHY 定数(変換またはコンストラクター関数 を介して作成)。
ST_DWITHIN の場合、距離引数は負ではない REAL 定数。
この機能には、 検索最適化サービスと同様の制限が適用 されることに注意してください。
その他のパフォーマンスの考慮事項¶
検索最適化サービスは選択性の高い述語用に設計されており、述語は地理空間オブジェクト間の近接性によってフィルターされるため、テーブル内の近接性によって地理空間オブジェクトをクラスタリングすると、パフォーマンスが向上する可能性があります。ベーステーブルが頻繁に変更されるかどうかに応じて、データをロードするときに並べ替え順序を指定するか、自動クラスタリングを使用して、データをクラスタ化できます。
- ソート済みデータのロード
ベーステーブルのデータが頻繁に変更されない場合は、データをロードするときに並べ替え順序を指定できます。その後、GEOGRAPHY 列で検索の最適化を有効にできます。例:
CREATE TABLE new_table AS SELECT * FROM source_table ORDER BY st_geohash(geom); ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
ベースデータに大きな変更を加えるたびに、手動でデータを並べ替えることができます。
- 自動クラスタリング
ベーステーブルが頻繁に更新される場合は、 ALTER TABLE ... CLUSTER BY ... コマンドを使用して 自動クラスタリング を有効にし、テーブルが変更されると自動的に再クラスタリングされるようにします。
次の例では、タイプ VARCHAR の新しい列
geom_geohash
を追加し、その新しい列に GEOGRAPHY 列geom
のgeohashまたはH3インデックスを格納します。次に、新しい列をクラスタキーとして自動クラスタリングを有効にします。このアプローチは、変更されたテーブルの部分を自動的に再クラスター化します。CREATE TABLE new_table AS SELECT *, ST_GEOHASH(geom) AS geom_geohash FROM source_table; ALTER TABLE new_table CLUSTER BY (geom_geohash); ALTER TABLE new_table ADD SEARCH OPTIMIZATION ON GEO(geom);
地理空間関数を使用する例¶
次のステートメントは、このセクションの例で使用されるテーブルを作成および構成します。最後のステートメントは、 ON 句を ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで使用して、 g1
GEOGRAPHY 列の検索最適化を追加します。
CREATE OR REPLACE TABLE geospatial_table (id NUMBER, g1 GEOGRAPHY);
INSERT INTO geospatial_table VALUES
(1, 'POINT(-122.35 37.55)'),
(2, 'LINESTRING(-124.20 42.00, -120.01 41.99)'),
(3, 'POLYGON((0 0, 2 0, 2 2, 0 2, 0 0))');
ALTER TABLE geospatial_table ADD SEARCH OPTIMIZATION ON GEO(g1);
サポートされている述語の例¶
次のクエリは、検索最適化サービスでサポートされているクエリの例です。検索最適化サービスにより、検索アクセスパスを使用して、このクエリのパフォーマンスを向上させることができます。
SELECT id FROM geospatial_table WHERE
ST_INTERSECTS(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'));
以下は、検索最適化サービスでサポートされている追加の述語の例です。
...
ST_INTERSECTS(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
TO_GEOGRAPHY('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_CONTAINS(
g1,
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'))
...
ST_WITHIN(
TO_GEOGRAPHY('{"type" : "MultiPoint","coordinates" : [[-122.30, 37.55], [-122.20, 47.61]]}'),
g1)
...
ST_WITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_COVERS(
TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'),
g1)
...
ST_COVERS(
g1,
TO_GEOGRAPHY('POINT(0 0)'))
...
ST_COVEREDBY(
TO_GEOGRAPHY('POLYGON((1 1, 2 1, 2 2, 1 2, 1 1))'),
g1)
...
ST_COVEREDBY(
g1,
TO_GEOGRAPHY('POINT(-122.35 37.55)'))
...
ST_DWITHIN(
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1,
100000)
...
ST_DWITHIN(
g1,
TO_GEOGRAPHY('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
100000)
GEOGRAPHY 定数の作成例¶
以下は、 GEOGRAPHY 定数に対してさまざまな 変換関数およびコンストラクター関数 を使用する述語の例です。
...
ST_INTERSECTS(
g1,
ST_GEOGRAPHYFROMWKT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'))
...
ST_INTERSECTS(
ST_GEOGFROMTEXT('POLYGON((0 0, 1 0, 1 1, 0 1, 0 0))'),
g1)
...
ST_CONTAINS(
ST_GEOGRAPHYFROMEWKT('POLYGON((-74.17 40.64, -74.1796875 40.58, -74.09 40.58, -74.09 40.64, -74.17 40.64))'),
g1)
...
ST_WITHIN(
ST_GEOGRAPHYFROMWKB('01010000006666666666965EC06666666666C64240'),
g1)
...
ST_COVERS(
g1,
ST_MAKEPOINT(0.2, 0.8))
...
ST_INTERSECTS(
g1,
ST_MAKELINE(
TO_GEOGRAPHY('MULTIPOINT((0 0), (1 1))'),
TO_GEOGRAPHY('POINT(0.8 0.2)')))
...
ST_INTERSECTS(
ST_POLYGON(
TO_GEOGRAPHY('SRID=4326;LINESTRING(0.0 0.0, 1.0 0.0, 1.0 2.0, 0.0 2.0, 0.0 0.0)')),
g1)
...
ST_WITHIN(
g1,
TRY_TO_GEOGRAPHY('POLYGON((-1 -1, -1 4, 4 4, 4 -1, -1 -1))'))
...
ST_COVERS(
g1,
ST_GEOGPOINTFROMGEOHASH('s00'))
サポートされている述語の接続詞(AND)¶
述語の論理積(つまり、AND)を使用するクエリでは、いずれかの述語が上記の条件に準拠している場合、検索最適化によってクエリのパフォーマンスが改善します。
たとえば、クエリに次のようなものがあるとします。
where condition_x and condition_y
いずれかの条件が個別に数行を返す場合(つまり、 condition_x
が数行を返す場合、 または condition_y
が数行を返す場合)は、検索最適化によってパフォーマンスが改善します。
condition_x
は数行を返すが、 condition_y
は多くの行を返す場合は、検索最適化によってクエリのパフォーマンスが向上する場合があります。
これらの追加の例 をご参照ください。
サポートされている述語の論理和(OR)¶
述語の論理和を使用するクエリ(つまり、OR)では、 すべての 述語が上記の条件に準拠している場合、検索最適化によってクエリのパフォーマンスが改善します。
たとえば、クエリに次のようなものがあるとします。
where condition_x or condition_y
各条件が個別に数行を返す場合(つまり、 condition_x
が数行を返し、 加えて condition_y
が数行を返す場合)は、検索最適化によってパフォーマンスが改善します。
condition_x
は数行を返すが、 condition_y
は多くの行を返す場合、検索最適化によってはクエリのパフォーマンスは向上 しません。
論理和の場合、分離された各述語はクエリ内で決定的にはなりません。他の述語は、検索最適化によってパフォーマンスが改善するかどうかを判断する前に評価する必要があります。
結合¶
検索最適化サービスは、結合のパフォーマンスを直接改善しません。ただし、テーブルで検索最適化が有効になっていて述語が選択的である場合は、結合の前にいずれかのテーブルから行をフィルタリングするパフォーマンスを改善できます。
両方のテーブルで検索最適化を有効にする必要はありません。検索最適化を使用するかどうかの決定は、テーブルごとに個別に行われます。
ビュー¶
検索最適化サービスにより、ビュー(セキュアビューを含む)のパフォーマンスを間接的に改善できます。ビューのベーステーブルで検索最適化が有効になっていて、クエリがそのテーブルに選択的述語を使用している場合、検索最適化サービスは、行をフィルタリングするときのパフォーマンスを改善できます。
ビュー内のすべてのテーブルで検索最適化を有効にする必要はありません。検索の最適化は、各テーブルで個別に実行されます。
マスキングポリシーおよび行アクセスポリシーを使用したテーブル¶
検索最適化サービスにより、マスキングポリシーと行アクセスポリシーを使用するテーブルのクエリパフォーマンスを向上させることができます。
注釈
検索の最適化が有効になっている場合、マスキングポリシーまたは行アクセスポリシーのために値を表示できないユーザーは、その値が存在するかどうかをより確実に推測できる可能性があります。検索の最適化の有無にかかわらず、クエリの待機時間の違いは、ポリシーによって制限されたデータの有無に関するヒントを提供できますが、状況によっては、この効果が検索の最適化によって拡大される可能性があります。
たとえば、行アクセスポリシーにより、ユーザーは country = US
を含む行にアクセスできませんが、データには country = US
を含む行が含まれていないとします。次に、 country
列に対して検索の最適化が有効になっており、ユーザーが WHERE country = US
でクエリを実行するとします。クエリは期待どおりに空の結果を返しますが、クエリは検索の最適化を使用しない場合よりも高速に実行される可能性があります。この場合、ユーザーは、クエリの実行にかかった時間に基づいて、データに country = US
の行が含まれていないと推測できます。
検索最適化サービスでサポートされていないクエリ¶
検索最適化サービスでは、次をサポートしていません。
外部テーブル。
マテリアライズドビュー。
COLLATE 句 で定義された列。
列の連結。
分析表現。
テーブル列にキャストします(文字列にキャストされる固定小数点数を除く)。
検索最適化は、定数値に対する暗黙的および明示的キャストを使用した述部をサポートしていますが、実際のテーブル列に値をキャストする述部はサポートしていません(INTEGER および NUMBER から VARCHAR へのキャストを除く)。
たとえば、次の述語は、定数値(テーブル列の値ではない)に対して暗黙的および明示的なキャストを使用するため、サポートされています。
-- Supported predicate -- (where the string '2020-01-01' is implicitly cast to a date) WHERE timestamp1 = '2020-01-01'; -- Supported predicate -- (where the string '2020-01-01' is explicitly cast to a date) WHERE timestamp1 = '2020-01-01'::date;
次の述語は、テーブル列の値に対するキャストを使用するため、サポートされていません。
-- Unsupported predicate -- (where values in a VARCHAR column are cast to DATE) WHERE to_date(varchar_column) = '2020-01-01';
検索最適化サービスは、キャスト後の値ではなく、元の列の値を考慮します。その結果、検索最適化サービスは、これらの述語を持つクエリには使用されません。
前述のように、このルールの例外は、テーブル列の NUMBER または INTEGER 値を VARCHAR 値にキャストする場合です。検索最適化サービスは、このタイプの述語をサポートします。
-- Supported predicate -- (where values in a numeric column are cast to a string) WHERE cast(numeric_column as varchar) = '2'
検索最適化はアクティブなデータに対してのみ機能するため、Time Travelを使用するクエリのパフォーマンスは、検索最適化によっては向上しません。
テーブルの検索最適化の構成¶
注釈
大きなテーブル(テラバイト(TB)以上のデータを含むテーブル)に検索最適化を追加すると、短期間にクレジット消費量が即座に増加する可能性があります。
テーブルに検索最適化を追加すると、メンテナンスサービスは、すぐにバックグラウンドでテーブルの検索アクセスパスの構築を開始します。テーブルが大きい場合、メンテナンスサービスがこの作業を大規模に並列化する可能性があり、その結果、短期間にコストが増加する可能性があります。
大きなテーブルに検索最適化を追加する前に、 これらのコストの見積もりを取得 して、何が起きるかを把握します。
検索最適化をテーブルに追加するには、ON 句の有無にかかわらず、 ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドを使用します。
ON 句を指定せずにコマンドを使用すると、検索アクセスパスが設定され、 サポートされているデータ型 を使用するすべての列に対して EQUALITY 検索方法を使用して、クエリのパフォーマンスを向上します。
他の型のクエリパフォーマンスを向上させたい場合、または検索最適化のために構成されている列をより詳細に制御する必要がある場合は、 ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドで ON 句 を使用します。
ADD SEARCH OPTIMIZATION の ON 句で、検索最適化を有効にする列を指定します。特定の列の検索最適化を有効にすると、検索方法も指定できます(たとえば、等価検索と IN 検索の場合は EQUALITY、GEOGRAPHY 検索の場合は GEO、部分文字列検索の場合は SUBSTRING)。
検索最適化のコストを管理するために、検索最適化を必要としない特定の列から検索最適化を削除できます。
次のセクションでは、テーブルの検索最適化を構成する方法について説明します。
特定の列に対する検索最適化の構成¶
特定の列の検索最適化を構成するには、 ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドを ON 句と合わせて使用します。
注釈
このコマンドを実行しているときは、 テーブルに検索最適化を追加する権限 を持つロールを使用します。
ON 句は、特定の列の検索最適化を構成することを指定します。構文の詳細については、 ALTER TABLE ... ADD SEARCH OPTIMIZATION のセクション をご参照ください。
注釈
テーブル内の該当するすべての列に等価および IN 述語の検索最適化を適用するだけの場合は、 テーブル全体に対する検索最適化の追加および維持 をご参照ください。
このコマンドを実行すると、 列が検索最適化用に構成されていることを確認できます。
次のセクションには、検索最適化の構成を指定する方法を示す例が含まれています。
例: 特定の列に対する等価および IN 述語のサポート¶
テーブル t1
内の列 c1
、 c2
、および c3
の等価述語を使用して検索を最適化するには、次のステートメントを実行します。
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2, c3);
ON 句で同じ検索方法を複数回指定することもできます。
-- This statement is equivalent to the previous statement.
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1), EQUALITY(c2, c3);
例: 該当するすべての列に対する等価および IN 述部のサポート¶
テーブル内の該当するすべての列に対して等価述語を使用して検索を最適化するには、次のステートメントを実行します。
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(*);
次の点に注意してください。
検索方法およびターゲットの構文の説明 で説明されているように、特定の方法では、アスタリスクと特定の列を指定することはできません。
ON 句を省略すると、テーブル内の該当するすべての列に対する等価および IN 述語の検索最適化も構成されますが、 ON 句の指定と省略には違いがあります。 テーブル全体に対する検索最適化の追加および維持 をご参照ください。
例: さまざまなタイプの述語のサポート¶
列 c1
および c2
の等価述語による検索と、列 c3
の部分文字列検索を最適化するには、次のステートメントを実行します。
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1, c2), SUBSTRING(c3);
例: VARIANT のフィールドに対する等価および IN 述語のサポート¶
VARIANT 列 c4
のフィールド user
にネストされた VARIANT フィールド uuid
の等価述語を使用して検索を最適化するには、次のステートメントを実行します。
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c4:user.uuid);
例: 地理空間関数のサポート¶
c1
列の GEOGRAPHY オブジェクトで地理空間関数を使用する述語で検索を最適化するには、次のステートメントを実行します。
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON GEO(c1);
テーブル全体に対する検索最適化の追加および維持¶
サポートされているデータ型のすべての列(VARIANT および GEOGRAPHY を除く)に EQUALITY を指定するだけの場合は、ON 句なしで ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドを使用します。
注釈
このコマンドを実行しているときは、 テーブルに検索最適化を追加する権限 を持つロールを使用します。
例:
alter table test_table add search optimization;
構文の詳細については、 ALTER TABLE にある検索最適化のセクション をご参照ください。
このコマンドを実行すると、 列が検索最適化用に構成されていることを確認できます。
後で追加された列への影響¶
ON 句なしで ALTER TABLE ... ADD SEARCH OPTIMIZATION コマンドを実行すると、その後テーブルに追加されるすべての列も EQUALITY 用に構成されます。
ただし、同じテーブルで ON 句のある ALTER TABLE ... { ADD | DROP } SEARCH OPTIMIZATION を指定すると、その後テーブルに追加される列は EQUALITY 用に自動では構成されません。これらの新しく追加された列を EQUALITY 用に構成するには、 ALTER TABLE ... ADD SEARCH OPTIMIZATION ON ... を実行する必要があります。
テーブルが検索最適化用に構成されていることの確認¶
テーブルとその列が検索最適化用に構成されていることを確認するには、
テーブルとその列の 検索最適化構成を表示 します。
SHOW TABLES コマンドを実行して、検索最適化が追加されていることを確認し、テーブルがどの程度最適化されているかを判断します。
例:
SHOW TABLES LIKE '%test_table%';
このコマンドの出力により、
SEARCH_OPTIMIZATION が
ON
であることを確認します。これは、検索最適化が追加されたことを示しています。SEARCH_OPTIMIZATION_PROGRESS の値を確認します。これは、これまでに最適化されたテーブルの割合を指定します。
検索最適化が最初にテーブルに追加されたとき、パフォーマンスの利点はすぐには現れません。検索最適化サービスは、バックグラウンドでデータの入力を開始します。メンテナンスがテーブルの現在の状態に追いつくにつれて、利点の増加が明らかになります。
クエリを実行して検索最適化が機能していることを確認する前に、テーブルが完全に最適化されたことを示すまで待ちます。
クエリを実行して、検索最適化が機能していることを確認します。
Snowflakeオプティマイザーは、特定のクエリに対して検索最適化サービスをいつ使用するかを自動的に選択することに注意してください。ユーザーは、どのクエリ検索最適化を使用するかについては制御できません。
検索最適化サービスが最適化するように設計されているクエリを選択します。 検索最適化から恩恵を受けるテーブルおよび列の特定 をご参照ください。
ウェブ UI で、このクエリのクエリプランを表示し、クエリノード「検索最適化アクセス」がクエリプランの一部であることを確認します。
テーブルの検索最適化構成の表示¶
テーブルの検索最適化構成を表示するには、 DESCRIBE SEARCH OPTIMIZATION コマンドを使用します。
たとえば、次のステートメントを実行して、列の検索最適化を構成するとします。
ALTER TABLE t1 ADD SEARCH OPTIMIZATION ON EQUALITY(c1);
DESCRIBE SEARCH OPTIMIZATION を実行すると、次の出力が生成されます。
DESCRIBE SEARCH OPTIMIZATION ON t1;
+---------------+----------+--------+------------------+--------+
| expression_id | method | target | target_data_type | active |
+---------------+----------+--------+------------------+--------+
| 1 | EQUALITY | C1 | NUMBER(38,0) | true |
+---------------+----------+--------+------------------+--------+
検索用に最適化されたテーブルの操作¶
検索最適化を使用するテーブルを操作する場合は、検索最適化サービスへの影響に注意する必要があります。
テーブルの変更¶
列のデフォルト値を変更すると、検索アクセスパスが無効になります。
検索アクセスパスが無効になった後に再度検索の最適化を使用するには、 SEARCH OPTIMIZATION プロパティをドロップ し、テーブルに SEARCH OPTIMIZATION プロパティを追加 しなおす必要があります。
列を追加、ドロップ、または名前変更した場合、検索アクセスパスは引き続き有効です。
特定の列を指定せずに、テーブル全体の検索最適化を有効にした場合、テーブルに列を追加すると、新しい列は検索アクセスパスに自動的に追加されます。ただし、列の検索最適化を有効にするときに ON 句を使用した場合、新しい列は自動的に追加されません。
テーブルから列をドロップすると、ドロップされた列は検索アクセスパスから自動的に削除されます。
列の名前を変更する場合、検索アクセスパスを変更する必要はありません。
テーブルをドロップすると、 SEARCH OPTIMIZATION プロパティと検索アクセスパスもドロップされます。次に注意してください。
テーブルのドロップを解除すると、テーブルのプロパティとして検索最適化がすぐに再確立されます。
テーブルをドロップすると、検索アクセスパスのデータ保持期間はテーブルと同じになります。
テーブルから SEARCH OPTIMIZATION プロパティをドロップ すると、検索アクセスパスが削除されます。テーブルに再度 SEARCH OPTIMIZATION プロパティを追加する 場合は、メンテナンスサービスのために検索アクセスパスを再作成する必要があります。(プロパティをアンドロップする方法なし。)
テーブル、スキーマ、またはデータベースのクローニング¶
テーブル、スキーマまたはデータベースをクローンすると、各テーブルの SEARCH OPTIMIZATION プロパティと検索アクセスパスもクローンされます。(テーブル、スキーマ、またはデータベースをクローニングすると、各テーブルとそれに対応する検索アクセスパスの ゼロコピークローン が作成される。)
CREATE TABLE ... LIKEを使用して、元のテーブルと同じ列を持つ新しい空のテーブルを作成する場合、 SEARCH OPTIMIZATION プロパティは新しいテーブルにコピーされないことに注意してください。
セカンダリデータベースのテーブルの操作(データベース複製のサポート)¶
プライマリデータベースのテーブルで SEARCH OPTIMIZATION プロパティが有効になっている場合、プロパティはセカンダリデータベースの対応するテーブルに複製されます。
セカンダリデータベースの検索アクセスパスは複製されませんが、代わりに自動的に再構築されます。このプロセスには、 検索最適化サービスのコストの管理 で説明されているのと同じ種類のコストが発生することに注意してください。
テーブルの共有¶
データプロバイダーは、 Secure Data Sharing を使用して、検索の最適化が有効になっているテーブルを共有できます。
共有テーブルをクエリする場合、データコンシューマーは、検索最適化サービスによるパフォーマンス向上のメリットを受けることができます。
検索最適化サービスのコストの管理¶
検索最適化サービスは、ストレージリソースとコンピューティングリソースの両方のコストに影響します。
ストレージリソース:検索最適化サービスにより、検索アクセスパスデータ構造を作成します。この構造には、検索最適化が有効になっている各テーブルにスペースが必要です。検索アクセスパスのストレージコストは、次のような複数の要因によって異なります。
テーブル内における個別の値の数(NDVs)。すべての列に検索アクセスパスを使用するデータ型があり、各列のすべてのデータ値が一意であるという極端な場合、必要なストレージは元のテーブルのサイズと同じ程度の大きさになる可能性があります。
ただし、通常、サイズは元のテーブルのサイズの約1/4です。
コンピューティングリソース:
テーブルに検索最適化を追加すると、最初のビルドフェーズ中にリソースが消費されます。
検索最適化サービスを維持するには、リソースも必要です。チャーンが多い場合(つまり、テーブル内で大量のデータが変更される場合)は、リソースの消費量が多くなります。これらのコストは、取り込まれた(追加または変更された)データの量にほぼ比例します。削除にもいくらかのコストがかかります。
自動クラスタリング は、検索最適化を使用してテーブル内のクエリの遅延を改善できますが、検索最適化のメンテナンスコストをさらに増加させる可能性があります。テーブルのチャーンレートが高い場合は、自動クラスタリングを有効にしてテーブルの検索最適化を構成すると、テーブルが検索最適化用に構成されている場合よりもメンテナンスコストが高くなる可能性があります。
Snowflakeは、実際に使用されたリソースに対してのみアカウントに請求することにより、効率的なクレジット使用状況を確保します。請求は1秒単位で計算されます。
コンピューティング時間あたりのコストについては、 Snowflakeサービス利用テーブル の「サーバーレス機能クレジットテーブル」をご参照ください。
検索最適化サービスを有効にすると、 サービスの使用料金を表示 できます。
ちなみに
Snowflakeは、この機能を徐々に始めて(最初は検索最適化をいくつかのテーブルのみに追加するなど)、コストと利点を注意深く監視することをお勧めします。
コストの見積もり¶
テーブルに検索最適化を追加し、検索最適化のために特定の列を構成するためのコストを見積もるには、 SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS 関数を使用します。
一般的に、コストは次の要素に比例します。
この機能が有効になっている列の数、およびそれらの列内にある個別の値の数。
これらのテーブルで変更されるデータの量。
重要
SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS 関数によって返されるコストの見積もりはベストエフォートです。実際の実現コストは、見積もられたコストとは最大50%(またはまれに数倍)異なる場合があります。
構築およびストレージのコストの見積もりは、テーブル内にある行のサブセットのサンプリングに基づいています。
メンテナンスコストの見積もりは、テーブル内の最近の作成、削除、更新アクティビティに基づいています。
コストの表示¶
ウェブインターフェイスまたは SQL を使用して、検索最適化サービスの請求コストを表示できます。 コンピューティングコストの調査 をご参照ください。
コストの削減¶
慎重に 検索最適化を有効にするテーブルおよび列を選択する ことにより、検索最適化サービスのコストを制御できます。
さらに、検索最適化サービスのコストを削減するには、
SnowflakeはテーブルでDML操作をバッチ処理することをお勧めします。
DELETE
:テーブルに最新の期間(例:最新の日、週、月)のデータが保存されている場合は、古いデータを削除してテーブルをトリミングする際に、検索最適化サービスで変更を考慮する必要があります。場合によっては、削除の頻度を減らすことでコストを削減できる場合があります(例: 1時間ごとではなく1日ごと)。INSERT
、UPDATE
、およびMERGE
:これらのタイプの DML ステートメントをテーブルでバッチ処理すると、検索最適化サービスにより、メンテナンスのコストを削減できます。
テーブル全体を再クラスタリングする場合は、再クラスタリングの前にそのテーブルの SEARCH OPTIMIZATION プロパティをドロップ し、再クラスタリング後、テーブルに SEARCH OPTIMIZATION プロパティを追加 しなおすことを検討してください。
部分文字列検索(
ON SUBSTRING(col)
)または VARIANTs(ON EQUALITY(variant_col)
)の検索最適化を有効にする前に、 SYSTEM$ESTIMATE_SEARCH_OPTIMIZATION_COSTS を呼び出してコストを見積もります。これらの検索方法の最初のビルドとメンテナンスは計算集約型になる可能性があるため、パフォーマンスとコストの間のトレードオフを評価する必要があります。
特定の列またはテーブル全体からの検索最適化の削除¶
特定の列に対する検索最適化構成を削除できます。または、テーブル全体から SEARCH OPTIMIZATION プロパティを削除できます。
特定の列に対する検索最適化のドロップ¶
特定の列の検索最適化構成をドロップするには、 ON 句のある ALTER TABLE ... DROP SEARCH OPTIMIZATION コマンドを使用します。
たとえば、 DESCRIBE SEARCH OPTIMIZATION コマンドを実行すると、次の式が出力されるとします。
DESCRIBE SEARCH OPTIMIZATION ON t1;
+---------------+-----------+-----------+-------------------+--------+
| expression_id | method | target | target_data_type | active |
+---------------+-----------+-----------+-------------------+--------+
| 1 | EQUALITY | C1 | NUMBER(38,0) | true |
| 2 | EQUALITY | C2 | VARCHAR(16777216) | true |
| 3 | EQUALITY | C4 | NUMBER(38,0) | true |
| 4 | EQUALITY | C5 | VARCHAR(16777216) | true |
| 5 | EQUALITY | V1 | VARIANT | true |
| 6 | SUBSTRING | C2 | VARCHAR(16777216) | true |
| 7 | SUBSTRING | C5 | VARCHAR(16777216) | true |
| 8 | GEO | G1 | GEOGRAPHY | true |
| 9 | EQUALITY | V1:"key1" | VARIANT | true |
| 10 | EQUALITY | V1:"key2" | VARIANT | true |
+---------------+-----------+-----------+-------------------+--------+
列 c2
の部分文字列に対する検索最適化をドロップするには、次のステートメントを実行します。
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON SUBSTRING(c2);
列 c5
のすべてのメソッドに対する検索最適化をドロップするには、次のステートメントを実行します。
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON c5;
列 c5
は等価検索と部分文字列検索を最適化するように構成されているため、上記のステートメントは c5
の等価検索と部分文字列検索の構成をドロップします。
列 c1
の等価性に対する検索最適化をドロップし、式 IDs 6
および 8
で指定された構成をドロップするには、次のステートメントを実行します。
ALTER TABLE t1 DROP SEARCH OPTIMIZATION ON EQUALITY(c1), 6, 8;
構文の詳細については、 ALTER TABLE ... DROP SEARCH OPTIMIZATION のセクション をご参照ください。
テーブルからの検索最適化の削除¶
テーブルから SEARCH OPTIMIZATION プロパティを削除するには、
テーブルから検索最適化を削除する権限 があるロールに切り替えます。
ON 句なしで ALTER TABLE ... DROP SEARCH OPTIMIZATION コマンドを実行します。
ALTER TABLE [IF EXISTS] <table_name> DROP SEARCH OPTIMIZATION;
例:
alter table test_table drop search optimization;
詳細については、 ALTER TABLE ... DROP SEARCH OPTIMIZATION のセクション をご参照ください。
例¶
次のコードは、検索最適化サービスの作成と使用を示しています。
データがあるテーブルの作成から始めます。
create or replace table test_table (id int, c1 int, c2 string, c3 date) as select * from values (1, 3, '4', '1985-05-11'), (2, 4, '3', '1996-12-20'), (3, 2, '1', '1974-02-03'), (4, 1, '2', '2004-03-09'), (5, null, null, null);SEARCH OPTIMIZATION プロパティをテーブルに追加します。
alter table test_table add search optimization;次のクエリでは、検索最適化サービスを使用できます。
select * from test_table where id = 2;select * from test_table where c2 = '1';select * from test_table where c3 = '1985-05-11';select * from test_table where c1 is null;select * from test_table where c1 = 4 and c3 = '1996-12-20';次のクエリでは、暗黙的なキャストが列ではなく定数に対して行われるため、検索最適化サービスを使用できます。
select * from test_table where c2 = 2;次のキャストはテーブルの列にあるため、検索最適化サービスを使用 できません。
select * from test_table where cast(c2 as number) = 2;IN 句は、検索最適化サービスによってサポートされています。
select id, c1, c2, c3 from test_table where id IN (2, 3) order by id;述語が検索最適化サービスによって個別にサポートされている場合は、述語を接続詞
AND
で結合でき、検索最適化サービスによって引き続きサポートされます。select id, c1, c2, c3 from test_table where c1 = 1 and c3 = TO_DATE('2004-03-09') order by id;DELETE および UPDATE (および MERGE)でも、検索最適化サービスを使用することができます。
delete from test_table where id = 3;update test_table set c1 = 99 where id = 4;
クエリアクセラレーションとの互換性¶
検索最適化と クエリアクセラレーション 併用して、クエリのパフォーマンスを最適化できます。まず、検索最適化により、クエリに不要な マイクロパーティション を削除できます。次に、 対象となるクエリ については、クエリアクセラレーションによって残りの作業の一部をサービスが提供する共有コンピューティングリソースにオフロードできます。
両方のサービスによって高速化されるクエリのパフォーマンスは、ワークロードと利用可能なリソースによって異なります。