Indexes in CData Virtuality Server

インデックスは、テーブルの結合や結果のフィルタリングのような典型的なデータベース処理を高速化するために広く使用されています。CData Virtuality Server は、分析ストレージテーブル(Materialized Table)上のインデックスの使用をサポートし、分析プロセスをさらに改善します。

ほとんどの場合、クエリのパフォーマンスを大幅に向上させるには、ライブデータの代わりに Optimizations(つまり Materialized Table)を使用することをお勧めします。例えば、主要な分析レポートが大規模なデータに依存している場合、ローカルコンテンツをより迅速に分析することができます。システムは、クエリのパフォーマンスを向上させる可能性のある推奨インデックスを自動的に推奨することで、重い負荷を軽減します。さらに、インデックスを作成したり、システムが実用的でない物理インデックスを作成しないようにすることもできます。

CData Virtuality Server は、サポートされているインデックスの種類を自動的に検出し、クエリに応じて推奨します。Tableauのようなビジネスインテリジェンスツールを介して)直接または間接的に送られてくるクエリから学習します。こ のセ ク シ ョ ンでは、 こ の動作について説明 し 、 解析 ス ト レージ ・ プ ラ ッ ト フ ォーム と それ ら がサポー ト す る イ ンデ ッ ク ス の概要を示 し ます。

How are Indexes Recommended

クエリがCData Virtuality Server へ送信されるたびに、CData Virtuality Server は、リソースを消費し、インデックスによって改善できるクエリの特定の部分を自動的にチェックします。インデックスには3つの種類があります:

  • WHERE : インデックスは、フィルタリングを含むクエリに基づいて推奨されます;
  • JOIN : インデックスは、複数のオブジェクトを結合するクエリに基づいて推奨されます;
  • Manual: インデックスはユーザーによって定義されます。

WHERE

WHERE句が使用されている場合、Server はそれを検出し、句から1つ以上のインデックスを推奨します。インデックスがすでに存在する場合、システムはカウンタを更新し、どのインデックスがより頻繁に使用されているかを知ることができます。

以下にクエリの例を示します:

SELECT *
FROM "views.foo"
WHERE col1 > 0;;

 col1に Recommended Index を掲載します。

JOIN

Server はまた、JOIN基準を考慮し、それに応じて Recommended Indexes を推奨します。単純なJOIN、ソースからの列と別のソースからの列を比較する場合、両方がインデックスとして推奨されます。以下はそのようなクエリの例です:

SELECT a.col1, b.col2
FROM "views.foo" AS a
INNER JOIN "views.bar" as b ON a.col1 = b.col2;;

"views.foo"."col1""views.bar"."col2"の 2 つのインデックスが追加または更新されます。

Manual Indexes

最後に、あらゆる種類のインデックスを作成することができます(これらはmanualという型になります)。CData Virtuality Server は手動インデックスを推奨していません。

Multiple Recommendations of Index

覚えておくべき1つの重要な点は、異なるシステム観測によって、推奨されるときにインデックスが複数回表示される可能性があるということです。同時に、DISTINCTインデックスが複数回作成されることはありません。 

以下は、システムがインデックスとして同じカラムを2回推奨するクエリの例です:

SELECT a.col1, b.col2
FROM "views.foo" AS a
INNER JOIN "views.bar" AS b ON a.col1 = b.col2
WHERE a.col1 > 5;;

このクエリを実行すると、3つの推奨インデックスが得られます:

  • "views.foo"."col1" (type ) JOIN
  • "views.foo"."col1" (type ) WHERE
  • "views.bar"."col2" (type ) JOIN

Kinds of Supported Indexes

CData Virtuality Server は、使用している Analytical Storage プラットフォームに応じて、以下の最も一般的な種類のインデックスをサポートします:

  • シングルカラムインデックス
  • 複数列インデックス(複合インデックスと呼ばれることもあります。)
  • エクスプレッションインデックス

Single-column and Multi-column Indexes

特定のインデックスが役に立つ場所を自動的に検出するだけでなく、システムは最も適切なインデックスを推奨します。以下はその例です:

SELECT a.col1, b.col2
FROM "views.foo"
WHERE col1 > 0 AND col2 < 9;;

このクエリに対して、システムは2つの単一列インデックスと1つの複数列インデックスを推奨します:

  • シングル・カラム・インデックス:"views.foo"."col1"および"views.foo"."col2"
  • 複数列のインデックス:("views.foo"."col1", "views.foo"."col2")

Expression Index

式インデックスが便利な場合の例として、YEAR関数を使用すると、指定された日付または TIMESTAMP の年だけを返すことができます。

ビジネスインテリジェンスツールでは、売上などのデータを年、月、またはその他の期間に基づいて分析することがよくあります。しかし、ほとんどの場合、タイムスタンプ全体を含むカラムは1つだけであり、それが全体としてインデックスされている場合、YEAR (someColumn)のような比較は、someColumnのシングルカラムインデックスを使用しません。式インデックスは、必要な比較に使用されるデータタイプを正確に格納することができます。

以下はその例です:

SELECT *
FROM "dwh.foo"
WHERE YEAR("someColumn") > 2010;;

このクエリを実行すると、システムはYEAR(someColumn)式インデックスを推奨します。

Limitations

インデックスを使用する際の唯一のLIMITは、使用できるインデックスの種類が使用する分析ストレージ(というより、この分析ストレージの背後にある技術)に依存することです。以下は、プラットフォームとサポートされているインデックスの種類の概要です:

Analytical Storage Platform

Single-column Indexes

Multi-column Indexes

Expression Indexes

PostgreSQL

+

+

+

Oracle

+

+

+

MySQL

+

+

-

MS SQL Server

+

+

-

Amazon Redshift

-

-

-

Snowflake

-

-

-

Google BigQuery

-

-

-

Status of Indexes

インデックスには以下のステータスがあります:

  • NEW: インデックスはシステムによって推奨されます; CData Virtuality Server のみ
  • ACCEPTED(CData Virtuality Studio ではEnabled): インデックスはシステムによって推奨され、その後ユーザーによって承認されます; すべての手動インデックスもこのステータスになります; インデックスが作成されます
  • REJECTED(CData Virtuality Studio ではDisabled): インデックスは作成されません
The REJECTED status has the highest priority. If an index appears in the recommendations several times with different statuses, and one of them is REJECTED, this index will be rejected.

Changing Index Status

Via CData Virtuality Studio

CData Virtuality Studio からインデックスのステータスを簡単に変更できます。以下はその手順です:

  1. Optimizations タブに移動し、目的の最適化を右クリックします。
  2. コンテキストメニューからManage recommended indexes を選択します。このOptimization 用の管理ダイアログが開きます:

  3.  必要なインデックスの行とStatus カラムの交点にあるセルをクリックし、必要なステータスを選択します(CData Virtuality Studio では、EnabledACCEPTEDDisabledREJECTED)。OK をクリックして確認します:

Via SQL

インデックスのステータスを変更する前に、以下の手順でインデックスのID を調べてください:

  1. Optimizations タブで、変更するインデックスを含む最適化を検索します:
  2. recOptIDをOptimization ID として、以下のSQL Queries を実行します:

    SELECT *
    FROM "SYSADMIN.RecommendedIndexes"
    WHERE "recOptID"=32771;;

例えば、インデックスがWHERE句とJOIN 基準で使用されている場合などです。parentRecOptId が異なるため、常に異なるインデックスを見分けることができます:

そして、SQL クエリを使用して特定のインデックスのステータスを変更する方法です:

CALL SYSADMIN.setIndexStatus(indexId => <indexId>, indexStatus => <status>);;

インデックスには以下のステータスのいずれかがあることを覚えておいてください:

  • ACCEPTED
  • REJECTED
  • NEW

ID が8のインデックスの例をいくつか示します:

CALL SYSADMIN.setIndexStatus(indexId => 8, indexStatus => 'ACCEPTED');;
CALL SYSADMIN.setIndexStatus(indexId => 8, indexStatus => 'REJECTED');;

 setIndexStatus() プロシージャは、OPTION $NOFAIL とともに使用できます。このオプションを有効にすると、プロシージャは例外をスローしません。

We do not recommend using the NEW status for non-system recommended indexes to have new indexes always come from the CData Virtuality Server.

This status cannot be set via the CData Virtuality Studio, and, for consistency, we do not recommend setting it manually.

Index Creation Policy

CData Virtuality Server では、最適化インデックスの作成方法を決定できます。グローバルと各Optimization の両方で設定を設定し、微調整することができます。

Please note that indexes are only created at replication time, and changes to them take effect next time a full replication of the optimization runs.

Global Policy

グローバル設定は、複数のOptimization を一度に制御するために使用できます。グローバルポリシーが変更されると、「デフォルト」に設定されているすべての個別ポリシーが自動的に変更されます。

デフォルトのグローバル設定は、常にすべてのタイプの設定を作成するようになっています ( 'NEW_ACCEPTED' )。

Individual Policy

各 Optimizations は、異なるインデックス作成ポリシーで個別に設定することもできます。グローバル設定を使用する設定でない限り、個々のポリシーの優先順位は常に高くなります。それ以外の設定オプションは同じで、Default Options では、すべての Optimization が個々のポリシー'NEW_ACCEPTED'を使用するように設定されます。

Policy Options

設定可能なポリシーは以下のとおりです:

Policy setting

Name in CData Virtuality Studio

Index creation

NEW_ACCEPTED

New and Enabled

All indexes, except explicitly disabled, will be created upon the next full replication. This includes both indexes automatically recommended by the CData Virtuality Server and added manually.

The number of indexes in the NEW status that will be created for a particular table is controlled by the MAX_AUTO_INDEXES_PER_TABLE default option (default: 5)

ACCEPTED

Enabled

Only enabled indexes will be created. Indexes automatically recommended by the CData Virtuality Server will not be created unless explicitly accepted by manually setting it to ACCEPTED/Enabled. Indexes with the statuses Disabled and New will not be created

NONE

None

No indexes will be created

DEFAULT

Default

This setting can be used to configure the individual policy for single optimizations. The setting default automatically applies the global policy

Setting Global and Individual Policies

グローバルポリシーはSQL でのみ設定できますが、個別ポリシーはSQL とCData Virtuality Studio で設定できます。

Setting Global Policy (SQL Only)

現時点では、グローバル・インデックス作成ポリシーは、以下のSQLクエリを使用してのみ変更できます:

CALL setDefaultOptionValue('INDEX_CREATION_POLICY', <status>);;

以下は3つのステータスの例です:

CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'NEW_ACCEPTED');;
CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'ACCEPTED');;
CALL SYSADMIN.setDefaultOptionValue('INDEX_CREATION_POLICY', 'NONE');;

Setting Individual Policy

CData Virtuality Studio 経由

単一の Optimization の個別ポリシーを変更するには、次の手順に従います:

  1. Optimizations タブに移動し、目的の最適化を右クリックします。
  2. コンテキストメニューからManage recommended indexes を選択します。この Optimization の管理ダイアログが開きます:
  3. インデックスの作成フィールドから必要なポリシーを選択します。OK をクリックして確認します:
SQL経由

SQL を使用して個々のポリシーを変更するには、Optimization ID ( recOptId ) が必要です。クエリ自体はこのようになります:

CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(recOptId => <id>, allowIndexCreationByStatus => <status>);;

 以下は4つのステータスの例です:

CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(recOptId => 32771, allowIndexCreationByStatus => 'NEW_ACCEPTED');;
CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(recOptId => 32771, allowIndexCreationByStatus => 'ACCEPTED');;
CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(recOptId => 32771, allowIndexCreationByStatus => 'NONE');;
CALL SYSADMIN.setRecOptAllowIndexCreationByStatus(recOptId => 32771, allowIndexCreationByStatus => 'DEFAULT');;

Overview of Index Creation

この表では、すべてのステータスと、そのステータスを持つインデックスが作成されるかどうかの個別およびグローバルポリシー設定を示します:

Status of index (Studio view)

Individual Policy Setting

Global Policy Setting

Created?

NEW (New)

NEW_ACCEPTED

ignored

Yes

NEW (New)

ACCEPTED

ignored

No

NEW (New)

NONE

ignored

No

NEW (New)

DEFAULT

NEW_ACCEPTED

Yes

NEW (New)

DEFAULT

ACCEPTED

No

NEW (New)

DEFAULT

NONE

No

ACCEPTED (Enabled)

NEW_ACCEPTED

ignored

Yes

ACCEPTED (Enabled)

ACCEPTED

ignored

Yes

ACCEPTED (Enabled)

NONE

ignored

No

ACCEPTED (Enabled)

DEFAULT

NEW_ACCEPTED

Yes

ACCEPTED (Enabled)

DEFAULT

ACCEPTED

Yes

ACCEPTED (Enabled)

DEFAULT

NONE

No

REJECTED (Disabled)

NEW_ACCEPTED

ignored

No

REJECTED (Disabled)

ACCEPTED

ignored

No

REJECTED (Disabled)

NONE

ignored

No

REJECTED (Disabled)

DEFAULT

NEW_ACCEPTED

No

REJECTED (Disabled)

DEFAULT

ACCEPTED

No

REJECTED (Disabled)

DEFAULT

NONE

No