CREATE UNIQUE CLUSTERED INDEX index-name ON TABLE owner.catalog.schema.table (field1 ASC, field2 DESC)
インデックス名
インデックス名は、指定されたテーブル内では一意の必要があります。インデックス名は、識別子規約に従い、以下の制限を受けます。既定のインデックス名は、簡単な識別子です。インデックス名は区切り文字付き識別子とすることができます。インデックス名は、128 文字を超えることはできません。インデックス名では、大文字と小文字が区別されません。
InterSystems IRIS は、("SqlName" と呼ばれる) 入力された名前を使用して、クラスおよびグローバル内で対応するインデックス・プロパティ名を生成します。このインデックス・プロパティ名には英数字 (文字と数字) のみを使用でき、最大長は 96 文字です。InterSystems IRIS は、インデックス・プロパティ名を生成するために、最初に指定した SqlName から句読点を削除し、次に 96 文字以内の一意の識別子を生成します。
-
インデックス名は、フィールド、テーブル、ビューと同じ名前にすることができますが、このような名前の重複はお勧めできません。
-
インデックス・プロパティ名 (句読点の削除後) は一意である必要があります。重複する SQL インデックス名を指定すると、SQLCODE -324 エラーが生成されます。既存の SQL インデックス名とは句読点のみ異なる SQL インデックス名を指定すると、一意のインデックス・プロパティ名を作成するために、最後の文字が大文字 (“A” で始まる) に置き換えられます。したがって、句読点のみ異なる SQL インデックス名を作成することは可能です (ただし推奨はしません)。
-
インデックス・プロパティ名は文字で始まる必要があります。したがって、インデックス名の先頭の字または最初の句読点の後ろの字は、文字にする必要があります。有効な文字は、$ZNAME テストに合格する文字です。SQL インデックス名の最初の文字が句読点 (% または _) で、2 番目の文字が数字である場合、InterSystems IRIS は、削除されたインデックス・プロパティ名の最初の文字として小文字の “n” を追加します。
-
インデックス名は 31 文字よりも大幅に長くすることができますが、最初の 31 文字の英数字と異なるインデックス名にすると作業が簡単になります。
管理ポータルの SQL インタフェース [カタログの詳細] には、各インデックスの SQL インデックス名 ([SQL マップ名]) および対応するインデックス・プロパティ名 ([インデックス名]) が表示されます。
既存インデックスと同じ名前のインデックスを作成する場合に何が起こるかは、以下に説明しています。
既存インデックス
既定で InterSystems IRIS は、テーブルに既存インデックスと同じ名前のインデックスを作成することを拒否し、SQLCODE -324 エラーを発行します。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[既存インデックスに対して DDL CREATE INDEX を許可] 設定が表示されます。既定値は 0 で、この設定を推奨します。このオプションを 1 に設定した場合、InterSystems IRIS はクラス定義から既存インデックスを削除して、CREATE INDEX を実行して再作成します。CREATE INDEX で指定されたテーブルから、指定されたインデックスを削除します。このオプションにより、UNIQUE 制約インデックスの削除/再作成が可能になります (DROP INDEX コマンドでは実行できません)。主キー・インデックスの削除/再作成は、"ALTER TABLE" コマンドを参照してください。
管理ポータル、[システム管理]、[構成]、[SQL とオブジェクトの設定]、[SQL] から [冗長な DDL ステートメントを無視] チェック・ボックスにチェックを付けることにより、このオプション (および他の同様の作成、変更、および削除のオプション) をシステム全体で設定できます。
ただし、このオプションが既存インデックスの再作成を許可するように設定されていても、テーブルにデータが格納されている場合は、主キー IDKEY インデックスの再作成はできません。これを実行しようとすると、SQLCODE -324 エラーが生成されます。
テーブル名
既存のテーブルの名前を指定する必要があります。
-
table-name が存在しない場合、CREATE INDEX は SQLCODE -30 エラーで失敗し、%msg が "テーブルの 'SQLUSER.MYTABLE' は存在しません" に設定されます。
-
table-name がビューの場合、CREATE INDEX は SQLCODE -30 エラーで失敗し、%msg が [ビュー SQLUSER.MYVIEW 上の CREATE INDEX 'My_Index' の試みは失敗しました。] に設定されます。インデックスは、ビューではなく、テーブルに対してのみサポートされています。
インデックスを作成するとテーブルの定義が変更されます。ユーザにテーブル定義を変更する特権がない場合、CREATE INDEX は QLCODE -300 エラーで失敗し、%msg が "DDL がクラス schema.tablename に対して有効になっていません" に設定されます。
フィールド名
インデックスを作成するフィールド名は 1 つ以上指定する必要があります。フィールド名は 1 つだけ指定するか、コンマで区切った複数のフィールド名のリストを括弧で囲んで指定します。重複するフィールド名を使用でき、インデックス定義に保持できます。複数のフィールドを指定した場合、Group By State の後で各州内の Group By City を実行するような、GROUP BY 操作のパフォーマンスが向上する場合があります。通常は、重複するデータが大量にあるフィールド (または複数のフィールド) にインデックスを作成しないでください。例えば、人のデータベースの Name フィールドでインデックスを作成するのは、ほとんどの人名が一意であるため、問題ありません。しかし、State フィールドでインデックスを作成するのは、ほとんどの場合、重複するデータが大量に出現するため、適切ではありません。指定するフィールドは、テーブルまたはテーブルの永続クラスのスーパークラスで定義される必要があります (もちろん、すべてのクラスをコンパイルする必要があります)。存在しないフィールドを指定すると、SQLCODE -31 エラーが生成されます。
通常のデータ・フィールドに加えて、CREATE INDEX を使用して以下のインデックスを作成できます。
インデックスをストリーム値フィールドに対して作成することはできません。
IDKEY フィールド (プロパティ) のいずれかが [SQL 計算] の場合には、IDKEY フィールドが複数あるインデックスを作成することはできません。この制限は、IDKEY フィールドが 1 つしかないインデックスには適用されません。1 つのインデックスに複数の IDKEY フィールドがある場合は、各フィールドの区切り文字列として “||” (二重の垂直バー) が使用されています。したがって、IDKEY フィールドのデータにはこの文字列を使用できません。
埋め込みオブジェクト (%SerialObject) 内のフィールド
埋め込みオブジェクト内のフィールドにインデックスを作成するには、その埋め込みオブジェクトを参照するテーブル (%Persistent クラス) でインデックスを作成します。CREATE INDEX の field-name で、次の例のように、テーブル (%Persistent オブジェクト) 内の参照フィールドの名前を埋め込みオブジェクト (%SerialObject) 内のフィールド名にアンダーバーで結合して指定します。
CREATE INDEX StateIdx ON TABLE Sample.Person (Home_State)
ここでは、Home は、State フィールドを含む埋め込みオブジェクト Sample.Address を参照する Sample.Person 内のフィールドです。
永続クラスの参照プロパティに関連付けられている埋め込みオブジェクト・レコードのみにインデックスが作成されます。%SerialObject プロパティにインデックスを直接作成することはできません。
埋め込みオブジェクト (シリアル・オブジェクトとも呼ばれる) の定義の詳細は、"埋め込みオブジェクト (%SerialObject)" を参照してください。埋め込みオブジェクトで定義されているプロパティ (フィールド) にインデックスを作成する方法の詳細は、"埋め込みオブジェクト (%SerialObject) のプロパティのインデックス作成" を参照してください。
インデックス・クラス名
このオプションの構文を使用すると、ユーザが SQL を使用して機能インデックスのクラスとパラメータを指定できます。
以下に SQL の例を示します。
CREATE INDEX HistIdx ON TABLE Sample.Person (MedicalHistory) AS %iFind.Index.Basic (LANGUAGE='en', LOWER=1)
詳細は、"InterSystems SQL の使用法" の "SQL Search のためのソースのインデックス作成" を参照してください。
WITH DATA 節
この節を指定すると、インデックスの読み込みのみでクエリを解決できるようになるので、ディスク I/O の量が減り、パフォーマンスが向上します。
field-name で文字照合を使用する場合は、field-name と WITH DATA の datafield-name に同じフィールドを指定する必要があります。これにより、マスタ・マップに移動せずに、照合されていない値を取得できます。field-name 内の値が文字照合を使用しない場合は、WITH DATA の datafield-name でこのフィールドを指定しても利点がありません。
WITH DATA の datafield-name にはインデックスが作成されていないフィールドを指定できます。これにより、多くのクエリをマスタ・マップに移動せずにインデックスから実行できます。トレードオフは、維持する必要があるインデックスの数です。また、インデックスにデータを追加すると非常に大きくなるので、データを必要としない操作が遅くなります。
テーブルの永続クラスに対し、スーパークラスで定義されるフィールドを WITH DATA datafield-name で指定できます。
UNIQUE キーワード
UNIQUE キーワードを使用して、インデックス内の各レコードが一意の値を持つよう指定できます。具体的には、インデックス内に (つまり、インデックスがあるテーブル内に) 同じ照合された値を持つレコードは存在しないよう指定できます。既定では、大半のインデックスは、(大文字小文字に関係なく検索するように) 大文字の文字照合を使用します。この場合、“Smith” と “SMITH” は等しい値であり、それぞれ一意的ではありません。CREATE INDEX では、既定以外のインデックス文字列照合を指定できません。クラス定義でインデックスを定義することで、個々のインデックスに対して異なる文字列照合を指定できます。
ネームスペースの既定の照合を変更することで、既定でフィールド/プロパティの大文字と小文字が区別されるようにすることができます。このオプションを変更するには、ネームスペースですべてのクラスをリコンパイルし、すべてのインデックスを再構築する必要があります。管理ポータルで [クラス] オプションを選択します。次にストアド・クエリのネームスペースを選択し、[コンパイル] オプションを使用して、対応するクラスをリコンパイルします。その後すべてのインデックスを再構築します。これで大文字と小文字が区別されるようになります。
Caution:
他のユーザがテーブルのデータにアクセスしている間はインデックスを再構築しないでください。再構築すると、クエリ結果が不正確になる可能性があります。
BITMAP キーワード
BITMAP キーワードを使用する場合、このインデックスは、ビットマップ・インデックスであることを指定できます。ビットマップ・インデックスは、ビット位置が行 ID を表す1 つ以上のビット文字列と、その行内のフィールドに固有の値が存在する (1) または存在しない (0) ことを表す各ビット値 (または組み合わされる field-name フィールドの値) で構成されます。InterSystems SQL は、データの挿入、更新、削除を行う場合にこれらの位置ビットを (圧縮されたビット文字列として) 維持します。ビットマップ・インデックスと通常のインデックスのどちらを使用しても、INSERT、UPDATE、または DELETE 操作のパフォーマンスには大きな違いはありません。ビットマップ・インデックスは、クエリ操作の多数のタイプで非常に効率的です。ビットマップ・インデックスは、以下の特性を持ちます。
-
ビットマップ・インデックスを定義できるテーブル (クラス) は、システムによって割り当てられた正の整数の RowID 値を使用しているものか、カスタム ID 値の定義に主キー IDKEY を使用しているもの (IDKEY が %Integer タイプで MINVAL が 0 より大きい単一のプロパティ、または %Numeric タイプで SCALE が 0 と等しく MINVAL が 0 より大きい単一のプロパティに基づいている場合) に限られます。
$SYSTEM.SQL.Util.SetOption()Opens in a new tab メソッド SET status=$SYSTEM.SQL.Util.SetOption("BitmapFriendlyCheck",1,.oldval) を使用すると、この制限をコンパイル時にチェックし、定義されたビットマップ・インデックスが %Storage.SQL クラス内で許可されるかどうかを判断するようにシステム全体用の構成パラメータを設定できます。このチェックは、%Storage.SQL を使用するクラスにのみ適用されます。既定値は 0 です。$SYSTEM.SQL.Util.GetOption("BitmapFriendlyCheck")Opens in a new tab を使用すると、このオプションの現在の構成を判断できます。
ビットマップ・インデックスは、既定の (%Storage.Persistent) 構造を使用しているテーブルに対してのみ定義できます。子テーブルなど、複合キーを持つテーブルでは、ビットマップ・インデックスを使用できません。テーブルの作成に (クラス定義の使用と反対に) DDL を使用する場合、これらの要件に合致し、ビットマップ・インデックスを有効に利用できます。
-
ビットマップ・インデックスは、フィールドの個別値の数が限られ、比較的少ない場合にのみ使用できます。例えば、性別、国籍、タイムゾーンなどのフィールドに適しています。ビットマップは、UNIQUE 制約を持つフィールドでは使用しないでください。フィールドに 10,000 を超える個別値がある場合や、複数のインデックス・フィールドに 10,000 を超える個別値がある場合は、ビットマップを使用しないでください。
-
ビットマップ・インデックスは、WHERE 節内で論理 AND および OR 演算子と組み合わせると非常に効果的です。一般に 2 つ以上のフィールドを組み合わせてクエリを実行する場合は、これらのフィールドにビットマップ・インデックスを定義すると効果的です。
詳細は、"ビットマップ・インデックス" を参照してください。
BITMAPEXTENT キーワード
ビットマップ・エクステント・インデックスは、テーブル自体のビットマップ・インデックスです。InterSystems SQL はこのインデックスを使用して、テーブル内のレコード (行) 数を返す COUNT(*) のパフォーマンスを改善します。テーブルには最大 1 つのビットマップ・エクステント・インデックスを指定できます。複数のビットマップ・エクステント・インデックスを作成しようとすると、SQLCODE -400 エラーが発生し、%msg ERROR #5445: 複数のエクステント・インデックスが定義されています: DDLBEIndex というメッセージが表示されます。
CREATE TABLE を使用して定義されたすべてのテーブルでは、自動的にビットマップ・エクステント・インデックスが定義されます。この自動的に生成されたインデックスには、インデックス名 DDLBEIndex および SQL マップ名 %%DDLBEIndex が割り当てられます。クラスとして定義されたテーブルには、$ClassName のインデックス名と SQL マップ名で定義されたビットマップ・エクステント・インデックスを指定できます。
CREATE BITMAPEXTENT INDEX を使用して、テーブルにビットマップ・エクステント・インデックスを追加したり、自動的に生成されたビットマップ・エクステント・インデックスの名前を変更したりできます。指定する index-name は、テーブルの table-name と対応するクラス名にする必要があります。これはインデックスの SQL マップ名になります。field-name または WITH DATA 節は指定できません。
以下の例では、ビットマップ・エクステント・インデックスを、インデックス名 DDLBEIndex と SQL マップ名 Patient を指定して作成します。Sample.Patient に既に %%DDLBEIndex ビットマップ・エクステント・インデックスがある場合、この例ではそのインデックスを次の SQL マップ名 Patient の名前に変更します。
&sql(CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient)
WRITE !,"SQL code: ",SQLCODE
詳細は、"ビットマップ・エクステント・インデックス" を参照してください。
BITSLICE キーワード
BITSLICE キーワードを使用する場合、このインデックスは、ビットスライス・インデックスであることを指定できます。ビットスライス・インデックスは、計算で使用される数値データでのみ使用されます。ビットスライス・インデックスは、各数値データをバイナリのビット文字列として表します。ビットスライス・インデックスは、ビットマップ・インデックスのようにブーリアン・フラグを使用して数値データのインデックスを作成するのではなく、各レコードに個別のビット文字列である各数値のビット文字列を作成します。これは高速の集約計算のためにのみ使用される非常に特殊なタイプのインデックスです。例えば、以下はビットスライス・インデックスを使用する例です。
SELECT SUM(Salary) FROM Sample.Employee
文字列データ・フィールドについてビットスライス・インデックスを作成できますが、ビットスライス・インデックスは、これらのデータ値をキャノニック形式の数値として表します。つまり、“abc” のような数値以外の文字列には 0 としてインデックスが作成されます。このタイプのビットスライス・インデックスは、文字列フィールドの値があるレコードをすばやくカウントし、NULL のレコードをカウントしないような場合に使用できます。
ビットスライス・インデックスは、SQL クエリ・オプティマイザによって使用されないので、WHERE 節では使用しないでください。
INSERT、UPDATE、または DELETE 操作を使用してビットスライス・インデックスを生成および維持すると、ビットマップ・インデックスや通常のインデックスを使用するよりも速度が大幅に遅くなります。複数のビットスライス・インデックスを使用したり、頻繁に更新されるフィールドでビットスライス・インデックスを使用すると、パフォーマンス・コストが増える可能性があります。
ビットスライス・インデックスは、正の整数値を持つ、システムが割り当てた行 ID を持つレコードでのみ使用できます。ビットスライス・インデックスは、1 つの field-name でのみ使用できます。WITH DATA 節は指定できません。
詳細は、"ビットスライス・インデックス" を参照してください。
COLUMNAR キーワード
COLUMNAR キーワードを使用して、このインデックスが列指向インデックスになることを指定できます。目的の列を頻繁にクエリするものの、それが属するテーブルが行ストレージ構造を基盤としている場合に列指向インデックスを使用します。既定では、テーブルのそれぞれの行が $LIST として別々のグローバル添え字で格納されます。詳細は、"列指向インデックス" および "SQL テーブルのストレージ・レイアウトの選択" を参照してください。
インデックスの再構築
CREATE INDEX 文を使用してインデックスの生成を行うと自動的にインデックスが構築されます。しかし、インデックスを明示的に再構築することが必要になる場合もあります。
Caution:
他のユーザがテーブルのデータにアクセスしている場合、インデックスを再構築するには追加手順が必要となります。これを行わないと、クエリ結果が不正確になる場合があります。詳細は、"READONLY アクティブ・システム上でのインデックスの構築" を参照してください。
インデックスは以下のように構築/再構築できます。
-
BUILD INDEX SQL コマンドを使用する。
-
管理ポータルを使用して、指定クラス (テーブル) のすべてのインデックスを再構築する。
-
%BuildIndices() メソッドを使用する。
非アクティブ・テーブルのすべてのインデックスを再構築するには、以下を実行します。
SET status = ##class(myschema.mytable).%BuildIndices()
既定では、このコマンドにより、インデックスが再構築前に削除されます。この既定の削除をオーバーライドし、%PurgeIndices() メソッドを使用して、指定したインデックスを明示的に削除できます。特定範囲の ID の値で %BuildIndices() を呼び出す場合、既定では InterSystems IRIS はインデックスを削除しません。
以下のようにして、指定したインデックスを削除/再構築することもできます。
SET status = ##class(myschema.mytable).%BuildIndices($ListBuild("NameIDX","SpouseIDX"))
前述のように、壊れたインデックスの削除または再構築、あるいはインデックスの大文字/小文字区別の設定変更が可能です。ビットマップ・インデックスを再圧縮するには、削除/再構築ではなく %SYS.Maint.BitmapOpens in a new tab メソッドを使用します。
詳細は、"インデックスの構築" を参照してください。
例
以下の埋め込み SQL の例では、Fred という名前のテーブルを作成してから、Fred テーブルの Lastword フィールドと Firstword フィールドに対して (入力された名前 "Fred_Index" から句読点を削除して) "FredIndex" というインデックスを作成します。
&sql(CREATE TABLE Fred (
TESTNUM INT NOT NULL,
FIRSTWORD CHAR (30) NOT NULL,
LASTWORD CHAR (30) NOT NULL,
CONSTRAINT FredPK PRIMARY KEY (TESTNUM))
)
IF SQLCODE=0 { WRITE !,"Table created" }
ELSEIF SQLCODE=-201 { WRITE !,"Table already exists" }
ELSE { WRITE !,"SQL table create error code is: ",SQLCODE
QUIT }
&sql(CREATE INDEX Fred_Index
ON TABLE Fred
(LASTWORD,FIRSTWORD))
IF SQLCODE=-324 {
WRITE !,"Index already exists"
QUIT }
ELSEIF SQLCODE=0 { WRITE !,"Index created" }
ELSE { WRITE !,"SQL index create error code is: ",SQLCODE
QUIT }
以下の例は、Staff テーブルの City フィールドに対して、"CityIndex" という名前のインデックスを作成します。
CREATE INDEX CityIndex ON Staff (City)
以下の例は、Staff テーブルの EmpName フィールドに対して、EmpIndex という名前のインデックスを作成します。フィールド内に同一の値を持つ行を避けるために、UNIQUE 制約を使用します。
CREATE UNIQUE INDEX EmpIndex ON TABLE Staff (EmpName)
以下の例は、Purchase テーブルの SKU フィールドに対して、“SKUIndex” という名前のビットマップ・インデックスを作成します。BITMAP キーワードは、コードがビットマップ・インデックスであることを示します。
CREATE BITMAP INDEX SKUIndex ON TABLE Purchases (SKU)
関連項目
