CREATE INDEX 文

CREATE INDEX文により表に索引を作成できます。索引は表のひとつ以上の列に作成できます。

構文

CREATE [UNIQUE] INDEX 索引名
ON 表名 ( 単純列名 [ ASC | DESC ]
    [ , 単純列名 [ ASC | DESC ]] * )

The Derbyにて索引に可能な列の最大数は16です。

索引名は128文字を超えてはなりません。

同じ列が単一のCREATE INDEX文に2つ以上あってはなりません。なお異なる索引であれば、同じ列をもってかまいません。

Derbyは索引を使って、情報の操作を行う文の効率を向上します。(Derbyのチューニングを参照してください。)。さらにUNIQUEの索引により、情報が整合性を保っているか検証する事ができます。

索引名はスキーマにて一意です。(データベースシステムによっては、同じスキーマの異なるテーブルに同じ名前の索引を持つことができますが、Derbyは違います。) 索引と表の片方にスキーマ名が明示されて、もう片方に明示されなかった場合、索引と表は同じスキーマにあるものとされます。索引と表の両方にスキーマ名が明示されて、それぞれのスキーマ名が異なっていた場合、例外が発生します。表にも索引にもスキーマ名が明示されなかった場合、現在のスキーマが使われます。

既定でDerbyは索引をそれぞれの列に対して昇順で作成します。列名の後にASCと書いても既定の振る舞いと同じように動作します。列名の後にDESCと書くと、索引を作るときにDerbyはその列を降順で扱います。降順を列に指定することで、昇順と降順が混ざった並び順あるいは降順の問い合わせや、索引のある列の最大値や最小値を選択する問い合わせが、効率よく実行されることを期待できます。

限定された索引名を指定する場合、スキーマ名はSYSで始まってはなりません。

索引と制約

一意性・主キー・外部キー制約を作成すると、制約を適用または支援する索引が作成されます。(このような索引のことを支援索引と呼びます。)もし列や列の集合に、UNIQUEないしPRIMARY KEYの制約が存在している場合、それらの列に索引を作成することはできません。Derbyは既にその索引をシステムが決めた名前で作成しています。制約を支援する索引にシステムが与えた名前は、索引の名前でシステム表に問い合わせれば、簡単に見つけることができるでしょう。既にUNIQUEの索引が存在している列の集合に、主キーまたは一意性制約を追加すると、同じ列の集合に複数の索引が造られることとなります。一つがもともとあったUNIQUEの制約でもう一つが、制約に併せて作成された支援索引です。

FLIGHTS_PKという名前の制約を支援する索引を見つけるには次のようにします。

SELECT CONGLOMERATENAME FROM SYS.SYSCONGLOMERATES,
SYS.SYSCONSTRAINTS WHERE
SYS.SYSCONGLOMERATES.TABLEID = SYSCONSTRAINTS.TABLEID 
AND CONSTRAINTNAME = 'FLIGHTS_PK'

CREATE INDEX OrigIndex ON Flights(orig_airport);
-- 金額は通常大きな値から小さな値の順で並べられるので、
-- 索引を降順で作成します。
CREATE INDEX PAY_DESC ON SAMP.EMPLOYEE (SALARY);
-- 索引をより大きいページサイズで作成します。
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize','8192');
CREATE INDEX IXSALE ON SAMP.SALES (SALES);
call SYSCS_UTIL.SYSCS_SET_DATABASE_PROPERTY('derby.storage.pageSize',NULL);

ページサイズとキーサイズ

注: 索引にあるキーの列サイズの合計は、ページサイズの半分以下で無ければなりません。もし既存行に作成しようとしている索引のキー列の合計が索引のページサイズの半分より大きな行があるならば、索引の作成は失敗します。このエラーは索引を作成したとき、表の既存行が基準に合わないときのみ発生します。索引が作成された後は、基準のキーサイズを超えた行の挿入が失敗します。

文の依存

SELECT、INSERT、UPDATE、UPDATE WHERE CURRENT、DELETEやDELETEの準備済み文は、その表がCREATE INDEX文の対象となった場合、索引の作成とともに無効になります。一方表について開いているカーソルは影響を受けません。