SQL 構文リファレンス : CREATE INDEX
 
このページをシェアする                  
CREATE INDEX
CREATE INDEX ステートメントを使用して、指定されたテーブルに名前付きインデックスを作成します。
構文
CREATE [UNIQUE | PARTIAL] [NOT MODIFIABLE] INDEX インデックス名 [USING インデックス番号] [IN DICTIONARY] ON テーブル名 [インデックス定義]
 
インデックス名 ::= ユーザー定義名
 
インデックス番号 ::= ユーザー定義の値(0 から 118 までの整数)
 
テーブル名 ::= ユーザー定義名
 
インデックス定義 ::= (インデックス セグメントの定義[, インデックス セグメントの定義]...)
 
インデックス セグメントの定義 ::= 列名 [ASC | DESC]
備考
VARCHAR 型の列が CHAR 型の列と異なるのは、長さバイト(Btrieve lstring)またはゼロ終端バイト(Btrieve zstring)のいずれかが予約されており、そのため有効な記憶域が 1 バイト増える点です。言い換えると、CHAR (100) の列を作成すると、レコード内で 100 バイトを占めます。VARCHAR (100) は 101 バイトを占めます。NVARCHAR 型の列が NCHAR 型の列と異なるのは、ゼロ終端文字が予約されており、そのため有効な記憶域が 2 バイト増える点です。言い換えると、NCHAR(50) の列を作成すると、レコード内で 100 バイトを占めます。NVARCHAR(50) の列は 102 バイトを占めます。
PSQL でインデックスを作成する場合、その処理は、ステートメントに IN DICTIONARY か USING、またはその両方が含まれているかどうかによって変わります。結果をまとめた表を次に示します。
操作
処理と結果
追加情報
CREATE INDEX
成功した場合、インデックスはデータ ファイルと X$Index の両方に追加されます。
データ ファイルにインデックスが定義されていない場合、作成されるインデックスはインデックス 0 になります。
データ ファイルに 1 つ以上のインデックスが定義されている場合、作成されるインデックスは、未使用のインデックス番号のうち最小の番号になります。
どちらの場合も、同じ番号を持つ新しいインデックスが X$Index に同様に挿入されます。
メタデータ バージョン 1 用の X$Index、またはメタデータ バージョン 2 用の X$Index を参照してください。
CREATE INDEX
IN DICTIONARY
成功した場合、インデックスは X$Index にのみ追加されます。データ ファイルには何も挿入されません。
データ ファイルを調べて、どのインデックス番号が使用可能であるかを判定します。
データ ファイルにインデックスが定義されていない場合、X$Index に挿入されるインデックスの番号は 0 になります。
データ ファイルに 1 つ以上のインデックスが定義されている場合、データベース エンジンは、追加するインデックスと列およびインデックスの属性が一致するインデックスが X$Index にまだ定義されていないかどうかを確認します。
一致が見つかった場合は、そのインデックス番号を使用して、インデックスが X$Index に追加されます。
一致が見つからない場合は、<データ ファイルのうち最大のインデックス番号>+ 1 がインデックス番号に使用されます。
X$Index にあり、データ ファイルに一致するキーがないインデックスはファントム インデックスと呼ばれ、データベース エンジンでは使用されません。
IN DICTIONARY を参照してください。
CREATE INDEX
USING
インデックス番号
成功した場合、指定したインデックス番号を持つインデックスはデータ ファイルと X$Index の両方に追加されます。
インデックス番号がデータ ファイルまたは X$Index のいずれかで既に使用されている場合は、エラーが返されます。
USING を参照してください。
CREATE INDEX
USING
インデックス番号
IN DICTIONARY
成功した場合、指定したインデックス番号を持つインデックスは X$Index にのみ追加されます。データ ファイルには何も挿入されません。
指定したインデックス番号は、データ ファイルには存在するが X$Index には存在せず、列およびインデックスの属性が追加するインデックスと一致する場合、指定したインデックス番号のインデックスが X$Index に追加されます。それ以外はエラーが返されます。
IN DICTIONARY を参照してください。
インデックス セグメント
インデックス セグメントは、インデックス定義に指定されている 1 つの列に対応します。複数セグメントのインデックスは、複数列の組み合わせとして作成されたものです。
ファイルに定義するすべてのインデックスで使用できるセグメントの総数は、そのファイルのページ サイズによって異なります。
ページ サイズ(バイト数)
ファイル バージョンによる最大キー セグメント数
8.x 以前
9.0
9.5
13.0
512
8
8
切り上げ2
切り上げ2
1,024
23
23
97
切り上げ2
1,536
24
24
切り上げ2
切り上げ2
2,048
54
54
97
切り上げ2
2,560
54
54
切り上げ2
切り上げ2
3,072
54
54
切り上げ2
切り上げ2
3,584
54
54
切り上げ2
切り上げ2
4,096
119
119
2043
1833
8,192
N/A1
119
4203
3783
16,384
N/A1
N/A1
4203
3783
1 N/A は「適用外」を意味します。
2 「切り上げ」は、ページ サイズを、ファイル バージョンでサポートされる次のサイズへ切り上げることを意味します。たとえば、512 は 1024 に切り上げられ、2560 は 4096 に切り上げるということです。
3 9.5 以降の形式のファイルでは 119 以上のセグメントを指定できますが、インデックスの数は 119 に制限されます。
ヌル値を許可する列には考慮も必要です。たとえば、ページ サイズが 4096 バイトのデータ ファイルでは、1 ファイル当たりのインデックス セグメント数は 119 に制限されます。真のヌルがサポートされるインデックス付きのヌル値を許可する列には 2 つのセグメントから成るインデックスが必要なため、1 つのテーブルではインデックス付きのヌル値を許可する列(Btrieve ファイルでは、インデックス付きでヌル値を許可する真のヌル フィールド)は 59 個までしか持てません。ページ サイズが小さくなると、この制限も小さくなります。
ファイル バージョンを 7.x 以降として作成され、TRUENULLCREATE がオンに設定されているファイルは、真のヌルをサポートします。それより前のファイル形式で作成されたファイル、あるいは Pervasive.SQL 7 を使用するか TRUENULLCREATE をオフに設定して作成されたファイルは、真のヌルをサポートせず、この制限を受けません。
UNIQUE
UNIQUE インデックス キーは、特定の行に対してインデックスで定義された列の組み合わせがファイル内で重複しないことを保証します。これは、複数セグメントのインデックスの場合には、個々の列が重複しないことを保証しませんし、また要求もしません。
メモ:次のデータ型以外のすべてのデータ型にインデックスを設定できます。
BIT
BLOB
CLOB
LONGVARBINARY
LONGVARCHAR
NLONGVARCHAR
Status Codes and Messages』のステータス コード 6008:セグメントが多すぎます。も参照してください。
PARTIAL
CREATE INDEX ステートメントで PARTIAL キーワードを使用すると、長さの合計が 255 バイトを超える 1 つの列または列のグループでインデックスを作成できます。
部分インデックスは、大きな列のプレフィックスを使用して作成されるか、あるいは小さな列を複数組み合わせて作成されるため、大きな列のプレフィックスを用いた検索の方がより速く実行できます。したがって、WHERE 句で 'WHERE column_name LIKE 'prefix%' のような制限を使用しているクエリは、インデックスを何も使用しない場合とは対照的に、部分インデックスを使用することによって実行が速くなります。
CREATE INDEX ステートメントに PARTIAL キーワードを含めた場合、インデックス列の幅とオーバーヘッドが 255 バイト以上でなければ、PARTIAL キーワードは無視され、代わりに標準のインデックスが作成されます。
メモ:幅は列の実際のサイズを指し、オーバーヘッドはヌル インジケーターや文字列の長さなどを指します。
PARTIAL の制限事項
PARTIAL を使用する際には次の制限が適用されます。
部分インデックスは、データ型が CHAR または VARCHAR の列にしか追加できません。
部分インデックス列は、必ずインデックス定義内の最後のセグメントであるか、あるいはインデックス定義内の唯一のセグメントである必要があります。
部分インデックス列がインデックス内の唯一のセグメントである場合、列のサイズは最大 8,000 バイトにすることができますが、ユーザー データのインデックス セグメントのサイズは 255 バイトになります。
エンジンは、厳密な等式を含んでいるクエリや、部分列にかかわる ORDER BY、GROUP BY、JOIN などの照合操作を実行している場合には、部分インデックスを使用しません。
部分インデックスは、WHERE 句の次のような形式の制限と照合する場合にのみ使用されます。
WHERE col = 'literal'
WHERE col LIKE 'literal%'
WHERE col = ?
WHERE col LIKE ?
ここで、literal、つまり実引数値にはどのような長さの値も指定できます。部分インデックス列でインデックス処理されたバイト数より短くても長くてもかまいません。LIKE 句の形式が 'prefix%' でない場合、部分インデックスは使用されません。
WHERE 句が前述の制限事項に適合する場合は、実行プランを立てる際に部分インデックスが使用されます。
メモ:ALTER TABLE を使用して部分インデックス列の長さを変更したとき、変更後の長さが 255 バイトのインデックスに収まるようになった場合、あるいは 255 バイトを超えてしまった場合には、ユーザーの責任の下、ユーザーの要求に従って、そのインデックスの削除および再作成を行ってください。
このセクションでは、CREATE PARTIAL INDEX のいくつかの例を示します。
次の例では、データ型とサイズの指定された PartID、PartName、SerialNo、および Description 列を含む、Part_tbl という名前のテーブルが作成されます。
CREATE TABLE part_tbl (partid INT, partname CHAR(50), serialno VARCHAR(200), description CHAR(300));
次に、Description 列を使って、idx_01 という名前の部分インデックスを作成します。
CREATE PARTIAL INDEX idx_01 on part_tbl (description);
インデックスで使用される Description 列は 300 バイトありますが、PARTIAL キーワードを使用することにより、先頭の 255 バイト(オーバーヘッドを含む)だけをプレフィックスとしてインデックスで使用できるようになります。
============ 
次の例では、前の例と同じテーブルに idx_02 という部分インデックスを作成します。代わりに、この例では PartId、SerialNo、および Description 列をまとめてインデックスに使用します。
CREATE PARTIAL INDEX idx_02 on part_tbl (partid, serialno, description);
次の表は、どのようにして大きい列がインデックスに割り当てられるかを理解できるよう、インデックス列の詳細を示しています。
列名
データ型
サイズ
オーバーヘッド
インデックス内のサイズ
PartID
Integer
4
 
4
SerialNo
Varchar
200
1
201
Description
Char
300
 
50
インデックスの合計サイズ
255
NOT MODIFIABLE
この属性は、インデックスが変更されないようにします。複数セグメントのインデックスでは、この属性はすべてのセグメントに適用されることに留意してください。このセグメントのいずれかを変更しようとすると、ステータス コード 10:キー フィールドは変更できません。が返されます。
次の例では、Person テーブルに変更できないセグメント インデックスが作成されます。
CREATE NOT MODIFIABLE INDEX X_Person on Person(ID, Last_Name)
USING
インデックスを作成するときにインデックス番号を制御するには、このキーワードを使用します。インデックス番号を制御することは、リレーショナル エンジンを使ってデータにアクセスする場合だけでなく、MicroKernel エンジンを使って直接データ ファイルからアクセスする場合にも重要です。
インデックスを作成すると、データ ファイルと X$Index の両方に指定したインデックス番号が挿入されます。
指定したインデックス番号がどちらかのファイルで既に使用されている場合は、エラー コードが返されます。X$Index の場合はステータス コード 5:レコードのキー フィールドに重複するキー値があります。、データ ファイルの場合はステータス コード 6:キー番号パラメーターが不正です。が返されます。
CREATE INDEX "citizen-x" USING 3 On Person (citizenship)
IN DICTIONARY
このキーワードは、基となる物理データは変更しないままで DDF に変更を加えたいことを、データベース エンジンに通知します。この機能を使用すると、対応するデータ ファイルと同期していないテーブルの辞書定義を訂正したり、辞書に定義を作成して既存のデータ ファイルと合致させたりすることができます。これが必要となるのは、ほとんどの場合、Btrieve(トランザクショナル)アプリケーション(DDF を使用しない)によってデータ ファイルが作成され、使用されるときです。その場限りのクエリやレポートでは、リレーショナル エンジンを使用してデータにアクセスする必要があります。
通常、データベース エンジンは DDF とデータ ファイルの完全な同期を保ちます。IN DICTIONARY ステートメントを指定しないでインデックスを作成すると、データベース エンジンは X$Index とデータ ファイルに同じインデックス番号を割り当てます。IN DICTIONARY は、インデックスを X$Index にのみ追加できるようにします。
注意: IN DICTIONARY は強力で高度な機能です。これは、システム管理者によってのみ、もしくは絶対的に必要な場合にのみ使用してください。DDF の変更を基となるデータ ファイルへの変更と並行して行わないと、不正な結果セット、パフォーマンスの問題、予期しない結果などの重大な問題が生じることがあります。
ファントム インデックス、つまり、DDF にのみ存在しデータ ファイルには存在しないインデックスを作成した場合、IN DICTIONARY を使用しないでそのインデックスを削除しようとすると、ステータス コード 6:キー番号パラメーターが不正です。が返されることがあります。このエラーは、データベース エンジンがデータ ファイルからインデックスを削除しようとしても、そのようなインデックスはデータ ファイルに存在しないために削除できないことから発生します。
インデックスの作成時に、SQL ステートメントで IN DICTIONARY と USING の両方を使用した場合、USING で指定された番号を使用する新しいインデックスは、指定されたインデックス番号のセグメントが SQL 列と合致する場合には、DDF にのみ挿入されます。USING キーワードで指定された番号が SQL 列と合致しないか、またはデータ ファイルに存在しない場合、SQL エンジンは「Btrieve キー定義がインデックス定義と一致しません」というエラー メッセージを返します。これにより、ファントム インデックスが作成されないようになります。
メモ:IN DICTIONARY キーワードをバウンド データベースで使うことはできません。
このセクションでは、IN DICTIONARY のいくつかの例を示します。
次の例では、データ ファイルと関連付けられない「デタッチされた」テーブルが作成され、その後でテーブル定義へのインデックスの追加と削除が行われます。このインデックスは、関連付けられる基となる Btrieve インデックスが存在しないため、デタッチされたインデックスとなります。
CREATE TABLE t1 IN DICTIONARY (c1 int, c2 int)
CREATE INDEX idx_1 IN DICTIONARY on t1(c1)
DROP INDEX t1.idx_1 IN DICTIONARY
============ 
次の例では、既存のテーブル T1 を使用します。データ ファイルには key1 が定義されていますが、現在のところ X$Index には定義されていません。
CREATE INDEX idx_1 USING 1 IN DICTIONARY on T1 (C2)
関連項目
DROP INDEX