Oracleのインデックス（索引）の超簡易メモ。
|
|
create index インデックス名 on テーブル名
(項目名, 項目名…);
デフォルトではB-Tree（二分探索の改良版らしい）の索引が作られる。
降順の索引を作成すると、自動的にファンクション索引となる。[2010-07-29]
（ファンクション索引を作成する場合にはquery rewrite権限が必要だが、降順索引の場合は不要）
SQL> create index IX_EMP_SAL_D on EMP (SAL desc);
ビットマップインデックスは、カーディナリー（値の種類数）が低い場合に有効なインデックス。
例えば性別は男・女の2種類しか値を持たないので、カーディナリーが低い。
フラグも0と1しか持たないのでカーディナリーが低い。
ステータスで限定的な値をとるのも低い方だろう。
ビットマップインデックスを張っておけば、その項目を条件に使った全件検索に効果的だと思われる。
select * from テーブル where フラグ='0';
（そうは言っても、検索結果がテーブル全体の大多数を占めるようならあまり意味は無いが…）
ビットマップインデックスの構造上、更新には不利らしいが、以下のような更新ならそれほどでもないんじゃないかと期待。
update テーブル set フラグ='1' where フラグ='0';
（未処理のレコードを全て処理済に変える、という感じ）
あと、範囲指定（「ステータス between 1 and 3」とか）も不向きらしい。
ビットマップインデックスを作るには、通常の索引作成のSQLに対し、以下のように「bitmap」を加えるだけ。
create bitmap index インデックス名 on テーブル名
(項目名, 項目名…);
ただし、ビットマップインデックスはEnterprise Editionでないと作れないらしい。
ORA-00439: 機能は使用可能ではありせん: Bit-mapped indexes
SQL> select * from v$option where parameter ='Bit-mapped indexes';
PARAMETER VALUE
------------------- ------
Bit-mapped indexes FALSE
create index文において、項目名を指定する箇所に演算（式）を書くと、ファンクション索引となる。[2010-07-29]
SQL> create index IX_EMP_DEPT on EMP (substr(DEPTNO,1,1));
create index IX_EMP_DEPT on EMP (substr(DEPTNO,1,1))
*
行1でエラーが発生しました。:
ORA-01031: 権限が不足しています。
ファンクション索引を作成するには、query rewriteの権限が必要なんだそうだ。
SYSユーザー(as sysdba)にて SQL> grant query rewrite to scott;
SQL> create index IX_EMP_DEPT on EMP (substr(DEPTNO,1,1)); 索引が作成されました。
なお、ファンクション索引は通常のインデックス作成より時間がかかるんだそうだ。（複雑な計算式になればなるほど、時間がかかる）
参考： おら! オラ! Oracleの新インデックスの検証 その６
SQLのWHERE条件にファンクション索引で指定した計算式を使うと、定義したファンクション索引が使用される。
SQL> set autot trace exp SQL> select ENAME,DEPTNO from EMP where substr(DEPTNO, 1, 1) = '2'; 実行計画 ---------------------------------------------------------- 0 SELECT STATEMENT Optimizer=CHOOSE (Cost=1 Card=1 Bytes=9) 1 0 TABLE ACCESS (BY INDEX ROWID) OF 'EMP' (Cost=1 Card=1 Bytes=9) 2 1 INDEX (RANGE SCAN) OF 'IX_EMP_DEPT' (NON-UNIQUE) (Cost=1 Card=1)
インデックス名の変更方法。[2010-02-19]
alter index インデックス名 rename to 新インデックス名;
インデックスがプライマリキーの場合、制約の方の名前はこのSQL文では変わらない。別途制約名も変更する必要がある。
テーブルに付けられているインデックスを知るにはuser_indexesビューを使用する。
テーブルに付いているインデックスの項目を知るには、以下のようなSQLを実行すればよい。[2007-12-13]
（user_系で見つからない場合は、all_系で挑戦）
col table_name format a16 trunc col index_name format a24 col uniqueness format a4 trunc col column_name format a24
select i.table_name, i.index_name, i.uniqueness, c.column_name from user_indexes i, user_ind_columns c where i.index_name = c.index_name and i.table_name = 'テーブル名' order by index_name,table_name,column_position ;
| ビュー名 | 項目 | 内容 | 更新日 | ||||||
|---|---|---|---|---|---|---|---|---|---|
| user_indexes | INDEX_TYPE | インデックスの種類。
|
2010-07-29 | ||||||
| UNIQUENESS | ユニーク（一意）かどうか。 | 2010-07-29 | |||||||
| LAST_ANALYZED | 最後に分析（統計情報が取得）された日時。 | 2010-07-29 | |||||||
| PARTITIONED | パーティション化されているかどうか。 | 2010-07-29 | |||||||
| user_ind_columns | DESCEND | 昇順・降順のどちらか。 | 2010-07-29 | ||||||
| user_ind_expressions | COLUMN_EXPRESSION | ファンクション索引の式。 | 2010-07-29 |
→DBMS_METADATA.GET_DDL
→整合性制約の項目を知る方法
ファンクション索引でどのような式になっているかを確認するにはuser_ind_expressionsビューを使用する。[2010-07-29]
ファンクション索引でない項目については、このビューにはレコードは存在しない。
select INDEX_NAME, COLUMN_EXPRESSION, COLUMN_POSITION from user_ind_expressions where TABLE_NAME = 'テーブル名' order by INDEX_NAME,COLUMN_POSITION ;
| パターン | 式の例 | 説明 |
|---|---|---|
| 降順の場合 |
"SAL" |
降順を指定した項目の場合はファンクション索引となるが、項目名だけが登録されている。 |
| 式（演算）の場合 |
SUBSTR(TO_CHAR("DEPTNO"),1,1)
|
作成時に「substr(DEPTNO,1,1)」と指定した。DEPTNOはnumber型だからto_char()が補足されている。 |
インデックスの状態はindex_statsビューで確認できる。[2010-02-04]
ただし、参照する前に分析を実行しておく必要がある。
SQL> analyze index インデックス名 validate structure; 索引が分析されました。 SQL> select NAME,USED_SPACE from index_stats; NAME USED_SPACE ------------------------------ ---------- PK_EMP 335
参考： @ITのBツリーインデックスに最高のパフォーマンスを（1/4）
Oracle9i以降では、インデックスが使用されたかどうかをモニタリングすることが出来る。[2010-02-04]
alter index monitoring usageでモニタリングを開始する。終了するにはnomonitoring。
モニタリングしている間に指定したインデックスが使用されると、v$object_usageビューのUSEDが「YES」になる。
SQL> alter index PK_EMP monitoring usage; --インデックスのモニタリングを開始 索引が変更されました。 SQL> select * from v$object_usage; INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------------------ ------------------------------ --- --- ------------------- ------------------- PK_EMP EMP YES NO 02/04/2010 20:58:37 SQL> select count(*) from emp; --テーブルフルスキャンなので、インデックスは使われない COUNT(*) ---------- 24 SQL> select * from v$object_usage; INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------------------ ------------------------------ --- --- ------------------- ------------------- PK_EMP EMP YES NO 02/04/2010 20:58:37 SQL> select * from emp where empno=123; --インデックスを使用 レコードが選択されませんでした。 SQL> select * from v$object_usage; INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------------------ ------------------------------ --- --- ------------------- ------------------- PK_EMP EMP YES YES 02/04/2010 20:58:37 SQL> alter index PK_EMP nomonitoring usage; --インデックスのモニタリングを終了 索引が変更されました。 SQL> select * from v$object_usage; INDEX_NAME TABLE_NAME MON USE START_MONITORING END_MONITORING ------------------------------ ------------------------------ --- --- ------------------- ------------------- PK_EMP EMP NO YES 02/04/2010 20:58:37 02/04/2010 21:03:03
インデックスを一時的に無効化することが出来る。[2009-12-24]
大量データをINSERTする前に無効化し、終わった後で有効化すると効率が良いらしい。
（無効化・有効化と言っても、意味合いはインデックスのDROP・CREATEと同様らしい？ 無効化するとuser_indexes等で照会できなくなる）
alter table テーブル disable constraint インデックス名; alter table テーブル disable primary key; alter table テーブル disable unique(項目名, …); ←ユニークキーが存在する場合
alter table テーブル enable 〜; ←有効化
…この構文は、「索引の無効化・有効化」と言うより、「制約の無効化・有効化」だなぁ。
テーブルにデータを追加したり削除したりしていると、インデックスが断片化してくるらしい。[2009-12-24]
再作成（再構築）とか結合とかするといいらしい。
alter index インデックス名 rebuild; ←再作成 alter index インデックス名 coalesce; ←結合
インデックスを再構築すると、テーブルロックがかかるらしい。[2010-02-19]
しかし「rebuild online」という指定（Oracle8i以降）にすれば、テーブルロックでなく行ロックになるらしい。
「結合：COALESCE」は、散在する空き領域の結合（断片化の解消）をすることらしい。[2010-02-19]
なお、「結合」はEnterprise Editionでないと使えないらしい。[2009-12-24]
SQL> alter index PK_EMP coalesce;
alter index PK_EMP coalesce
*
行1でエラーが発生しました。:
ORA-00439: 機能は使用可能ではありせん: Coalesce Index
SQL> select * from v$option where parameter ='Coalesce Index';
PARAMETER VALUE
--------------- ------
Coalesce Index FALSE
Oracle11gから、不可視索引というものが導入されたらしい。[2009-12-24]
インデックスとしては存在しているけれども、オプティマイザーが使用しない状態になるんだそうだ。
インデックスの有無による動作確認に使用する想定らしい。
alter index インデックス名 invisible; ←不可視化 alter index インデックス名 visible; ←可視化
select index_name, visibility from user_indexes where index_name='インデックス名';