Hitachi

Hitachi Advanced Data Binder SQLリファレンス

3.5.1　CREATE INDEX文の指定形式および規則

実表の列にインデクス（B-treeインデクス，テキストインデクス，またはレンジインデクス）を定義します。B-treeインデクス，テキストインデクス，およびレンジインデクスについては，マニュアルHADB　システム構築・運用ガイドのB-treeインデクス，テキストインデクス，またはレンジインデクスを参照してください。

なお，複数の列に対してB-treeインデクスを定義することができます。1つの列に対して定義したB-treeインデクスを単一列インデクスといい，複数の列に対して定義したB-treeインデクスを複数列インデクスといいます。

重要

行を格納するセグメントが割り当てられている状態の実表に対してインデクスを定義した場合，インデクスが未完状態（インデクスデータが作成されない状態）になります。

例えば，次に示すタイミングでは，行を格納するセグメントが割り当てられていない状態です。この状態のときに実表にインデクスを定義すると，インデクスは正常に作成されます。

  • 実表の定義直後

  • TRUNCATE TABLE文の実行直後

B-treeインデクスが未完状態の場合は，未完状態のB-treeインデクスを使用した検索や，表に対するINSERT，UPDATE，およびDELETEが実行できません。

テキストインデクスが未完状態の場合は，未完状態のテキストインデクスを使用した検索や，表に対するINSERT，UPDATE，およびDELETEが実行できません。

レンジインデクスが未完状態の場合は，未完状態のレンジインデクスを使用した検索や，表に対するINSERTおよびUPDATEが実行できません。

インデクスの未完状態の解除方法については，マニュアルHADB　システム構築・運用ガイドのB-treeインデクスが未完状態になったときの対処方法，テキストインデクスが未完状態になったときの対処方法，またはレンジインデクスが未完状態になったときの対処方法を参照してください。

行を格納するセグメントが割り当てられている状態については，マニュアルHADB　システム構築・運用ガイドのB-treeインデクスを定義する場合の注意点（B-treeインデクスの未完状態）を参照してください。

〈この項の構成〉

(1)　指定形式

CREATE INDEX文：：＝
     CREATE 〔UNIQUE〕 INDEX インデクス名
          ON 表名 (列名 〔｛ASC｜DESC｝〕〔,列名 〔｛ASC｜DESC｝〕〕…)
        〔IN DBエリア名〕
        〔PCTFREE=未使用領域の比率〕
          EMPTY
        〔INDEXTYPE ｛BTREE｜TEXT 〔WORDCONTEXT〕｜RANGE｝〕
        〔CORRECTIONRULE〕
        〔DELIMITER ｛DEFAULT｜ALL｝〕
        〔EXCLUDE NULL VALUES〕
メモ

  • PCTFREE，EMPTY，INDEXTYPE，CORRECTIONRULE，DELIMITER，およびEXCLUDE NULL VALUESをまとめてインデクスオプションといいます。

  • インデクスオプションは，どの順序で指定してもかまいません。

定義するインデクスの種類によって指定できるオプションが次の表に示すように異なります。

表3‒4　インデクス定義時に指定できるオプション

項番

CREATE INDEXの各オプション

B-treeインデクスを定義する場合

テキストインデクスを定義する場合

レンジインデクスを定義する場合

1

UNIQUE

○

×

×

2

インデクス名

○

○

○

3

ON 表名

○

○

○

4

列名

○

○

○

5

｛ASC｜DESC｝

○

×

×

6

IN DBエリア名

○

○

○

7

PCTFREE

○

○

×

8

EMPTY

○

○

○

9

INDEXTYPE

○

○

○

10

CORRECTIONRULE

×

○

×

11

DELIMITER

×

○

×

12

EXCLUDE NULL VALUES

○

×

×
（凡例）

○：指定が必要なオプション，または指定を検討するオプションです。

×：指定不要なオプションです。

メモ

CREATE INDEX文で主キーを定義することはできません。主キーを定義するには，CREATE TABLE文で一意性制約定義を指定してください。

ページの先頭へ

(2)　指定形式の説明

各オプションの説明で，【B-treeインデクス】と表記されているオプションは，B-treeインデクスを定義するときに指定できるオプションです。【テキストインデクス】と表記されているオプションは，テキストインデクスを定義するときに指定できるオプションです。【レンジインデクス】と表記されているオプションは，レンジインデクスを定義するときに指定できるオプションです。【共通】と表記されているオプションは，B-treeインデクス，テキストインデクス，およびレンジインデクス共通のオプションです。

●UNIQUE　　【B-treeインデクス】

B-treeインデクスをユニークインデクスとして定義する場合に指定します。ユニークインデクスとは，キー値（B-treeインデクスを定義した列の値）の重複を許さないB-treeインデクスのことです。ただし，ナル値を含むキー値の場合，重複した値があっても重複キーにはなりません。

複数列インデクスの場合は，どれか1つの列の値が異なれば，異なるキー値となります。

UNIQUEを指定した場合，キー値が重複するようなデータの更新または追加ができません。

なお，CREATE TABLE文でチャンク指定を指定した実表に，ユニークインデクスは定義できません。

●インデクス名　　【共通】

定義するインデクスのインデクス名を指定します。インデクス名の指定規則については，「6.1.5　名前の修飾」の「(3)　インデクス名の指定形式」を参照してください。

なお，すでに定義されているインデクスのインデクス名は指定できません。

●ON 表名　　【共通】

インデクスを定義する実表の表名を指定します。表名の指定規則については，「6.1.5　名前の修飾」の「(2)　表名の指定形式」を参照してください。

なお，表名にビュー表は指定できません。

●(列名 〔｛ASC｜DESC｝〕 〔,列名 〔｛ASC｜DESC｝〕〕…)　　【共通】
・B-treeインデクスの場合

B-treeインデクスを定義する列の列名，およびB-treeインデクスのキー値の並び順を指定します。

列名：

B-treeインデクスを定義する列の列名を指定します。列名は，16個まで指定できます。列名を複数指定する場合，同じ列名を指定できません。

列名を複数指定した場合，そのB-treeインデクスは複数列インデクスになります。

ASC：

B-treeインデクスのキー値を昇順に並べる場合に指定します。

DESC：

B-treeインデクスのキー値を降順に並べる場合に指定します。

単一列インデクスに対してDESCを指定しても無視されます。インデクスのキー値は，常に昇順に並べられます（ASCが指定されたと仮定されます）。

ASCおよびDESCの指定を省略した場合，ASCが仮定されます。

・テキストインデクスまたはレンジインデクスの場合

テキストインデクスまたはレンジインデクスを定義する列の列名を指定します。

テキストインデクスおよびレンジインデクスの場合，列名を2つ以上指定することはできません。また，ASCおよびDESCを指定することはできません。

そのため，テキストインデクスおよびレンジインデクスの場合，指定形式は次のようになります。

(列名)

●IN DBエリア名　　【共通】

インデクスを格納するDBエリアのDBエリア名を指定します。

「IN DBエリア名」の指定を省略した場合，サーバ定義のadb_sql_default_dbarea_sharedオペランドに指定したDBエリアに，インデクスが格納されます。

なお，次のどちらかの条件を満たす場合に，「IN DBエリア名」の指定を省略すると，CREATE INDEX文がエラーになります。

  • サーバ定義のadb_sql_default_dbarea_sharedオペランドの指定を省略している場合

  • サーバ定義のadb_sql_default_dbarea_sharedオペランドに，存在しないDBエリアを指定している場合，またはデータ用DBエリア以外のDBエリアを指定している場合

●PCTFREE=未使用領域の比率　　【B-treeインデクス，テキストインデクス】

〜〈符号なし整数〉((0〜99))《30》（単位：％）

B-treeインデクスまたはテキストインデクスのインデクスページ内の未使用領域の比率を指定します。0〜99（単位：％）を指定します。省略すると，30％が仮定されます。

データがインポートされてインデクスが作成される際，またはインデクスが再作成される際，ここで指定した未使用領域の比率に従ってB-treeインデクスまたはテキストインデクスのデータが格納されます。

インデクスページ内の未使用領域の比率の目安については，マニュアルHADB　システム構築・運用ガイドのB-treeインデクスのインデクスページ内の未使用領域の確保（PCTFREE），またはテキストインデクスのインデクスページ内の未使用領域の確保（PCTFREE）を参照してください。

なお，PCTFREEは複数回指定できません。

●EMPTY　　【共通】

EMPTYは必ず指定してください。EMPTYを省略すると，CREATE INDEX文が実行できません。

EMPTYは複数回指定できません。

●INDEXTYPE ｛BTREE｜TEXT 〔WORDCONTEXT〕｜RANGE｝　　【共通】

定義するインデクスの種類を指定します。

BTREE：

インデクスをB-treeインデクスとして定義する場合に指定します。

TEXT 〔WORDCONTEXT〕：

インデクスをテキストインデクスとして定義する場合に指定します。ワード検索用のテキストインデクスを定義する場合は，TEXT WORDCONTEXTと指定します。

RANGE：

インデクスをレンジインデクスとして定義する場合に指定します。

INDEXTYPEの指定を省略した場合，BTREE（B-treeインデクス）が仮定されます。

なお，INDEXTYPEは複数回指定できません。

●CORRECTIONRULE　　【テキストインデクス】

表記ゆれ補正検索に対応しているテキストインデクスを定義する場合に指定します。テキストインデクスの表記ゆれ補正検索については，マニュアルHADB　システム構築・運用ガイドの表記ゆれ補正検索を参照してください。

なお，HADBサーバで使用する文字コードがShift-JISの場合（環境変数ADBLANGの指定値がSJISの場合），表記ゆれ補正検索ができません。そのため，CORRECTIONRULEは指定できません。

また，CORRECTIONRULEは複数回指定できません。

メモ

このオプションの指定を，テキストインデクス表記ゆれ補正指定といいます。

●DELIMITER ｛DEFAULT｜ALL｝　　【テキストインデクス】

ワード検索をする際の単語の区切り文字の種類を指定します。

DEFAULT：

ワード検索をする際，次の文字を区切り文字として扱います。

  • 半角空白（0x20）

  • タブ（0x09）

  • 改行（0x0A）

  • 復帰（0x0D）

  • ピリオド（0x2E）

  • 疑問符（0x3F）

  • 感嘆符（0x21）

ALL：

ワード検索をする際，次の文字を区切り文字として扱います。

  • 半角空白（0x20）

  • タブ（0x09）

  • 改行（0x0A）

  • 復帰（0x0D）

  • ピリオド，疑問符，および感嘆符を含む1バイトの記号（0x21～0x2F，0x3A～0x40，0x5B～0x60，0x7B～0x7E）

このオプションを指定する場合は，INDEXTYPEにTEXT WORDCONTEXTを指定している必要があります。

INDEXTYPEにTEXT WORDCONTEXTを指定している場合に，DELIMITERの指定を省略したときは，DEFAULTが仮定されます。

メモ

このオプションの指定をテキストインデクス区切り文字指定といいます。

●EXCLUDE NULL VALUES　　【B-treeインデクス】

このオプションを指定すると，B-treeインデクスを作成する際，ナル値だけで構成されるB-treeインデクスのキー値を作成しません。列の大半の値がナル値の列をインデクス構成列にする場合は，このオプションの指定を検討してください。

このオプションを指定すると，ナル値だけで構成されるB-treeインデクスのキー値を作成しない分，B-treeインデクスの作成時間を短縮できます。そのため，データインポートに掛かる時間を短縮できたり，B-treeインデクスのデータ容量を削減できたりなどのメリットがあります。

なお，B-treeインデクスのインデクス構成列に，非ナル値制約が定義されている場合，そのB-treeインデクスに対してはこのオプションを指定できません。

また，EXCLUDE NULL VALUESは複数回指定できません。

メモ

このオプションの指定をナル値除外指定といいます。

ページの先頭へ

(3)　実行時に必要な権限

CREATE INDEX文を実行する場合，CONNECT権限およびスキーマ定義権限が必要になります。

ページの先頭へ

(4)　規則

(a)　インデクス共通の規則

  1. 自分（HADBサーバに接続中の認可識別子のHADBユーザ）が所有している実表に対してだけインデクスを定義できます。ほかのHADBユーザが所有している実表に対してはインデクスを定義できません。

  2. ビュー表にインデクスは定義できません。

  3. インデクスは，B-treeインデクス，テキストインデクス，およびレンジインデクスを合わせて，1つの表に32個まで定義できます。

  4. インデクスは，B-treeインデクス，テキストインデクス，およびレンジインデクスを合わせて，システム内で合計8,192個（ディクショナリ表（実表）およびシステム表（実表）に定義されたインデクスは除く）まで定義できます。

  5. 1つのDBエリアに格納できるインデクスは400個までです。

  6. 同一列に対して，B-treeインデクス（単一列インデクス），テキストインデクス，およびレンジインデクスを定義できます。

  7. マルチチャンク表にインデクスを定義する場合は，マニュアルHADB　システム構築・運用ガイドのデータ用DBエリアにマルチチャンク表を格納する場合の考慮点を参照してください。

  8. コマンドの中断によって更新不可状態となった表にインデクスを定義することはできません。

(b)　B-treeインデクスに関する規則

  1. 単一列インデクスを定義する場合は，次に示す条件式を満たす必要があります。条件式を満たさない場合は単一列インデクスを定義できません。

    単一列インデクスを定義する列の長さ≦MIN｛（a÷3）−128，4036 ｝（単位：バイト）

    a：B-treeインデクスを格納するDBエリアのページサイズ

    単一列インデクスを定義する列の長さは，次の表から求めてください。

    表3‒5　単一列インデクスを構成する列の長さ

    項番

    列のデータ型

    列の長さ（単位：バイト）

    1

    INTEGER

    8

    2

    SMALLINT

    4

    3

    DECIMAL(m,n)

    または

    NUMERIC(m,n)

    1≦m≦4の場合

    2

    5≦m≦8の場合

    4

    9≦m≦16の場合

    8

    17≦m≦38の場合

    16

    4

    DOUBLE PRECISIONまたはFLOAT

    8

    5

    CHAR(n)

    n

    6

    VARCHAR(n)

    n

    7

    DATE

    4

    8

    TIME(p)

    3＋↑p÷2↑

    9

    TIMESTAMP(p)

    7＋↑p÷2↑

    10

    BINARY(n)

    n

    11

    VARBINARY(n)

    n
    （凡例）

    m，n：正の整数

    p：0，3，6，9，または12

  2. 複数列インデクスを定義する場合は，次に示す条件式を満たす必要があります。条件式を満たさない場合は複数列インデクスを定義できません。

    複数列インデクスを構成する列の定義長の合計≦MIN｛（a÷3）−128，4036 ｝（単位：バイト）

    a：B-treeインデクスを格納するDBエリアのページサイズ

    複数列インデクスを構成する列の定義長の合計は，次の表から求めてください。

    表3‒6　複数列インデクスを構成する列の長さ

    項番

    列のデータ型

    複数列インデクスを構成する列の長さ（単位：バイト）※

    各列の定義長の合計が255バイト以下の場合

    各列の定義長の合計が256バイト以上の場合

    構成列が固定長だけの場合

    構成列に可変長を含む場合

    1

    INTEGER

    9

    9

    10

    2

    SMALLINT

    5

    5

    6

    3

    DECIMAL(m,n)

    または

    NUMERIC(m,n)

    1≦m≦4の場合

    3

    3

    4

    5≦m≦8の場合

    5

    5

    6

    9≦m≦16の場合

    9

    9

    10

    17≦m≦38の場合

    17

    17

    18

    4

    DOUBLE PRECISIONまたはFLOAT

    9

    9

    10

    5

    CHARACTER(n)

    n＋1

    n＋1

    n＋2

    6

    VARCHAR(n)

    n＋1

    −

    n＋2

    7

    DATE

    5

    5

    6

    8

    TIME(p)

    4＋↑p÷2↑

    4＋↑p÷2↑

    5＋↑p÷2↑

    9

    TIMESTAMP(p)

    8＋↑p÷2↑

    8＋↑p÷2↑

    9＋↑p÷2↑

    10

    BINARY(n)

    n＋1

    n＋1

    n＋2

    11

    VARBINARY(n)

    n＋1

    −

    n＋2
    （凡例）

    m，n：正の整数

    p：0，3，6，9，または12

    −：該当しません。

    注※

    各列の定義長の合計が255バイト以下の場合の列長を基に計算した結果，合計が256バイト以上になったときには，各列の定義長の合計が256バイト以上の場合を基に列の長さを計算し直してください。

  3. 次に示すB-treeインデクスは複数個定義できません。

    • 列構成が同じであり，かつすべての列の昇順，降順の指定が同じであるB-treeインデクス

    • 列構成が同じであり，かつすべての列の昇順，降順の指定が逆であるB-treeインデクス

  4. 単一列インデクスが定義されている列にも，複数列インデクスを定義できます。

  5. 複数列インデクスを定義する場合，各列の指定順序が，キー値作成の優先順位になります。

  6. 配列型の列を定義している表には，B-treeインデクスを定義できません。

(c)　テキストインデクスに関する規則

  1. 次に示すデータ型の列に対してテキストインデクスを定義できます。

    • CHARACTER型

    • VARCHAR型

  2. インデクス構成列が同じテキストインデクスを複数定義できません。

  3. カラムストア表にテキストインデクスは定義できません。

(d)　レンジインデクスに関する規則

  1. レンジインデクスは，次に示すデータ型の列に対しては定義できません。

    • 定義長が33バイト以上のCHARACTER型

    • VARCHAR型

    • BINARY型

    • VARBINARY型

  2. インデクス構成列が同じレンジインデクスを複数定義できません。

ページの先頭へ

(5)　例題

例題1（B-treeインデクスを定義する場合）

店舗表（SHOPSLIST）に対して，次に示すB-treeインデクスを定義します。

  • 店舗コード列（SHOP_CODE）に単一列インデクス（SHOP_CODE_IDX）を定義する

  • B-treeインデクスはユニークインデクスとする

  • B-treeインデクスをDBエリア（DBAREA01）に格納する

  • 店舗表（SHOPSLIST）には行の追加が頻繁に発生するため，インデクスページ内の未使用領域の比率を50パーセントとする

CREATE UNIQUE INDEX "SHOP_CODE_IDX"
      ON "SHOPSLIST" ("SHOP_CODE")
    IN "DBAREA01"
    PCTFREE = 50
    EMPTY
例題2（B-treeインデクスを定義する場合）

店舗表（SHOPSLIST）に対して，次に示すB-treeインデクスを定義します。

  • 店舗コード列（SHOP_CODE）と地域コード列（RGN_CODE）をインデクス構成列とした複数列インデクス（SHOP_RGN_IDX）を定義する

  • 店舗コードは昇順（ASC）に，地域コードは降順（DESC）にインデクスのキー値を並べる

  • B-treeインデクスをDBエリア（DBAREA01）に格納する

CREATE INDEX "SHOP_RGN_IDX"
      ON "SHOPSLIST" ("SHOP_CODE" ASC,"RGN_CODE" DESC)
    IN "DBAREA01"
    EMPTY
例題3（テキストインデクスを定義する場合）

従業員表（EMPLOYEE）に対して，次に示すテキストインデクスを定義します。

  • 住所列（ADDRESS）にテキストインデクス（ADDRESS_IDX）を定義する

CREATE INDEX "ADDRESS_IDX"
      ON "EMPLOYEE" ("ADDRESS")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE TEXT

表記ゆれ補正検索ができるテキストインデクスを定義する場合は，次のように下線部分の指定が必要になります。

CREATE INDEX "ADDRESS_IDX"
      ON "EMPLOYEE" ("ADDRESS")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE TEXT
    CORRECTIONRULE

ワード検索用のテキストインデクスを定義する場合は，次のように下線部分の指定が必要になります。

CREATE INDEX "ADDRESS_IDX"
      ON "EMPLOYEE"("ADDRESS")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE TEXT WORDCONTEXT
    DELIMITER DEFAULT
例題4（レンジインデクスを定義する場合）

店舗表（SHOPSLIST）に対して，次に示すレンジインデクスを定義します。

  • 店舗コード列（SHOP_CODE）にレンジインデクス（SHOP_CODE_RIDX）を定義する

  • レンジインデクスをDBエリア（DBAREA01）に格納する

CREATE INDEX "SHOP_CODE_RIDX"
      ON "SHOPSLIST" ("SHOP_CODE")
    IN "DBAREA01"
    EMPTY
    INDEXTYPE RANGE

ページの先頭へ

All Rights Reserved. Copyright (C) 2012, 2021, Hitachi, Ltd.