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
): インデックスは作成されません
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 からインデックスのステータスを簡単に変更できます。以下はその手順です:
- Optimizations タブに移動し、目的の最適化を右クリックします。
- コンテキストメニューからManage recommended indexes を選択します。このOptimization 用の管理ダイアログが開きます:
- 必要なインデックスの行とStatus カラムの交点にあるセルをクリックし、必要なステータスを選択します(CData Virtuality Studio では、
Enabled
はACCEPTED
、Disabled
はREJECTED
)。OK をクリックして確認します:
Via SQL
インデックスのステータスを変更する前に、以下の手順でインデックスのID を調べてください:
- Optimizations タブで、変更するインデックスを含む最適化を検索します:
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 |
---|---|---|
|
| 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: |
|
| 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 |
|
| No indexes will be created |
|
| 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 の個別ポリシーを変更するには、次の手順に従います:
- Optimizations タブに移動し、目的の最適化を右クリックします。
- コンテキストメニューからManage recommended indexes を選択します。この Optimization の管理ダイアログが開きます:
- インデックスの作成フィールドから必要なポリシーを選択します。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? |
---|---|---|---|
|
| ignored | Yes |
|
| ignored | No |
|
| ignored | No |
|
|
| Yes |
|
|
| No |
|
|
| No |
|
| ignored | Yes |
|
| ignored | Yes |
|
| ignored | No |
|
|
| Yes |
|
|
| Yes |
|
|
| No |
|
| ignored | No |
|
| ignored | No |
|
| ignored | No |
|
|
| No |
|
|
| No |
|
|
| No |