CREATE INDEX

特権とロック

CREATE INDEX コマンドは特権を必要とする操作です。CREATE INDEX を使用する前に、%ALTER_TABLE 管理者特権または指定されたテーブルに対する %ALTER 特権があることを確認する必要があります。特権がない場合は、SQLCODE -99 エラー (特権違反) が返されます。%CHECKPRIV コマンドを呼び出すことにより、現在のユーザが %ALTER 特権を持っているかどうかを確認できます。$SYSTEM.SQL.CheckPriv()Opens in a new tab メソッドを呼び出すことにより、指定のユーザが %ALTER 特権を持っているかどうかを確認できます。適切な特権を持っている場合は、GRANT コマンドを使用してこのような特権を割り当てることができます。

CREATE INDEX は、テーブル・クラスの定義に [DdlAllowed] が含まれている場合を除き、永続クラスを定義して作成したテーブルでは使用できません。使用すると、操作は SQLCODE -300 エラーで失敗し、%msg が ”DDL がクラス schema.tablename に対して有効になっていません” に設定されます。

CREATE INDEX 文は table-name に対してテーブル・レベルのロックを取得します。これにより、他のプロセスはこのテーブルのデータを変更できなくなります。このロックは、CREATE INDEX 操作が終了すると自動的に解除されます。CREATE INDEX では、インデックス・データの入力を含め、インデックス作成操作が完了するまで、対応するクラス定義はロックされたままになります。

互換性のみにサポートされるオプション

Caché SQL は、構文解析の目的にのみ以下の CREATE INDEX オプションをサポートし、既存の SQL コードから Caché SQL への変換を支援します。これらのオプションは、実際には機能しません。

CLUSTERED | NONCLUSTERED
owner.catalog.
ASC | DESC

CREATE UNIQUE CLUSTERED INDEX index-name  ON TABLE owner.catalog.schema.table   (field1 ASC, field2 DESC)

インデックス名

インデックス名は、指定されたテーブル内では一意の必要があります。インデックス名は、識別子規約に従い、以下の制限を受けます。既定のインデックス名は、簡単な識別子です。インデックス名は、128 文字を超えることはできません。インデックス名では、大文字と小文字が区別されません。

Caché は、("SqlName" と呼ばれる) 入力された名前を使用して、クラスおよびグローバル内で対応するインデックス名を生成します。このインデックス名には英数字 (文字と数字) のみを使用でき、最大長は 96 文字です。Caché は、まず入力された SqlName から句読点を削除し、96 文字 (以内) の一意の識別子を生成して、一意にするために必要であれば最後の文字を大文字に置き換えて、インデックス名を生成します。

• インデックス名は、フィールド、テーブル、ビューと同じ名前にすることができますが、このような名前の重複はお勧めできません。

• インデックス名 (句読点の削除後) は文字で始まる必要があります。インデックス名の先頭の字または最初の句読点の後ろの字は、文字にする必要があります。有効な文字は、$ZNAME テストに合格する文字です。最初の文字が句読点 (% または _) で、2 番目の文字が数字である場合、Caché は、削除された名前の最初の文字として小文字の “n” を追加します。

• インデックス名 (句読点の削除後) は一意である必要があります。重複するインデックス名を指定すると、SQLCODE -324 エラーが生成されます。既存のインデックス名とは句読点のみ異なるインデックス名を指定すると、一意のインデックス名を作成するために、最後の文字が大文字 (“A”　で始まる) に置き換えられます。このため、句読点文字のみ異なるインデックス名を作成することは可能ですがお勧めできません。

• インデックス名は 31 文字よりも大幅に長くすることができますが、最初の 31 文字の英数字と異なるインデックス名にすると作業が簡単になります。

既存インデックスと同じ名前のインデックスを作成する場合に何が起こるかは、以下に説明しています。

既存インデックス

既定で Caché は、テーブルに既存インデックスと同じ名前のインデックスを作成することを拒否し、SQLCODE -324 エラーを発行します。この振る舞いは以下のように構成可能です。

• $SYSTEM.SQL.SetDDLNo324()Opens in a new tab メソッド呼び出し。現在の設定を確認するには、$SYSTEM.SQL.CurrentSettings()Opens in a new tab を呼び出します。これにより、[SQLCODE=-324 エラーの抑制] の設定が表示されます。

• 管理ポータルに進み、システム, 構成, 一般SQL設定 を選択します。[Allow DDL CREATE INDEX for Existing Index] の現在の設定を表示します。

既定値は “いいえ” (0) です。既定で Caché は、テーブルに既存インデックスと同じ名前のインデックスを作成することを拒否し、SQLCODE -324 エラーを発行します。ここでは、この設定を推奨します。

このオプションを “はい” (1) に設定した場合、Caché はクラス定義から既存インデックスを削除して、CREATE INDEX を実行して再作成します。CREATE INDEX で指定されたテーブルから、指定されたインデックスを削除します。このオプションにより、UNIQUE 制約インデックスの削除/再作成が可能になります (DROP INDEX コマンドでは実行できません)。主キー・インデックスの削除/再作成は、"ALTER TABLE" コマンドを参照してください。

ただし、このオプションが既存インデックスの再作成を許可するように設定されていても、テーブルにデータが格納されている場合は、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 を使用して以下のインデックスを作成できます。

• SERIAL フィールド (シリアル・クラスのプロパティ)

• IDENTITY フィールド

• コレクションの ELEMENTS 値または KEYS 値

インデックスをストリーム値フィールドに対して作成することはできません。

IDKEY フィールド (プロパティ) のいずれかが [SQL 計算] の場合には、IDKEY フィールドが複数あるインデックスを作成することはできません。この制限は、IDKEY フィールドが 1 つしかないインデックスには適用されません。1 つのインデックスに複数の IDKEY フィールドがある場合は、各フィールドの区切り文字列として　“||” (二重の垂直バー) が使用されています。したがって、IDKEY フィールドのデータにはこの文字列を使用できません。

WITH DATA 節

この節を指定すると、インデックスの読み込みのみでクエリを解決できるようになるので、ディスク I/O の量が減り、パフォーマンスが向上します。

field-name が文字照合を使用する場合は、field-name と WITH DATA の datafield-name に同じフィールドを指定する必要があります。これにより、マスタ・マップに移動する必要なく照合されていない値を取得できます。field-name 内の値が文字照合を使用しない場合は、WITH DATA の datafield-name でこのフィールドを指定しても利点がありません。

WITH DATA の datafield-name にはインデックスが作成されていないフィールドを指定できます。これにより、多くのクエリをマスタ・マップに移動せずにインデックスから実行できます。トレードオフは、維持する必要があるインデックスの数です。また、インデックスにデータを追加すると非常に大きくなるので、データを必要としない操作が遅くなります。

UNIQUE キーワード

UNIQUE キーワードを使用して、インデックス内の各レコードが一意の値を持つよう指定できます。具体的には、インデックス内に (つまり、インデックスがあるテーブル内に) 同じ照合された値を持つレコードは存在しないよう指定できます。既定では、大半のインデックスは、(大文字小文字に関係なく検索するように) 大文字の文字照合を使用します。この場合、“Smith” と “SMITH” は等しい値であり、それぞれ一意的ではありません。CREATE INDEX では、既定以外のインデックス文字列照合を指定できません。クラス定義でインデックスを定義することで、個々のインデックスに対して異なる文字列照合を指定できます。

ネームスペースの既定の照合を変更することで、既定でフィールド/プロパティの大文字と小文字が区別されるようにすることができます。このオプションを変更するには、ネームスペースですべてのクラスをリコンパイルし、すべてのインデックスを再構築する必要があります。管理ポータルで [クラス] オプションを選択します。次にストアド・クエリのネームスペースを選択し、[コンパイル] オプションを使用して、対応するクラスをリコンパイルします。その後すべてのインデックスを再構築します。これで大文字と小文字が区別されるようになります。

BITMAP キーワード

BITMAP キーワードを使用する場合、このインデックスは、ビットマップ・インデックスであることを指定できます。ビットマップ・インデックスは、ビット位置が行 ID を表す1 つ以上のビット文字列と、その行内のフィールドに固有の値が存在する (1) または存在しない (0) ことを表す各ビット値 (または組み合わされる field-name フィールドの値) で構成されます。Caché SQL は、データの挿入、更新、削除を行う場合にこれらの位置ビットを (圧縮されたビット文字列として) 維持します。ビットマップ・インデックスと通常のインデックスのどちらを使用しても、INSERT、UPDATE、または DELETE 操作のパフォーマンスには大きな違いはありません。ビットマップ・インデックスは、クエリ操作の多数のタイプで非常に効率的です。ビットマップ・インデックスは、以下の特性を持ちます。

• ビットマップ・インデックスを定義できるテーブル (クラス) は、システムによって割り当てられた正の整数の ID 値を使用しているものか、カスタム ID 値の定義に IDKEY を使用しているもの (IDKEY が %Integer タイプで MINVAL が 0 より大きい単一のプロパティ、または %Numeric タイプで SCALE が 0 と等しく MINVAL が 0 より大きい単一のプロパティに基づいている場合) に限られます。 $SYSTEM.SQL.SetBitmapFriendlyCheck()Opens in a new tab メソッドを使用すると、この制限をコンパイル時にチェックするようシステム全体用の構成パラメータを設定できます。$SYSTEM.SQL.GetBitmapFriendlyCheck()Opens in a new tab を使用すると、このオプションの現在の構成を判断できます。

  ビットマップ・インデックスは、既定の (%CacheStorageOpens in a new tab) 構造を使用しているテーブルに対してのみ定義できます。子テーブルなど、複合キーを持つテーブルでは、ビットマップ・インデックスを使用できません。テーブルの作成に (クラス定義の使用と反対に) DDL を使用する場合、これらの要件に合致し、ビットマップ・インデックスを有効に利用できます。

• ビットマップ・インデックスは、フィールドの個別値の数が限られ、比較的少ない場合にのみ使用できます。例えば、性別、国籍、タイムゾーンなどのフィールドに適しています。ビットマップは、UNIQUE 制約を持つフィールドでは使用しないでください。フィールドに 10,000 を超える個別値がある場合や、複数のインデックス・フィールドに 10,000 を超える個別値がある場合は、ビットマップを使用しないでください。

• ビットマップ・インデックスは、WHERE 節内で論理 AND および OR 演算子と組み合わせると非常に効果的です。一般に 2 つ以上のフィールドを組み合わせてクエリを実行する場合は、これらのフィールドにビットマップ・インデックスを定義すると効果的です。

詳細は、“Caché SQL 最適化ガイド” の “インデックスの定義と構築” の章の “ビットマップ・インデックス” のセクションを参照してください。

BITMAPEXTENT キーワード

ビットマップ・エクステント・インデックスは、テーブル自体のビットマップ・インデックスです。Caché SQL はこのインデックスを使用して、テーブル内のレコード (行) 数を返す COUNT(*) のパフォーマンスを改善します。テーブルには最大 1 つのビットマップ・エクステント・インデックスを指定できます。複数のビットマップ・エクステント・インデックスを作成しようとすると、SQLCODE -400 エラーが発生し、%msg ERROR #5445: 複数のエクステント・インデックスが定義されています: DDLBEIndex というメッセージが表示されます。

Caché 2015.2 以降では、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 の名前に変更します。

  ZNSPACE "Samples"
  &sql(CREATE BITMAPEXTENT INDEX Patient ON TABLE Sample.Patient)
  WRITE !,"SQL code: ",SQLCODE

詳細は、“Caché SQL 最適化ガイド” の “インデックスの定義と構築” の章の “ビットマップ・エクステント・インデックス” のセクションを参照してください。

BITSLICE キーワード

BITSLICE キーワードを使用する場合、このインデックスは、ビットスライス・インデックスであることを指定できます。ビットスライス・インデックスは、計算で使用される数値データでのみ使用されます。ビットスライス・インデックスは、各数値データをバイナリのビット文字列として表します。ビットスライス・インデックスは、ビットマップ・インデックスのようにブーリアン・フラグを使用して数値データのインデックスを作成するのではなく、各レコードに個別のビット文字列である各数値のビット文字列を作成します。これは高速の集約計算のためにのみ使用される非常に特殊なタイプのインデックスです。例えば、以下はビットスライス・インデックスを使用する例です。

SELECT SUM(Salary) FROM Sample.Employee

文字列データ・フィールドにビットスライス・インデックスを作成できますが、ビットスライス・インデックスでは、これらのデータ値は、キャノニック形式の数値として表されます。つまり、"abc" などの非数値文字列はすべて 0 としてインデックス付けされます。このタイプのビットスライス・インデックスは、文字列フィールドに値があって NULL としてカウントされないレコードを迅速にカウントする際に使用できます。

ビットスライス・インデックスは、SQL クエリ・オプティマイザによって使用されないので、WHERE 節では使用しないでください。

INSERT、UPDATE、または DELETE 操作を使用してビットスライス・インデックスを生成および維持すると、ビットマップ・インデックスや通常のインデックスを使用するよりも速度が大幅に遅くなります。複数のビットスライス・インデックスを使用したり、頻繁に更新されるフィールドでビットスライス・インデックスを使用すると、パフォーマンス・コストが増える可能性があります。

ビットスライス・インデックスは、正の整数値を持つ、システムが割り当てた行 ID を持つレコードでのみ使用できます。ビットスライス・インデックスは、1 つの field-name でのみ使用できます。WITH DATA 節は指定できません。

詳細は、“Caché SQL 最適化ガイド” の “インデックスの定義と構築” の章の “ビットスライス・インデックス” のセクションを参照してください。

インデックスの再構築

CREATE INDEX 文を使用してインデックスの生成を行うと自動的にインデックスが構築されます。しかし、インデックスを明示的に再構築することが必要になる場合もあります。

非アクティブ・テーブルのすべてのインデックスを再構築するには、以下を実行します。

  SET status = ##class(myschema.mytable).%BuildIndices()

既定では、このコマンドにより、インデックスが再構築前に削除されます。この既定の削除をオーバーライドし、%PurgeIndices() メソッドを使用して、指定したインデックスを明示的に削除できます。特定範囲の ID の値で %BuildIndices() を呼び出す場合、既定では Caché はインデックスを削除しません。

以下のようにして、指定したインデックスを削除/再構築することもできます。

  SET status = ##class(myschema.mytable).%BuildIndices($ListBuild("NameIDX","SpouseIDX"))

前述のように、壊れたインデックスの削除または再構築、あるいはインデックスの大文字/小文字区別の設定変更が可能です。ビットマップ・インデックスを再圧縮するには、削除/再構築ではなく %SYS.Maint.BitmapOpens in a new tab メソッドを使用します。

管理ポータルを使用して、指定クラス (テーブル) のすべてのインデックスを再構築することもできます。

詳細は、“Caché SQL 最適化ガイド” の “インデックスの定義と構築” の章の “インデックスの構築” のセクションを参照してください。