Oracle SQL：索引

表からデータ行を取出す場合には順次読出しと言われる方法があります。

これは表を先頭から表の最後に向かって順次読み出してゆき、クエリーの条件に合致した行を見つけるまで行われます。
表の行数が少ない場合はこの方法でも問題はありませんが、行数が数千から数万を越える表を順次読出しではクエリーの応答が非常に遅くなってしまいます。

このためにクエリー条件の列にインデックスを持てば、行の読出しを行う時にインデックスを検索しその後実際のデータ行の読出しを行います。 インデックスはＢツリー形式で作成されており検索をすばやく行えます。


おすすめ書籍



CREATE INDEX [ UNIQUE , BITMAP ] <インデックス名> ON <表名> ( <列名> )

<UNIQUE>属性


　この属性は索引を作成する列に同じ値が2個以上存在することができません。つまりユニークな値（一意）で無ければなりません。
　この指定が無い場合は重複キーを許すことになります。

尚、<列名>は列名リストを示していますが、2個以上の列を指定することで複数の列を元にキーを作成できます。

索引は、クエリーでの検索で処理速度を上げるために利用するものですが、Oracleでは一般的にクエリーが返す値が、表の5%未満である場合に索引を利用する様です。また、キーのとりうる値がばらばらの場合はいいのですが、同じ値が多数ある列にキーを作成しても検索のパフォーマンスは上がらないようです。

以下に索引を作成したほうが良い場合を示します。

（１）列に、他の表との結合に利用するものがある場合
　　　売上伝票の伝票番号と売上明細の伝票番号のようなもの

（２）クエリーのWHERE句で多く使用される列で、そのときのクエリーの結果が5%未満の列を返すとき

（３）表全体の行があまりにも少ない場合は索引は作成しないほうが良いようです。


※主キー及び索引の作成例について

CREATE TABLE TT_売上(
         売上番号       NUMBER  ( 8)
        ,売上日         DATE
        ,得意先コード   NUMBER  ( 6)
        ,担当者コード   NUMBER  ( 3)
);
ALTER TABLE TT_売上明細 ADD PRIMARY KEY (売上番号);

CREATE TABLE TT_売上明細(
         売上番号       NUMBER  ( 8)
        ,明細番号       NUMBER  ( 3)
        ,商品コード     NUMBER  ( 6)
        ,売上数量       NUMBER  ( 6)
);
ALTER TABLE TT_売上明細 ADD PRIMARY KEY (売上番号,明細番号);

表の作成のところでも示してありますが、表に対して主キーを作成するときにはCREATE INDEX文ではなくALTER TABLE文を利用します。
作成される索引は、索引としてはどちらで作成したものもほぼ同じものであり主キーの他に索引を作成したい場合にCREATE INDEX文を利用します。
以下に、TT_売上の売上日に索引を作成する場合を示します。

CREATE INDEX 売上KEY ON TT_売上 (売上日);

この場合、売上日は重複する可能性はありますので、UNIQUE属性は付加していません。
一般的に売上データは売上の統計データを作成する場合売上日の範囲でデータの抽出を行いますので、索引を作成すると処理速度をあげることができると思います。

　処理速度が向上するのであればと、他の項目や、組み合わせでの索引を付ける場合がありますが、検索処理は早くなっても売上データを追加、更新、削除の処理で索引に対する処理が発生し時間が掛かってしまいます。

その結果、システム全体の処理速度のパフォーマンスが低下することがあります。どの項目に索引を付けるかは、どの検索処理が早くなければならないかを考えるべきだと思います。このあたりのことは、それぞれのシステムで個々にケースバイケースで試行錯誤しながらになるのではないでしょうか。


＜主キーについての注意＞
　以前私がかかわったシステムで、以下の様な表（TT_工程処理）がありました。（私自身がシステム設計を行ったわけではありませんでした）　この表は工場での製品毎の指図番号に対応した工程プロセスの時間的な履歴を集めて処理をするものでした。

　主キーとして日付から工程番号までの5項目が設定されています。実際に工程処理を画面からデータを処理するプログラムを担当したのですが、入力されたデータを変更する場合主キーの部分のデータを変更が発生し、プログラム的に少し厄介な感じがありました。

こういった主キーとなるデータ項目が増えた場合には、代替となる1個のユニークなキーを導入したほうがすっきりすると思います。

[TT_工程処理２]としてCREATE TABLE文の工程処理番号がそれに当たります。
また、[TT_工程処理]の5項目はCREATE INDEX文で別の索引とすれば、処理向上は図れると思います。

CREATE TABLE TT_工程処理(
         日付           DATE            /*時刻までのデータを設定する*/
        ,担当者コード   NUMBER(3)
        ,製品コード     NUMBER(8)
        ,指図番号       NUMBER(8)
        ,工程番号       NUMBER(3)
        ,処理区分       NUMBER(1)
        ...                             /*その他の項目*/
        ...
)
ALTER TABLE TT_工程処理 ADD CONSTRAINT 工程処理KEY
 PRIMARY KEY (日付,担当者コード,製品コード,指図番号,工程番号);


CREATE TABLE TT_工程処理２(
         工程処理番号   NUMBER(10)      
        ,日付           DATE            /*時刻までのデータを設定する*/
        ,担当者コード   NUMBER(3)
        ,製品コード     NUMBER(8)
        ,指図番号       NUMBER(8)
        ,工程番号       NUMBER(3)
        ,処理区分       NUMBER(1)
        ...                             /*その他の項目*/
        ...
)
ALTER TABLE TT_工程処理 ADD CONSTRAINT 工程処理KEY
 PRIMARY KEY (工程処理番号);

CREATE INDEX 工程処理IDX ON TT_工程処理 (日付,担当者コード,製品コード,指図番号,工程番号);




ページのトップへ戻る