11 データベース・アプリケーションでの索引の使用
索引は表およびクラスタと関連付けられているオプション構造で、これによりSQL問合せをより迅速に実行することができます。このマニュアルで、索引の使用により、索引がない場合よりも高速に情報を検索できるように、Oracle Databaseの索引は、表データへのより高速なアクセス・パスを提供します。索引は問合せをリライトすることなく使用できます。結果は同じですが、より高速に得られます。
関連項目:
-
索引および索引構成表の詳細は、『Oracle Database概要』を参照してください。
-
索引管理の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
索引およびクラスタによってどのようにパフォーマンスが向上または低下する可能性があるかの詳細は、『Oracle Database SQLチューニング・ガイド』を参照してください。
トピック:
11.1 索引を管理するためのガイドライン
索引管理のガイドラインの概要を、次に示します。
-
表データの挿入後の索引の作成
-
正しい表および列の索引付け
-
パフォーマンスのための索引列の順序付け
-
表当たりの索引数の制限
-
不要な索引の削除
-
遅延セグメント作成の理解
-
索引サイズの見積りと記憶域パラメータの設定
-
索引ごとの表領域の指定
-
索引作成の並列化の検討
-
NOLOGGING
を使用する索引の作成の検討 -
使用禁止または非表示の索引をいつ使用するかの理解
-
索引の結合または再構築のコストとメリットの検討
-
制約を使用禁止または削除する前のコストの検討
11.3 ドメイン索引を使用する場合
ドメイン索引(アプリケーション・ドメイン索引とも呼ばれます)はデータ・カートリッジ(検索エンジンまたは地理情報システムなど)を使用して実装されたアプリケーションに特有のカスタマイズされた索引です。
関連項目:
-
ドメイン索引をいつ構築するかを決定するための概念的背景については、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
-
ドメイン索引の詳細は、『Oracle Database概要』を参照してください。
11.4 ファンクション索引を使用する場合
ファンクション索引は1つまたは複数の列を含む式の値計算し、それを索引に格納します。索引式は、算術式、あるいはSQLファンクション、PL/SQLファンクション、パッケージ・ファンクションまたはCコールアウトを含む式のいずれかです。ファンクション索引は、照合キーに基づく言語ソート、SQL文の効率的な言語依存照合、および大/小文字を区別しないソートもサポートします。
ファンクション索引は、索引式を使用する問合せのパフォーマンスを向上させます(特に式の計算が集中型の場合)。ただし、次の点に注意してください。
-
データベースは索引を使用しない文の処理でも索引式を評価する必要があります。
-
頻繁に変更される列のファンクション索引は、データベースで維持するためにコストがかかります。
オプティマイザはファンクション索引をコストベースの最適化のためだけに使用できます。一方、列の索引はコストベースの最適化およびルールベースの最適化の両方のために使用できます。
ノート:
-
ファンクション索引には
NULL
値を含めることはできません。そのため、列にNULL
を含む可能性のある索引式が含まれていないことを確認するか、または索引式にNVL
関数を使用してNULL
を他の値に置き換えてください -
Oracle Databaseは、降順索引をファンクション索引として扱います。
トピック:
関連項目:
-
ファンクション索引のその他の概念は、『Oracle Database概要』を参照してください。
-
ファンクション索引の作成の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
ファンクション索引の言語の詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。
-
オプティマイザのファンクション索引の使用方法の詳細は、『Oracle Database概要』を参照してください。
-
パフォーマンスのためのファンクション索引の使用方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください
-
NVL
の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
索引の作成の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
11.4.1 ファンクション索引のメリット
ファンクション索引には次のようなメリットがあります。
-
ファンクション索引を使用すると、データベースが全索引スキャンではなく索引範囲スキャンを実行できる機会が増加します。
WHERE
句によって大きな表の15%未満の行が選択された場合、索引範囲スキャンでは通常応答時間が短くなります。式がファンクション索引に実体化されていると、オプティマイザは式で選択される行数をより正確に見積もることができます。Oracle Databaseでは、索引式は仮想列として表されます。ここで、
ANALYZE
文でヒストグラムを作成できます。 -
ファンクション索引は事前計算を行い、式の値を格納します。
問合せは式の値を計算するかわりに索引から得ることができます。値を必要とする問合せが増え、索引式が計算集中型になればなるほど、索引により、アプリケーションのパフォーマンスが向上します。
-
ファンクション索引はオブジェクト列または
REF
列に対して作成できます。索引式はオブジェクト・タイプを戻すメソッドを起動することができます。
-
ファンクション索引により、より強力なソートを実行できます。
索引式では、大/小文字を区別しないソートのためのSQL関数
UPPER
およびLOWER
(例11-3を参照)、および言語ベースのソートのためのSQL関数NLSSORT
を呼び出すことができます。
関連項目:
-
索引範囲スキャンおよび索引スキャンの詳細は、『Oracle Database概要』を参照してください。
-
ANALYZE
文の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。 -
ファンクション索引の詳細は、『Oracle Databaseオブジェクト・リレーショナル開発者ガイド』を参照してください。
-
ファンクション索引に関連する例は、「例: オブジェクト列でのファンクション索引」および『Oracle Database SQL言語リファレンス』を参照してください。
-
NLSSORT
SQL関数に関連する例は、「例: 言語依存ソート用のファンクション索引」を参照してください。
11.4.2 ファンクション索引のデメリット
ファンクション索引には次のようなデメリットがあります。
-
オプティマイザはファンクション索引をコストベースの最適化のためだけに使用できます。ルールベースの最適化には使用できません。
コストベースの最適化はディクショナリに格納された統計情報を使用します。ファンクション索引の統計情報を収集するには、
DBMS_STATS
.GATHER_TABLE_STATS
またはDBMS_STATS
.GATHER_SCHEMA_STATS
を呼び出します。 -
ファンクション索引は、索引自体と索引が定義される表が分析されるまで、使用されません。
索引と索引が定義される表を分析するには
DBMS_STATS
.GATHER_TABLE_STATS
またはDBMS_STATS
.GATHER_SCHEMA_STATS
を起動します。 -
ファンクション索引は、
OR
拡張が実行された場合、使用されません。 -
索引式が呼び出すすべてのスキーマ・レベルまたはパッケージ・レベルPL/SQLファンクションが、DETERMINISTICである(同じ入力に対して常に同じ結果を戻す)ことを確認する必要があります。
ファンクションは
DETERMINISTIC
として宣言する必要がありますが、Oracle Databaseはこのアサーションをチェックしないので、ファンクションが実際にDETERMINISTICであることを確認する必要があります。DETERMINISTIC
ファンクションのセマンティックを変更し、再コンパイルすると、依存するファンクション索引およびマテリアライズド・ビューを手動で再作成する必要があります。それ以外の場合は、旧バージョンのファンクションの結果がレポートされます。 -
索引式がファンクション起動の場合、ファンクションの戻り型は制約付きにできません。
ファンクションの戻り型を
NOT
NULL
で制約できないため、索引を使用する問合せがNULL
値をフェッチできないことを保証する必要があります。そうしないと、データベースは全表スキャンを実行します。 -
索引式では、集計関数は呼び出せません。
-
ビットマップされたファンクション索引は、降順索引にはできません。
-
索引式のデータ型は、
VARCHAR2
、RAW
、LONGRAW
、または長さが不明のPL/SQLデータ型にはできません。つまり、長さが不明な式には索引付けできません。ただし、そのような式の既知の長さのサブストリングには索引付けできます。たとえば:
CREATE OR REPLACE FUNCTION initials ( name IN VARCHAR2 ) RETURN VARCHAR2 DETERMINISTIC IS BEGIN RETURN('A. J.'); END; / /* Invoke SUBSTR both when creating index and when referencing function in queries. */ CREATE INDEX func_substr_index ON EMPLOYEES(SUBSTR(initials(FIRST_NAME),1,10)); SELECT SUBSTR(initials(FIRST_NAME),1,10) FROM EMPLOYEES;
関連項目:
-
ファンクション索引のノートについては、『Oracle Database SQL言語リファレンス』を参照してください。
-
ファンクション索引の制限事項については、『Oracle Database SQL言語リファレンス』を参照してください。
-
制限事項を含む
CREATE
FUNCTION
文の詳細は、『Oracle Database PL/SQL言語リファレンス』を参照してください。 -
DBMS_STATS
、GATHER_TABLE_STATS
、DBMS_STATS
およびGATHER_SCHEMA_STATS
の詳細は、『Oracle Database PL/SQLパッケージおよびタイプ・リファレンス』を参照してください。 -
集計関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
-
ファンクション索引の詳細は、『Oracle Database SQL言語リファレンス』を参照してください。
11.4.3 例: 算術式の事前計算用のファンクション索引
算術式を計算するために複合索引を作成できます。
例11-1は列a
、b
およびc
のある表を作成し、表に索引を作成し、表に問合せを実行します。索引は式a+b*(c-1)
を表す仮想列、列a
および列b
の3つの列に対するコンポジット索引です。問合せはそのWHERE
句に索引付けされた式を使用します。そのため、全索引スキャンのかわりに索引範囲スキャンを使用できます。
例11-1 算術式の事前計算用のファンクション索引
索引を作成する表を作成します。
DROP TABLE Fbi_tab; CREATE TABLE Fbi_tab ( a INTEGER, b INTEGER, c INTEGER );
索引を作成します。
CREATE INDEX Idx ON Fbi_tab (a+b*(c-1), a, b);
この問合せは全索引スキャンのかわりに索引範囲スキャンを使用できます。
SELECT a FROM Fbi_tab WHERE a+b*(c-1) < 100;
ノート:
この例では、複合索引(複数の表列に対する索引)を使用します。
関連項目:
-
高速全索引スキャンの詳細は、『Oracle Database概要』を参照してください。
-
複合索引の詳細は、『Oracle Database概要』を参照してください。
11.4.4 例: オブジェクト列でのファンクション索引
例11-2では、オブジェクト・タイプReg_obj
が定義され、そこに都市に関する情報が格納されると仮定します。例では、最初の列にタイプReg_obj
を含む表、パラメータにタイプReg_obj
を指定したDETERMINISTICファンクション、ファンクションを起動する2つのファンクション索引を作成します。最初の問合せは最初の索引を使用して赤道から1000マイル以上離れた都市を迅速に検索します。2番目の問合せは2番目の索引(コンポジット索引)を使用して、気温の差が20未満で最高気温が75よりも高い都市を迅速に検索します(表は例が移入されていないので問合せで行は戻りません )。
例11-2 オブジェクト列でのファンクション索引
オブジェクト列を含む表を作成します。
DROP TABLE Weatherdata_tab;
CREATE TABLE Weatherdata_tab (
Reg_obj INTEGER,
Maxtemp INTEGER,
Mintemp INTEGER
);
パラメータにタイプReg_obj
を指定したDETERMINISTICファンクションを作成します。
CREATE OR REPLACE FUNCTION Distance_from_equator ( Reg_obj IN INTEGER ) RETURN INTEGER DETERMINISTIC IS BEGIN RETURN(3000); END; /
最初のファンクション索引を作成します。
CREATE INDEX Distance_index
ON Weatherdata_tab (Distance_from_equator (Reg_obj));
問合せで索引式を使用します。
SELECT * FROM Weatherdata_tab WHERE (Distance_from_equator (Reg_Obj)) > '1000';
結果:
no rows selected
2番目のファンクション(コンポジット)索引を作成します。
CREATE INDEX Compare_index
ON Weatherdata_tab ((Maxtemp - Mintemp) DESC, Maxtemp);
問合せで索引式および索引付けされた列を使用します。
SELECT * FROM Weatherdata_tab WHERE ((Maxtemp - Mintemp) < '20' AND Maxtemp > '75');
結果:
no rows selected
11.4.5 例: 大/小文字を区別しないより高速の検索用のファンクション索引
例11-3では、EMPLOYEES
表でより高速な大/小文字を区別しない検索ができる索引を作成してから、問合せで索引式を使用します。
例11-3 大/小文字を区別しないより高速の検索用のファンクション索引
索引を作成します。
CREATE INDEX emp_lastname ON EMPLOYEES (UPPER(LAST_NAME));
問合せで索引式を使用します。
SELECT first_name, last_name
FROM EMPLOYEES
WHERE UPPER(LAST_NAME) LIKE 'J%S_N';
結果:
FIRST_NAME LAST_NAME -------------------- ------------------------- Charles Johnson 1 row selected.
11.4.6 例: 言語依存ソート用のファンクション索引
言語依存ソート用NLSSORT
APIを使用できます。
例11-4では、1つの列NAME
を含む表、照合順序GERMAN
を使用して列をソートするファンクション索引を作成してから、表のすべての列を選択し、NAME
により順序付けします。問合せは索引を使用できるのでより高速です(問合せはドイツ語セッションで実行され、NLS_SORT
はGERMAN
、NLS_COMP
はANSI
であると仮定します。そうでない場合、問合せでこれらのグローバリゼーション・サポートのパラメータの値を指定する必要があります)。
例11-4 言語依存ソート用のファンクション索引
索引を作成する表を作成します。
DROP TABLE nls_tab;
CREATE TABLE nls_tab (NAME VARCHAR2(80));
索引を作成します。
CREATE INDEX nls_index
ON nls_tab (NLSSORT(NAME, 'NLS_SORT = GERMAN'));
表のすべての列を選択し、NAME
によって順序付けします。
SELECT * FROM nls_tab
WHERE NAME IS NOT NULL
ORDER BY NAME;