11gリリース1（11.1）
E05760-03
 目次 |
 索引 |
| Oracle Database 管理者ガイド 11gリリース1（11.1） E05760-03 |
|
この章の内容は次のとおりです。
索引とは、表とクラスタに対応付けられるオプションの構造です。索引を使用することで、表に対してSQL問合せを高速に実行できます。このマニュアルの索引によって情報を素早く検索できるのと同じように、Oracle Databaseの索引は表データへの高速なアクセス・パスを提供します。索引を使用するために、問合せを書きなおす必要はありません。索引を使用しなくても処理結果は同じですが、索引を使用するとより短時間で結果が表示されます。
Oracle Databaseは、補完的なパフォーマンス機能を持つ複数の索引付け方法を提供します。次の方法があります。
索引は、対応付けられた表内のデータから論理的にも物理的にも独立しています。索引は独立した構造体であり、記憶域を必要とします。索引は、実表、データベース・アプリケーションまたはその他の索引に影響を与えることなく、作成または削除できます。索引に対応する表に対して行の挿入、更新および削除が発生すると、データベースは索引を自動的にメンテナンスします。索引を削除しても、すべてのアプリケーションは引き続き動作可能です。ただし、それまで索引が付けられていたデータへのアクセスが遅くなります。
ここでは、索引管理のガイドラインについて説明します。この項の内容は、次のとおりです。
SQL*Loaderまたはインポート・ユーティリティを使用して、表にデータを挿入またはロードすることがあります。表の索引作成は、データを挿入またはロードした後のほうが効率的です。データをロードする前に索引を1つ以上作成すると、行が挿入されるたびにすべての索引の更新が必要になります。
すでにデータが格納されている表に索引を作成するには、ソート領域が必要です。索引の作成ユーザーに割り当てられているメモリーから確保されるソート領域もあります。各ユーザーのソート領域の大きさは、初期化パラメータSORT_AREA_SIZEによって決まります。また、データベースでは、索引作成のときにのみユーザーの一時表領域に割り当てられる一時セグメントとの間でソート情報のスワップが行われます。
特定の条件下では、SQL*Loaderのダイレクト・パス・ロードを使用してデータを表にロードし、データがロードされたときに索引が作成されるようにすることも可能です。
索引を作成するかどうか判断する際は、次のガイドラインを参考にしてください。
列のタイプによっては、索引を付けるほうが望ましいものがあります。次のような特性を1つ以上持つ列には、索引の作成を検討してください。
WHERE COL_X > -9.99 * power(10,125)
この句は、次の句よりも適しています。
WHERE COL_X IS NOT NULL
これは、最初の句ではCOL_Xの索引を使用しているためです（COL_Xは数値列とします）。
次のような特性を持つ列は、索引付けには適していません。
LONG列およびLONG RAW列には索引を作成できません。
仮想列には、一意索引または非一意索引を作成できます。
CREATE INDEX文の列の順序は、問合せのパフォーマンスに影響を与えます。一般的には、最も頻繁に使用する列を最初に指定します。
たとえば、col1、col2およびcol3の各列にアクセスする問合せを高速にするために、列にまたがる単一の索引を作成すると、col1のみにアクセスする問合せ、またはcol1とcol2にアクセスする問合せが速くなります。しかし、col2のみにアクセスする問合せ、col3のみにアクセスする問合せ、およびcol2とcol3にアクセスする問合せは速くなりません。
表は、多数の索引を持つことができます。ただし、索引の数が多いほど、表を変更するときに発生するオーバーヘッドが増加します。特に、行を挿入したり削除したりするときは、その表の索引もすべて更新する必要があります。また、列を更新するときには、その列を含む索引もすべて更新する必要があります。
このように、表からデータを検索する速度とその表を更新する速度は二律背反的です。たとえば、表が主に読取り専用である場合、索引を増やすと有効ですが、表が頻繁に更新される場合は、索引を少なくすることをお薦めします。
次のような状況では、索引の削除を検討してください。
索引を作成する前にそのサイズを見積っておくと、ディスク領域の計画と管理がいっそう容易になります。索引の見積りサイズの合計と、表、UNDO表領域およびREDOログ・ファイルの見積りを使用して、作成するデータベースを格納するために必要なディスク容量を決定できます。この見積りを利用して適切なハードウェアを購入できます。
個々の索引の見積りサイズを使用することで、索引が使用するディスク領域をより適切に管理できます。索引を作成するときに、適切な記憶域パラメータを設定し、その索引を使用するアプリケーションのI/Oパフォーマンスを改善できます。たとえば、索引を作成する前に索引の最大サイズを見積る場合を想定します。索引を作成するときに記憶域パラメータを設定すると、その表のデータ・セグメントに割り当てるエクステントを少なくできます。そのため、索引データすべてが比較的ディスク領域の連続した部分に格納されます。これによって、この索引に関係したディスクI/O操作に要する時間が短くなります。
単一の索引エントリの最大サイズは、データ・ブロック・サイズのおよそ2分の1です。
索引を主キー制約または一意キー制約を規定するために使用する場合、その索引のために作成する索引セグメントの記憶域パラメータは、次のどちらかの方法で設定できます。
索引はどの表領域にも作成できます。索引は、その索引を付けた表と同じ表領域にも、異なる表領域にも作成できます。表とその索引に対して同じ表領域を使用すると、（表領域やファイルのバックアップなどの）データベースのメンテナンスやアプリケーションの可用性確保の面で便利です。これは、すべての関連するデータが常にまとまってオンラインになっているためです。
表とその索引に対して（異なるディスク上にある）異なる表領域を使用すると、表と索引を同じ表領域に格納するよりも、パフォーマンスが向上します。これは、ディスクの競合が解消されるためです。表とその索引に対して異なる表領域を使用し、一方の（データまたは索引のいずれかを含む）表領域がオフラインになっている場合には、その表を参照している文が動作する保証はありません。
表作成をパラレル化できるのと同様に、索引の作成もパラレル化できます。複数のプロセスが同時に動作して索引を作成するため、1つのサーバー・プロセスが順に索引を作成する場合よりも高速に索引を作成できます。
索引を並行して作成する場合、問合せサーバー・プロセスごとに別々の記憶域パラメータが使用されます。したがって、INITIAL値を5MB、並行度を12で索引を作成する場合は、作成時に60MB以上の記憶域を使用します。
CREATE INDEX文でNOLOGGINGを指定すると、索引の作成時に最小限のREDOログ・レコードしか生成されません。
NOLOGGINGを使用して索引を作成すると、次のような利点があります。
一般に、LOGGINGを指定しないで索引を作成した場合、小規模な索引より大規模な索引のほうが相対的にパフォーマンスの向上が大きくなります。小規模な索引をLOGGINGを指定しないで作成しても、索引作成に要する時間にはほとんど影響しません。一方、大規模な索引では、特に索引作成のパラレル化もあわせて指定したときに、パフォーマンスが著しく向上します。
不適切な索引サイズの設定やサイズの拡大によって、索引の断片化が生じることがあります。断片化を解消または低減するには、索引を再作成するか、索引を結合します。ただし、どちらの作業を行う場合も、事前に各選択肢のコストと利点を分析し、状況に最も有効な方法を選択してください。表19-1は、索引を再作成する場合と結合する場合のコストと利点を示しています。
再利用のために解放できるBツリー索引のリーフ・ブロックがある場合は、次の文を使用してそのようなリーフ・ブロックをマージできます。
ALTER INDEX vmoore COALESCE;
図19-1は、索引vmooreに対するALTER INDEX COALESCEの効果を示しています。処理を実行する前は、最初の2つのリーフ・ブロックが半分満たされています。これは、断片化を低減できる余地があることを示しています。つまり、1番目のブロックをいっぱいにして、2番目のブロックを空にすることができます。
一意キーと主キーには対応する索引があるため、一意キー制約や主キー制約を使用禁止または削除するかどうかを検討するときには、索引の削除と作成にかかわるコストを分析してください。また、一意キー制約や主キー制約に対する索引が大きい場合には、その索引を削除して再作成するよりも、その制約を使用可能な状態のままにするほうが時間を節約できます。一意キー制約や主キー制約を削除または使用禁止にするときには、索引を保持するか削除するかを明示的に指定することもできます。
ここでは、索引の作成方法について説明します。自分のスキーマに索引を作成するには、次の条件の少なくとも1つが成り立つことが必要です。
自分のスキーマ以外のスキーマに索引を作成するには、次の条件がすべて成り立つことが必要です。
CREATE ANY INDEXシステム権限を持っている。
UNLIMITED TABLESPACEシステム権限を持っている。
この項の内容は、次のとおりです。
SQL文CREATE INDEXを使用して、索引を明示的に（整合性制約の他に）作成できます。次の文は、emp表のename列に対してemp_enameという名前の索引を作成します。
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75);
索引に対して、複数の記憶域設定および1つの表領域が明示的に指定されています。索引に記憶域オプション（INITIALやNEXTなど）を指定しない場合、デフォルトの表領域または指定された表領域のデフォルトの記憶域オプションが自動的に使用されます。
索引は、一意にすることも、重複を許可することもできます。一意索引を使用すると、表の複数行のキー列に重複した値が入らないことが保証されます。非一意索引では、列の値にこのような制限はありません。
一意索引を作成するには、CREATE UNIQUE INDEX文を使用します。次の例では、一意索引を作成しています。
CREATE UNIQUE INDEX dept_unique_index ON dept (dname) TABLESPACE indx;
別の方法として、目的の列に一意整合性制約を定義することもできます。データベースでは、一意のキーに対して自動的に一意索引を定義することによって、一意整合性制約を規定します。この操作については、次の項を参照してください。ただし、問合せのパフォーマンス向上のために必要な索引は、一意索引も含め、明示的に作成することをお薦めします。
Oracle Databaseは、表に一意キー整合性制約または主キー整合性制約を規定するために、一意キーまたは主キーの一意索引を作成します。この索引は、制約を使用可能にしたときに、データベースによって自動的に作成されます。CREATE TABLE文またはALTER TABLE文を発行して索引を作成する場合は、それ以外に必要なアクションはありませんが、必要に応じて、USING INDEX句を指定して索引作成を制御することができます。これは、制約を定義して使用可能にする場合、および定義したが使用禁止にしていた制約を使用可能にする場合のどちらでも可能です。
一意キー制約または主キー制約を使用可能にし、対応する索引を作成するには、表の所有者が索引を格納する表領域の割当て制限またはUNLIMITED TABLESPACEシステム権限を持っている必要があります。特に指定しないかぎり、制約に対応付けられた索引には、常に制約と同じ名前が付けられます。
USING INDEX句を使用すると、一意キー制約または主キー制約に対応する索引の記憶域オプションを設定できます。次のCREATE TABLE文は、主キー制約を使用可能にして、対応する索引の記憶域オプションを指定します。
CREATE TABLE emp ( empno NUMBER(5) PRIMARY KEY, age INTEGER) ENABLE PRIMARY KEY USING INDEX TABLESPACE users;
一意キー制約および主キー制約に対応付けられた索引をより明示的に制御する場合、データベースでは次のことが可能です。
これらのオプションは、USING INDEX句を使用して指定します。次にいくつかの例を示します。
例1:
CREATE TABLE a ( a1 INT PRIMARY KEY USING INDEX (create index ai on a (a1)));
例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では、索引を再使用する前に索引を作成するための句の再編成を試みます。
Oracle Databaseでは、索引の作成または再作成の際、リソース・コストをほとんど使用せずに統計を収集できます。これらの統計はデータ・ディクショナリに格納され、SQL文の実行計画を選択する際にオプティマイザによって常時使用されます。次の文では、表empの列enameに対して索引emp_enameを作成すると同時に、索引、表および列の各統計を計算します。
CREATE INDEX emp_ename ON emp(ename) COMPUTE STATISTICS;
極端に大きい索引を作成するときは、次の手順を使用して、索引の作成に大きな一時表領域を割り当てることを検討してください。
CREATE TABLESPACE文またはCREATE TEMPORARY TABLESPACE文を使用して、新しい一時表領域を作成します。
ALTER USER文でTEMPORARY TABLESPACEオプションを指定して、この一時表領域を自分の新しい一時表領域として割り当てます。
CREATE INDEX文を使用して、索引を作成します。
DROP TABLESPACE文を使用して、この表領域を削除します。次にALTER USER文を使用して、自分の一時表領域を元の一時表領域に再設定します。
この手順により、通常使用している一時表領域（ほとんどの場合、共有されている）が極度に肥大化して今後のパフォーマンスに影響を及ぼす問題を回避できます。
索引はオンラインで作成または再作成できます。これにより、実表に索引を作成または再作成しながら、同じ実表を更新できます。索引の作成中でもデータ操作言語（DML）操作が実行できます。しかし、データ定義言語（DDL）操作は実行できません。また、索引の作成中または再作成中のパラレル実行はサポートされていません。
次の文は、オンラインでの索引作成操作を示しています。
CREATE INDEX emp_name ON emp (mgr, emp1, emp2, emp3) ONLINE;
ファンクション索引を使用すると、関数や式から返される値を修飾する問合せが可能です。関数や式の値は、事前に計算されて索引に格納されます。
ユーザー定義のファンクションに基づく索引の場合は、従来型の索引を作成するための前提条件に加えて、これらのファンクションにDETERMINISTICのマークが設定されている必要があります。さらに、これらのファンクションを別のユーザーが所有している場合は、ファンクション索引で使用するすべてのユーザー定義ファンクションに対するEXECUTEオブジェクト権限を持っている必要があります。
また、ファンクション索引を使用するには、次のことを行います。
ファンクション索引の具体例として、ファンクション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;
表の所有者は、ファンクション索引で使用されるファンクションに対してEXECUTE権限を持つ必要があります。
ファンクション索引は使用するファンクションに依存するため、ファンクションの変更時に無効にできます。ファンクションが有効な場合は、ALTER INDEX...ENABLE文を使用して、使用禁止になっているファンクション索引を使用可能にすることができます。ALTER INDEX...DISABLE文では、ファンクション索引を使用禁止にすることができます。ファンクションの本体で作業をする場合は、ファンクション索引を使用禁止にするか検討してください。
キー圧縮を使用して索引を作成すると、キー列の接頭辞が同じ値で繰り返し格納されることを回避できます。
キー圧縮によって、索引キーは接頭辞および接尾辞エントリに分割されます。圧縮するために、接頭辞エントリは索引ブロック内のすべての接尾辞エントリ間で共有されます。このような共有によって、領域が大幅に節約され、各索引ブロックに格納できるキー数が増え、パフォーマンスが向上します。
キー圧縮は、次のような状況で役立ちます。
ROWIDを追加してキーを一意にしている非一意索引がある場合。このような状況でキー圧縮を使用すると、重複キーは接頭辞エントリとして索引ブロックにROWIDなしで格納されます。残りの行は、ROWIDのみからなる接尾辞エントリとなります。
キー圧縮を使用可能にするには、COMPRESS句を使用します。また、接頭辞の長さをキー列の数で指定できます。これにより、キー列が接頭辞および接尾辞エントリにどのように分割されるかが決まります。たとえば、次の文は、索引リーフ・ブロック内のキーの重複発生を圧縮します。
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users COMPRESS 1;
索引の再作成中にCOMPRESS句を指定することもできます。たとえば、次のようにして、再作成中に圧縮を使用禁止にすることができます。
ALTER INDEX emp_ename REBUILD NOCOMPRESS;
リリース11gからは、不可視索引を作成できます。不可視索引とは、セッションまたはシステム・レベルでOPTIMIZER_USE_INVISIBLE_INDEXES初期化パラメータを明示的にTRUEに設定しないかぎり、オプティマイザで無視される索引です。索引を使用禁止にしたり削除するかわりに、索引を不可視にできます。不可視索引を使用すると、次の操作を実行できます。
使用禁止状態の索引とは異なり、不可視索引はDML文が終了するまで保持されます。
不可視索引を作成するには、SQL文CREATE INDEXでINVISIBLE句を指定します。次の文は、emp表のename列に対してemp_enameという名前の不可視索引を作成します。
CREATE INDEX emp_ename ON emp(ename) TABLESPACE users STORAGE (INITIAL 20K NEXT 20k PCTINCREASE 75) INVISIBLE;
|
関連項目:
|
索引を変更するには、その索引が自分のスキーマに含まれているか、またはALTER ANY INDEXシステム権限を持っている必要があります。ALTER INDEX文では、次の操作を実行できます。
LOGGINGまたはNOLOGGINGの指定
索引の列構造は変更できません。
これらの操作のいくつかについて、次の項で詳しく説明します。
主キーと一意キーの整合性制約を規定するためにデータベースによって作成される索引も含めて、どの索引の記憶域パラメータも、ALTER INDEX文を使用して変更できます。たとえば、次の文はemp_ename索引を変更します。
ALTER INDEX emp_ename STORAGE (PCTINCREASE 50);
記憶域パラメータINITIALとMINEXTENTSは変更できません。他の記憶域パラメータの新しい設定はすべて、その後に索引に割り当てられるエクステントにのみ影響します。
整合性制約を実装する索引では、ENABLE句のUSING INDEX副次句を指定したALTER TABLE文を発行することによって、記憶域パラメータを調整できます。たとえば、次の文は、表empに作成された索引の記憶域オプションを変更して、主キー制約を規定します。
ALTER TABLE emp ENABLE PRIMARY KEY USING INDEX;
既存の索引を再作成する前に、表19-1の説明に従って、索引を再作成する方法と結合する方法のコストと利点を比較してください。
索引を再作成するときは、既存の索引をデータソースとして使用できます。このようにして索引を作成すると、記憶特性の変更や新しい表領域への移動ができます。既存のデータソースに基づいて索引を再作成すると、ブロック内の断片化も解消されます。索引を削除してCREATE INDEX文を使用するよりも、既存の索引を再作成した方がパフォーマンスは優れています。
次の文は、既存の索引emp_nameを再作成します。
ALTER INDEX emp_name REBUILD;
REBUILD句は、索引名の直後で他のオプションより前に指定する必要があります。この句をDEALLOCATE UNUSED句と組み合せて使用することはできません。
索引はオンラインで再作成できます。オンラインで再作成することにより、再作成と同時に実表を更新できます。次の文は、emp_name索引をオンラインで再作成します。
ALTER INDEX emp_name REBUILD ONLINE;
索引の再作成に必要な大きさの領域がない場合、かわりに索引を結合する方法が使用できます。索引の結合はオンライン操作です。
可視索引を不可視にするには、次の文を発行します。
ALTER INDEX index INVISIBLE;
不可視索引を可視にするには、次の文を発行します。
ALTER INDEX index VISIBLE;
索引が可視か不可視かを調べるには、ディクショナリ・ビューUSER_INDEXES、ALL_INDEXESまたはDBA_INDEXESを問い合せます。たとえば、索引IND1が不可視かどうかを調べるには、次の問合せを発行します。
SELECT INDEX_NAME, VISIBILITY FROM USER_INDEXES WHERE INDEX_NAME = 'IND1'; INDEX_NAME VISIBILITY ---------- ---------- IND1 VISIBLE
索引の名前を変更するには、次の文を発行します。
ALTER INDEX index_name RENAME TO new_name;
Oracle Databaseには、索引を監視し、それらが使用されているかどうかを判断する手段が用意されています。未使用の索引がある場合は、それを削除して不要な文によるオーバーヘッドを解消できます。
索引の使用状況の監視を開始するには、次の文を発行します。
ALTER INDEX index MONITORING USAGE;
その後、監視を停止するには、次の文を発行します。
ALTER INDEX index NOMONITORING USAGE;
監視中の索引について、それが使用されているかどうか確認するには、ビューV$OBJECT_USAGEを問い合せます。このビューにはUSED列があり、監視期間中に索引が使用されたかどうかによってYESまたはNOの値を持ちます。また、このビューには監視の開始時間と停止時間も記録され、MONITORING列（YES/NO）には使用状況の監視が現在アクティブであるかどうかが示されます。
MONITORING USAGEを指定するたびに、指定した索引のV$OBJECT_USAGEビューはリセットされます。前回の使用方法の情報はクリアまたはリセットされ、新しい開始時間が記録されます。NOMONITORING USAGEを指定すると、これ以上の監視は実行されず、監視期間の終了時間が記録されます。次にALTER INDEX...MONITORING USAGE文が発行されるまで、ビューの情報は変更されずに保持されます。
索引のキー値を頻繁に挿入、更新および削除していると、索引がその取得した領域を効果的に使用しなくなる可能性があります。そこで、定期的な間隔で索引の領域使用の効率を監視します。まず、ANALYZE INDEX...VALIDATE STRUCTURE文を使用して索引の構造を分析した後、次のようにINDEX_STATSビューを問い合せます。
SELECT PCT_USED FROM INDEX_STATS WHERE NAME = 'index';
索引の領域使用の割合は、どれくらい頻繁に索引キーが挿入、更新または削除されるかによって変化します。次の一連の操作を何度か実行し、索引の平均的な領域使用効率の履歴を作成してください。
索引の領域使用がその平均を下回っているときは、索引を削除してから再作成または結合することによって、索引の領域を圧縮できます。
索引を削除するには、その索引が自分のスキーマに含まれているか、またはDROP ANY INDEXシステム権限を持っている必要があります。
索引を削除するのは、次のような場合です。
索引が削除されると、その索引のセグメントのエクステントはすべて、索引を含んでいる表領域に戻され、表領域内の他のオブジェクトで利用できます。
索引を削除する方法は、索引の作成方法、つまりCREATE INDEX文によって索引を明示的に作成したか、または表にキー制約を定義することによって索引を暗黙的に作成したかによって異なります。CREATE INDEX文を使用して明示的に作成した索引は、DROP INDEX文で削除できます。次の文は、emp_ename索引を削除します。
DROP INDEX emp_ename;
使用可能になっている一意キー制約や主キー制約に対応付けられた索引のみを削除することはできません。制約に対応付けられた索引を削除するには、制約自体を使用禁止にするかまたは削除します。
|
関連項目:
|
次のビューには、索引に関する情報が表示されます。
