索引（INDEX）とは、データベースから高速にデータを検索するためのオブジェクトです
ほとんどの場合、索引を使用した検索は高速に動作します。
しかし、次の場合は索引検索よりも全表走査のほうが高速に動作する場合があります。
索引の特徴として検索は高速になりますが、逆に更新系の処理(INSERT,UPDATE、DELETE)は索引のメンテナンスによる
オーバーヘッドが発生しますので遅くなります。
索引を作成するには、以下のようなガイドラインがあります
・索引を作成した方がよい場合 大規模な表の、1%から15%程度の行を頻繁に検索する。 列の値が、比較的一意である WHERE句の条件として、頻繁に使用される列である。 ・索引を作成しない方がよい場合 列内に、異なる値がほとんどない（例えば、性別など）。 NULL値が多く、NULLでない値を検索しない。 表が小規模である。 ・一般に、参照整合性制約で使用される外部キー列には、索引の作成を考えるべきです
索引は、表の列の値を抜き出して並べ替えて、ある特定の値をみつけやすくしたものです。
索引には表とは別にディスクの領域が必要です。
検索を実施するときには、必要と判断すればOracleが自動的に索引を参照します。
索引が作成されている表のデータに変更が加えられた場合は、自動的に索引にも変更が加えられます。
たとえば、従業員の情報を格納する表があり、名前の列に対して索引が作成されているとします。
表に新しい従業員の情報を加えた場合は、自動的に索引にも情報が加えられます。
索引の中では値がソートされているので、ORDERBYなどのソートを必要とする処理で索引が使用されることがあります。
ORDERBYを処理する場合、索引の順で表を読み込めば読み込んだ時点でソートされています。
したがって、ソートの処理を省略できます。
索引があることで検索のパフォーマンスはよくなりますが、索引が作成されている表に
データを入力したり変更したりする場合は、
表だけでなく索引にも変更が必要になり、それだけパフォーマンスが悪くなります。
索引には一意索引と一意でない索引の2種類あります。
一意索引では、索引の中に同じ値を含むことができません。
一意でない索引では、索引の中に同じ値が含まれていても構いません。
| 番号 | 番地 | 説明 |
|---|---|---|
| 1111 | 111-111 | ←番号：1111は入力不可。同じ値は入ってはいけない |
| 2222 | 222-222 | |
| - | - |
| 番号 | 番地 | 説明 |
|---|---|---|
| 1111 | 111-111 | ←番号：1111。同じ値が入ってもよい |
| 2222 | 222-222 | |
| 1111 | 111-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 <索引用の表領域>
索引の作成は、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 表領域名] [記憶領域管理パラメータ]]
データベース内に作成された索引を確認するには、USER_INDEXES、
USER_IND_COLUMNSというデータ・ディクショナリを使用します。
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は、以下の列で構成されています。
| COLUMN | 説明 |
|---|---|
| INDEX_NAME | 索引名 |
| TABLE_NAME | 表またはクラスタの名前 |
| COLUMN_POSITION | 索引内の列または属性の位置 |
| COLUMN_LENGTH | 索引が設定された列または属性の長さ |
employee_index1の索引を確認したい時は…
SELECT * FROM USER_INDEXES
WHERE INDEX_NAME = 'EMPLOYEE_INDEX1';
このSQL文を実行する。
索引を削除するには、DROP INDEX文を使用します。
DROP INDEX文では削除する索引を指定します。
DROP INDEX <削除する索引名> 例: DROP INDEX employee_index1;
この例では、employee_index1という索引を削除しています。
2001/03/02 担当：K.F