| Oracle Databaseアドバンスト・アプリケーション開発者ガイド 11gリリース1（11.1） E05687-02 |
|
 戻る |
 次へ |
この章では、データベース・アプリケーションで索引を使用する方法について説明します。
内容は次のとおりです。
|
関連項目:
|
アプリケーションで索引を使用する場合、DBAに、権限の付与または初期化パラメータの変更を要求する必要がある場合があります。
新しい索引を作成するには、対応する表を所有するか、またはその表に対するINDEXオブジェクト権限が必要です。また、索引を含むスキーマは、索引を含む予定の表領域に対する割当て制限、またはUNLIMITED TABLESPACEシステム権限が必要です。別のユーザーのスキーマに索引を作成するには、CREATE ANY INDEXシステム権限が必要です。
列に索引を作成して、問合せを高速化できます。索引を使用すると、表の行全体のわずかな部分を戻す操作でのデータ・アクセスが、より高速になります。
通常、次のような状況の場合、列に索引を作成します。
列を頻繁に問い合せる場合
列に参照制約がある場合
列に一意キー制約がある場合
索引はどの列にも作成できますが、列が前述のような状況で使用されていない場合は、索引を作成してもパフォーマンスは改善されず、リソースが無駄に占有されます。
データベースでは制約を使用した列に索引を作成しますが、このような列には索引を明示的に作成することをお薦めします。
次の方法を使用して、索引付けに最適な列を判断できます。
問合せ文に対する理論的な実行計画を表示するには、EXPLAIN PLAN機能を使用します。
問合せ文に使用される実際の実行計画を判断するには、動的パフォーマンス・ビューV$SQL_PLANを使用します。
索引がデフォルトで使用されていないときに、その索引を使用するのがより効率的な場合は、問合せヒントを使用して、索引を使用するようにできます。
次の項では、SQL文を使用して索引を作成、変更および削除する方法を説明し、索引を管理するためのガイドラインを示します。
|
関連項目:
|
内容は次のとおりです。
索引は、通常、データが表に挿入された後、または（SQL*Loaderまたはインポートによって）ロードされた後で作成します。そうしないと、索引更新のオーバーヘッドによって、挿入操作またはロード操作に時間がかかります。この規則の例外として、クラスタにデータを挿入するときは、事前にそのクラスタに索引を作成しておく必要があります。
すでにデータを持っている表に対して索引を作成する場合、Oracle Databaseはソート領域を使用します。データベースは、索引の作成者に対して割り当てられたメモリー内のソート領域（ユーザー当たりの容量はSORT_AREA_SIZE初期化パラメータによって決まります）を使用しますが、さらに索引作成のために割り当てられた一時セグメントとの間で、ソート情報をスワップする必要があります。索引が非常に大きい場合、次の手順を完了すると効果があることがあります。
CREATE TABLESPACE文を使用して新しい一時表領域を作成します。
ALTER USER文のTEMPORARY TABLESPACEオプションを使用して、この表領域を自分の新しい一時表領域にします。
CREATE INDEX文を使用して索引を作成します。
DROP TABLESPACE文を使用してこの表領域を削除します。ALTER USER文を使用して自分の一時表領域を元の一時表領域にリセットします。
条件によっては、SQL*Loaderのダイレクト・パス・ロードを使用してデータを表にロードし、データをロードしながら索引を作成できます。
|
関連項目: ダイレクト・パス・ロードの詳細は、『Oracle Databaseユーティリティ』を参照してください。 |
どのような場合に索引を作成するかは、次のガイドラインを使用して判断してください。
大きな表で頻繁に検索される行の割合が約15%未満の場合は索引を作成してください。ただし、このしきい値の割合は、表スキャンの相対速度、および索引キーに対してクラスタ化された行データの量によって大きく異なります。表スキャンが高速であるほど割合は低くなり、クラスタ化されている行データが多いほど割合は高くなります。
パフォーマンスを改善するために、結合に使用される列に索引を付けてください。
主キーおよび一意キーは自動的に索引を持ちますが、外部キーにも索引を作成する必要がある場合があります。詳細は、第6章「アプリケーション開発におけるデータ整合性のメンテナンス」を参照してください。
小さな表には索引は必要ありません。問合せにかなり時間がかかるときには、表が大きくなっていることがあります。
列の中には、索引付けの候補があります。次の特長を1つでも持つ列は、索引付けの候補列となります。
列の値が一意であるか、若干の重複がある。
値の範囲が広い（通常の索引に適している）。
値の範囲が狭い（ビットマップ索引に適している）。
列には多くのNULLが含まれるが、問合せで、値を持つすべての行が選択されることがよくある。この場合、次のようなすべての非NULL値と一致する比較は、
WHERE COL_X >= -9.99 *power(10,125)
次の方が適切です。
WHERE COL_X IS NOT NULL
これは、最初の句がCOL_Xに対する索引を使用するためです（COL_Xは数値列であると想定）。
次の特長を持つ列は、索引を付ける対象として適していません。
列の中に多くのNULLがあり、非NULL値は検索されない。
LONG列およびLONG RAW列は、索引付けできません。
単一の索引エントリのサイズは、データ・ブロック内の使用可能領域の約2分の1（さらにオーバーヘッドを差し引いたもの）を超えることはできません。データベース管理者に相談して、索引に必要な領域を判断してください。
索引が多いほど、表を変更するときに多くのオーバーヘッドが発生します。行が挿入または削除されるとき、その表のすべての索引も更新される必要があります。列が更新されるときには、その列に対するすべての索引も更新される必要があります。
索引による問合せパフォーマンスの向上と、更新によるパフォーマンス・オーバーヘッドを比較する必要があります。たとえば、表が主に読込み専用の場合は索引数を増やすと有効ですが、表が頻繁に更新される場合は索引数を減らす方が有効です。
CREATE INDEX文には、任意の順序で列を指定できますが、CREATE INDEX文における列の順序は、問合せのパフォーマンスに影響する可能性があります。一般に、最も使用頻度が高いと予想される列を索引の先頭に置いてください。複合索引は、複数の列を使用して作成できます。これらすべての列またはその一部を参照する問合せには、同じ複合索引を使用できます。
たとえば、例5-1に示すようなVENDOR_PARTS表の列を想定します。
例5-1 VENDOR_PARTS表
SQL> SELECT * FROM vendor_parts 2 ORDER BY vendor_id; VENDOR_ID PART_NO UNIT_COST --------- ------- ---------- 1010 10440 .27 1010 457 5.12 1012 457 4.95 1012 8300 1.19 1012 10441 .39 1012 10440 .25 1220 8300 1.33 1292 457 5.28 8 rows selected. SQL>
ベンダーは5社、各ベンダーはおよそ1000個の部品を持っていると想定しています。
VENDOR_PARTS表は、次のSQL文によって問い合せるとします。
SQL> SELECT * FROM vendor_parts 2 WHERE part_no = 457 AND vendor_id = 1012 3 ORDER BY vendor_id; VENDOR_ID PART_NO UNIT_COST --------- ------- ---------- 1012 457 4.95 1 row selected. SQL>
このような問合せのパフォーマンスを向上させるには、次のように最も選択頻度の高い列（最も多くの値を持つ列）を先頭にした複合索引を作成します。
SQL> CREATE INDEX ind_vendor_id 2 ON vendor_parts (part_no, vendor_id); Index created. SQL>
複合索引は、索引の先頭部分を使用する問合せの検索速度を向上させます。したがって、この例では、PART_NO列のみを使用するWHERE句が指定された問合せでもパフォーマンスは向上します。個別値が5つのみのため、VENDOR_IDに単独で索引を指定しても有効ではありません。
データベースは、問合せに関係する表についての統計情報があると、索引をより有効に使用できます。ユーザーまたはDBAは、DBMS_STATS.GATHER_TABLE_STATISTICS、DBMS_STATS.GATHER_SCHEMA_STATISTICSなどのプロシージャを起動して、定期的に統計情報を収集できます。このようなプロシージャの詳細は、『Oracle Database PL/SQLパッケージ・プロシージャおよびタイプ・リファレンス』を参照してください。
問合せを高速化しない。表が非常に小さいか、または表に数多くの行があっても索引エントリが非常に少ない。
アプリケーションに、索引を使用する問合せが含まれていない。
索引が使用されているかどうかを調べるため、これを監視できます。索引が未使用である、使用頻度が少ない、または効果がないと思われる方法で使用されている場合は、ただちに削除するか、不要であることが明確になるまでは非表示とし、明確になった時点で削除できます。非表示の索引が必要であると判明した場合には、再度表示できます。
索引が削除されると、その索引のセグメントのすべてのエクステントは、索引を含む表領域に戻され、表領域内の他のオブジェクトに対して使用可能になります。
索引を削除するには、SQL文DROP INDEXを使用します。たとえば、索引Emp_nameを削除するには次の文を入力します。
DROP INDEX Emp_ename;
表が削除されると、対応付けられていたすべての索引は削除されます。
索引を削除するには、その索引が自スキーマに含まれているか、またはDROP ANY INDEXシステム権限が必要です。
|
関連項目:
|
表に対する索引を作成して、対応する表に対して発行される問合せのパフォーマンスを改善できます。また、クラスタに対して索引を作成することもできます。最大32列までの複数列に対して複合索引を作成できます。複合索引キーは、データ・ブロック内の使用可能領域の約2分の1（さらにオーバーヘッドを差し引いたもの）を超えることはできません。
Oracle Databaseは、一意キー制約または主キー制約を施行するために、索引を自動的に作成します。一般に、一意性を施行するには、このような制約を作成する方が、廃止されたCREATE UNIQUE INDEX構文を使用するより有効です。
索引を作成するには、SQL文CREATE INDEXを使用します。
例5-2 索引の作成
SQL> /* Create index for single column, SQL> to speed up queries that test that column: */ SQL> SQL> CREATE INDEX emp_phone ON EMPLOYEES(PHONE_NUMBER); Index created. SQL> SQL> /* Create index for single column, SQL> specifying some physical attributes for index: */ SQL> SQL> CREATE INDEX emp_lastname ON EMPLOYEES(LAST_NAME) 2 STORAGE (INITIAL 20K 3 NEXT 20k 4 PCTINCREASE 75) 5 PCTFREE 0; Index created. SQL> SQL> /* Create index for two columns, SQL> to speed up queries that test either first column or both columns: */ SQL> SQL> CREATE INDEX emp_id_email ON EMPLOYEES(EMPLOYEE_ID, EMAIL); Index created. SQL> SQL> /* For query that sorts on UPPER(LASTNAME), SQL> index on LAST_NAME column does not speed up operation, SQL> and it might be slow to invoke function for each result row. SQL> Create function-based index SQL> that precomputes the result of the function for each column value, SQL> speeding up queries that use the function for searching or sorting: */ SQL> SQL> CREATE INDEX emp_upper_lastname ON EMPLOYEES(UPPER(LAST_NAME)); Index created. SQL>
ドメイン索引は、データ・カートリッジを使用して実装された、特殊な目的を持つアプリケーションに適しています。ドメイン索引は、空間データ、オーディオ・データ、ビデオ・データなどの複雑なデータの操作に有効です。そのようなアプリケーションを開発する必要がある場合は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
Oracle Databaseは、このような複雑なデータの管理に有効な多くの特殊なデータ・カートリッジを提供しています。検索エンジンまたは地理情報システムを作成する必要がある場合に、適切な種類の索引を作成するのみで、ほとんどの作業を行えます。
ファンクション索引とは、式に対して作成される索引です。これによって、列のみの場合より索引機能が拡張されます。ファンクション索引により、データ・アクセスの方法が多様化します。
|
注意:
|
ファンクション索引で索引付けされた式は、算術式、あるいはPL/SQLファンクション、パッケージ・ファンクション、CコールアウトまたはSQL関数を含む式のいずれかです。ファンクション索引は、照合キーに基づく言語ソート、SQL文の効率的な言語依存照合、および大/小文字を区別しないソートもサポートします。
他の索引と同様に、ファンクション索引も問合せのパフォーマンスを改善します。たとえば、複雑な計算式に頻繁にアクセスする必要がある場合は、式を索引内に格納しておくことができます。こうしておくと、その式にアクセスする必要があるときには、式はすでに計算済です。ファンクション索引のメリットについては、「ファンクション索引のメリット」を参照してください。
ファンクション索引には、列に対する索引と同じすべてのプロパティがあります。列に対する索引はコストベース最適化とルールベース最適化の両方で使用できますが、ファンクション索引を使用できるのはコストベース最適化のみです。ファンクション索引に関するその他の制限については、「ファンクション索引の制限」を参照してください。
|
関連項目:
|
内容は次のとおりです。
オプティマイザが全表スキャンのかわりにレンジ・スキャンを実行できる機会が増加します（例5-3）。
述語によって大きな表の15%未満の行が選択された場合、レンジ・スキャンでは、通常、応答時間が短くなります。式がファンクション索引に実体化されていると、オプティマイザは式で選択される行数をより正確に見積もることができます。（ファンクション索引の式が仮想列として表されるので、ANALYZEでこのような列のヒストグラムを作成できます。）
計算集中型の関数の値を事前に計算し、この値を索引内に格納します。
索引には、頻繁にアクセスする計算集中型の式を格納できます。その式にアクセスする必要があるときには、値はすでに計算済です。これによって、問合せ実行パフォーマンスが大幅に改善されます。
オブジェクト列およびREF列に対して索引を作成します。
オブジェクトを記述するメソッドは、索引作成対象の関数として使用できます。たとえば、MAPメソッドを使用してオブジェクト型列の索引を作成できます。
UPPER関数およびLOWER関数を使用した大/小文字を区別しないソート、DESCキーワードを使用した降順ソート、およびNLSSORT関数を使用した言語ベースのソートを実行できます。
|
注意: Oracle Databaseでは、DESCキーワードを使用すると、列は降順にソートされます。このような索引は、ファンクション索引として扱われます。降順索引は、ビットマップ化または逆キー索引化できません。ビットマップ最適化に使用することもできません。Oracle Databaseバージョン8より前のDESCの機能を使用する場合は、CREATE INDEX文からDESCキーワードを削除してください。 |
例5-3では、索引が(Column_a + Column_b)に構築されます。このため、SELECT文のWHERE句の式では、オプティマイザが全表スキャンのかわりにレンジ・スキャンを実行できます。
例5-3 オプティマイザによるレンジ・スキャンの実行を可能にするファンクション索引
SQL> DROP TABLE Example_tab;
DROP TABLE Example_tab
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Example_tab (Column_a INTEGER, Column_b INTEGER);
Table created.
SQL> -- Populate table
...
SQL> SELECT * FROM Example_tab ORDER BY Column_a;
COLUMN_A COLUMN_B
---------- ----------
1 2
2 4
3 6
4 8
5 10
5 rows selected.
SQL> CREATE INDEX Idx ON Example_tab(Column_a + Column_b);
Index created.
SQL> SELECT * FROM Example_tab
2 WHERE Column_a + Column_b < 10
3 ORDER BY Column_a;
COLUMN_A COLUMN_B
---------- ----------
1 2
2 4
3 6
3 rows selected.
SQL>
例5-4の内容は次のとおりです。
ファンクション索引Distance_indexは、表内の各都市に対してオブジェクト・メソッドDistance_from_equatorをコールします。このメソッドは、オブジェクト列 Reg_Objに対して適用されます。問合せはDistance_indexを使用して、赤道からの距離が1000マイルを超える都市を高速に検索します。（例ではこの表にデータが移入されていないため、問合せによって行は返されません。）
ファンクション索引Compare_indexは、気温差と最高気温を格納します。気温差の結果は降順でソートされます。問合せはCompare_indexを使用して、気温差が20未満で最高気温が75を超える表の行を高速に検索します。（例ではこの表にデータが移入されていないため、問合せによって行は返されません。）
例5-4 ファンクション索引
SQL> DROP TABLE Weatherdata_tab;
DROP TABLE Weatherdata_tab
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> CREATE TABLE Weatherdata_tab
2 (Reg_obj INTEGER, Maxtemp INTEGER, Mintemp INTEGER);
Table created.
SQL> CREATE OR REPLACE FUNCTION Distance_from_equator
2 (Reg_obj IN INTEGER)
3 RETURN INTEGER
4 DETERMINISTIC
5 IS
6 BEGIN
7 RETURN(3000);
8 END;
9 /
Function created.
SQL> CREATE INDEX Distance_index
2 ON Weatherdata_tab (Distance_from_equator (Reg_obj));
Index created.
SQL>
SQL> SELECT * FROM Weatherdata_tab
2 WHERE (Distance_from_equator (Reg_Obj)) > '1000';
no rows selected
SQL>
SQL> CREATE INDEX Compare_index
2 ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);
Index created.
SQL>
SQL> SELECT * FROM Weatherdata_tab
2 WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');
no rows selected
SQL>
ファンクション索引には次の制約があります。
ファンクション索引を使用できるのは、コストベース最適化のみです。ファンクション索引を有効にするには、DBMS_STATS.GATHER_TABLE_STATISTICSまたはDBMS_STATS.GATHER_SCHEMA_STATISTICSを起動してください。
索引式で使用されるすべてのトップレベルまたはパッケージ・レベルのPL/SQLファンクションは、DETERMINISTICとして宣言する必要があります。これらのファンクションは、たとえばUPPER関数のように、同じ入力に対して常に同じ結果を戻します。Oracle Databaseはアサーションが真であることを確認しないので、サブプログラムが実際にDETERMINISTICであることを確認する必要があります。
キーワードDETERMINISTICの使用方法のセマンティクスの規則を次に示します。
トップレベルのサブプログラムはDETERMINISTICとして宣言できます。
パッケージ・レベルのサブプログラムは、パッケージ仕様部ではDETERMINISTICとして宣言できますが、パッケージ本体では宣言できません。パッケージ本体内でDETERMINISTICを使用すると、例外が発生します。
プライベート・サブプログラム（別のサブプログラム内またはパッケージ本体内で宣言されるサブプログラム）は、DETERMINISTICとして宣言できます。
DETERMINISTICサブプログラムは、起動されるサブプログラムがDETERMINISTICとして宣言されているかどうかにかかわらず、別のサブプログラムを起動できます。
DETERMINISTICファンクションのセマンティックを変更し、再コンパイルすると、既存のファンクション索引およびマテリアライズド・ビューにより、旧バージョンのファンクションの結果がレポートされます。このように、ファンクションのセマンティックを変更する場合、依存するファンクション索引およびマテリアライズド・ビューを手動で作成する必要があります。
ファンクション索引で使用される式には集計関数を含めることはできません。式で参照するのは、表の同一行の列のみです。
索引を使用する前に、表または索引を分析する必要があります。
ビットマップ最適化では、降順索引を使用できません。
ファンクション索引は、OR拡張が実行された場合、使用されません。
索引ファンクションは、NOT NULLとマーク付けできません。全表スキャンを回避するには、問合せがNULL値をフェッチできないことを保証する必要があります。
ファンクション索引は、PL/SQLファンクションから長さが不明なVARCHAR2データ型またはRAWデータ型を戻す式は使用できません。回避策として、既知の長さのサブストリング関数を索引付けすることで、関数出力のサイズを制限します。次に例を示します。
SQL> /* initials function might return any number of letters, SQL> so limit return value to 10 characters for index: */ SQL> SQL> CREATE INDEX func_substr_index ON 2 EMPLOYEES(SUBSTR(initials(FIRST_NAME),1,10)); Index created. SQL> SQL> /* Invoke SUBSTR both when creating index and when referencing SQL> function in queries. */ SQL> SQL> SELECT SUBSTR(initials(FIRST_NAME),1,10) FROM EMPLOYEES; ...
|
関連項目: CREATE FUNCTIONの制約は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 |
次の文は、表EMP_TABでの大/小文字を区別しない検索をより高速にします。
CREATE INDEX emp_lastname ON EMPLOYEES (UPPER(LAST_NAME));
SELECT文では、UPPER(LAST_NAME)に対するファンクション索引を使用して、:KEYCOLのような名前を持つすべての従業員を戻します。
SELECT * FROM EMPLOYEES WHERE UPPER(LAST_NAME) LIKE 'J%S_N';
次の文は、列A、BおよびCを使用して各行の値を計算し、その結果を索引に格納します。
CREATE INDEX Idx ON Fbi_tab (a + b * (c - 1), a, b);
SELECT文では、索引のレンジ・スキャン（式が索引Idxの接頭辞であるため）または高速全索引スキャン（索引で高い並列度を指定してある場合はこの方が適切な場合があります）のいずれかを使用できます。
SELECT a FROM Fbi_tab WHERE a + b * (c - 1) < 100;
次の例では、各国語の照合順序に基づいたソートでのファンクション索引の使用方法を示します。NLSSORT関数は、照合順序GERMANを使用して名前ごとにソート・キーを戻します。
CREATE INDEX nls_index ON nls_tab (NLSSORT(NAME, 'NLS_SORT = GERMAN'));
次のSELECT文は、表のすべての内容を選択し、NAMEによって順序付けます。行はドイツ語照合順序を使用して順序付けられます。ドイツ語のセッションでは、NLS_SORTはGerman、NLS_COMPはANSIに設定されるため、グローバリゼーション・サポート・パラメータをSELECT文に指定する必要はありません。
SELECT * FROM nls_tab
WHERE NAME IS NOT NULL
ORDER BY NAME;
