21 索引の管理
索引は、データ・アクセスを高速化できます。索引を作成、変更、監視および削除できます。
- 索引について
索引は表およびクラスタと関連付けられているオプション構造で、これにより表に対するSQL問合せをより迅速に実行できます。 - 索引を管理するためのガイドライン
ガイドラインに従って索引を管理できます。 - 索引の作成
いくつかの異なるタイプの索引を作成できます。明示的な索引の作成、および制約に関連付けられた索引の作成ができます。 - 索引の変更
索引の記憶域の特性を変更する、再作成する、使用不能にする、または表示/非表示にするなどのタスクを完了することによって、索引を変更することができます。 - 索引の使用領域の監視
索引のキー値が頻繁に挿入、更新および削除されると、時間が経つにつれて領域の効率性が失われることがあります。 - 索引の削除
索引は、DROP INDEX
文により削除できます。 - 索引のデータ・ディクショナリ・ビュー
索引に関する情報について一連のデータ・ディクショナリ・ビューを問い合せることができます。
親トピック: スキーマ・オブジェクト
21.1 索引について
索引は表およびクラスタと関連付けられているオプション構造で、これにより表に対するSQL問合せをより迅速に実行できます。
このマニュアルで、索引の使用により、索引がない場合よりも高速に情報を検索できるように、Oracle Databaseの索引は、表データへのより高速なアクセス・パスを提供します。索引は問合せをリライトすることなく使用できます。結果は同じですが、より高速に得られます。
Oracle Databaseは、補完的なパフォーマンス機能を持つ複数の索引付け方法を提供します。それらは次のとおりです。
-
Bツリー索引: デフォルトの設定で、最も一般的です。
-
Bツリー・クラスタ索引: 特にクラスタ用に定義します。
-
ハッシュ・クラスタ索引: 特にハッシュ・クラスタ用に定義します。
-
グローバル索引とローカル索引: パーティション表とパーティション索引に関連します。
-
逆キー索引: Oracle Real Application Clustersアプリケーションに最も役立ちます。
-
ビットマップ索引: サイズが小さいので、小さい値の集合を持つ列に効果的です。
-
ファンクション索引: 事前計算された関数や式の値を含みます。
-
ドメイン索引: アプリケーションまたはカートリッジに固有の索引です。
索引は、対応付けられた表内のデータから論理的にも物理的にも独立しています。索引は独立した構造体であり、記憶域を必要とします。索引は、実表、データベース・アプリケーションまたはその他の索引に影響を与えることなく、作成または削除できます。索引に対応する表に対して行の挿入、更新および削除が発生すると、データベースは索引を自動的にメンテナンスします。索引を削除しても、すべてのアプリケーションは引き続き動作可能です。ただし、それまで索引が付けられていたデータへのアクセスが遅くなります。
関連項目:
-
索引の概要については、『Oracle Database概要』を参照してください。
親トピック: 索引の管理
21.2 索引を管理するためのガイドライン
ガイドラインに従って索引を管理できます。
- 表データ挿入後の索引の作成
データは、通常、SQL*Loaderまたはインポート・ユーティリティを使用して表に挿入またはロードされます。データの挿入またはロードの後に表の索引を作成すると効率がよくなります。データのロード前に1つ以上の索引を作成すると、データベースに行が挿入されるたびに索引を更新する必要があります。 - 正しい表および列への索引付け
索引付けに適した表および列についてのガイドラインに従います。 - パフォーマンスのための索引列の順序
CREATE
INDEX
文の列の順序は、問合せのパフォーマンスに影響を与える可能性があります。一般的には、最も頻繁に使用する列を最初に指定します。 - 表当たりの索引数の制限
表は、任意の数の索引を持つことができます。ただし、索引の数が多いほど、表を変更するときに発生するオーバーヘッドが増加します。 - 不必要な索引の削除
必要とされない索引は、削除することをお薦めします。 - 索引およびセグメント作成の遅延
索引セグメントの作成は、対応付けられた表でセグメント作成の遅延が発生すると、遅延されます。これは、索引セグメントの作成には対応付けられた表の動作が反映されるためです。 - 索引サイズの見積りと記憶域パラメータの設定
索引を作成する前にそのサイズを見積っておくと、ディスク領域の計画と管理がいっそう容易になります。 - 各索引の表領域の指定
任意の表領域に索引を作成できます。索引は、その索引を付けた表と同じ表領域にも、異なる表領域にも作成できます。 - 索引作成のパラレル化
表作成をパラレル化できるのと同様に、索引の作成もパラレル化できます。複数のプロセスが同時に動作して索引を作成するため、1つのサーバー・プロセスが順に索引を作成する場合よりも高速に索引を作成できます。 - 索引作成時のNOLOGGINGの使用
CREATE INDEX
文でNOLOGGING
を指定すると、索引の作成時に最小限のREDOログ・レコードしか生成されません。 - 使用禁止または不可視索引の使用について
使用禁止または不可視索引は、バルク・ロードのパフォーマンスを向上させる場合、索引を削除する前に削除によって発生する影響をテストする場合、またはオプティマイザによるその索引の使用を停止する場合に使用します。 - 同じ列セットに対する複数の索引の作成について
索引がいくらか異なる場合、同じ列セットに複数の索引を作成できます。たとえば、同じ列セットに対してBツリー索引とビットマップ索引を作成できます。 - 索引の結合と再作成に関するコストと利点の検討
不適切な索引サイズの設定やサイズの増加によって、索引の断片化が生じることがあります。断片化を解消または低減するには、索引を再作成するか、索引を結合します。ただし、どちらの作業を行う場合も、事前に各選択肢のコストと利点を分析し、状況に最も有効な方法を選択してください。 - 制約を使用禁止または削除する前のコストの検討
一意キーと主キーには対応する索引があるため、UNIQUE
制約やPRIMARY KEY
制約を使用禁止または削除するかどうかを検討するときには、索引の削除と作成にかかわるコストを考慮に入れてください。 - 索引数を減らすためのインメモリー列ストアの使用の検討
インメモリー列ストアは、システム・グローバル領域(SGA)のオプション部分で、高速スキャン用に最適化された表、表パーティション、その他のデータベース・オブジェクトのコピーが格納されます。インメモリー列ストアでは、表データがSGAに行ではなく列ごとに格納されます。
関連項目:
-
Oracleが提供する各種索引付け方法の説明など、索引と索引付けの概念に関する情報は、『Oracle Database概要』を参照してください。
-
ビットマップ索引の詳細は、『Oracle Database SQLチューニング・ガイド』および 『Oracle Databaseデータ・ウェアハウス・ガイド』
-
ドメイン固有のオペレータと索引付け方法の定義方法およびOracle Databaseサーバーへの統合方法については、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
親トピック: 索引の管理
21.2.1 表データ挿入後の索引の作成
データは、通常、SQL*Loaderまたはインポート・ユーティリティを使用して表に挿入またはロードされます。データの挿入またはロードの後に表の索引を作成すると効率がよくなります。データのロード前に1つ以上の索引を作成すると、データベースに行が挿入されるたびに索引を更新する必要があります。
すでにデータが格納されている表に索引を作成するには、ソート領域が必要です。索引の作成ユーザーに割り当てられているメモリーから確保されるソート領域もあります。各ユーザーのソート領域の大きさは、初期化パラメータSORT_AREA_SIZE
によって決まります。また、データベースでは、索引作成のときにのみユーザーの一時表領域に割り当てられる一時セグメントとの間でソート情報のスワップが行われます。
特定の条件下では、SQL*Loaderのダイレクト・パス・ロードを使用してデータを表にロードし、データがロードされたときに索引が作成されるようにすることも可能です。
関連項目:
SQL*Loaderを使用したダイレクト・パス・ロードの詳細は、『Oracle Databaseユーティリティ』を参照してください。
親トピック: 索引を管理するためのガイドライン
21.2.2 正しい表および列への索引付け
索引付けに適した表および列についてのガイドラインに従います。
索引を作成するかどうか判断する際は、次のガイドラインを参考にしてください。
-
大きな表で頻繁に検索される行の割合が15%未満の場合は索引を作成してください。割合は、表スキャンの相対速度、および索引キーに対する行データの分散度によって大きく異なります。表スキャンが高速であるほど割合は低くなり、クラスタ化されている行データが多いほど割合は高くなります。
-
複数の表を結合するパフォーマンスを改善するには、結合に使用する列に索引を付けます。
ノート:
主キーおよび一意キーには自動的に索引が作成されますが、外部キーには必要に応じて索引を作成できます。
-
小さい表に索引は不要です。問合せに時間がかかる場合は、表のサイズが大きくなっていることが考えられます。
索引付けに適した列
列のタイプによっては、索引を付ける方が望ましいものがあります。次のような特性を1つ以上持つ列には、索引の作成を検討してください。
-
一意の値が比較的多い。
-
値の範囲が広い(通常の索引が適している)。
-
値の範囲が狭い(ビットマップ索引が適している)。
-
列に多くのNULLが含まれているが、通常の問合せでは必ず値を持つ列を選択する。この場合は次の句を使用します。
WHERE COL_X > -9.99 * power(10,125)
この句は、次の句よりも適しています。
WHERE COL_X IS NOT NULL
これは、最初の句では
COL_X
の索引を使用しているためです(COL_X
は数値列とします)。
索引付けに適さない列
次のような特性を持つ列は、索引付けには適していません。
-
列にNULLが多く含まれていて、NULL以外の値を検索することがない。
LONG
列およびLONG
RAW
列には索引を作成できません。
仮想列
関連項目:
親トピック: 索引を管理するためのガイドライン
21.2.3 パフォーマンスのための索引列の順序付け
CREATE
INDEX
文の列の順序は、問合せのパフォーマンスに影響を与えます。一般的には、最も頻繁に使用する列を最初に指定します。
たとえば、col1
、col2
およびcol3
の各列にアクセスする問合せを高速にするために、列にまたがる単一の索引を作成すると、col1
のみにアクセスする問合せ、またはcol1
とcol2
にアクセスする問合せが速くなります。しかし、col2
のみにアクセスする問合せ、col3
のみにアクセスする問合せ、およびcol2
とcol3
にアクセスする問合せは速くなりません。
ノート:
先頭列のカーディナリティが非常に低い場合などには、データベースでは、このタイプの索引が使用されることがあります。索引スキップ・スキャンの詳細は、『Oracle Database概要』を参照してください。
親トピック: 索引を管理するためのガイドライン
21.2.4 表当たりの索引数の制限
表は、多数の索引を持つことができます。ただし、索引の数が多いほど、表を変更するときに発生するオーバーヘッドが増加します。
特に、行を挿入したり削除したりするときは、その表の索引もすべて更新する必要があります。また、列を更新するときには、その列を含む索引もすべて更新する必要があります。
このように、表からデータを検索する速度とその表を更新する速度は二律背反的です。たとえば、表が主に読取り専用である場合、索引を増やすと有効ですが、表が頻繁に更新される場合は、索引を少なくすることをお薦めします。
親トピック: 索引を管理するためのガイドライン
21.2.5 不必要な索引の削除
必要とされない索引は、削除することをお薦めします。
次のような状況では、索引の削除を検討してください。
-
問合せを高速化しない。これには、たとえば表が非常に小さい場合や、表の行数は多いものの、索引エントリが非常に少ない場合などがあります。
-
アプリケーションの問合せが索引を使用しない場合。
-
索引を再作成する前にいったん削除する必要がある場合。
関連項目:
親トピック: 索引を管理するためのガイドライン
21.2.6 索引およびセグメント作成の遅延
索引セグメントの作成は、対応付けられた表でセグメント作成の遅延が発生すると、遅延されます。これは、索引セグメントの作成には対応付けられた表の動作が反映されるためです。
関連項目:
詳細は、「セグメント作成の遅延の理解」を参照してください
親トピック: 索引を管理するためのガイドライン
21.2.7 索引サイズの見積りと記憶域パラメータの設定
索引を作成する前にそのサイズを見積っておくと、ディスク領域の計画と管理がいっそう容易になります。
索引の見積りサイズの合計と、表、UNDO表領域およびREDOログ・ファイルの見積りを使用して、作成するデータベースを格納するために必要なディスク容量を決定できます。この見積りを利用して適切なハードウェアを購入できます。
個々の索引の見積りサイズを使用することで、索引が使用するディスク領域をより適切に管理できます。索引を作成するときに、適切な記憶域パラメータを設定し、その索引を使用するアプリケーションのI/Oパフォーマンスを改善できます。たとえば、索引を作成する前に最大サイズを予測したとします。次に索引の作成時に記憶域パラメータを設定すると、表データ・セグメントに対して割り当てられるエクステントが少なくなり、すべての索引データはディスク領域の比較的連続したセクションに格納されます。これにより、この索引に関するディスクのI/O操作にかかる時間が削減されます。
単一の索引エントリの最大サイズは、データベースのブロック・サイズによって異なります。
索引を主キー制約または一意キー制約を規定するために使用する場合、その索引のために作成する索引セグメントの記憶域パラメータは、次のどちらかの方法で設定できます。
-
ENABLE
...CREATE
TABLE
文またはALTER
TABLE
文のUSING
INDEX
句 -
ALTER
INDEX
文のSTORAGE
句
関連項目:
-
索引のサイズに関する制限の詳細は、Oracle Databaseリファレンスを参照してください
-
拡張データ型列への索引の作成の詳細は、Oracle Database SQL言語リファレンスを参照してください
親トピック: 索引を管理するためのガイドライン
21.2.8 各索引の表領域の指定
索引はどの表領域にも作成できます。索引は、その索引を付けた表と同じ表領域にも、異なる表領域にも作成できます。
表とその索引に対して同じ表領域を使用すると、(表領域やファイルのバックアップなどの)データベースのメンテナンスやアプリケーションの可用性確保の面で便利です。これは、すべての関連するデータが常にまとまってオンラインになっているためです。
表とその索引に対して(異なるディスク上にある)異なる表領域を使用すると、表と索引を同じ表領域に格納するよりも、パフォーマンスが向上します。これは、ディスクの競合が解消されるためです。表とその索引に対して異なる表領域を使用し、一方の(データまたは索引のいずれかを含む)表領域がオフラインになっている場合には、その表を参照している文が動作する保証はありません。
親トピック: 索引を管理するためのガイドライン
21.2.9 索引作成のパラレル化
表作成をパラレル化できるのと同様に、索引の作成もパラレル化できます。複数のプロセスが同時に動作して索引を作成するため、1つのサーバー・プロセスが順に索引を作成する場合よりも高速に索引を作成できます。
索引を並行して作成する場合、問合せサーバー・プロセスごとに別々の記憶域パラメータが使用されます。したがって、INITIAL
値を5MB、並行度を12で索引を作成する場合は、作成時に60MB以上の記憶域を使用します。
関連項目:
パラレル実行の使用に関する詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。
親トピック: 索引を管理するためのガイドライン
21.2.10 索引作成時のNOLOGGINGの使用
CREATE INDEX
文でNOLOGGING
を指定すると、索引の作成時に最小限のREDOログ・レコードしか生成されません。
ノート:
NOLOGGING
を使用して作成された索引はアーカイブされないため、索引作成後にバックアップを実行してください。
NOLOGGING
を使用して索引を作成すると、次のような利点があります。
-
REDOログ・ファイルの領域を節約できます。
-
索引の作成に要する時間が削減できます。
-
大規模な索引のパラレル作成のパフォーマンスが向上します。
一般に、LOGGING
を指定しないで索引を作成した場合、小規模な索引より大規模な索引の方が相対的にパフォーマンスの向上が大きくなります。小規模な索引をLOGGING
を指定しないで作成しても、索引作成に要する時間にはほとんど影響しません。一方、大規模な索引では、特に索引作成のパラレル化もあわせて指定したときに、パフォーマンスが著しく向上します。
親トピック: 索引を管理するためのガイドライン
21.2.11 使用禁止または不可視索引の使用について
使用禁止または不可視索引は、バルク・ロードのパフォーマンスを向上させる場合、索引を削除する前に削除によって発生する影響をテストする場合、またはオプティマイザによるその索引の使用を停止する場合に使用します。
使用禁止索引
使用禁止索引は、オプティマイザで無視され、DMLではメンテナンスされません。索引を使用禁止にする理由の1つに、バルク・ロードのパフォーマンス向上があります。(行の挿入時にデータベースで索引のメンテナンスを実行する必要がないため、バルク・ロードが速くなります。)索引を削除した後に再度作成する場合はCREATE
INDEX
文のパラメータを正確に覚えておく必要がありますが、この方法では索引を使用禁止にした後に再構築できます。
使用禁止状態の索引を作成することも、既存の索引または索引パーティションに使用禁止のマークを付けることもできます。場合によっては、データベースで索引の構築中に障害が発生したときなどに、索引に使用禁止のマークが付けられることがあります。パーティション化された索引のパーティションのうちの1つを使用禁止にした場合、その索引の他のパーティションは有効なままです。
使用禁止の索引または索引パーティションを使用するには、再構築するか、または削除して再作成する必要があります。表を切り捨てると、使用禁止の索引が有効になります。
既存の索引を使用禁止にすると、その索引セグメントが削除されます。
使用禁止の索引の機能は、SKIP_UNUSABLE_INDEXES
の初期化パラメータの設定により異なります。SKIP_UNUSABLE_INDEXES
がTRUE
(デフォルト)の場合は、次のようになります。
-
表に対するDML文は続行されますが、使用禁止索引のメンテナンスは実行されません。
-
一意制約を規定する使用禁止索引がある場合は、DML文はエラーで終了します。
-
パーティション化されていない索引では、オプティマイザは
SELECT
文のアクセス計画の作成時に使用禁止索引を考慮しません。唯一の例外は、索引がINDEX()
のヒントで明示的に指定された場合です。 -
1つ以上のパーティションが使用禁止となっているパーティション索引に対しては、オプティマイザでは表の拡張を使用できます。オプティマイザでは、表の拡張を使用して、問合せを
UNION
ALL
文に変換し、ここに、索引付けされたパーティションにアクセスしたり、使用禁止索引を使用してパーティションにアクセスする副問合せを含めます。オプティマイザでは、パーティションで使用できる、最も効率的なアクセス方法を選択できます。表の拡張の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。
SKIP_UNUSABLE_INDEXES
がFALSE
の場合は、次のようになります。
-
使用禁止の索引または索引パーティションがある場合は、これらの索引または索引パーティションを更新するDML文はエラーで終了します。
-
SELECT
文は、使用禁止索引または使用禁止索引パーティションが存在しても、オプティマイザでアクセス計画に使用されない場合は、通常どおり実行されます。ただし、オプティマイザが使用禁止索引または使用禁止索引パーティションを使用する場合は、この文はエラーで終了します。
不可視索引
不可視索引を作成したり、既存の索引を不可視にできます。不可視索引は、セッションまたはシステム・レベルでOPTIMIZER_USE_INVISIBLE_INDEXES
初期化パラメータを明示的にTRUE
に設定しないかぎり、オプティマイザで無視されます。使用禁止索引と異なり、不可視索引はDML文中でも維持されます。パーティション化された索引は不可視にできますが、個別の索引パーティションを不可視にし、残りのパーティションを可視のままにすることはできません。
不可視索引を使用して、次のことを実行できます。
-
索引を削除する前に、削除した状態をテストできます。
-
アプリケーション全体に影響を与えずに、アプリケーションの特定の操作またはモジュールに対して一時的な索引構造を使用できます。
-
索引がすでに存在する列セットに索引を追加します。
親トピック: 索引を管理するためのガイドライン
21.2.12 同じ列セットに対する複数の索引の作成について
同じ列セットに対して、一部が異なる複数の索引を作成できます。たとえば、同じ列セットに対してBツリー索引とビットマップ索引を作成できます。
同じ列のセットに複数の索引がある場合、同時に表示できる索引は1つのみで、他の索引は不可視である必要があります。
同じ列セットに対して異なる索引を作成することは、要求を満たすための柔軟性を実現するのに役立ちます。また、既存の索引を削除して異なる属性を使用して再作成することなく、アプリケーションの移行を実行するために、同じ列セットに対して複数の索引を作成することもできます。
使用例が異なると、役立つ索引のタイプが異なります。たとえば、Bツリー索引は、数多くの同時トランザクションが発生するオンライン・トランザクション処理(OLTP)システムで一般的に使用され、ビットマップ索引は、ほとんど問合せに使用されるデータ・ウェアハウス・システムで一般的に使用されます。同様に、ローカル・パーティション化索引とグローバル・パーティション化索引は、異なる使用例において役立ちます。ローカル・パーティション索引は、パーティションのメンテナンス操作が自動的に適用されるため、管理が容易です。グローバル・パーティション索引は、索引のパーティション化スキームを表のパーティション化スキームと異なるものにする場合に役立ちます。
次のうち、少なくとも1つの索引特性が異なる場合に、同じ列セットに対して複数の索引を作成できます。
-
索引のタイプが異なります。
異なるタイプの索引の詳細は、「索引について」および『Oracle Database概要』を参照してください。
ただし、次の例外があります。
-
同じ列セットに対して、Bツリー索引とBツリー・クラスタ索引を作成することはできません。
-
同じ列セットに対して、Bツリー索引と索引構成表を作成することはできません。
-
-
索引によって、異なるパーティション化が使用されます。
パーティション化は、次のように異なることがあります。
-
パーティション化されていない索引と、パーティション化された索引
-
ローカル・パーティション索引と、グローバル・パーティション索引
-
パーティション化タイプ(レンジまたはハッシュ)が異なる索引
-
-
索引の一意性プロパティが異なります。
同じ列セットに対して、一意索引と、非一意索引を作成できます。
親トピック: 索引を管理するためのガイドライン
21.2.13 索引の結合と再作成に関するコストと利点の検討
不適切な索引サイズの設定やサイズの拡大によって、索引の断片化が生じることがあります。断片化を解消または低減するには、索引を再作成するか、索引を結合します。ただし、どちらの作業を行う場合も、事前に各選択肢のコストと利点を分析し、状況に最も有効な方法を選択してください。
表21-1は、索引を再作成する場合と結合する場合のコストと利点を示しています。
表21-1 索引の結合と再作成に関するコストと利点
索引の再作成 | 索引の結合 |
---|---|
索引を別の表領域に迅速に移動できる。 |
索引を別の表領域に移動することはできない。 |
多くのディスク領域を必要とし、コストが高い。 |
必要なディスク領域が少ないため、コストが低い。 |
新しいツリーを作成して、可能であればその高さを縮小する。 |
ツリーの同じブランチ内のリーフ・ブロックを結合する。 |
オリジナルの索引を削除せずに、記憶域パラメータと表領域パラメータを迅速に変更できる。 |
索引のリーフ・ブロックを迅速に解放できる。 |
再利用のために解放できるBツリー索引のリーフ・ブロックがある場合は、次の文を使用してそのようなリーフ・ブロックをマージできます。
ALTER INDEX vmoore COALESCE;
図21-1は、ALTER INDEX COALESCE
が索引vmoore
に与える影響を示しています。操作を実行する前は、最初の2つのリーフ・ブロックは50%使用されています。したがって、フラグメンテーションを削減して最初のブロック全体を使用しながら、2番目のブロックを解放することが可能になります。
親トピック: 索引を管理するためのガイドライン
21.2.14 制約を使用禁止または削除する前のコストの検討
一意キーと主キーには対応する索引があるため、UNIQUE
制約やPRIMARY KEY
制約を使用禁止または削除するかどうかを検討するときには、索引の削除と作成にかかわるコストを考慮に入れてください。
また、UNIQUE
キーやPRIMARY KEY
制約に対する索引が大きい場合には、その索引を削除して再作成するよりも、その制約を使用可能な状態のままにする方が時間を節約できます。UNIQUE
制約やPRIMARY KEY
制約を削除または使用禁止にするときには、索引を保持するか削除するかを明示的に指定することもできます。
関連項目:
親トピック: 索引を管理するためのガイドライン
21.2.15 索引数を減らすためのインメモリー列ストアの使用の検討
インメモリー列ストアは、システム・グローバル領域(SGA)のオプション部分で、高速スキャン用に最適化された表、表パーティション、その他のデータベース・オブジェクトのコピーが格納されます。インメモリー列ストアでは、表データがSGAに行ではなく列ごとに格納されます。
ノート:
この機能は、Oracle Database 12cリリース1 (12.1.0.2)以降で使用可能です。
OLTPまたはデータ・ウェアハウス環境で使用される表には通常、分析およびレポート作成の問合せのパフォーマンスを向上させるために、複数の索引が作成されます。このような索引は、データ操作言語(DML)文のパフォーマンスを低下させる可能性があります。インメモリー列ストアに表を格納すると、問合せのパフォーマンスに影響を及ぼすことなく、分析およびレポート作成の問合せに使用される索引を大幅に削減または消去できます。このような索引を消去すると、トランザクションおよびデータ・ロード操作のパフォーマンスを向上させることができます。
21.3 索引の作成
いくつかの異なるタイプの索引を作成できます。明示的な索引の作成、および制約に関連付けられた索引の作成ができます。
Live SQL:
Oracle Live SQLでの索引の作成に関連する例を参照して実行するには、Oracle Live SQL: 索引の作成にアクセスしてください。
- 索引の作成の前提条件
索引を作成する前に、前提条件を満たす必要があります。 - 索引の明示的な作成
SQL文CREATE INDEX
を使用して、明示的に(整合性制約外に)索引を作成できます。 - 一意索引の明示的な作成
索引は、一意にも非一意にもできます。一意索引を使用すると、表の複数行のキー列に重複した値が入らないことが保証されます。非一意索引では、列の値にこのような制限はありません。 - 制約に対応付けられた索引の作成
SQL文CREATE TABLE
またはALTER TABLE
を発行すると、制約に関連付けられた索引を作成できます。 - 大きな索引の作成
非常に大きな索引を作成する場合は、索引の作成のために大きな一時表領域の割当てを検討してください。 - 索引のオンラインでの作成
オンラインで索引を作成および再作成できます。そのため、実表の索引を作成または再作成する際に、同時に実表も更新できます。 - ファンクション索引の作成
ファンクション索引により、関数や式から返される値を修飾する問合せが容易になります。関数や式の値は、事前に計算されて索引に格納されます。 - 圧縮された索引の作成
データベースが大きくなったら、ディスク領域を節約するために、索引圧縮の使用を検討してください。 - 使用禁止索引の作成
UNUSABLE
状態の索引を作成した場合、オプティマイザで無視され、DMLではメンテナンスされません。使用禁止の索引を使用可能にする場合、再構築するか、または削除して再作成する必要があります。 - 不可視索引の作成
不可視索引とは、セッションまたはシステム・レベルでOPTIMIZER_USE_INVISIBLE_INDEXES
初期化パラメータを明示的にTRUE
に設定しないかぎり、オプティマイザで無視される索引です。 - 同じ列セットに対する複数の索引の作成
索引がいくらか異なる場合、同じ列セットに複数の索引を作成できます。
親トピック: 索引の管理
21.3.1 索引作成の前提条件
索引を作成する前に、前提条件を満たす必要があります。
自分のスキーマ内に索引を作成する場合は、次のうち少なくとも1つの前提条件が満たされている必要があります。
-
索引を付ける表またはクラスタが、自分のスキーマに格納されている。
-
索引を付ける表に対する
INDEX
権限を持っている。 -
CREATE ANY INDEX
システム権限を持っている。
別のスキーマ内に索引を作成する場合は、次のすべての前提条件が満たされている必要があります。
-
CREATE ANY INDEX
システム権限を持っている。 -
目的のスキーマの所有者が、索引または索引パーティションを作成する表領域への割当て制限を持っているか、
UNLIMITED TABLESPACE
システム権限を持っている。
親トピック: 索引の作成
21.3.2 索引の明示的な作成
SQL文CREATE INDEX
を使用して、索引を明示的に(整合性制約の他に)作成できます。
次の文は、emp
表のename
列に対してemp_ename
という名前の索引を作成します。
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k);
索引に対して、複数の記憶域設定および1つの表領域が明示的に指定されています。索引に記憶域オプション(INITIAL
やNEXT
など)を指定しない場合、デフォルトの表領域または指定された表領域のデフォルトの記憶域オプションが自動的に使用されます。
Live SQL:
Oracle Live SQLの関連する例をOracle Live SQL: 索引の作成で参照して実行してください。
関連項目:
CREATE INDEX
文の構文と制限事項については、『Oracle Database SQL言語リファレンス』
親トピック: 索引の作成
21.3.3 一意索引の明示的な作成
索引は、一意にすることも、重複を許可することもできます。一意索引を使用すると、表の複数行のキー列に重複した値が入らないことが保証されます。非一意索引では、列の値にこのような制限はありません。
一意索引を作成するには、CREATE UNIQUE INDEX
文を使用します。次の例では、一意索引を作成しています。
CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;
別の方法として、目的の列に一意整合性制約を定義することもできます。データベースでは、一意のキーに対して自動的に一意索引を定義することによって、
一意整合性制約を規定します。この詳細は次の項に記載されています。ただし、問合せのパフォーマンス向上のために必要な索引は、一意索引も含め、明示的に作成することをお薦めします。
Live SQL:
Oracle Live SQLの関連する例をOracle Live SQL: 索引の作成で参照して実行してください。
関連項目:
パフォーマンス向上のための索引作成の詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。
親トピック: 索引の作成
21.3.4 制約に対応付けられた索引の作成
SQL文CREATE TABLE
またはALTER TABLE
を発行すると、制約に関連付けられた索引を作成できます。
- 制約に対応付けられた索引の作成について
Oracle Databaseは、表にUNIQUE
キー整合性制約またはPRIMARY KEY
整合性制約を規定するために、一意キーまたは主キーの一意索引を作成します。 - 制約に対応付けられた索引に対する記憶域オプションの指定
USING INDEX
句を使用すると、UNIQUE
制約またはPRIMARY KEY
制約に対応する索引の記憶域オプションを設定できます。 - 制約に対応付けられた索引の指定
制約に対応付けられた索引の詳細を指定できます。
親トピック: 索引の作成
21.3.4.1 制約に対応付けられた索引の作成について
Oracle Databaseは、表にUNIQUE
キー整合性制約またはPRIMARY KEY
整合性制約を規定するために、一意キーまたは主キーの一意索引を作成します。
この索引は、制約を使用可能にしたときに、データベースによって自動的に作成されます。CREATE TABLE
文またはALTER TABLE
文を発行して索引を作成する場合は、それ以外に必要なアクションはありませんが、必要に応じて、USING INDEX
句を指定して索引作成を制御できます。これは、制約を定義して使用可能にする場合、および定義したが使用禁止にしていた制約を使用可能にする場合のどちらでも可能です。
UNIQUE
制約またはPRIMARY KEY
制約を使用可能にし、対応する索引を作成するには、表の所有者が索引を格納する表領域の割当て制限またはUNLIMITED TABLESPACE
システム権限を持っている必要があります。特に指定しないかぎり、制約に対応付けられた索引には、常に制約と同じ名前が付けられます。
ノート:
並列性を利用できるように制約を使用可能にする効率的な手順は、「整合性制約の効率的な使用: 手順」を参照してください。
親トピック: 制約に対応付けられた索引の作成
21.3.4.2 制約に対応付けられた索引に対する記憶域オプションの指定
USING INDEX
句を使用すると、 UNIQUE
制約またはPRIMARY KEY
制約に対応する索引の記憶域オプションを設定できます。
次のCREATE TABLE
文は、主キー制約を使用可能にして、対応する索引の記憶域オプションを指定します。
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;
親トピック: 制約に対応付けられた索引の作成
21.3.4.3 制約に対応付けられた索引の指定
制約に対応付けられた索引の詳細を指定できます。
UNIQUE
制約およびPRIMARY KEY
制約に対応付けられた索引をより明示的に制御する場合、データベースでは次のことが可能です。
-
制約を規定するために使用する既存の索引の指定
-
索引の作成と制約の規定に使用する
CREATE
INDEX
文の指定
これらのオプションは、USING
INDEX
句を使用して指定します。次にいくつかの例を示します。
例1:
CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
Live SQL:
Oracle Live SQLの関連する例をOracle Live SQL: 索引の作成で参照して実行してください。
例2:
CREATE TABLE b( b1 INT, b2 INT, CONSTRAINT bu1 UNIQUE (b1, b2) USING INDEX (create unique index bi on b(b1, b2)), CONSTRAINT bu2 UNIQUE (b2, b1) USING INDEX bi);
例3:
CREATE TABLE c(c1 INT, c2 INT); CREATE INDEX ci ON c (c1, c2); ALTER TABLE c ADD CONSTRAINT cpk PRIMARY KEY (c1) USING INDEX ci;
単一の文で制約とともに索引を作成し、同じ文でその索引を別の制約にも使用する場合、Oracleでは、索引を再使用する前に索引を作成するための句の再編成を試みます。
関連項目:
親トピック: 制約に対応付けられた索引の作成
21.3.5 大きな索引の作成
非常に大きな索引を作成する場合は、索引の作成のために大きな一時表領域の割当てを検討してください。
これを行うには、次のステップを完了します。
CREATE TABLESPACE
文またはCREATE TEMPORARY TABLESPACE
文を使用して、新しい一時表領域を作成します。- これを自分の新しい一時表領域にするには、
ALTER USER
文のTEMPORARY TABLESPACE
オプションを使用します。 CREATE INDEX
文を使用して、索引を作成します。DROP TABLESPACE
文を使用して、この表領域を削除します。次にALTER USER
文を使用して、自分の一時表領域を元の一時表領域に再設定します。
この手順により、通常使用している一時表領域(ほとんどの場合、共有されている)が極度に肥大化して今後のパフォーマンスに影響を及ぼす問題を回避できます。
親トピック: 索引の作成
21.3.6 オンラインでの索引の作成
索引をオンラインで作成および再作成できます。そのため、実表の索引を作成または再作成する際に、同時に実表も更新できます。
索引の作成中にDML操作を実行できますが、DDL操作はできません。索引をオンラインで作成または再作成するとき、パラレルDMLはサポートされません。
次の文は、オンラインでの索引作成操作を示しています。
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
ノート:
オンライン索引ビルドが完了するまでの時間は、表のサイズおよび同時に実行しているDML文の数に比例することに注意してください。したがって、オンライン索引ビルドはDMLアクティビティが低いときに開始することをお薦めします。
Live SQL:
Oracle Live SQLの関連する例をOracle Live SQL: 索引の作成で参照して実行してください。
関連項目:
親トピック: 索引の作成
21.3.7 ファンクション索引の作成
ファンクション索引を使用すると、関数や式から返される値を修飾する問合せが可能です。関数や式の値は、事前に計算されて索引に格納されます。
ユーザー定義のファンクションに基づく索引の場合は、従来型の索引を作成するための前提条件に加えて、これらのファンクションにDETERMINISTIC
のマークが設定されている必要があります。また、ファンクションに基づく索引はファンクションの所有者の資格証明を使用して実行されるため、ファンクションに対するEXECUTE
オブジェクト権限がある必要があります。
ノート:
CREATE INDEX
では、ファンクション索引で最後に使用された関数のタイムスタンプが格納されます。このタイムスタンプは、索引の妥当性チェック時に更新されます。ファンクション索引について表領域のPoint-in-Timeリカバリを実行する場合、索引で最後に使用された関数のタイムスタンプが索引に格納されたタイムスタンプより新しい場合は、その索引には無効を示すマークが設定されます。ANALYZE INDEX...VALIDATE STRUCTURE
文を使用して、この索引の妥当性をチェックする必要があります。
ファンクション索引の具体例として、ファンクションarea(geo)
に定義されているファンクション索引(area_index
)を定義する次の文を示します。
CREATE INDEX area_index ON rivers (area(geo));
次のSQL文では、area(geo)
がWHERE
句で参照されているため、オプティマイザは索引area_index
の使用を考慮します。
SELECT id, geo, area(geo), desc FROM rivers WHERE Area(geo) >5000;
ファンクション索引は使用するファンクションに依存するため、ファンクションの変更時に無効にできます。ファンクションが有効な場合は、ALTER INDEX...ENABLE
文を使用して、使用禁止になっているファンクション索引を使用可能にすることができます。ALTER INDEX...DISABLE
文では、ファンクション索引を使用禁止にすることができます。ファンクションの本体で作業をする場合は、ファンクション索引を使用禁止にするか検討してください。
ノート:
ファンクション索引を作成するかわりに、仮想列をターゲット表に追加して仮想列に索引を付けることができます。詳細は、「表について」を参照してください。
関連項目:
-
ファンクション索引の詳細は、『Oracle Database概要』を参照してください。
-
アプリケーションでファンクション索引を使用する際の詳細と使用例については、『Oracle Database開発ガイド』を参照してください。
親トピック: 索引の作成
21.3.8 圧縮索引の作成
データベースが大きくなったら、ディスク領域を節約するために、索引圧縮の使用を検討してください。
- 接頭辞圧縮を使用した索引の作成
接頭辞圧縮(キー圧縮とも呼ばれます)を使用して索引を作成すると、キー列の接頭辞が同じ値で繰り返し格納されることが回避されます。接頭辞圧縮は、先頭列で数多くの重複が発生する非一意索引に対して最も有効です。 - 高度な索引圧縮を使用した索引の作成
高度な索引圧縮は、接頭辞圧縮の候補として適切でないものを含め、サポートされているすべての索引で適切に機能します。拡張索引圧縮を使用して索引を作成すると、索引への効率的なアクセスを提供しつつ、すべての一意の索引および一意ではない索引のサイズが減少し、圧縮率が著しく向上します。
親トピック: 索引の作成
21.3.8.1 接頭辞圧縮を使用した索引の作成
接頭辞圧縮(キー圧縮とも呼ばれます)を使用して索引を作成すると、キー列の接頭辞が同じ値で繰り返し格納されることが回避されます。接頭辞圧縮は、先頭列で数多くの重複が発生する非一意索引に対して最も有効です。
接頭辞圧縮によって、索引キーは接頭辞および接尾辞エントリに分割されます。圧縮するために、接頭辞エントリは索引ブロック内のすべての接尾辞エントリ間で共有されます。このような共有によって、領域が大幅に節約され、各索引ブロックに格納できるキー数が増え、パフォーマンスが向上します。
接頭辞圧縮は、次のような状況で役立ちます。
-
ROWID
を追加してキーを一意にしている非一意索引がある場合。このような状況で接頭辞圧縮を使用すると、重複キーは接頭辞エントリとして索引ブロックにROWID
なしで格納されます。残りの行は、ROWID
のみからなる接尾辞エントリとなります。 -
一意の複数列索引がある場合。
接頭辞圧縮を使用可能にするには、COMPRESS
句を使用します。また、接頭辞の長さをキー列の数で指定して、キー列が接頭辞および接尾辞エントリにどのように分割されるかを識別できます。たとえば、次の文は、索引リーフ・ブロックの重複するキーを圧縮します。
CREATE INDEX hr.emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;
また、再作成中に、COMPRESS
句を指定することもできます。たとえば、次のようにして、再作成中に圧縮を使用禁止にすることができます。
ALTER INDEX hr.emp_ename REBUILD NOCOMPRESS;
ALL_INDEXES
ビューおよびALL_PART_INDEXES
ビューのCOMPRESSION
列には、索引が圧縮されるかどうかが表示され、圧縮される場合は索引で使用可能な圧縮のタイプが表示されます。
Live SQL:
Oracle Live SQLの関連する例をOracle Live SQL: 索引の作成で参照して実行してください。
関連項目:
-
接頭辞圧縮の詳細は、『Oracle Database概要』を参照してください
親トピック: 圧縮索引の作成
21.3.8.2 拡張索引圧縮を使用した索引の作成
拡張索引圧縮は、接頭辞圧縮の適切な候補ではない索引を含むすべてのサポートされている索引で正常に動作します。拡張索引圧縮を使用して索引を作成すると、索引への効率的なアクセスを提供しつつ、すべての一意の索引および一意ではない索引のサイズが減少し、圧縮率が著しく向上します。
パーティション索引の場合は、パーティションごとにパーティションの圧縮タイプを指定できます。親索引が圧縮されていない場合でも、索引パーティションに対して拡張索引圧縮を指定できます。
拡張索引圧縮は、ブロック・レベルに作用し、各ブロックを最適に圧縮できます。
拡張索引圧縮を有効にするにはCOMPRESS ADVANCED
句を使用し、次の圧縮レベルを指定できます。
-
LOW
: このレベルは、最小のCPUオーバーヘッドで低い圧縮率を提供します。COMPRESS ADVANCED LOW
を有効化する前に、データベースの互換性レベルが12.1.0以上である必要があります。 -
HIGH
: このレベル(デフォルト)では、わずかなCPUオーバーヘッドでより高い圧縮率が提供されます。COMPRESS ADVANCED HIGH
を有効化する前に、データベースの互換性レベルが12.2.0以上である必要があります。CREATE INDEX
DDL文を実行すると、ブロックは行で満たされます。高圧縮レベルでブロックが一杯になると、次の行を挿入するための十分な領域が確保される場合、ブロックは拡張索引圧縮を使用して圧縮されます。ブロックが一杯なると、受信キーの挿入のための十分な領域が確保される場合、ブロックは拡張索引圧縮を使用して再圧縮され分割を回避します。
ALL_INDEXES
ビューのCOMPRESSION
列には、索引が圧縮されるかどうかが表示され、圧縮される場合は索引で使用可能な圧縮のタイプが表示されます。COMPRESSION
列に指定できる値は、ADVANCED HIGH
、ADVANCED LOW
、DISABLED
またはENABLED
です。ALL_IND_PARTITIONS
ビューおよびALL_IND_SUBPARTITIONS
ビューのCOMPRESSION
列は、パーティションまたはサブパーティションで索引圧縮がENABLED
またはDISABLED
のいずれであるかを示します。
ノート:
-
拡張索引圧縮は、ビットマップ索引または索引構成表ではサポートされていません。
-
低レベルの拡張索引圧縮を有効にした場合は、単一列の一意の索引に拡張索引圧縮を指定できません。高レベルの拡張索引圧縮が有効にされている場合、この制限は適用されません。
例21-1 索引作成時に低レベルの拡張索引圧縮を有効にする
たとえば、次の文を実行すると、hr.emp_mndp_ix
索引の作成時に低レベルの拡張索引圧縮が使用可能になります。
CREATE INDEX hr.emp_mndp_ix ON hr.employees(manager_id, department_id) COMPRESS ADVANCED LOW;
例21-2 索引の再作成時に高レベルの拡張索引圧縮を有効にする
また、索引の再作成中に、COMPRESS ADVANCED
句を指定することもできます。たとえば、次のようにして、再作成中にhr.emp_manager_ix
索引に対して高レベルの拡張索引圧縮を使用可能にできます。
ALTER INDEX hr.emp_manager_ix REBUILD COMPRESS ADVANCED HIGH;
親トピック: 圧縮索引の作成
21.3.9 使用禁止索引の作成
UNUSABLE
状態の索引を作成した場合、オプティマイザで無視され、DMLではメンテナンスされません。使用禁止の索引を使用可能にする場合、再構築するか、または削除して再作成する必要があります。
索引がパーティション化されている場合、索引パーティションはすべてUNUSABLE
としてマークされます。
データベースは使用禁止索引の作成時に索引セグメントを作成しません。
次の手順では、使用禁止の索引を作成する方法と、データベースに索引に関する詳細を問い合せる方法を示します。
使用禁止索引を作成するには:
-
必要に応じて、索引を付ける表を作成します。
たとえば、次のように、
hr.employees_part
という名前でハッシュ・パーティション表を作成します。sh@PROD> CONNECT hr Enter password: ** Connected. hr@PROD> CREATE TABLE employees_part 2 PARTITION BY HASH (employee_id) PARTITIONS 2 3 AS SELECT * FROM employees; Table created. hr@PROD> SELECT COUNT(*) FROM employees_part; COUNT(*) ---------- 107
-
キーワード
UNUSABLE
で索引を作成します。次の例では、
employees_part
にローカル・パーティション索引を作成し、索引パーティションにp1_i_emp_ename
およびp2_i_emp_ename
という名前を付け、p1_i_emp_ename
を使用禁止にしています。hr@PROD> CREATE INDEX i_emp_ename ON employees_part (employee_id) 2 LOCAL (PARTITION p1_i_emp_ename UNUSABLE, PARTITION p2_i_emp_ename); Index created.
-
(オプション)データ・ディクショナリに問い合せて索引が使用禁止になっていることを確認できます。
次の例では、索引
i_emp_ename
とその2つのパーティションのステータスを問い合せて、パーティションp2_i_emp_ename
のみが使用禁止になっていることを示しています。hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS 2 FROM USER_INDEXES 3 WHERE INDEX_NAME = 'I_EMP_ENAME' 4 UNION ALL 5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS 6 FROM USER_IND_PARTITIONS 7 WHERE PARTITION_NAME LIKE '%I_EMP_ENAME%'; INDEX OR PARTITION NAME STATUS ------------------------------ -------- I_EMP_ENAME N/A P1_I_EMP_ENAME UNUSABLE P2_I_EMP_ENAME USABLE
-
(オプション)データ・ディクショナリに問い合せて、パーティションの記憶域が存在するかどうかを確認できます。
たとえば、次の問合せは索引パーティション
p2_i_emp_ename
のみがセグメントを占有していることを示しています。p1_i_emp_ename
は使用禁止として作成したため、セグメントが割り当てられませんでした。hr@PROD> COL PARTITION_NAME FORMAT a14 hr@PROD> COL SEG_CREATED FORMAT a11 hr@PROD> SELECT p.PARTITION_NAME, p.STATUS AS "PART_STATUS", 2 p.SEGMENT_CREATED AS "SEG_CREATED", 3 FROM USER_IND_PARTITIONS p, USER_SEGMENTS s 4 WHERE s.SEGMENT_NAME = 'I_EMP_ENAME'; PARTITION_NAME PART_STA SEG_CREATED -------------- -------- ----------- P2_I_EMP_ENAME USABLE YES P1_I_EMP_ENAME UNUSABLE NO
関連項目:
-
使用禁止索引の作成方法および制限事項の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: 索引の作成
21.3.10 不可視索引の作成
不可視索引とは、セッションまたはシステム・レベルでOPTIMIZER_USE_INVISIBLE_INDEXES
初期化パラメータを明示的にTRUE
に設定しないかぎり、オプティマイザで無視される索引です。
不可視索引を作成するには:
-
CREATE INDEX
文をINVISIBLE
キーワードとともに指定します。次の文は、
emp
表のename
列に対してemp_ename
という名前の不可視索引を作成します。CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k) INVISIBLE;
関連項目:
-
不可視索引の作成の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: 索引の作成
21.3.11 同じ列セットに対する複数の索引の作成
同じ列セットに対して、一部が異なる複数の索引を作成できます。
同じ列セットに対して複数の索引を作成する場合は、次の前提条件が満たされている必要があります。
-
「索引の作成」に示されている、必要な権限の前提。
-
同じ列セットに対する索引のうち、任意の時点で可視にできるのは1つのみとなります。
可視の索引を作成する場合、その列セットに対するすべての既存の索引が不可視である必要があります。
かわりに、列セットに対して不可視索引を作成できます。
たとえば、次のステップを実行すると、oe.orders
表の同じ列セットに対して、Bツリー索引とビットマップ索引が作成されます。
親トピック: 索引の作成
21.4 索引の変更
索引は、記憶域の特性の変更、再作成、使用不能化、表示/非表示にするなどのタスクを完了することで変更できます。
- 索引の変更について
索引を変更するには、その索引が自分のスキーマに含まれているか、またはALTER ANY INDEX
システム権限を持っている必要があります。 - 索引の記憶域特性の変更
主キーと一意キーの整合性制約を規定するためにデータベースによって作成される索引も含めて、どの索引の記憶域パラメータも、ALTER INDEX
文を使用して変更できます。 - 既存の索引の再作成
索引を再作成するときは、既存の索引をデータソースとして使用できます。このようにして索引を作成すると、記憶特性の変更や新しい表領域への移動ができます。既存のデータソースに基づいて索引を再作成すると、ブロック内の断片化も解消されます。 - 索引の使用禁止化
索引を使用禁止にした場合、オプティマイザで無視され、DMLではメンテナンスされません。パーティション化された索引のパーティションのうちの1つを使用禁止にした場合、その索引の他のパーティションは有効なままです。 - 索引の不可視化または可視化
索引を非表示にするのは、使用禁止または削除の代替手段です。 - 索引の名前変更
RENAME
句を含むALTER INDEX
文を使用して、索引の名前を変更できます。 - 索引の使用状況の監視
Oracle Databaseには、索引を監視し、それらが使用されているかどうかを判別する手段が用意されています。未使用の索引がある場合は、それを削除して不要な文によるオーバーヘッドを解消できます。
親トピック: 索引の管理
21.4.1 索引の変更について
索引を変更するには、その索引が自分のスキーマに含まれているか、またはALTER ANY INDEX
システム権限を持っている必要があります。
ALTER INDEX
文では、次の操作を実行できます。
-
既存の索引の再作成または結合
-
未使用領域の割当て解除または新規エクステントの割当て
-
パラレル実行の指定(または指定解除)およびその並列度の変更
-
記憶域パラメータまたは物理属性の変更
-
LOGGING
またはNOLOGGING
の指定 -
接頭辞圧縮の使用可能または使用禁止の設定
-
拡張圧縮を有効化または無効化します
-
索引へのUNUSABLEマークの設定
-
索引の不可視化
-
索引の名前変更
-
索引使用状況の監視の開始または停止
索引の列構造は変更できません。
関連項目:
-
ALTER
INDEX
文の構文は、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: 索引の変更
21.4.2 索引の記憶域特性の変更
主キーと一意キーの整合性制約を規定するためにデータベースによって作成される索引も含めて、どの索引の記憶域パラメータも、ALTER INDEX
文を使用して変更できます。
たとえば、次の文はemp_ename
索引を変更します。
ALTER INDEX emp_ename STORAGE (NEXT 40);
パラメータINITIAL
とMINEXTENTS
は変更できません。他の記憶域パラメータの新しい設定はすべて、その後に索引に割り当てられるエクステントにのみ影響します。
整合性制約を実装する索引では、ENABLE
句のUSING INDEX
副次句を指定したALTER TABLE
文を発行することによって、記憶域パラメータを調整できます。たとえば、次の文は、表emp
に作成された索引の記憶域オプションを変更して、主キー制約を規定します。
ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;
関連項目:
ALTER INDEX
文の構文と制限事項については、『Oracle Database SQL言語リファレンス』
親トピック: 索引の変更
21.4.3 既存の索引の再作成
索引を再作成するときは、既存の索引をデータソースとして使用できます。このようにして索引を作成すると、記憶特性の変更や新しい表領域への移動ができます。既存のデータソースに基づいて索引を再作成すると、ブロック内の断片化も解消されます。
索引を削除してからCREATE INDEX
文を使用するよりも、既存の索引を再構築する方がパフォーマンスに優れています。既存の索引を再作成する前に、「索引の結合と再作成に関するコストと利点の検討」の説明に従って、索引を再作成する方法と結合する方法のコストと利点を比較してください。
次の文は、既存の索引emp_name
を再作成します。
ALTER INDEX emp_name REBUILD;
REBUILD
句は索引名の直後に使用し、他のオプションの前に置く必要があります。DEALLOCATE UNUSED
句とともに使用することはできません。
索引はオンラインで再作成できます。オンラインで再作成することにより、再作成と同時に実表を更新できます。次の文は、emp_name
索引をオンラインで再作成します。
ALTER INDEX emp_name REBUILD ONLINE;
別のユーザーのスキーマにオンラインで索引を再作成するには、ALTER ANY INDEX
システム権限が必要です。
関連項目:
親トピック: 索引の変更
21.4.4 索引の使用禁止化
索引を使用禁止にした場合、オプティマイザで無視され、DMLではメンテナンスされません。パーティション化された索引のパーティションのうちの1つを使用禁止にした場合、その索引の他のパーティションは有効なままです。
使用禁止の索引または索引パーティションを使用する場合は、事前に索引または索引パーティションを再作成するか、または削除した後再作成する必要があります。
次の手順では、索引および索引パーティションを使用禁止にする方法と、オブジェクトのステータスを問い合せる方法を示します。
索引を使用禁止にするには:
-
データ・ディクショナリに問い合せて、既存の索引または索引パーティションが使用可能であるか、使用禁止であるかを確認します。
たとえば、次の問合せを発行します(スペースの節約のため出力を切り捨てています)。
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED 2 FROM USER_INDEXES 3 UNION ALL 4 SELECT PARTITION_NAME AS "INDEX OR PART NAME", STATUS, SEGMENT_CREATED 5 FROM USER_IND_PARTITIONS; INDEX OR PART NAME STATUS SEG ------------------------------ -------- --- I_EMP_ENAME N/A N/A JHIST_EMP_ID_ST_DATE_PK VALID YES JHIST_JOB_IX VALID YES JHIST_EMPLOYEE_IX VALID YES JHIST_DEPARTMENT_IX VALID YES EMP_EMAIL_UK VALID NO . . . COUNTRY_C_ID_PK VALID YES REG_ID_PK VALID YES P2_I_EMP_ENAME USABLE YES P1_I_EMP_ENAME UNUSABLE NO 22 rows selected.
前述の出力は、索引パーティション
p1_i_emp_ename
のみが使用禁止であることを示しています。 -
索引または索引パーティションを使用禁止にするには、
UNUSABLE
キーワードを指定します。次の例では、索引
emp_email_uk
を使用禁止にしています。hr@PROD> ALTER INDEX emp_email_uk UNUSABLE; Index altered.
次の例では、索引パーティション
p2_i_emp_ename
を使用禁止にしています。hr@PROD> ALTER INDEX i_emp_ename MODIFY PARTITION p2_i_emp_ename UNUSABLE; Index altered.
-
(オプション)データ・ディクショナリに問い合せて、ステータス変更を確認できます。
たとえば、次の問合せを発行します(スペースの節約のため出力を切り捨てています)。
hr@PROD> SELECT INDEX_NAME AS "INDEX OR PARTITION NAME", STATUS, 2 SEGMENT_CREATED 3 FROM USER_INDEXES 4 UNION ALL 5 SELECT PARTITION_NAME AS "INDEX OR PARTITION NAME", STATUS, 6 SEGMENT_CREATED 7 FROM USER_IND_PARTITIONS; INDEX OR PARTITION NAME STATUS SEG ------------------------------ -------- --- I_EMP_ENAME N/A N/A JHIST_EMP_ID_ST_DATE_PK VALID YES JHIST_JOB_IX VALID YES JHIST_EMPLOYEE_IX VALID YES JHIST_DEPARTMENT_IX VALID YES EMP_EMAIL_UK UNUSABLE NO . . . COUNTRY_C_ID_PK VALID YES REG_ID_PK VALID YES P2_I_EMP_ENAME UNUSABLE NO P1_I_EMP_ENAME UNUSABLE NO 22 rows selected.
i_emp_ename
セグメントとemp_email_uk
セグメントが消費している領域を問い合せると、どちらのセグメントも存在しなくなったことが示されます。hr@PROD> SELECT SEGMENT_NAME, BYTES 2 FROM USER_SEGMENTS 3 WHERE SEGMENT_NAME IN ('I_EMP_ENAME', 'EMP_EMAIL_UK'); no rows selected
関連項目:
-
UNUSABLE
キーワードの詳細および制限については、『Oracle Database SQL言語リファレンス』を参照してください。
親トピック: 索引の変更
21.4.5 索引の不可視化または可視化
索引を非表示にするのは、使用禁止または削除の代替手段です。
不可視索引は、セッションまたはシステム・レベルでOPTIMIZER_USE_INVISIBLE_INDEXES
初期化パラメータを明示的にTRUE
に設定しないかぎり、オプティマイザで無視されます。個別の索引パーティションは不可視にできません。試みた場合はエラーとなります。
索引を不可視にするには:
-
次のSQL文を発行します。
ALTER INDEX index INVISIBLE;
不可視状態の索引を可視にするには:
-
次のSQL文を発行します。
ALTER INDEX index VISIBLE;
ノート:
同じ列セットに対して複数の索引がある場合、これらの索引のうち、任意の時点で可視にできるのは1つのみとなります。列セットに対する索引を可視にしようとしたとき、同じ列セットに対する別の索引が可視であると、エラーが返されます。
索引が可視か不可視かを判別するには:
-
USER_INDEXES
、ALL_INDEXES
またはDBA_INDEXES
ディクショナリ・ビューの問合せを行います。たとえば、索引
ind1
が不可視かどうかを判別するには、次の問合せを発行します。SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IND1'; INDEX_NAME VISIBILITY ---------- ---------- IND1 VISIBLE
親トピック: 索引の変更
21.4.6 索引の名前変更
RENAME
句を含むALTER INDEX
文を使用して、索引の名前を変更できます。
索引の名前を変更するには、次の文を発行します。
ALTER INDEX index_name RENAME TO new_name;
親トピック: 索引の変更
21.4.7 索引の使用状況の監視
Oracle Databaseには、索引を監視し、それらが使用されているかどうかを判別する手段が用意されています。未使用の索引がある場合は、それを削除して不要な文によるオーバーヘッドを解消できます。
索引の使用状況の監視を開始するには、次の文を発行します。
ALTER INDEX index MONITORING USAGE;
その後、監視を停止するには、次の文を発行します。
ALTER INDEX index NOMONITORING USAGE;
監視中の索引について、それが使用されているかどうかを確認するには、ビューUSER_OBJECT_USAGE
を問い合せます。このビューにはUSED
列があり、監視期間中に索引が使用されたかどうかによってYES
またはNO
の値を持ちます。また、このビューには監視の開始時間と停止時間も記録され、MONITORING
列(YES
/NO
)には使用状況の監視が現在アクティブであるかどうかが示されます。
MONITORING USAGE
を指定するたびに、指定した索引のUSER_OBJECT_USAGE
ビューはリセットされます。前回の使用方法の情報はクリアまたはリセットされ、新しい開始時間が記録されます。NOMONITORING USAGE
を指定すると、これ以上の監視は実行されず、監視期間の終了時間が記録されます。次にALTER INDEX...MONITORING USAGE
文が発行されるまで、ビューの情報は変更されずに保持されます。
親トピック: 索引の変更
21.5 索引の領域使用の監視
索引のキー値が頻繁に挿入、更新および削除されると、時間が経つにつれて領域の効率性が失われることがあります。
索引の領域の使用状況の効率性を定期的に監視するには、まずANALYZE INDEX...VALIDATE STRUCTURE
文を使用し、次にINDEX_STATS
ビューを問い合せて索引構造を分析します。
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
索引の領域使用の割合は、どれくらい頻繁に索引キーが挿入、更新または削除されるかによって変化します。次の一連の操作を何度か実行し、索引の平均的な領域使用効率の履歴を作成してください。
-
統計分析
-
索引の検証
-
PCT_USED
のチェック -
索引の削除および再作成(または結合)
索引の領域使用がその平均を下回っているときは、索引を削除してから再作成または結合することによって、索引の領域を圧縮できます。
関連項目:
親トピック: 索引の管理
21.6 索引の削除
索引は、DROP INDEX
文により削除できます。
索引を削除するには、その索引が自分のスキーマに含まれているか、またはDROP ANY INDEX
システム権限を持っている必要があります。
索引を削除するのは、次のような場合です。
-
索引が不要になった場合。
-
対応する表に対して発行した問合せで、索引が予想されたパフォーマンスの改善を達成していない場合。たとえば、表が非常に小さい、または表には多くの行があるものの、索引エントリが非常に少ないなどがこれに該当します。
-
アプリケーションに索引を使用するデータ問合せが含まれない場合。
-
索引が無効になり、再作成する前に削除する必要がある場合。
-
索引がかなり断片化し、再作成する前に削除する必要がある場合。
索引が削除されると、その索引のセグメントのエクステントはすべて、索引を含んでいる表領域に戻され、表領域内の他のオブジェクトで利用できます。
索引を削除する方法は、索引の作成方法、つまりCREATE INDEX
文によって索引を明示的に作成したか、または表にキー制約を定義することによって索引を暗黙的に作成したかによって異なります。CREATE INDEX
文を使用して明示的に作成した索引は、DROP INDEX
文で削除できます。次の文は、emp_ename
索引を削除します。
DROP INDEX emp_ename;
使用可能になっているUNIQUE
キー制約やPRIMARY KEY
制約に対応付けられた索引のみを削除することはできません。制約に対応付けられた索引を削除するには、制約自体を使用禁止にするかまたは削除します。
ノート:
表を削除すると、対応する索引はすべて自動的に削除されます。
関連項目:
-
DROP INDEX
文の構文と制限事項については、『Oracle Database SQL言語リファレンス』 -
索引の削除にかわる方法については、「索引の不可視化または可視化」
親トピック: 索引の管理
21.7 索引のデータ・ディクショナリ・ビュー
索引に関する情報について一連のデータ・ディクショナリ・ビューを問い合せることができます。
次のビューには、索引に関する情報が表示されます。
ビュー | 説明 |
---|---|
|
|
これらのビューには、表の索引の列が表示されます。これらのビューの一部の列には、 |
|
これらのビューには、各索引パーティションのパーティションレベルのパーティション化情報、パーティションの記憶域パラメータ、 |
|
これらのビューには、表のファンクション索引の式が表示されます。 |
|
これらのビューには、索引のオプティマイザ統計が含まれています。 |
|
最後に発行された |
|
最後に発行された |
|
|
親トピック: 索引の管理