CREATE INDEX
CREATE INDEX文は、表またはマテリアライズド・ビューの1つ以上の列に索引を作成します。
必要な権限
- 所有者の場合、権限は不要です。
- 所有者でない場合は、システム権限の
CREATEANYINDEXまたはオブジェクト権限のINDEXが必要です。
TimesTen Scaleoutでの使用
この文は、TimesTen Scaleoutでサポートされています。グローバル索引またはローカル索引を作成できます。
SQL構文
次に、範囲索引を作成する構文を示します。GLOBALキーワード、オプションのINCLUDE句およびオプションのIndexDistributionClause句を使用して、グローバル索引を作成します。グローバル索引は、TimesTen Scaleoutでのみサポートされます。
CREATE [GLOBAL][UNIQUE] INDEX [Owner.]IndexName ON [Owner.]TableName (ColumnName [ASC | DESC][,... ]) [INCLUDE (ColumnName[,…])] [IndexDistributionClause]
次に、ハッシュ索引を作成する構文を示します。GLOBALキーワードを使用して、グローバル索引を作成します。オプションのINCLUDE句およびオプションのIndexDistributionClause句は、グローバル索引にのみ使用できます。グローバル索引は、TimesTen Scaleoutでのみサポートされます。
CREATE [GLOBAL][UNIQUE] HASH INDEX [Owner.]IndexName ON [Owner.]TableName (ColumnName [ASC | DESC][,... ] ) [INCLUDE (ColumnName [,…])] [ PAGES = RowPages | CURRENT ] [IndexDistributionClause]
IndexDistributionClauseの構文は、グローバル索引(TimesTen Scaleoutでサポート)にのみ使用できます。
IndexDistributionClause::= DISTRIBUTE BY HASH [(ColumnName [,…])]
パラメータ
| パラメータ | 説明 |
|---|---|
|
|
TimesTen Scaleout:
|
|
|
範囲索引およびハッシュ索引の両方に |
|
|
ハッシュ索引を作成する場合は、 |
|
|
新しい索引に割り当てる名前。表の2つの索引に同じ名前を付けることはできません。所有者を指定する場合、表の所有者と同じ名前を指定する必要があります。 |
|
|
索引を作成する表またはマテリアライズド・ビューを指定します。 |
|
|
索引キーとして使用する列の名前。主索引キーから補助索引キーの順に、最大32列を指定できます。 |
|
|
索引の順序を昇順(デフォルト)または降順で指定します。現在、TimesTenでは、この句は無視されます。 |
|
|
|
|
|
表に行がない場合は、 |
|
|
この句を指定しない場合は、グローバル索引定義で定義された列が分散キーを形成します。 この句を指定する場合は、
ColumnName句をオプションで指定できます。
|
TimesTen Scaleoutの索引
- グローバル索引: 表内のすべての行をハッシュ分散スキームにマップします。グローバル索引を作成すると、TimesTen Scaleoutは、ローカル索引と索引キー列へのハッシュ分散スキームを使用したマテリアライズド・ビューを作成します。マテリアライズド・ビューで作成されるローカル索引は、問合せのパフォーマンスをさらに最適化します。
- ローカル索引: 各データベース要素に作成されます。データベース要素の索引は、データベース要素の表の行にマップされます。表に分散キー列を含まない索引列に対する問合せでは、すべてのレプリカ・セットの要素と通信する必要があります。
TimesTen Scaleoutでの索引の使用の詳細は、『Oracle TimesTen In-Memory Database Scaleoutユーザーズ・ガイド』の「索引の理解」を参照してください。
TimesTen Scaleoutでのグローバルまたはローカル索引の選択
-
グローバル索引を使用する対象を次に示します。
- 一意の列: グローバル一意索引は、一意制約チェックをより効率的に実行することにより、問合せの実行を最適化します。TimesTen Scaleoutは、一意性の検証のために、すべてのレプリカ・セットにアクセスするのではなく分散キー列を使用します。ただし、分散キーが索引キーのサブセットの場合は、ローカル索引を作成します。
- 表の分散キーにすべての列を含まない等式述語を持つ問合せ。
- 主キー列を持つ問合せで頻繁に結合される列のグループ。
- 問合せで頻繁に使用される索引以外の列:
INCLUDE句を使用してグローバル索引を定義し、これらの索引以外の列を含めます。このような場合、問合せを満たすために表にアクセスする必要はありません。 - 索引キーが表の分散キーの接頭辞である索引。
- ローカル索引を使用する対象を次に示します。
- 一意でない列: 索引キーが一意でない列のみで構成されている場合は、ローカルの一意でない索引を作成します。
- 表の分散キーと同じ列を含む索引キー。
- 表の分散キーが索引キーの接頭辞である状況。
- 表の分散キーにすべての列が含まれる等式述語を持つ問合せ。
詳細は、『Oracle TimesTen In-Memory Database Scaleoutユーザーズ・ガイド』の「索引の理解」を参照してください。
グローバル索引の説明
- グローバル索引を作成するには、
GLOBALキーワードを指定する必要があります。索引は、デフォルトではローカルです。 - デフォルトでは、グローバル索引はハッシュによって分散されます。また、ハッシュによってのみ分散できます。ローカル索引は分散されません。
- グローバル索引を作成すると、TimesTen Scaleoutは、内部的に独自のマテリアライズド・ビューと、そのマテリアライズド・ビューに独自のローカル索引を作成します。
- グローバル索引によって、結合を使用した問合せ実行の効率が向上します。ただし、内部マテリアライズド・ビュー(グローバル索引の定義時に作成されるビュー)のメンテナンスのためにDML操作は遅くなります。
- グリッドに新しい要素が追加されると、その新しい要素にスキーマがレプリケートされます。さらに、行が再分散され、索引が再構築されます。これには、グローバル索引が含まれます。同様に、要素がグリッドから削除されたときにも、行が再分散され、索引が再作成されます。
- グローバル索引は、ハッシュによって分散された表と参照によって分散された表に定義できます。親表と子(第1レベルの参照)表のグローバル索引がサポートされます。ただし、孫表または第1レベルの参照表以外の表には、グローバル索引を定義できません。
- 重複によって分散された表には、グローバル索引を定義できません。
グローバル索引の制限事項:
- 分散キーの列リストには、
ROWID疑似列またはROWID型の列を含めることができません。 - グローバル一時表ではサポートされません。
- 読取り専用キャッシュ・グループではサポートされません。
- マテリアライズド・ビューではサポートされません。
グローバル索引の構文およびセマンティック・ルール
グローバル索引を作成するには、GLOBALキーワードを指定する必要があります。GLOBALキーワードを指定しないと、ローカル索引が作成されます。グローバル索引は、索引キー列のハッシュによって分散されます。
GLOBALキーワードを指定した場合は、グローバル索引に固有の次の句を必要に応じて指定できます。
INCLUDE句: 索引に非キー列を含めることができるオプションの句。そのような列が索引を使用する問合せで頻繁にアクセスされる場合は、パフォーマンスが向上する可能性があります。-
IndexDistributionClause: ハッシュ分散に使用する列を指定できるオプションの句。この句を指定しない場合は、索引列が分散キーを形成します。索引の分散キーは、表の分散キーと同じにすることはできません。 -
例:
グローバル範囲索引:
Command> CREATE GLOBAL INDEX globalindex1 ON mytab (a) INCLUDE (b,c) DISTRIBUTE BY HASH (a); Command> indexes mytab; Indexes on table SAMPLEUSER.MYTAB: MYTAB: unique range index on columns: C B GLOBALINDEX1: global non-unique range index on columns: A Included columns: B C 2 indexes found. 2 indexes found on 1 table. Command> drop table mytab;グローバル・ハッシュ索引:
Command> CREATE TABLE mytab (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, a TT_INTEGER NOT NULL, PRIMARY KEY (c,b)) DISTRIBUTE BY HASH (a,b); Command> CREATE GLOBAL HASH INDEX globalhashindex1 ON mytab(a) INCLUDE (b,c) PAGES=200 DISTRIBUTE BY HASH (a); Command> indexes MYTAB; Indexes on table SAMPLEUSER.MYTAB: MYTAB: unique range index on columns: C B GLOBALHASHINDEX1: global non-unique hash index on columns: A Included columns: B C 2 indexes found. 2 indexes found on 1 table.
その他の例は、「例: TimesTen Scaleout」を参照してください。
TimesTen Scaleoutの索引についての一般的な説明
-
TimesTenでは、一意でない範囲索引がデフォルトで作成されます。一意の範囲索引を作成するには、
CREATEUNIQUE INDEXを指定します。 -
一意でないハッシュ索引を作成する場合は、
CREATEHASHINDEXを指定します。一意のハッシュ索引を作成するには、CREATEUNIQUEHASHINDEXを指定します。 -
UNIQUEを指定した場合、索引付けされた列の既存の行の値はすべて一意である必要があります。 -
新しい索引は、
DROP INDEX文を使用して索引を削除するか、その索引に関連付けられた表を削除しないかぎり自動的にメンテナンスされます。 -
準備された文が新しく索引付けされた表を参照している場合、その文を次回に実行する際に自動的に再度準備されます。これにより、文は新しい索引を活用することができます。
-
他の接続が所有する空でないインスタンスが一時表にある場合、別の接続によって一時表に索引を作成することはできません。
-
言語比較を使用する場合は、言語索引を作成できます。言語索引ではソート・キー値が使用され、ソート・キー値用の記憶域が必要です。1つの索引に指定できる
NLS_SORTの値は、一意の値を1つのみです。言語索引および言語比較の詳細は、『Oracle TimesTen In-Memory Databaseオペレーション・ガイド』の「言語索引の使用」を参照してください。 -
冗長な索引を作成すると、TimesTenにより警告またはエラーが生成されます。
ttRedundantIndexCheckをコールして、表に対する冗長な索引のリストを表示します。 -
ハッシュ索引のサイズまたは型を変更するには、ハッシュ索引を削除して新しい索引を作成します。
-
ハッシュ索引は、固定サイズで作成され、表の存続中は変化しません。ハッシュ索引のサイズを変更するには、索引を削除して再作成します。ハッシュ索引のページが十分でない場合は、ハッシュの衝突が発生して索引の検索が遅くなります。ハッシュ・キー比較は高速な操作であるため、少数のハッシュ衝突がTimesTenにパフォーマンスの問題を発生させることはありません。
ハッシュ索引のサイズが適性に設定されていることを確認するには、
SETPAGES句のRowPagesパラメータの値により、アプリケーションで表の想定サイズを示す必要があります。この値は、表の想定行数を256で除算して計算します。たとえば、表に256,000行ある場合は、RowPagesに1000を指定します(256000/256=1000)。 -
索引に指定できる最大列数は32です。
問合せ処理での索引の使用
適正な索引は問合せのパフォーマンス向上につながります。索引の使用が有益な問合せと有益でない問合せがあります。また、問合せでの索引の選択も重要です。
範囲索引は、索引列の値のほとんどが一意の場合に、範囲検索および完全一致の処理に適しています。たとえば、範囲索引が列(C1,C2)に定義されている場合、次の種類の述語の処理に索引を使用できます。ConstantOrParamは定数値または動的パラメータを指し、rangeは演算子>、<、>=または<=:を指します。
-
C1=ConstantOrParamANDC2=ConstantOrParam -
C1=ConstantOrParamANDC2rangeConstantOrParam -
C1=ConstantOrParam -
C1rangeConstantOrParam
範囲索引は、等式述語および範囲述語の処理、ソート処理やグループ化処理に効果的です。範囲索引は、一意の値が多数ある索引列に使用します。範囲索引で指定する列の順序は関連しています。範囲索引を使用する問合せの述語内の式の順序は関連していません。問合せ処理時は、表で複数の範囲索引を定義している場合でも、表の各スキャンで範囲索引が1つだけ使用されます。
ハッシュ索引は等式述語の処理に効果的です。最適なパフォーマンスのためには、ハッシュ索引のサイズを適正にする必要があります。PAGESパラメータを使用してハッシュ索引のサイズを指定します。指定したPAGESの値が小さすぎると、多数のハッシュ衝突が発生し、ハッシュ索引にアクセスする文のパフォーマンスが低下します。ハッシュ索引で指定された列の順序は関連せず、範囲索引を使用する問合せの述語内の式の順序は関連しません。ハッシュ索引または範囲索引のいずれかを使用して特定の等式述語を処理する場合は、ハッシュ索引による検索は範囲索引のスキャンより高速なため、ハッシュ索引が使用されます。
文レベルまたはトランザクション・レベルのオプティマイザ・ヒントを設定することで、オプティマイザで使用される索引を変更できることがあります。文レベルのオプティマイザ・ヒントの詳細は、「文レベルのオプティマイザ・ヒント」を参照してください。トランザクション・レベルのオプティマイザ・ヒントの詳細は、『Oracle TimesTen In-Memory Databaseリファレンス』の「ttOptSetFlag」、「ttOptSetOrder」または「ttOptUseIndex」を参照してください。
例: TimesTen Scaleout
次の例は、グローバル索引を作成するための構文の要件を示しています。グローバル索引を作成するには、GLOBALキーワードを指定する必要があります。
グローバル索引の構文の説明
この例では、グローバル索引でサポートされる構文を説明します。
3つの列(c,b,a)を含む表を作成して、これらの列の2つ(c,b)に主キーを定義します。列(a,b)のハッシュによって表を分散します。
(c,b)に、グローバル一意範囲索引を作成します。 Command> CREATE TABLE mytab1 (c TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL, a TT_INTEGER NOT NULL,
PRIMARY KEY (c,b)) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL UNIQUE INDEX mygix1 ON mytab1 (c,b);
Command> indexes mytab1;
Indexes on table SAMPLEUSER.MYTAB1:
MYGIX1: global unique range index on columns:
C
B
MYTAB1: unique range index on columns:
C
B
2 indexes found.
2 indexes found on 1 table.
INCLUDE句を指定します。
Command> CREATE GLOBAL INDEX mygix2 ON MYTAB1(b) include (a);
Command> indexes mytab1
Indexes on table SAMPLEUSER.MYTAB1:
MYGIX1: global unique range index on columns:
C
B
MYTAB1: unique range index on columns:
C
B
MYGIX2: global non-unique range index on columns:
B
Included columns:
A
3 indexes found.
3 indexes found on 1 table.bのハッシュで索引を分散します。 Command> DROP INDEX mygix2;
Command> CREATE GLOBAL INDEX mygix2 ON MYTAB1(b) INCLUDE (a) DISTRIBUTE BY HASH(b);
Command> INDEXES mytab1
Indexes on table SAMPLEUSER.MYTAB1:
MYGIX1: global unique range index on columns:
C
B
MYTAB1: unique range index on columns:
C
B
MYGIX2: global non-unique range index on columns:
B
Included columns:
A
3 indexes found.
3 indexes found on 1 table.
グローバル・ハッシュ索引を作成します。
Command> CREATE GLOBAL HASH INDEX mygix3 ON mytab1(a) PAGES =200;
Command> indexes mytab1;
Indexes on table SAMPLEUSER.MYTAB1:
MYGIX1: global unique range index on columns:
C
B
MYTAB1: unique range index on columns:
C
B
MYGIX3: global non-unique hash index on columns:
A
MYGIX2: global non-unique range index on columns:
B
Included columns:
A
4 indexes found.
4 indexes found on 1 table.
グローバル索引の分散キーと同一の表の分散キー
この例では、分散キーが表の分散キーと同一のグローバル索引は作成できないことを示します。この例では、mytab1表は、列(a,b)のハッシュで分散されます。列(a,b)を分散キーとして使用してグローバル索引を作成しようとすると、エラーが発生します。
Command> CREATE TABLE mytab1 (a TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL,
c TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL INDEX gix1 ON mytab1(a,b,c) DISTRIBUTE BY HASH (a,b);
2253: Distribution key for global index cannot be same as that of the table or
other global index. Consider creating a local index.
The command failed.
グローバル索引によって作成される独自のマテリアライズド・ビューと独自のローカル索引
この例では、グローバル索引を作成するときに、TimesTen Scaleoutによって独自の内部マテリアライズド・ビューと独自のローカル索引が作成されることを示します。列(a,b)のハッシュで分散されるmytab2表を作成します。列(b,a)のハッシュで分散される、一意でないグローバル範囲索引を作成します。ttIsql indexesコマンドを実行して、内部マテリアライズド・ビューにgix2グローバル索引と内部ローカル索引が作成されたことを確認します。その後で、ttIsql viewsコマンドを実行して、グローバル索引の作成の結果として内部マテリアライズド・ビューも作成作成されていることを確認します。ttIsql describeコマンドを実行して、内部マテリアライズド・ビューを確認します。内部マテリアライズド・ビューまたは内部ローカル索引は、明示的に削除することはできません。
Command> CREATE TABLE mytab2 (a TT_INTEGER NOT NULL, b TT_INTEGER NOT NULL,
c TT_INTEGER NOT NULL) DISTRIBUTE BY HASH (a,b);
Command> CREATE GLOBAL INDEX gix2 ON mytab2(a,b,c) DISTRIBUTE BY HASH (b,a);
Command> indexes;
Indexes on materialized view SAMPLEUSER.$GV9B55D3955D52:
$GVI9B55D3955D52: non-unique range index on columns:
A
B
C
1 index found.
Indexes on table SAMPLEUSER.MYTAB2:
GIX2: global non-unique range index on columns:
A
B
C
1 index found.
2 indexes found on 2 tables.
Command> views;
SAMPLEUSER.$GV9B55D3955D52
1 view found.
Command> describe SAMPLEUSER.$GV9B55D3955D52;
Materialized view SAMPLEUSER.$GV9B55D3955D52:
Global index: GIX2 (table: MYTAB2)
Columns:
A TT_INTEGER NOT NULL
B TT_INTEGER NOT NULL
C TT_INTEGER NOT NULL
DISTRIBUTE BY HASH (B, A)
1 view found.
例: TimesTen Classic
表を作成し、col2に一意のハッシュ索引を作成します。PAGES句は指定しないでください。PAGESを指定しない場合、ハッシュ表のサイズに現在の表のページ数が使用されます。INDEXESを使用して、索引が作成されたことを確認します。表に行を挿入し、SHOWPLANを1に設定し、オプティマイザがハッシュ索引を使用していることを確認します。
Command> CREATE TABLE tab (col1 NUMBER PRIMARY KEY NOT NULL, col2 VARCHAR2 (30));
Command> CREATE UNIQUE HASH INDEX hash1 ON tab (col2);
Command> INDEXES;
Indexes on table TESTUSER.TAB:
HASH1: unique hash index on columns:
COL2
TAB: unique range index on columns:
COL1
2 indexes found.
2 indexes found on 1 table.
Command> INSERT INTO tab VALUES (10, 'ABC');
Command> SHOWPLAN 1;
Command> SELECT * FROM tab where col2 = 'ABC';
Query Optimizer Plan:
STEP: 1
LEVEL: 1
OPERATION: RowLkHashScan
TBLNAME: TAB
IXNAME: HASH1
INDEXED CONDITION: TAB.COL2 = 'ABC'
NOT INDEXED: <NULL>
< 10, ABC >
1 row found.
表を作成し、col1に一意でないハッシュ索引を作成します。PAGES = CURRENTを使用して現在の表のページ数を使用してハッシュ索引のサイズを指定します。INDEXESを使用して、一意でないハッシュ索引が作成されることを確認します。
Command> CREATE TABLE tab2 (col1 NUMBER);
Command> CREATE HASH INDEX hash_index ON tab2 (col1) PAGES = CURRENT;
Command> INDEXES;
Indexes on table TESTUSER.TAB2:
HASH_INDEX: non-unique hash index on columns:
COL1
1 index found.
1 index found on 1 table.
表を作成し、col3に一意のハッシュ索引を作成します。PAGES = 100を使用して、ハッシュ索引のサイズにページ数100を指定します。INDEXESを使用して、一意のハッシュ索引が作成されることを確認します。
Command> CREATE TABLE tab3 (col1 NUMBER, col2 NUMBER, col3 TT_INTEGER);
Command> CREATE UNIQUE HASH INDEX unique_hash1 on tab3 (col3) PAGES = 100;
Command> INDEXES;
Indexes on table TESTUSER.TAB3:
UNIQUE_HASH1: unique hash index on columns:
COL3
1 index found.
1 index found on 1 table.
HRスキーマのregions表により、region_idに一意の索引が作成されます。regions表に対してttIsql INDEXESコマンドを発行します。一意の範囲索引regionsが表示されます。
Command> INDEXES REGIONS;
Indexes on table SAMPLEUSER.REGIONS:
REGIONS: unique range index on columns:
REGION_ID
(referenced by foreign key index COUNTR_REG_FK on table SAMPLEUSER.COUNTRIES)
1 index found.
1 index found on 1 table.
regions表に一意の索引iを作成し、region_id列に索引を作成してみます。警告メッセージが表示されます。
Command> CREATE UNIQUE INDEX i ON regions (region_id); Warning 2232: New index I is identical to existing index REGIONS; consider dropping index I
ttRedundantIndexCheckをコールして、この索引に対する警告メッセージを表示します。
Command> CALL ttRedundantIndexCheck ('regions');
< Index SAMPLEUSER.REGIONS.I is identical to index SAMPLEUSER.REGIONS.REGIONS;
consider dropping index SAMPLEUSER.REGIONS.I >
1 row found.
表redundancyを作成し、列co11およびcol2を定義します。col1およびcol2に2つのユーザー索引を作成します。2つ目の索引r2を作成しようとするとエラー・メッセージが表示されます。索引r1は作成されます。索引r2は作成されません。
Command> CREATE TABLE redundancy (col1 CHAR (30), col2 VARCHAR2 (30)); Command> CREATE INDEX r1 ON redundancy (col1, col2); Command> CREATE INDEX r2 ON redundancy (col1, col2); 2231: New index R2 would be identical to existing index R1 The command failed.
redundancy表に対してttIsqlコマンドのINDEXESを発行し、索引r1のみが作成されていることを表示します。
Command> INDEXES redundancy;
Indexes on table SAMPLEUSER.REDUNDANCY:
R1: non-unique range index on columns:
COL1
COL2
1 index found.
1 index found on 1 table.
この一意索引によって、部品の番号はすべて一意になります。
CREATE UNIQUE INDEX purchasing.partnumindex ON purchasing.parts (partnumber);
employees1表に、german_indexという名前の言語索引を作成します。2つ以上の言語ソートが必要な場合は、2つ目の言語索引を作成します。
Command> CREATE TABLE employees1 (id CHARACTER (21), id2 character (21)); Command> CREATE INDEX german_index ON employees1 (NLSSORT(id, 'NLS_SORT=GERMAN')); Command> CREATE INDEX german_index2 ON employees1 (NLSSORT(id2, 'nls_sort=german_ci')); Command> indexes employees1; Indexes on table SAMPLEUSER.EMPLOYEES1: GERMAN_INDEX: non-unique range index on columns: NLSSORT(ID,'NLS_SORT=GERMAN') GERMAN_INDEX2: non-unique range index on columns: NLSSORT(ID2,'nls_sort=german_ci') 2 indexes found. 1 table found.
関連項目