Oracle SQL インデックス作成/使用のサンプル

SQLのインデックスを作成/使用するサンプルです。

確認環境
・Oracle Express Edition 11g Release 2

インデックス(index)

インデックスは、レコードの検索結果の表示を早くするために使用します。
件数が多いデータから数件のデータを取得するときに効果が出ます。
→件数が少なく全件検索で良い場合は設定は不要です。
カーディナリティが高い場合、B-Treeインデックスを設定します。
→カーディナリティとは値の種類が多いことです。
→カーディナリティが低い場合(性別の男女等)、Bitmapインデックスを設定します。
インデックスの列がwhere句やテーブル結合の条件で使用されてはじめて機能します。
インデックスがある列のデータが更新されると、インデックスも更新されます。
→検索は早いが更新は遅いという状態になります。
インデックス(index)は、索引という意味です。
以下は、Oracleのインデックスのリンクです。
https://docs.oracle.com/cd/E57425_01/121/SQLRF/statements_5013.htm

インデックスを作成するサンプルです。

以下のテーブルがあるとします。

テーブルの項目のNameとCATEGORYにインデックスを作成します。

CREATE INDEX [UNIQUE|BITMAP] インデックス名
On テーブル名 (列名 [ASC|DESC][,列名...])
[TABLESPACE 表領域名];

CREATE INDEX TestIndex3
On SYOUHIN3 (NAME,CATEGORY);

デフォルトはBツリーインデックスが作成されます。

1行目のTestIndex1は、作成するインデックス名です。
2行目は、テーブル名(SYOUHIN3)とインデックスを適用する項目の2つ(NAMEとCATEGORY)を指定しています。
テーブルに関係なくインデックス名が既に存在する場合は作成できません。

Bitmapインデックス

CREATE BITMAP INDEX TestIndex12
On SYOUHIN3 (CATEGORY);

Bitmapインデックスは、Express Editionでは作成できません。
ORA-00439: 機能は有効ではありません: Bit-mapped indexes

インデックスを再作成する

ALTER INDEX TESTINDEX3 REBUILD;

Insert,Update,Deleteでインデックスの効率が悪くなった時に使用します。

ALTER INDEX TESTINDEX3 REBUILD ONLINE;は、Express Editionでは作成できません。
ORA-00439: 機能は有効ではありません: Online Index Build

インデックスを確認するサンプルです。

SELECT * FROM USER_INDEXES
where table_name = 'SYOUHIN3';

USER_INDEXESテーブルで作成したインデックスを確認できます。
テーブル名は、大文字小文字を区別します。

USER_INDEXESテーブル
INDEX_NAME	INDEX_TYPE	TABLE_OWNER	TABLE_NAME
SYS_C0011153	NORMAL	HR	SYOUHIN3
TESTINDEX3	NORMAL	HR	SYOUHIN3

1行目は、テーブル作成時に作成された主キーのインデックスです。
2行目は、作成したインデックスの行です。

※上記のUSER_INDEXESテーブルの列は他にもあります(全57列)。

インデックスを削除するサンプルです。

DROP INDEX TestIndex3;

drop indexとインデックス名を指定します。