CREATE INDEX
CREATE INDEX ステートメントは、現行サーバーで表の索引を作成します。
呼び出し
このステートメントは、アプリケーション・プログラムに組み込むことができ、また対話式に呼び出すこともできます。 これは、動的に準備できる実行可能ステートメントです。
権限
このステートメントの権限 ID が保持する特権には、少なくとも以下の 1 つ が含まれていなければなりません。
- スキーマ内に作成する特権。詳しくは、スキーマ内で作成する必要のある権限を参照してください。
- データベース管理者権限
このステートメントの権限 ID が保持する特権には、少なくとも以下の 1 つ が含まれていなければなりません。
- 次のシステム権限
- 論理ファイル作成 (CRTLF) コマンドに対する *USE 権限。
- データ・ディクショナリーに対する *CHANGE 権限。ただし、索引が作成され るライブラリーが、データ・ディクショナリーを持つ SQL スキーマの場合。
- データベース管理者権限
このステートメントの権限 ID によって保持される特権には、少なくとも次の 1 つが含まれていなければなりません。
- 参照される表の場合
- 該当の表に対する INDEX 特権。
- その表が入っているライブラリーに対する *EXECUTE システム権限
- データベース管理者権限
SQL 名が指定され、該当の表が作成されるライブラリーの名前と同じ名前の ユーザー・プロファイルが存在し、しかもその名前がステートメントの権限 ID と異なっている場合、ステートメントの権限 ID によって保持される特権 には、少なくとも次の 1 つが含まれていなければなりません。
- その名前を持つユーザー・プロファイルに対する *ADD システム権限
- データベース管理者権限
特殊タイプを参照する場合は、ステートメントの権限 ID によって保持される 特権に、少なくとも次のいずれか 1 つが含まれなければなりません。
- ステートメント内で識別された、それぞれの特殊タイプごとに、
- その特殊タイプに対する USAGE 特権、および
- その特殊タイプを含むライブラリーに対する *EXECUTE システム権限
- データベース管理者権限
SQL 特権に対応するシステム権限については、『表またはビューへの権限を検査する際の対応するシステム権限』を参照してください。
構文
>>-CREATE--+----------------------------+-----------------------> +-UNIQUE--+----------------+-+ | '-WHERE NOT NULL-' | '-ENCODED VECTOR-------------' >--INDEX--index-name--+-------------------------------------------+--> '-FOR SYSTEM NAME--system-object-identifier-' .-,------------------------. V .-ASC--. | >--ON--table-name--(----key-expression--+------+-+--)-----------> '-DESC-' >--+-------------------------+--index-options------------------>< '-WHERE--search-condition-' key-expression |--expression--+--------------------------------------------------------------+--| | .-AS-. | '-+----+--column-name--+-------------------------------------+-' | .-COLUMN-. | '-FOR--+--------+--system-column-name-' index-options |--+-------------------------------------+----------------------> | .-DISTINCT-. | '-WITH--integer--+----------+--VALUES-' >--+-----------------+------------------------------------------> +-NOT PARTITIONED-+ '-PARTITIONED-----' >--+--------------------------------------------------------------+--> | .-,-----------------------------------------. | | V | | '-INCLUDE--(----aggregate-function-name -(--expression--)-+--)-' .-PAGESIZE--64------. >--+-------------------+----------------------------------------> '-PAGESIZE--+-8---+-' +-16--+ +-32--+ +-128-+ +-256-+ '-512-' >--+---------------------------------------------------+--------> | .-ADD ALL COLUMNS----------. | '-RCDFMT--format-name--+--------------------------+-' +-ADD KEYS ONLY------------+ | .-,---------------. | | V | | '-ADD------column-name---+-' (1) >--+------------------+--+-------------------+------------------| '-media-preference-' '-memory-preference-' media-preference .-UNIT ANY-. |--+-UNIT SSD-+-------------------------------------------------| memory-preference |--KEEP IN MEMORY--+-NO--+--------------------------------------| '-YES-'
- 索引オプション はどのような順序で指定しても構いません。
説明
- UNIQUE
- 表に、同一の索引キーの値を持つ行が複数入るのを防止します。
UNIQUE を使用する場合、列のすべての NULL 値が等しいものと見なされます。例えば、NULL 値を入れることができる単一の列をキーにすると、その列には、NULL 値が 1 つしか入らなくなります。この制約が適用されるのは、表の行を更新するときと、新しい行を挿入するときです。
CREATE INDEX ステートメントの実行時にも、この制約が検査されます。 重複するキーの値を持つ行が既に表に入っている場合、索引は作成されません。
- UNIQUE WHERE NOT NULL
- 表に、同一の索引キーの値を持つ行が複数入るのを防止します。列が NULL 値の場合には、それらすべては等しいとは見なされません。 1 つの列に NULL 値が複数入ることが許可されます。その他の点では、UNIQUE と同じです。
- ENCODED VECTOR
- これを指定すると、結果の索引は、コード化ベクトル索引 (EVI) になります。
コード化ベクトル索引を使用して、行の順序を保証することはできません。 これは、データベース・マネージャーが照会のパフォーマンスを向上させる場合に使用します。 詳細については、「データベース パフォーマンスおよび Query 最適化」トピック集を参照してください。
- index-name
- 索引の名前を指定します。
暗黙的または明示的修飾子も含め、この名前は、現行サーバーに既に存在している索引、表、ビュー、別名、またはファイルと同じ名前にすることはできません。
SQL 名が指定されている場合、索引は、暗黙的または明示的修飾子で 指定しているスキーマ内に作成されます。
システム名が指定されている場合、索引名は、修飾子で指定しているスキーマ 内に作成されます。修飾されていない場合、索引名は、その索引の作成に使用した表と同じスキーマ内に作成されます。
索引名 が有効なシステム名でなく、FOR SYSTEM NAME 節 が使用されていない場合、DB2® for i は システム名を生成します。名前の生成に関する規則については、表名の生成の規則を参照してください。
- FOR SYSTEM NAME system-object-identifier
- 索引のシステム・オブジェクト ID を示します。システム・オブジェクト ID は、現行サーバーに
既に存在する表、ビュー、別名、または索引と同一であってはなりません。
システム・オブジェクト ID は、非修飾システム
ID でなければなりません。
システム・オブジェクト ID が 指定される場合、索引名 は有効なシステム・オブジェクト名であってはなりません。
- ON table-name
- その索引を作成したい表を指定します。
この表名 は、現行サーバーに存在している基本表 (ビューではなく) を識別するものでなくてはなりません。
表がパーティション化された表である場合、単一パーティションを識別する別名を指定できます。 その場合、作成される索引は、指定されたパーティション上だけで作成されます。
- key-expression
- 索引キーを構成する列または式を識別します。
索引に定義するキーの数は 120 を超えてはならず、それぞれのバイト長の合計は 32766-n を超えてはなりません。ここで、n は NULL が許されると指定されたキーの数です。
- expression
- 式 に列名 のみが入る場合、この列名は、表の列を識別する非修飾名でなければなりません。式 は列参照を
含んでいなければなりません。
次のような場合は、同一の列名 を複数回指定することはできません。
- WHERE 文節、INCLUDE 文節、または RCDFMT 文節が指定されている、
- 式が索引キーの一部として定義されている、または
- 列が AS 文節を使用して名前変更されている。
expression が列名ではない場合、フィールド・プロシージャーを含む列を expression が参照することはできません。
列名 では、LOB 列、XML 列、または DATALINK 列あるいは LOB 列、XML 列、または DATALINK 列に基づく特殊タイプを識別することはできません。expression が列名ではない場合、中間結果式および最終結果式のデータ・タイプを DATALINK、LOB、および XML にすることはできません。次のいずれも含めることはできません。- 副照会
- 集約関数
- 変数
- グローバル変数
- パラメーター・マーカー
- 特殊レジスター
- シーケンス参照
- OLAP の指定
- ROW CHANGE 式
- REGEXP_LIKE 述部
- 特殊タイプの作成に伴って暗黙に生成された関数以外のユーザー定義関数
- deterministic でない関数
- 以下の組み込みスカラー関数:
ATAN2 DLURLPATH MONTHNAME SCORE CARDINALITY DLURLPATHONLY MONTHS_BETWEEN SOUNDEX CONTAINS DLURLSCHEME NEXT_DAY TABLE_NAME CURDATE DLURLSERVER OVERLAY TABLE_SCHEMA CURTIME DLVALUE NOW TIMESTAMP_FORMAT DATAPARTITIONNAME ENCRYPT_AES RAISE_ERROR TIMESTAMPDIFF DATAPARTITIONNUM ENCRYPT_RC2 RAND TRUNC_TIMESTAMP DAYNAME ENCRYPT_TDES REGEXP_COUNT VARCHAR_FORMAT DBPARTITIONNAME GENERATE_UNIQUE REGEXP_INSTR VERIFY_GROUP_FOR_USER DECRYPT_BINARY GETHINT REGEXP_REPLACE WEEK_ISO DECRYPT_BIT IDENTITY_VAL_LOCAL REGEXP_SUBSTR WRAP DECRYPT_CHAR INSERT REPEAT XMLPARSE DECRYPT_DB LPAD REPLACE XMLVALIDATE DIFFERENCE LOCATE_IN_STRING ROUND_TIMESTAMP XSLTRANSFORM DLURLCOMPLETE 1 MAX_CARDINALITY RPAD 1 FILE LINK CONTROL と READ PERMISSION DB の属性が指定されたデータ・リンクの場合。
- column-name
- 索引の列の名前を指定します。
索引の複数の列または索引のシステム列名 に同一の名前を使用しないでください。
式 が列名でない場合および名前が指定されていない場合、名前は索引キー列に生成されます。 名前は SQLIXxxxxx になります。ここで xxxxx は、索引に固有の列名を作成する番号です。
- FOR COLUMN システム列名
- 列の IBM® i 名を指定します。
索引の複数の列または索引の列名に、同一の名前を使用しないでください。
システム列名が指定されず、また列名が有効なシステム列名でない場合には、システム列名が生成されます。 名前は SQLIXxxxxx になります。ここで xxxxx は、索引に固有の列名を作成する番号です。
- ASC
- 索引項目を列値の昇順で保持することを指定します。 ASC がデフォルトです。
- DESC
- 索引項目を列値の降順で保持することを指定します。
順序付けは、割り当ておよび比較で説明した比較規則にしたがって行われます。 NULL 値は、他のどの値よりも上位に置かれます。
- WHERE search-condition
- 索引に含まれる行に対して適用する条件を指定します。 検索条件 に、副照会を指定する述部を入れることはできません。 キー式 の制約事項としてリストされた項目のいずれも含めることはできません。
- WITH integer DISTINCT VALUES
- 特殊キー値の見積数を指定します。 この文節は、あらゆるタイプの索引に対して指定することができます。
コード化ベクトル索引の場合は、これを使用し、それぞれの特殊キー値に割り当てられるコードの初期サイズを決定します。1、2、および 4 バイトコードのみが使用されます。 INCLUDE 文節が指定されない場合、デフォルト値は 255 (1 バイトコード) です。 それ以外の場合は、4 バイトコードが使用されます。 索引の作成または再作成中に、特殊値の数が、コードのサイズでサポートされる最大数を超えた場合、コードのサイズが増加されます。
非コード化ベクトル索引の場合、この文節は無視されます。
- PARTITIONED
- 表に定義された各データ・パーティションごとに、 指定した列を使用して索引パーティションを作成することを指定します。 表名では、パーティション化された表を識別する必要があります。 索引が固有である場合、その索引の列はデータ・パーティション・キーの列と同じであるか、 そのスーパーセットでなければなりません。 索引が固有ではなく、表がパーティション化されている場合、PARTITIONED がデフォルトになります。
- NOT PARTITIONED
- 表に定義されたデータ・パーティションのすべてにわたる単一の索引を作成することを指定します。
表名では、パーティション化された表を識別する必要があります。
索引が固有であり、表がパーティション化されている場合、NOT PARTITIONED がデフォルトになります。
パーティション化されていない表の索引も、デフォルトではパーティション化されません。
エンコードされたベクトル索引が指定された場合、NOT PARTITIONED は使用できません。
- PAGESIZE
- 索引に使用する論理ページを K バイト単位で指定します。一般に、論理ページのサイズが大きい索引の方が、照会処理におけるスキャン時の効率がよくなります。
単純索引探索や個別キー検索の場合は、一般に論理ページのサイズが小さい索引の方が効率がよくなります。
PAGESIZE のデフォルト値はキーの長さで決まり、最小値は 64 です。
エンコードされたベクトル索引が指定された場合、PAGESIZE は使用できません。
- INCLUDE
- 索引に含まれる集約関数式を指定します。こうした集約関数式を指定すると、索引で、照会の集約結果を直接戻すことができるようになります。INCLUDE を使用できるのは、エンコード化ベクトル索引に対してのみです。
- aggregate-function-name ( expression )
- この集約関数名は、
組み込み関数 AVG、COUNT、COUNT_BIG、SUM、STDDEV、STDDEV_SAMP、
VARIANCE、 または VARIANCE_SAMP のいずれかでなければなりません。DISTINCT キーワードを指定してはなりません。この集約関数の expression 引数には、key-expression の制約事項としてリストされた項目のいずれも含めることはできません。
- RCDFMT format-name
- 索引の IBM i レコード・フォーマット名を指定する非修飾名です。format-name は、システム ID です。レコード・フォーマット名が指定されていない場合は、次のようになります。
- キー列が式でない場合およびキー列が AS 文節を使用して明示的に指定されていない場合、索引は、表名 のフォーマットを共有します。
- それ以外の場合、索引は表名 のフォーマットを共有し、 フォーマット名 は索引のシステム・オブジェクト名 と 同じです。
INCLUDE キーワードを指定する場合、 RCDFMT は使用できません。
- ADD ALL COLUMNS
- 表名 のすべての隠されていない列が索引のフォーマットに追加されるように指定します。 すべての列は、表名 のフォーマットに表示されるものと同一の順で定義され、索引キーとして定義された式の前に置かれます。
- ADD KEYS ONLY
- 索引キー列として指定された列のみが、索引のフォーマットに追加されるように指定します。 表名 からの他の列は追加されません。
- ADD column-name
- リストされた列が索引のフォーマットに追加されるように指定します。 索引キー列が先頭になり、その後に追加の列が続きます。
media-preference
索引の優先ストレージ・メディアを指定します。
- UNIT ANY
- どのストレージ・メディアも優先しません。索引のストレージは、使用可能な任意のストレージ・メディアから割り振られます。
- UNIT SSD
- ソリッド・ステート・ディスク・ストレージ・メディア を優先します。索引のストレージは、使用可能な場合にはソリッド・ステート・ディスク・ストレージ・メディアから割り振ることができます。
memory-preference
- KEEP IN MEMORY
- 索引のデータ
が照会で使用されるときに、データを主記憶域プールに入れるかどうかを指定します。
- NO
- データは主記憶域プールに入れられません。
- YES
- データは主記憶域プールに入れられます。
注
ステートメントの影響 : CREATE INDEX は、索引の記述を作成します。 指定した表に既にデータが入っていれば、CREATE INDEX によって、そのデータに関する索引項目が作成されます。 表にまだデータが入っていない場合、索引項目は、表にデータが挿入されたときに作成されます。
照合シーケンス: SBCS または混合データを含む列に対して作成される索引は、このステートメントの実行時点で有効な照合シーケンスに従って作成されます。 照合シーケンスが *HEX 以外の場合は、SBCS データまたは混合データのキーは、該当の照合シーケンスに基づいてキーが重み付けされた値です。
索引の属性 : 索引はキー付き論理ファイルとして作成されます。 索引が作成される場合、ファイル待ち時間とレコード待ち時間の属性は、論理ファイル作成 (CRTLF) コマンドの WAITFILE キーワードと WAITRCD キーワード上に指定されたデフォルト値に設定されます。
日時の結果列に使用される日時形式は ISO です。
分散表に対して作成される索引は、この表が配布されるサーバーのすべてで 作成されます。 分散表についての詳細は、DB2 マルチシステム (Multisystem) を参照してください。
索引の所有権: SQL 名が指定されている場合、
- 作成した索引が入れられるスキーマと同じ名前のユーザー・プロファイルが存在する場合、索引の所有者 はそのユーザー・プロファイルです。
- その他の場合は、索引の所有者 は、この ステートメントを実行しているスレッドのユーザー・プロファイルまたは グループ・ユーザー・プロファイルです。
システム名を指定した場合は、索引の所有者 は、この ステートメントを実行しているスレッドのユーザー・プロファイルまたはグループ・ユーザー・プロファイルです。
索引の権限 : SQL 名を使用する場合は、索引は、*PUBLIC に対するシステム権限 *EXCLUDE を使用して作成されます。 システム名を使用する場合、索引は、スキーマの作成権限 (CRTAUT) パラメーターによって決められる *PUBLIC に対する権限を使用して作成されます。
索引の所有者がグループ・プロファイルのメンバー (GRPPRF キーワード) であり、グループ権限が指定されている (GRPAUT キーワード) 場合は、そのグループ・プロファイルにも、その索引に対する権限が与えられます。
例
例 1: PROJECT 表に UNIQUE_NAM という名前の索引を作成します。 この索引の目的は、表内に、同じプロジェクト名 (PROJNAME) が重複して入ることがないようにすることです。 索引項目は昇順になります。
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: DEPARTMENT 表に DEPT_TYPE という名前の索引を作成します。 索引項目は、部門のタイプに従って昇順に並べます。部門のタイプは、部門番号 (DEPTNO) の 2 文字目および 3 文字目で判別されます。
CREATE INDEX DEPT_TYPE
ON DEPARTMENT (SUBSTR(DEPTNO,2,2))