kenken0807_DBメモ

つば九郎が好きなDBAです。Oracle Standard Editionでの運用やツールとかとかの備忘録。特に記載がない場合はoracle11gR2です。時々MySQL

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 SCANTEXTカラムのサイズが大きい場合無駄なブロックの読み込みが多く発生(下に例)

よって、実行する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

カラム順序の重複したインデックスを見つける

簡単にカラム順序が重複したインデックスを見つけることはできないかなぁということで
ツールを作成しました。

github.com

このツールを使用すると、指定したスキーマのテーブル一覧(テーブル指定も可)からカラム順序が重複したインデックスを表示します。
実際に削除はしないで表示のみなので確認後、削除したかったら削除するという手順。
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句を使用して
ロックなしでインデックスの削除が可能になっている。