Oracle Databaseで「データベース内にどのようなインデックスが存在するのか」「通常のB-tree系インデックスなのか、ビットマップやファンクション索引なのか」「UNIQUEかNONUNIQUEか」を調査したい場合、データディクショナリ・ビューのDBA_INDEXESが有力な確認手段になります。
Oracle公式ドキュメントでは、DBA_INDEXESはデータベース内のすべてのインデックスを記述するビューとされています。さらにINDEX_TYPE列でインデックスのタイプ、UNIQUENESS列で一意索引か非一意索引かを確認できます。Oracle Database Reference：DBA_INDEXES[参照]
ただし、「全インデックスを確認できる」という説明には権限上の注意があり、またインデックスを構成する列やファンクション索引の式まで詳しく調べる場合には、DBA_INDEXESだけではなくDBA_IND_COLUMNSやDBA_IND_EXPRESSIONSなどを組み合わせます。ここでは実務で使いやすいSQL例とともに整理します。
- DBA_INDEXESとは何を確認できるビューなのか
- INDEX_TYPE列でインデックスのタイプを確認できる
- UNIQUENESS列でUNIQUEかNONUNIQUEかを確認できる
- どの表のインデックスなのかも一緒に表示すると分かりやすい
- 「全インデックス」の意味には権限上の注意がある
- DBA_INDEXES・ALL_INDEXES・USER_INDEXESの違い
- DBA_INDEXESだけではインデックスを構成する列までは分からない
- タイプ・一意性・構成列をまとめて確認するSQL例
- ファンクション索引の式はDBA_IND_EXPRESSIONSで確認する
- UNIQUEインデックスとUNIQUE制約は同じ意味ではない
- インデックスの状態も一緒に確認すると実務的
- 統計情報の列を見る場合はDBMS_STATSとの関係にも注意
- 目的別にどのビューを使えばよいか
- まとめ｜DBA_INDEXESでタイプと一意性は確認できる
DBA_INDEXESとは何を確認できるビューなのか
DBA_INDEXESは、Oracle Databaseが提供する静的データディクショナリ・ビューの一つです。Oracle公式ドキュメントでは、DBA_INDEXESについてデータベース内のすべてのインデックスを記述すると説明されています。
インデックス名だけではなく、所有者、対象となる表、インデックスのタイプ、一意性、表領域、状態、パーティション関連情報、統計情報など、インデックスを管理・調査するときに役立つ多くの属性を参照できます。
そのため、DBAとしてデータベース全体のインデックス構成を棚卸ししたい場合には、最初に確認する代表的なビューと考えてよいでしょう。
INDEX_TYPE列でインデックスのタイプを確認できる
インデックスの種類を確認するときに利用するのがINDEX_TYPE列です。Oracle公式のALL_INDEXESの説明では、INDEX_TYPEにはNORMAL、BITMAP、FUNCTION-BASED NORMAL、FUNCTION-BASED BITMAP、DOMAIN、LOB、IOT – TOPなどの値が格納されます。DBA_INDEXESの列はTABLE_TYPEに関する差異を除きALL_INDEXESに対応しています。Oracle Database Reference：ALL_INDEXES[参照]
| INDEX_TYPEの代表例 | 概要 |
|---|---|
| NORMAL | 一般的なインデックス |
| NORMAL/REV | リバース・キー・インデックス |
| BITMAP | ビットマップ・インデックス |
| FUNCTION-BASED NORMAL | 関数や式を利用するファンクション索引 |
| FUNCTION-BASED BITMAP | ファンクション・ベースのビットマップ索引 |
| DOMAIN | ドメイン・インデックス |
| LOB | LOBに関連するインデックス |
| IOT – TOP | 索引構成表に関連するインデックス |
なお、実際に返される値や利用可能なインデックス機能にはOracle Databaseのバージョンによる違いがあります。運用しているバージョンのDatabase Referenceを確認することが重要です。
UNIQUENESS列でUNIQUEかNONUNIQUEかを確認できる
インデックスが一意かどうかを調べる場合はUNIQUENESS列を確認します。Oracle公式ドキュメントでは、この列はインデックスが一意ならUNIQUE、非一意ならNONUNIQUEであることを示すと説明されています。Oracle公式のUNIQUENESS列説明[参照]
したがって、データベース全体についてインデックス名、タイプ、一意性を一覧表示する基本形は次のようになります。
SELECT OWNER,
INDEX_NAME,
INDEX_TYPE,
UNIQUENESS
FROM DBA_INDEXES
ORDER BY OWNER, INDEX_NAME;例えばINDEX_TYPEがNORMAL、UNIQUENESSがUNIQUEなら、通常タイプの一意インデックスであることが分かります。NORMALとNONUNIQUEなら非一意の通常インデックスという具合に読み取れます。
どの表のインデックスなのかも一緒に表示すると分かりやすい
実際の調査ではインデックス名だけを見ても用途が分からないことがあります。そのため、対象表を示すTABLE_OWNERとTABLE_NAMEも一緒に取得するのがおすすめです。
SELECT OWNER,
INDEX_NAME,
INDEX_TYPE,
UNIQUENESS,
TABLE_OWNER,
TABLE_NAME
FROM DBA_INDEXES
ORDER BY TABLE_OWNER, TABLE_NAME, INDEX_NAME;これなら「どのスキーマの、どの表に、どのタイプのインデックスがあり、一意なのか」という関係を一覧で把握しやすくなります。
特定スキーマだけを調査したい場合にはWHERE句を追加します。Oracleの通常の未引用識別子として作成したスキーマ名は大文字で管理されるため、例えばAPPUSERなら次のように指定できます。
SELECT OWNER, INDEX_NAME, INDEX_TYPE, UNIQUENESS, TABLE_NAME
FROM DBA_INDEXES
WHERE OWNER = 'APPUSER'
ORDER BY TABLE_NAME, INDEX_NAME;「全インデックス」の意味には権限上の注意がある
DBA_INDEXESはデータベース全体を対象とするDBA系ビューですが、だからといって一般ユーザーが必ず自由に参照できるわけではありません。DBA_で始まるデータディクショナリ・ビューの参照には適切な権限が必要です。
つまり、「DBA_INDEXESをSELECTすればデータベース内の全インデックスを確認できる」という説明はビューの対象範囲としては正しいものの、実際にSQLを実行するユーザーがDBA_INDEXESを参照できる権限を持っていることが前提になります。
アプリケーション用の一般ユーザーでDBA_INDEXESを参照できない場合は、必要以上に強い権限を付与するのではなく、目的に応じてALL_INDEXESやUSER_INDEXESを使うことを検討します。
DBA_INDEXES・ALL_INDEXES・USER_INDEXESの違い
Oracleにはインデックス情報を確認するための似た名前のビューが用意されています。対象範囲が異なるため、目的に応じて使い分けます。
| ビュー | 主な対象 | 主な用途 |
|---|---|---|
| DBA_INDEXES | データベース内のインデックス | DBAによるデータベース全体の調査 |
| ALL_INDEXES | 現在のユーザーがアクセスできる表のインデックス | アクセス可能なオブジェクトの調査 |
| USER_INDEXES | 現在のユーザーが所有するインデックス | 自分のスキーマを調査 |
Oracle公式ドキュメントでも、DBA_INDEXESはデータベース内のインデックス、ALL_INDEXESは現在のユーザーがアクセスできる表のインデックス、USER_INDEXESは現在のユーザーが所有するインデックスを示すものとして区別されています。Oracle Database Administrator’s Guide[参照]
例えば、自分のスキーマに作成したインデックスだけ確認できれば十分なら、DBA_INDEXESを使う必要はありません。
SELECT INDEX_NAME, INDEX_TYPE, UNIQUENESS, TABLE_NAME
FROM USER_INDEXES
ORDER BY TABLE_NAME, INDEX_NAME;DBA_INDEXESだけではインデックスを構成する列までは分からない
DBA_INDEXESで重要な注意点が、「どの列にインデックスが張られているか」を詳細に確認するためのビューではないことです。DBA_INDEXESにはTABLE_NAMEなどはありますが、複合インデックスを構成する各列を順番に一覧化したい場合には別のビューを利用します。
OracleではDBA_IND_COLUMNSがインデックスの列情報を扱います。Oracle公式の管理者ガイドでも、DBA_IND_COLUMNS、ALL_IND_COLUMNS、USER_IND_COLUMNSは表のインデックス列についての情報を表示するビューとして案内されています。Oracle公式：Indexes Data Dictionary Views[参照]
例えば、インデックスを構成する列を確認する基本的なSQLは次のようになります。
SELECT INDEX_OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
COLUMN_NAME,
COLUMN_POSITION
FROM DBA_IND_COLUMNS
ORDER BY INDEX_OWNER, INDEX_NAME, COLUMN_POSITION;COLUMN_POSITIONを見ることで、複合インデックスの場合にどの順序で列が構成されているかも確認できます。
タイプ・一意性・構成列をまとめて確認するSQL例
インデックスの棚卸しでは、DBA_INDEXESとDBA_IND_COLUMNSを結合すると便利です。例えば次のようなSQLで、インデックスのタイプ、一意性、対象表、構成列を一緒に確認できます。
SELECT i.OWNER,
i.INDEX_NAME,
i.INDEX_TYPE,
i.UNIQUENESS,
i.TABLE_OWNER,
i.TABLE_NAME,
c.COLUMN_NAME,
c.COLUMN_POSITION
FROM DBA_INDEXES i
LEFT JOIN DBA_IND_COLUMNS c
ON c.INDEX_OWNER = i.OWNER
AND c.INDEX_NAME = i.INDEX_NAME
ORDER BY i.OWNER, i.INDEX_NAME, c.COLUMN_POSITION;例えば3列からなる複合インデックスなら、同じINDEX_NAMEが3行表示され、それぞれCOLUMN_POSITIONが1、2、3となります。単純なインデックス一覧より、実際の設計内容を把握しやすくなります。
ただし、ファンクション索引では単純な列名だけでは実際の式を十分に把握できないことがあります。その場合は次に説明するDBA_IND_EXPRESSIONSも確認します。
ファンクション索引の式はDBA_IND_EXPRESSIONSで確認する
INDEX_TYPEにFUNCTION-BASED NORMALなどが表示された場合、どのような式をインデックス化しているのか調べたくなることがあります。この情報を確認するために用意されているのがDBA_IND_EXPRESSIONSです。
Oracle公式の管理者ガイドでは、DBA_IND_EXPRESSIONS、ALL_IND_EXPRESSIONS、USER_IND_EXPRESSIONSは表のファンクション索引で使用される式に関する情報を表示すると説明されています。Oracle公式データディクショナリ・ビュー一覧[参照]
例えば、ファンクション索引について式を確認する場合は次のようなSQLが考えられます。
SELECT INDEX_OWNER,
INDEX_NAME,
TABLE_OWNER,
TABLE_NAME,
COLUMN_POSITION,
COLUMN_EXPRESSION
FROM DBA_IND_EXPRESSIONS
ORDER BY INDEX_OWNER, INDEX_NAME, COLUMN_POSITION;UNIQUEインデックスとUNIQUE制約は同じ意味ではない
UNIQUENESSがUNIQUEなら、そのインデックス自体が一意インデックスであることは確認できます。ただし、ここから直ちに「このインデックスはUNIQUE制約によって作られた」と判断するのは適切ではありません。
OracleではUNIQUEインデックスをCREATE UNIQUE INDEXで明示的に作成することもできます。一方、PRIMARY KEYやUNIQUE制約を有効化すると、その制約を実現するためにインデックスが利用されます。Oracle公式ドキュメントでも、一意インデックスとPRIMARY KEY・UNIQUE整合性制約との関係が説明されています。Oracle公式：Managing Indexes[参照]
そのため、「UNIQUENESS=’UNIQUE’だからPRIMARY KEYである」という判定はできません。PRIMARY KEYやUNIQUE制約との関係まで調査するなら、DBA_CONSTRAINTSなどの制約情報と照合する必要があります。
インデックスの状態も一緒に確認すると実務的
インデックス調査ではタイプと一意性だけでなく、STATUSも確認しておくと便利です。インデックスが存在していても、状態によっては通常どおり利用できない可能性があるためです。
SELECT OWNER,
INDEX_NAME,
INDEX_TYPE,
UNIQUENESS,
TABLE_OWNER,
TABLE_NAME,
STATUS
FROM DBA_INDEXES
ORDER BY OWNER, INDEX_NAME;さらにTABLESPACE_NAME、PARTITIONED、VISIBILITYなど、調査目的に合わせて必要な列を追加できます。DBA_INDEXESは単に「インデックス名を見るビュー」ではなく、インデックス管理に必要な多数の属性をまとめて確認できるビューです。
統計情報の列を見る場合はDBMS_STATSとの関係にも注意
DBA_INDEXESには統計情報に関する列もありますが、すべての値がインデックス作成時から常に最新状態で自動的に保証されるわけではありません。Oracle公式ドキュメントでは、このビューの統計情報を収集するためにDBMS_STATSパッケージを使用することが案内されています。Oracle公式：DBA_INDEXES[参照]
ただし、INDEX_TYPEやUNIQUENESSのような定義上の属性を確認することと、BLEVELやLEAF_BLOCKSなどオプティマイザ統計に関係する情報を評価することは分けて考える必要があります。
性能調査まで行うのであれば、DBA_INDEXESだけではなく、DBA_IND_STATISTICSなど関連ビューや実行計画、SQLの実行状況なども含めて確認するのが適切です。
目的別にどのビューを使えばよいか
Oracleにはインデックス関連のデータディクショナリ・ビューが複数あるため、目的を明確にすると選びやすくなります。
| 調べたい内容 | 代表的なビュー |
|---|---|
| データベース全体のインデックス概要 | DBA_INDEXES |
| アクセス可能なインデックス | ALL_INDEXES |
| 自分が所有するインデックス | USER_INDEXES |
| インデックスを構成する列 | DBA_IND_COLUMNS |
| ファンクション索引の式 | DBA_IND_EXPRESSIONS |
| インデックスの統計情報 | DBA_IND_STATISTICS |
| パーティション単位の情報 | DBA_IND_PARTITIONS |
Oracle公式のAdministrator’s Guideでも、これらのビューが目的別に整理されています。Oracle公式：Indexes Data Dictionary Views[参照]
まとめ｜DBA_INDEXESでタイプと一意性は確認できる
Oracle DatabaseのDBA_INDEXESを参照すれば、データベース内のインデックスについてINDEX_TYPEでタイプ、UNIQUENESSで一意性を確認できます。DBA_INDEXES自体はデータベース内のすべてのインデックスを記述するDBA向けビューですが、実際に参照するには必要な権限があることが前提です。
基本的な確認だけなら、SELECT OWNER, INDEX_NAME, INDEX_TYPE, UNIQUENESS FROM DBA_INDEXES;という形で十分です。対象表も確認したければTABLE_OWNERとTABLE_NAME、状態も確認したければSTATUSなどを追加します。
一方、「どの列で構成されているか」まで調べるならDBA_IND_COLUMNS、「ファンクション索引でどんな式を使っているか」ならDBA_IND_EXPRESSIONS、「UNIQUEインデックスがPRIMARY KEYやUNIQUE制約とどう関係しているか」まで調べるなら制約系のデータディクショナリ・ビューも確認する必要があります。
つまり、DBA_INDEXESはインデックス全体のタイプや一意性を把握する入口として非常に適していますが、インデックス設計の全情報が一つのビューだけに収録されているわけではありません。調査目的に応じて関連するDBA_IND_*ビューや制約系ビューを組み合わせるのが、Oracle Databaseのインデックス情報を正確に確認する基本的な方法です。
コメント