TOPスキーマ・オブジェクト >索引(INDEX)


索引(INDEX)


索引とは

索引(INDEX)とは、データベースから高速にデータを検索するためのオブジェクトです
ほとんどの場合、索引を使用した検索は高速に動作します。
しかし、次の場合は索引検索よりも全表走査のほうが高速に動作する場合があります。

索引の特徴として検索は高速になりますが、逆に更新系の処理(INSERT,UPDATE、DELETE)は索引のメンテナンスによる オーバーヘッドが発生しますので遅くなります。

索引を作成するには、以下のようなガイドラインがあります

・索引を作成した方がよい場合
  
  大規模な表の、1%から15%程度の行を頻繁に検索する。
  列の値が、比較的一意である
  WHERE句の条件として、頻繁に使用される列である。
  
・索引を作成しない方がよい場合
  
  列内に、異なる値がほとんどない(例えば、性別など)。
  NULL値が多く、NULLでない値を検索しない。
  表が小規模である。
  
・一般に、参照整合性制約で使用される外部キー列には、索引の作成を考えるべきです
	

索引は、表の列の値を抜き出して並べ替えて、ある特定の値をみつけやすくしたものです。
索引には表とは別にディスクの領域が必要です。

検索を実施するときには、必要と判断すればOracleが自動的に索引を参照します。
索引が作成されている表のデータに変更が加えられた場合は、自動的に索引にも変更が加えられます。
たとえば、従業員の情報を格納する表があり、名前の列に対して索引が作成されているとします。
表に新しい従業員の情報を加えた場合は、自動的に索引にも情報が加えられます。

Oracleでは、表を作成する時にPRIMARY KEY制約やUNIQUE制約を指定すると、 一意性の保証などの目的で、その列(または列の組み合わせ)に対して、 自動的に索引が作成されます。

索引の中では値がソートされているので、ORDERBYなどのソートを必要とする処理で索引が使用されることがあります。
ORDERBYを処理する場合、索引の順で表を読み込めば読み込んだ時点でソートされています。
したがって、ソートの処理を省略できます。

索引があることで検索のパフォーマンスはよくなりますが、索引が作成されている表に データを入力したり変更したりする場合は、
表だけでなく索引にも変更が必要になり、それだけパフォーマンスが悪くなります。

索引のメリットとデメリット

メリット
表のなかの特定の行を早く検索することができる。
ソートの作業を省略できる。
デメリット
索引のための領域が必要である。
データの入力や変更の作業に、索引変更のためのオーバーヘッドが加わる。

制約と索引

索引には一意索引と一意でない索引の2種類あります。
一意索引では、索引の中に同じ値を含むことができません。
一意でない索引では、索引の中に同じ値が含まれていても構いません。

一意索引

番号 番地 説明
1111111-111 ←番号:1111は入力不可。同じ値は入ってはいけない
2222222-222
--

一意でない索引

番号 番地 説明
1111111-111 ←番号:1111。同じ値が入ってもよい
2222222-222
1111111-222

一意制約は一意索引を利用して実現されています。
主キー制約も同じように一意索引を利用します。
つまり、一意キー制約あるいは主キー制約を定義すると、暗黙のうちに対応する列に一意索引が作成されます。

暗黙のうちに作成される一意索引の表領域の指定は、表を作成するときに明示的に指定できます。

CREATE TABLE department(
 deptno NUMBER(2)    CONSTRAINT pk_department PRIMARY KEY
  USING INDEX TABLESPASE <索引用の表領域>
 dname VARCHAR(14)
)
TABLESPACE user_data

このSQLのようにUSING INDEXのキーワードで、暗黙に作成される索引の表領域を明示的にしてできます。
ALTER TABLE文で一意キー制約を追加したり、無効であった一意キー制約を有効にする場合も、暗黙に索引が作成されます。
このような場合も、USING INDEX句で表領域を指定できます。

例:
  ALTER TABLE department ADD CONSTRAINT unique_dname UNIQUE(dname)
   USING INDEX TABLESPACE <索引用の表領域>

  ALTER TABLE department ENABLE CONSTRAINT unique_dname
   USING INDEX TABLESPACE <索引用の表領域>

(1)索引の作成

索引の作成は、CREATE INDEX文、 またはCREATE UNIQUE INDEX文で作成します。
CREATE INDEX文では、索引の名前、対象となる表と列などを指定します。

以下は、CREATE INDEX文の構文です。

  CREATE INDEX <作成したい表の索引名> 
   ON 索引する表名 (列名 [, 列名]...)
   [TABLESPACE 表領域名] 	
   [記憶領域管理パラメータ]	

例:	
  CREATE INDEX employee_index1	
   ON employee (ename);	

この例では、employee表のename列に、employee_indexという索引を作成しています。

以下は、CREATE UNIQUE INDEX文の構文です。

  CREATE UNIQUE INDEX 索引名
   ON 表名 (列名 [, 列名]...)
   [TABLESPACE 表領域名]
   [記憶領域管理パラメータ]

例:
  CREATE UNIQUE INDEX employee_index2
   ON employee (ename);

この例では、employee表のename列に、employee_indexという索引を作成しています。

CREATE INDEX文を使用した場合は一意ではない索引が作成され、CREATE UNIQUE INDEX文を使用した場合は一意索引が作成されます。
一意索引を作成する場合には通常はCREATE UNIQUE INDEX文は使わずに、表の制約とともに作成します。

※Oracleでは、列(または列の組み合わせ)に対してPRIMARY KEY制約やUNIQUE制約を付けると、自動的に索引が作成されます。
このような自動的に作成される索引に対して、表領域や記憶領域管理パラメータを指定したい場合は、 制約の指定のあとに「USING INDEX句」を記述してください。

[CONSTRAINT 制約名] PRIMARY KEY/UNIQUE
 [USING INDEX
 [TABLESPACE 表領域名]
 [記憶領域管理パラメータ]]

(2)索引の確認

データベース内に作成された索引を確認するには、USER_INDEXESUSER_IND_COLUMNSというデータ・ディクショナリを使用します。
USER_INDEXESは、全ユーザーが使用できます。

現在使用しているユーザーの索引の名前や、対象となる表の名前、一意な列についている索引かどうか、などが確認できます。

USER_INDEXESは、以下の列で構成されています。

USER_INDEXES

COLUMN 説明
INDEX_TYPE索引のタイプ
TABLE_OWNER索引を設定したオブジェクトの所有者
TABLE_NAME索引を設定したオブジェクトの名前
TABLE_TYPE索引を設定したオブジェクトの型
UNIQUENESS索引が一意かどうか:
TABLESPACE_NAME索引が格納されている表領域の名前
INI_TRANSトランザクション・エントリの初期数
MAX_TRANSトランザクション・エントリの最大数
INITIAL_EXTENT初期エクステントのサイズ(バイト単位)
NEXT_EXTENT第2エクステントのサイズ(バイト単位)
MIN_EXTENTSエクステントの最小数
MAX_EXTENTSエクステントの最大数
PCT_INCREASEエクステント・サイズの増加の割合(パーセント単位)
PCT_THRESHOLD ブロックあたりの1索引エントリーの割合。
この割合を超えると、キーでない列(デフォルト値)がオーバーフロー領域に格納される。
索引構成表で使用する
INCLUDE_COLUMN 索引構成表に含まれる最後の列番号。
INCLUDING句を指定して、オーバーフロー時にキーでない列を索引構成表に残すことができる
FREELISTSプロセス空きリストの数
FREELIST_GROUPS空きリスト・グループの数
PCT_FREE データ・ブロックに設定された空き領域の最小の割合(パーセント単位)
LOGGINGロギングが使用可能かどうか
BLEVEL Bツリー・レベル: ルート・ブロックからリーフ・ブロックまでの索引の深さ。
深さ0は、ルート・ブロックとリーフ・ブロックが同じであることを示す
LEAF_BLOCKS索引内のリーフ・ブロックの数
DISTINCT_KEYS 別個に索引付けされた値の数。
UNIQUE制約およびPRIMARY KEY制約を施行する索引の場合、この値は表USER_TABLES.NUM_ROWSの行数と同じ
AVG_LEAF_BLOCKS_PER_KEY索引内の各固有値が設定されているリーフ・ブロックの平均数。
AVG_DATA_BLOCKS_PER_KEY 索引内の固有値が設定されている表内のデータ・ブロックの平均数。
この統計値は、索引付きの列(1つまたは複数)の特定の値が設定されている行があるデータ・ブロックの平均数。
この統計値は、近似の整数に丸められる
CLUSTERING_FACTOR この統計は、索引の値に基づく表内の行の秩序度。
この統計値がブロック数に近いと、表は高い秩序度を持っている。
この場合、1つのリーフ・ブロック内の索引エントリは、 同じデータ・ブロック内の行を指している。
この統計値が行数に近いと、表はランダム。
この場合、同じリーフ・ブロック内の索引エントリが同じデータ・ブロック内の行を指す可能性はほとんどない。
STATUS索引の状態:DIRECT LOAD、VALID
NUM_ROWS索引内のデータの行数
SAMPLE_SIZEこの索引の分析で使用されるサンプル・サイズ
LAST_ANALYZEDこの索引が最後に分析された日付
DEGREE索引の走査に使用される問合せサーバー数
INSTANCES索引がいくつかのインスタンスにまたがって走査される場合の、インスタンスの数
PARTITIONEDこの索引がパーティション化されているかどうか
TEMPORARY一時的な表かどうか
GENERATED名前がOracleによって命名されたかどうか
BUFFER_POOLオブジェクトに指定したデフォルトのバッファ・プールの種類

USER_IND_COLUMNSは、全ユーザーが使用できます。
現在使用しているユーザーの索引の名前や、索引の設定されている列の名前などが確認できます。

USER_IND_COLUMNSは、以下の列で構成されています。

USER_INDEXES

COLUMN 説明
INDEX_NAME索引名
TABLE_NAME表またはクラスタの名前
COLUMN_POSITION索引内の列または属性の位置
COLUMN_LENGTH索引が設定された列または属性の長さ

employee_index1の索引を確認したい時は…

SELECT * FROM USER_INDEXES
WHERE INDEX_NAME = 'EMPLOYEE_INDEX1';

このSQL文を実行する。

(3)索引の削除

索引を削除するには、DROP INDEX文を使用します。
DROP INDEX文では削除する索引を指定します。

  DROP INDEX <削除する索引名>

例:
  DROP INDEX employee_index1;

この例では、employee_index1という索引を削除しています。



TOPスキーマ・オブジェクト >索引(INDEX)

2001/03/02 担当:K.F