Oracleでカラムの順序が重複したインデックスの検索の違いを見る
カラム順序が重複したインデックスが存在していると、検索にどのような違いがでるのか、また不要であれば削除すればよいか確認する。
環境は11.2.0.4 SEone。
読み込みブロック数を確認する
まず、以下テーブル(行10000件)を用意。
SQL> desc INDTEST; 名前 型 --------------------------------- ID NUMBER(38) (PK) ID2 NUMBER(38) (1-100のランダム) TEXT VARCHAR2(1000) (ランダムな文字列1000バイト)
これに対して、インデックスを追加。
indexname: ID2_IND columns: ID2 indexname: ID2_TEXT_IND columns: ID2,TEXT
ID2
カラムに対して重複したインデックスとなる。
これに対して検索の条件句として、以下があるとする。
1. where ID2 = ?
2. where ID2 = ? and TEXT = ?
2の場合は単純にID2_TEXT_IND
が使用されるので問題なし。
では1の場合はどうなるか。
select * from INDTEST where ID2=1; --------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 70400 | 101 (0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| INDTEST | 100 | 70400 | 101 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | ID2_IND | 100 | | 1 (0)| 00:00:01 | --------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID2"=1) 109 consistent gets 100 rows processed
オプティマイザはID2_IND
を使用するみたい。読み取りブロック数は109ブロック。
強制的にID2_TEXT_IND
を使用するように試したところ。
select /*+ INDEX(INDTEST ID2_TEXT_IND) */ * from INDTEST where ID2=1; -------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 100 | 70400 | 113 (0)| 00:00:02 | | 1 | TABLE ACCESS BY INDEX ROWID| INDTEST | 100 | 70400 | 113 (0)| 00:00:02 | |* 2 | INDEX RANGE SCAN | ID2_TEXT_IND | 100 | | 13 (0)| 00:00:01 | -------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - access("ID2"=1) 121 consistent gets 100 rows processed
インデックスは効いているが、TEXT
カラム分のブロックも読み込む必要があるため読み取りブロック数は121ブロックでID2_IND
を使用した場合よりも増えている。
読み取りブロック数は増えたが、ID2_IND
は不要であるように思える。
ID2_IND
を削除することのメリット
・ インデックス数が多いことによるINSERTのオーバヘッドの軽減
・ ディスク容量の節約
ID2_IND
を削除することのデメリット
・ 読み取りブロック数の増大。特にINDEX FULL SCAN
でTEXT
カラムのサイズが大きい場合無駄なブロックの読み込みが多く発生(下に例)
よって、実行するSELECT文の実行計画を確認しながら削除する必要があります。
INDEX FULL SCAN
の例。
select /*+ INDEX(INDTEST ID2_IND) */ count(id2) from INDTEST ; ---------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 21 (0)| 00:00:01 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | INDEX FULL SCAN| ID2_IND | 10000 | 30000 | 21 (0)| 00:00:01 | ---------------------------------------------------------------------------- 21 consistent gets select /*+ INDEX(INDTEST ID2_TEXT_IND) */ count(id2) from INDTEST ; --------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | --------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 3 | 1004 (1)| 00:00:13 | | 1 | SORT AGGREGATE | | 1 | 3 | | | | 2 | INDEX FULL SCAN| ID2_TEXT_IND | 10000 | 30000 | 1004 (1)| 00:00:13 | --------------------------------------------------------------------------------- 1003 consistent gets
カラム順序の重複したインデックスを見つける
簡単にカラム順序が重複したインデックスを見つけることはできないかなぁということで
ツールを作成しました。
このツールを使用すると、指定したスキーマのテーブル一覧(テーブル指定も可)からカラム順序が重複したインデックスを表示します。
実際に削除はしないで表示のみなので確認後、削除したかったら削除するという手順。
Listagg
関数を使っているので11gR1以降であれば使用可能です。
$ perl oracle-duplicate-indexes-checker.pl --db ORCL --user orauser --password orauser ------------------------------------------------------------------------------------------ drop_recommend: DROP INDEX BBBB_AA tablename: BBBB duplicate_index: BBBB_AA columns: AA index: BBBB_PK columns: AA,BB ------------------------------------------------------------------------------------------ drop_recommend: DROP INDEX ID2_IND tablename: INDTEST duplicate_index: ID2_IND columns: ID2 index: ID2_TEXT_IND columns: ID2,TEXT
drop_recommend・・drop indexする際のSQL文 tablename・・テーブル名 duplicate_index・・削除対象の重複したインデックス。ここには制約(PKなど)は含まれない。 index・・削除対象ではない重複したインデックス。ここには制約は含まれる。
また、DROP INDEX
時は表ロックを取得するので注意が必要。
Oracle 12cであればOracle Standard EditionでもONLINE
句を使用して
ロックなしでインデックスの削除が可能になっている。