SQL 解説書
CREATE INDEX ステートメントは、以下のものを作成するときに使います。
- DB2 表での索引
- 索引指定: データ・ソース表に索引があることを最適化プログラムに通知するメタデータ
呼び出し
このステートメントは、アプリケーション・プログラムに組み込むか、
あるいは動的 SQL ステートメントの使用によって発行することができます。
このステートメントは、動的に準備可能な実行可能ステートメントです。
しかし、バインド・オプション DYNAMICRULES BIND を適用する場合、
ステートメントを動的に準備することはできません (SQLSTATE 42509)。
許可
このステートメントの許可 ID には、以下の特権が少なくとも 1 つ含まれている必要があります。
- SYSADM または DBADM 権限
- 以下のいずれか
- その表に対する CONTROL 特権
- その表に対する INDEX 特権
および以下のいずれか
- データベースに対する IMPLICIT_SCHEMA 権限 (索引の暗黙または明示のスキーマ名が存在しない場合)
- スキーマに対する CREATEIN 特権 (索引のスキーマ名が既存のスキーマを指している場合)。
構文
>>-CREATE----+---------+--INDEX--index-name--------------------->
'-UNIQUE--'
.-,-----------------------.
(1) V .-ASC--. |
>----ON--+-table-name------+--(-----column-name--+------+--+---)->
| (2) | '-DESC-'
'-nickname--------'
>-----+---------------------+----------------------------------->
'-SPECIFICATION ONLY--'
>----*--+--------------------------------------------+---------->
| .-,--------------. |
| (3) V | |
'-INCLUDE---------(-----column-name---+---)--'
>----*--+----------------------------------------------------------------------------+---*->
+-CLUSTER--------------------------------------------------------------------+
'-EXTEND USING--index-extension-name--+------------------------------------+-'
| .-,----------------------. |
| V | |
'-(-----constant-expression---+---)--'
.-PCTFREE 10--------.
>----+-------------------+---*----+----------------------+--*--->
'-PCTFREE--integer--' '-MINPCTUSED--integer--'
.-DISALLOW REVERSE SCANS--.
>----+-------------------------+---*---------------------------><
'-ALLOW REVERSE SCANS-----'
注:
- 連合システムでは、table-name には、連合データベース内の表を指定します。
データ・ソース表を指定することはできません。
- nickname を指定する場合、
CREATE INDEX ステートメントにより、索引指定が作成されます。
INCLUDE、
CLUSTER、
PCTFREE、
MINPCTUSED、
DISALLOW REVERSE SCANS、
および
ALLOW REVERSE SCANS
は指定できません。
- INCLUDE 文節は UNIQUE が指定されている場合のみ指定できます。
説明
- UNIQUE
- ON table-name を指定する場合、
UNIQUE により、表には索引キーの値が同じである複数の行を含めることができなくなります。
行の更新、または新しい行の挿入を行う SQL ステートメントの終了時に、固有性が確保されます。
詳細については、付録 J, トリガーと制約の相互作用を参照してください。
この固有性は、CREATE INDEX ステートメントの実行の過程でも検査されます。
重複するキー値を含む行がすでに表に含まれている場合、索引は作成されません。
UNIQUE を使用する場合、ヌル値は他の値と同様に扱われます。
たとえば、キーが、ヌル値可能の単一列である場合、
その列では 1 つのヌル値しか含めることができません。
UNIQUE オプションの指定があり、しかも表に区分化キーがある場合、
索引キーの列は区分化キーのスーパーセットである必要があります。
つまり、固有索引キーに対して指定される列は、
区分化キーのすべての列を含んでいる必要があります (SQLSTATE 42997)。
ON nickname が指定されている場合、
索引キーのデータ・ソース表の各行に固有の値が含まれている場合に限り、
UNIQUE を指定するようにします。
固有性は検査されません。
table-name を宣言された一時表 (SQLSTATE 42995) にすることはできません。
- INDEX index-name
- 索引または索引指定を指定します。
暗黙または明示の修飾子を含む名前は、
カタログに記述されている索引または索引指定を識別するものであってはなりません。
修飾子は SYSIBM、SYSCAT、SYSFUN、
または SYSSTAT であってはなりません (SQLSTATE 42939)。
- ON table-nameまたは nickname
- table-name には、索引を作成する表を指定します。
表は、視点であってはならず、カタログに記述された基礎表または要約表でなければなりません。
これをカタログ表 (SQLSTATE 42832) または宣言された一時表 (SQLSTATE 42995) にすることはできません。
UNIQUE が指定されており、
table-name がタイプ付き表である場合には、
副表を指定することはできません (SQLSTATE 429B3)。
UNIQUE が指定されている場合は、
table-name を要約表とすることはできません (SQLSTATE 42809)。
nickname は、索引指定を作成するニックネームです。
この nickname により、索引が索引指定によって記述されているデータ・ソース表、
またはそのような表に基づくデータ・ソース視点のいずれかが参照されます。
この nickname は、カタログにリストされていなければなりません。
- column-name
- 索引の場合、column-name には索引キーを構成する列を指定します。
索引指定の場合、column-name は、
連合サーバーがデータ・ソース表の列を参照するときの名前になります。
各 column-name は、表の列を指定する非修飾名でなければなりません。
列は、16 個まで指定できます。
table-name がタイプ付き表である場合は、列を 15 個まで指定できます。
table-name が副表であるならば、
副表内には少なくとも 1 つの column-name を
スーパー表から継承するのではなく、新たに導入する必要があります。
同じ column-name を繰り返すことはできません (SQLSTATE 42711)。
指定された列の保管長の合計は、1024 バイトを超えてはなりません。
table-name がタイプ付き表である場合は、索引キーの長さ制限は 4 バイト減ります。
この長さは、列のデータ・タイプやヌル値可能か否かによって変動するシステムのオーバーヘッドにより、
より小さい値になることがあります。
この制限に影響を与えるオーバーヘッドの詳細については、
Byte Countsを参照してください。
それぞれの列の長さは、どれも 255 バイトを超えてはなりません。
LOB 列、DATALINK 列、
または LOB や DATALINK の特殊タイプ列を索引の一部として使用することはできません。
列の長さ属性が 255 バイトの限界内に収まる場合でも同じです (SQLSTATE 42962)。
構造タイプ列は、EXTEND USING 節も指定されている場合にのみ指定できます (SQLSTATE 42962)。
EXTEND USING 文節が指定される場合、列は 1 つしか指定できず、
列のタイプは構造タイプか、あるいは LOB、DATALINK、LONG VARCHAR、
LONG VARGRAPHIC を基にしたのではない特殊タイプでなければなりません (SQLSTATE 42997)。
- ASC
- 索引項目が、列の値の昇順で保持されるように指定します。
これがデフォルト設定です。
ASC は、EXTEND USING で定義される索引に指定することはできません (SQLSTATE 42601)。
- DESC
- 索引項目が、列の値の降順で保持されるように指定します。
DESC は、EXTEND USING で定義される索引に指定することはできません (SQLSTATE 42601)。
- SPECIFICATION ONLY
- このステートメントが、
nickname で参照するデータ・ソース表に適用される索引指定を作成するときに使われることを示します。
SPECIFICATION ONLY は、nickname を指定した場合に、
指定しなければなりません (SQLSTATE 42601)。
table-name を指定した場合には、
指定することはできません (SQLSTATE 42601)。
- INCLUDE
- このキーワードは、
一連の索引キー列に付加する追加の列を指定する文節を新たに指定します。
この文節によって組み込まれる列は、固有性を強制するために使用されることはありません。
これらの組み込み列で索引のみアクセスを実行することにより、
一部の照会のパフォーマンスを向上させることができます。
この列は、固有性を強制するために使用される列とは
区別する必要があります (SQLSTATE 42711)。
列の数および長さ属性の合計に対する制限は、
固有キーと索引にあるすべての列にも適用されます。
- column-name
- 索引には組み込まれているものの、
固有索引キーの一部ではない列を指定します。
固有索引キーの列に定義された規則と同様な次の規則が適用されます。
column-name に続けてキーワード ASC または DESC を指定しても構いませんが、
順序に影響はありません。
INCLUDE は、EXTEND USING で定義される索引に指定することはできません。
nickname が指定されている場合にも使用できません (SQLSTATE 42601)。
- CLUSTER
- 当該の索引を表のクラスター化索引として指定します。
クラスター化索引のクラスター係数は、
データが関連する表に挿入される時に、動的に維持または改善されます。
これは、この索引のキー値が同じ範囲にある行と物理的に近い位置に、
新しい行の挿入を試みることによって行われます。
ただし、表用のクラスター化索引が 1 つだけしかないために、
それが表の既存の索引の定義に使用されていて、
CLUSTER は指定できないということもありえます (SQLSTATE 55012)。
追加モードを使用するように定義されている表では、
クラスター化索引を作成できない場合があります (SQLSTATE 428D8)。
CLUSTER は、nickname が指定されている場合は使用できません (42601)。
- EXTEND USING index-extension-name
- この索引を管理するのに使用する index-extension を指定します。
この文節を指定する場合、1 つだけ column-name を指定しなければならず、
この列は構造タイプまたは特殊タイプでなければなりません (SQLSTATE 42997)。
index-extension-name は、
カタログに記述されている索引拡張を指定しなければなりません (SQLSTATE 42704)。
特殊タイプの場合には、列が、
索引拡張でソース・キー・パラメーターに対応するタイプと完全に一致していなければなりません。
構造タイプ列では、対応するソース・キー・パラメーターのタイプが、
列タイプのタイプまたはスーパータイプと同じでなければなりません (SQLSTATE 428E0)。
- constant-expression
- 索引拡張に必要な引き数の値を指定します。
各式は、対応する索引拡張パラメーターの定義されたデータ・タイプ (長さまたは精度、
およびスケールも含む) に完全に一致するデータ・タイプを持つ定数値でなければなりません (SQLSTATE 428E0)。
- PCTFREE integer
- 索引を構築する際に、各索引ページに残す空きスペースのパーセンテージを指定します。
ページの最初の項目は、制限なしで追加されます。
索引ページに項目を追加する場合には、
各ページに少なくとも integer パーセントを空きスペースとして残します。
integer の値は 0 〜 99 です。
ただし、10 よりも大きな値を指定しても、
非葉ページには 10 パーセントの空きスペースしか残されません。
デフォルト値は 10 です。
PCTFREE は、nickname が指定されている場合は使用できません (SQLSTATE 42601)。
- MINPCTUSED integer
- 索引をオンラインで再編成するかどうか、
そして索引の葉ページで使用されるスペースの最小パーセンテージについて、
その限界値を指定します。索引の葉ページからキーを削除した後、
そのページで使うスペースのパーセンテージが integer のパーセンテージを下回る場合、
このページにある残りのキーを近隣のページのキーにマージするよう試行されます。
いずれかのページに十分なスペースがあれば、マージが行われ、いずれかのページが削除されます。
integer の値は 0 〜 99 です。
しかし、パフォーマンス上の理由のため、50 以下の値をお勧めします。
MINPCTUSED は、nickname が指定されている場合は使用できません (SQLSTATE 42601)。
- DISALLOW REVERSE SCANS
- 索引において、前方向走査、
すなわち INDEX CREATE の実行時に定義した順序での走査だけをサポートすることを指定します。
これはデフォルト値です。
DISALLOW REVERSE SCANS は、nickname が指定されている場合は使用できません (SQLSTATE 42601)。
- ALLOW REVERSE SCANS
- 索引が前方向走査と反対方向走査の両方、すなわち、
INDEX CREATE の実行時に定義した順序と、
その反対 (つまり逆) の順序とをサポートすることを指定します。
ALLOW REVERSE SCANS は、nickname が指定されている場合は使用できません (SQLSTATE 42601)。
規則
- 既存の索引に一致する索引を作成しようとすると、
CREATE INDEX ステートメントはエラーになります (SQLSTATE 01550)。
2 つの索引記述は、以下の場合に重複していると見なされます。
- 列の集合 (キーと組み込み列の両方) と、索引内でのそれらの順序が、
既存の索引と同じであり、かつ
- 順序付け属性が同じであり、しかも
- 以前に存在していた索引と作成中の索引の両方が固有でないか、
または以前に存在していた索引が固有であり、さらに
- 以前に存在していた索引と作成中の索引の両方が固有である場合、
作成中の索引のキー列は、以前に存在していた索引のキー列と同一であるか、
またはそのスーパーセットになります。
注
- 指定した表にすでにデータが含まれる場合、CREATE INDEX は、
そのデータの索引項目を作成します。
表にまだデータが含まれていない場合、CREATE INDEX は索引記述を作成します。
索引項目は、データが表に挿入される時点で作成されます。
- 索引が作成され、データが表にロードされた時点で、
RUNSTATS コマンドを発行することをお勧めします。
(RUNSTATS については、コマンド解説書 を参照してください。)
RUNSTATS コマンドは、データベース表、列、
および索引について収集された統計値を更新します。
これらの統計値は、表への最適アクセス・パスを判別するために使用されます。
RUNSTATS コマンドを発行することによって、
データベース・マネージャーが新しい索引の特性を判別することができます。
- まだ存在していないスキーマ名を用いて索引を作成すると、
ステートメントの許可 ID に IMPLICIT_SCHEMA 権限がある場合に限り、
そのスキーマが暗黙に作成されます。
そのスキーマの所有者は SYSIBM です。
スキーマに対する CREATEIN 特権は PUBLIC に与えられます。
- 最適化プログラムは、実際の索引を作成する前に、
複数の索引を推奨することがあります。
詳細については、SET CURRENT EXPLAIN MODEを参照してください。
- 索引のあるデータ・ソース表に索引指定を定義している場合、
その索引指定の名前は索引の名前と一致していなくても構いません。
- 最適化プログラムは索引指定を使用して、
その指定を適用するデータ・ソース表へのアクセスを改善します。
- 索引指定の詳細は、索引指定を参照してください。
例
例 1:
PROJECT 表に対して UNIQUE_NAM という名前の索引を作成します。
この索引の目的は、
プロジェクト名 (PROJNAME) の値が同じ 2 つの項目が表に作成されないようにすることです。
索引項目は昇順に並べます。
CREATE UNIQUE INDEX UNIQUE_NAM
ON PROJECT(PROJNAME)
例 2:
EMPLOYEE 表に対して JOB_BY_DPT という名前の索引を作成します。
索引項目は、各部門 (WORKDEPT) の中ではジョブ名 (JOB) 順に昇順で並べます。
CREATE INDEX JOB_BY_DPT
ON EMPLOYEE (WORKDEPT, JOB)
例 3:
ニックネーム EMPLOYEE は、CURRENT_EMP というデータ・ソース表を参照します。
このニックネームを作成した後、索引が CURRENT_EMP で定義されます。
索引キー用に選んだ列は WORKDEBT と JOB です。
この索引を記述する索引指定を作成します。
この指定を参照することにより、最適化プログラムは、
索引が存在することと索引に含まれるキーを知ることになります。
この情報を利用して、最適化プログラムは、
表をアクセスするときの戦略を改善することができます。
CREATE UNIQUE INDEX JOB_BY_DEPT
ON EMPLOYEE (WORKDEPT, JOB)
SPECIFICATION ONLY
例 4: 構造タイプ列の位置に、
拡張索引タイプ SPATIAL_INDEX を作成します。
索引拡張 GRID_EXTENSION の記述が SPATIAL_INDEX を保守するのに使用されます。
リテラルが GRID_EXTENSION に指定されて、索引グリッド・サイズを作成します。
索引拡張の定義については、CREATE INDEX EXTENSION を参照してください。
CREATE INDEX SPATIAL_INDEX ON CUSTOMER (LOCATION)
EXTEND USING (GRID_EXTENSION (x'000100100010001000400010'))
[ ページのトップ | 前ページ | 次ページ | 目次 | 索引 ]