第10章　索引（補足）

1.索引とは？

■索引とは？

本の目次と同様に、目的のデータに高速にアクセスするために作成するオブジェクトである。
索引は、表のデータとは別に「列の値」と「行の位置情報（アドレス）」を持ち、列の値はソートされた状態で格納される。
制約によってOracleが自動的に作成する索引と、ユーザが任意に作成する索引がある。

■索引の使途

  1. 表のデータを全て検索（全文検査）するのではなく、索引を検索させることにより、検索を早める。

■索引の特徴

  1. Oracleの索引は、B*Tree構造を利用して格納される（デフォルト）。
  2. 主キー制約、一意キー制約を設定した列（または列の組み合わせ）には自動的に索引が作成される。（索引名には制約名が使われる）

■索引を作成したほうがよい列の条件

  1. 大規模な表の行の１〜１５％未満を頻繁に検索する場合（WHERE条件によりデータが１５％以下に絞り込まれる場合）。
  2. WHERE句の条件として頻繁に使用される列。
  3. 列にNULL値が多く、WHERE句ではNULL以外の値を指定して検索する場合。

■索引を作成しないほうがよい列の条件

  1. 異なる値をほとんど持っていない列の場合（性別や学年、クラスなど）。
  2. 列の値にNULL値が多く、NULLでない値を検索しない場合。
  3. 非常に小さな表である場合。
  4. 頻繁に更新、追加、削除される列。
    （元となる列の値が変わるたびに索引のデータがメンテナンスされるため、頻繁に更新される列に索引を作成すると、データベースのパフォーマンスが下がる場合がある）

■POINT

索引はSELECT（検索）を早めるために作成する！
（DMLの実行は遅くなる）

2.索引の作成

■構文

CREATE [UNIQUE] INDEX 索引名
　ON 表名（列名 [, 列名, ・・・]）
　[TABLESPACE 表領域名]
　[記憶領域管理パラメータ];

例）emp表のename列に（一意でない）索引を作成する

CREATE INDEX idx_emp_ename
　ON emp(ename)
　TABLESPACE user_data;

3.索引の確認

■索引を確認するデータ･ディクショナリ

1.USER_INDEXES
ある索引がどの表に付加されているかを確認
2.USER_IND_COLUMNS
ある索引がどの表のどの列に付加されているかを確認

《USER_INDEXESの列（抜粋）》

列名内容
INDEX_NAME索引名
TABLE_NAME索引が付加されている表名
TABLESPACE_NAME索引が格納されている表領域名

《USER_IND_COLUMNSの列（抜粋）》

列名内容
COLUMN_NAME索引が付加されている列名

4.索引の削除

■構文

DROP INDEX 索引名;

■POINT

  1. 索引を削除しても、元の表は残る。
  2. 表を削除した場合、表に付加されている索引も自動的に削除される。
  3. 主キー制約、一意キー制約定義時に自動的に作成される索引は削除できない。制約を削除または無効にすると自動的に索引が削除される。

まとめ・補足

■テーブル作成時に自動的に作成される索引について

テーブル作成時、PRIMARY KEY制約やUNIQUE制約をつけると、制約を指定した列の索引が作成される。また、索引名には制約名がつく。
この時、CREATE INDEX文と同様に、索引のデータが格納される表領域や記憶領域パラメータを指定することができる。

《表制約構文》

CONSTRAINT 制約名 PRIMARY KEY (列名)
　USING INDEX 記憶領域パラメータ;

《作成例》

CREATE TABLE dept (
　deptno NUMBER(2),
　dname VARCHAR2(9),
　loc VARCHAR(10),
　CONSTRAINT pk_dept PRIMARY KEY (deptno)
　　USING INDEX TABLESPACE indx
)
TABLESPACE users;

練習問題

問題１

emp表のename列に、単一列の非一意索引を作成した。索引が有効に使用される条件として正しい説明を選べ。

  1. emp表は、全文検索が中心である。
  2. emp表は、頻繁にDELETE文が実行される。
  3. ename列は、WHERE句の条件として頻繁に使用される。
  4. ename列は、WHERE句の条件として使用されることは少ない。

問題２

次のSQL文でアクセスする列には、全て単列で索引がつけてある。

SELECT a.ordid, a.cust_name, a.orderdate, b.itemid, b.qty * c.std_price
FROM ord a, item b, price c
WHERE a.ordid = b.ordid AND b.prod_id = c.prod_id
ORDER BY cust_name;

このSQLの実行時に使用される索引は、どの列につけた索引であるか選べ。

  1. ordid
  2. cust_name
  3. orderdate
  4. qty

[ TOP ]