1. オペレーション・ガイド
  2. TimesTenデータベースのデータの処理
  3. 索引の理解

索引の理解

索引は、表の検索のパフォーマンスを大幅に向上させる補助的なデータ構造です。

索引アドバイザを使用すると、特定のSQLワークロードに対して索引を推奨できます。詳細は、「索引アドバイザを使用した索引の推奨」を参照してください。

問合せの処理を高速化するために、索引は問合せオプティマイザによって自動的に使用されます。TimesTen問合せオプティマイザを参照してください。

索引は、表の各行で、索引を設定した列に一意の値を指定することによって一意なものとして指定できます。一意索引は、NULL値可能の列に対しても作成できます。SQL標準に従って、複数のnull値を一意索引に使用でき、一意索引により、同じ値セットを持つ複数の行を指定できます。

TimesTen Scaleoutでは、ローカル索引とグローバル索引の両方がサポートされています。ローカル索引を作成すると、その要素内の行にマップされる各要素に索引が作成されます。グローバル索引は、ローカル索引およびハッシュ分散スキームを含むマテリアライズド・ビューを索引キー列に作成することで、ハッシュ分散スキームのデータベース内のすべての行をマップします。『Oracle TimesTen In-Memory Database Scaleoutユーザーズ・ガイド』の索引の理解を参照してください。

TimesTenでは、データ値をソートする場合、NULL値はNULL以外のすべての値より大きい値とみなされます。『Oracle TimesTen In-Memory Database SQLリファレンス』のNull値を参照してください。

ユーザーは、索引の作成、破棄または変更の操作を実行するには適切な権限が必要です。それらの権限については、『Oracle TimesTen In-Memory Database SQLリファレンス』のSQL文で、すべてのSQL文の構文とともに説明されています。

次の項では、索引の管理方法について説明します。

索引タイプの概要

TimesTenでは、表への高速アクセスが可能になる次の2つのタイプの索引があります。表に最大500の範囲またはハッシュ索引を作成できます。

  • 範囲索引: 範囲索引は、特定の範囲内の列値がある行を検索する場合に有効です。範囲索引は、表の1つ以上の列に対して作成できます。

    範囲索引と等価結合は、一致検索および範囲検索(以上、以下など)で使用できます。あるフィールドに主キーを設定してFIELD > 10かどうかを確認する場合、主キー索引では検索は効率的に行われず、別の索引の方が効率的です。

    範囲索引はインメモリー・データ管理に最適化されており、列の値に基づく効率的なソートが可能です。

  • ハッシュ索引: ハッシュ索引は、一致検索の場合に有効です。ハッシュ索引は次のいずれかの方法で作成されます。

    • CREATE INDEX文を使用すると、表またはマテリアライズド・ビューの1つ以上の列にハッシュ索引または一意のハッシュ索引を作成できます。

    • CREATE TABLE... UNIQUE HASH ON文を使用すると、表の作成時に表の主キーに一意のハッシュ索引を作成できます。

    完全一致検索の場合、ハッシュ索引は範囲索引よりも高速になりますが、範囲索引より多くの領域が必要となります。ハッシュ索引は値自体の参照にのみ使用できます。SQL問合せで値の範囲が返される場合は、ハッシュ索引を使用できません。また、ハッシュ索引は、表スキャンによって取得される値のソートには効果がありません。

    TimesTenでは、問合せの処理を高速化する問合せの処理中に一時ハッシュ索引または一時範囲索引が自動的に作成されることがあります。または、データに高速にアクセスするために、RowIDによる検索を実行することもできます。『Oracle TimesTen In-Memory Database SQLリファレンス』のROWIDデータ型を参照してください

    ハッシュ索引の作成の詳細は、『Oracle TimesTen In-Memory Database SQLリファレンス』のCREATE INDEXおよびCREATE TABLEの項を参照してください。ハッシュ表のサイズを設定する方法の詳細は、「ハッシュ索引サイズの適切な設定」を参照してください。

索引の作成

索引を作成するには、SQL文CREATE INDEXを発行します。

TimesTenでは、索引名は大文字に変換されます。

TimesTen Scaleoutで索引を作成すると、分散マップ内のすべての要素の索引が作成されます。TimesTen Scaleoutは、各要素の索引に、その要素に格納されている行を移入します。

索引には所有者が存在します。索引の所有者は、基礎となる表を作成したユーザーです。TimesTenによって作成される索引(システム表の索引など)のユーザー名はSYSですが、レプリケーション表の索引のユーザー名はTTREPになります。

ノート:

LOB列には索引を作成できません。

次の例では、表customerの列cust_idに索引ixidを作成します。 

Command> CREATE INDEX ixid ON customer (cust_id);

次の文では、表作成の一部としてcustomer表に一意のハッシュ索引が作成されます。 

Command> CREATE TABLE customer 
(cust_id NUMBER NOT NULL PRIMARY KEY, 
cust_name CHAR(100) NOT NULL, 
addr CHAR(100), 
zip NUMBER, 
region CHAR(10)) 
UNIQUE HASH ON (cust_id) PAGES = 30;

次の文では、顧客名について、customer表に一意でないハッシュ索引が作成されます。 

Command> CREATE HASH INDEX custname_idx ON customer(cust_name);

Oracle TimesTen In-Memory Database SQLリファレンスのCREATE INDEXおよびALTER TABLEを参照してください。

索引の変更

ALTER TABLE文を使用して主キー制約を追加(または変更)し、範囲索引またはハッシュ索引の使用を指定できます。

CREATE INDEX文で作成した索引の場合、索引を変更してハッシュ索引から範囲索引へ、または範囲索引からハッシュ索引へ変換することはできません。

ALTER TABLE文のUSE RANGE INDEX句を使用すると、ハッシュ索引のかわりに範囲索引が使用されるように主キー制約を変更できます。ALTER TABLE文のUSE HASH INDEXを使用すると、範囲索引のかわりにハッシュ索引が使用されるように主キー制約を変更できます。『Oracle TimesTen In-Memory Database SQLリファレンス』のALTER TABLEを参照してください。

索引の破棄

TimesTen索引を破棄するには、SQL文DROP INDEXを発行します。

索引を一意に参照するには、アプリケーションで所有者および名前の両方を指定する必要があります。アプリケーションで所有者を指定しなかった場合、TimesTenは、コール元のユーザー名の下にある索引を検索し、次にユーザー名SYSの下にある索引を検索します。

次の例では、ixidという索引を削除します。 

Command> DROP INDEX ixid;

表を破棄すると、その表のすべての索引が自動的に破棄されます。TimesTen Scaleoutの索引を削除すると、すべての要素の索引が削除されます。

索引のサイズの見積り

TimesTenデータベースのサイズの増加は、最初の接続時に行うことができます。任意の索引を含むデータベースのサイズの見積りには、ttSizeユーティリティを使用します。

データベースのサイズを正確に見積ると、後でデータベースのサイズを変更する必要性を減らすために役立ちます。

次の例は、ttSizeユーティリティによる行数、インライン行のバイト数、表の索引のサイズおよび表の合計サイズの見積りを示します。 

% ttSize -tbl pat.tab1 mydb

Rows = 2

Total in-line row bytes = 17524
Indexes:

 Index PAT adds 6282 bytes
  Total index bytes = 6282

Total = 23806

索引アドバイザを使用した索引の推奨

適切な索引セットを使用すると、問合せパフォーマンスが向上します。索引アドバイザを使用すると、特定のSQLワークロードのパフォーマンスを向上させるための索引が推奨されます。

索引アドバイザは、読取り集中型の複雑な問合せを対象としています。書込み集中型のワークロードには、索引アドバイザの使用はお薦めしません。

索引アドバイザは、SQLワークロードを評価し、結合、単一表スキャン、およびORDER BYまたはGROUP BY操作のパフォーマンスを向上させることができる索引を推奨します。索引アドバイザは、マテリアライズド・ビュー用の実表やキャッシュ・グループ内の表など、特定の目的に使用する表を区別しません。SQLワークロードの問合せで表が使用されるかぎり、索引アドバイザはその表について索引を推奨することができます。

索引アドバイザは、推奨される索引ごとにCREATE文を生成し、発行する文を選択できます。索引アドバイザは次の索引を推奨できるため、データベース管理者は、新しい索引に推奨された各CREATE文を適用する前に見直す必要があります。

  • 既存の索引の複製になっている索引。

  • SQLワークロード時に作成および削除される表、または表の列の索引。ただし、表、または表の列を作成するDDLの後で、かつ、その削除の前で、SQLワークロードで推奨される索引にCREATE文を追加できます。

  • データが一意でないデータ・セット用の一意な索引など、作成することができない索引。この場合は、この推奨を無視する必要があります。

  • 索引作成オプションでは、UNIQUEまたは一意でない索引のいずれかとして索引を作成できます。索引アドバイザはどちらの索引タイプも推奨しています。推奨される両方の索引が同じ名前ため、それらの索引のうちの1つのみを作成できます。オプティマイザは、指定されたワークロードにはUNIQUE索引が適していると考えますが、一意でない索引を作成することも選択できます。列に一意の値のみが含まれている場合は、UNIQUE索引の作成を検討してください。列に一意でない値が含まれている場合は、一意でない索引の作成を検討してください。

索引アドバイザは、次のことに対応していません。

  • メモリー使用率を最適化しません。

  • メンテナンスのコストを考慮しません。

  • 有用でない場合は既存の索引を削除するということは推奨しません。

  • グローバル一時表用の索引は推奨しません。

推奨されるステップでは、次のように索引アドバイザを使用します。

索引アドバイザを実行するための準備

索引アドバイザを実行する前に、オプションで、関連するオプティマイザ・ヒントを設定すること、SQLワークロードに含まれている表の統計を更新すること、文を取得の間に強制的に再準備されるようにすることもできます。

  1. 索引アドバイザは問合せ計画に依存するため、索引アドバイザを有効にしてワークロードを実行する前に、SQLワークロードに使用する関連オプティマイザ・ヒントを設定します。「オプティマイザ・ヒントの使用による実行計画の変更」を参照してください。

  2. SQLワークロードに含まれている表の統計を更新し、取得時に文を強制して再準備させます。これにより、データ収集の最新の統計が提供され、最新の統計に基づいて文が再準備されます。

SQLワークロードに含まれている表の統計を、組込みプロシージャttOptUpdateStats、ttOptEstimateStatsまたはttOptSetTblStatsのうちの1つで更新します。組込みプロシージャでは、invalidateパラメータを1に設定して、示されている表を参照しているすべてのコマンドを無効にし、再実行時に自動的に再準備されるようにこれらのコマンドを強制します。これにより、統計は確実に最新状態になります。

  • ttOptUpdateStats組込みプロシージャは、表のすべての統計を完全に更新します。ただし、これには時間がかかります。

  • ttOptEstimateStatsは、示された表のわずかな割合の行に基づいて統計を評価します。

  • ttOptSetTblStatsは、ユーザーによって指定された既知の値に統計を設定します。

ノート:

Oracle TimesTen In-Memory DatabaseリファレンスのttOptUpdateStats、ttOptEstimateStatsおよびttOptSetTblStatsを参照してください。

次の例は、これらの表のうちの10%の行のランダム・サンプルを評価することで、現行のユーザー用のすべての表の統計を推定します。また、これらの表を参照する、すでに準備されたすべてのコマンドを無効にします。

Command> call ttOptEstimateStats ( '', 1, '10 PERCENT' );

索引推奨項目を生成するために使用するデータの取得

索引推奨項目を生成するために索引アドバイザに必要となる情報を取得するには、ttIndexAdviceCaptureStartおよびttIndexAdviceCaptureEnd組込みプロシージャをコールします。

  1. ttIndexAdviceCaptureStart組込みプロシージャをコールして、索引情報を収集するプロセスを開始します。

  2. SQLワークロードを実行します。

  3. ttIndexAdviceCaptureEnd組込みプロシージャをコールして、索引情報収集プロセスを終了します。

ノート:

データ収集プロセスの終了後は、「索引推奨項目およびデータ収集情報の取得」の説明に従って、索引推奨項目を取得できます。

ttIndexAdviceCaptureStart組込みプロシージャをコールしてデータ収集プロセスを開始する場合、次のものを指定します。

  • captureLevelパラメータでは、収集する索引情報を現行の接続向けまたはデータベース全体向けのいずれにするかを指定します。独立した接続に対して競合することなく、複数の接続レベルの取得を同時に実行できます。データベースレベルの取得は、接続レベルの取得と並行して実行できます。データベースレベルの取得と接続レベルの取得の間には競合がないため、データベースレベルの取得を開始したときにすでに進行中の未処理の接続レベルの取得は、目的どおりに完了します。ただし、最初のデータベースレベルの取得が引き続き有効な場合にデータベースレベルの取得の2番目のリクエストを開始すると、エラーが返されます。また、最初の接続レベルの取得が引き続き有効な場合に同じ接続から接続レベルの取得の2番目のリクエストが開始されるときも、エラーが返されます。

    データベースレベルの取得用にttIndexAdviceCaptureStartを起動すると、すでに進行中の未処理の接続レベルの取得は完了します。

  • captureModeパラメータには、次のいずれかのシナリオでデータ収集を実行することを指定します。

    • SQLワークロードの現行の処理を使用して索引情報の収集を実行します。

    • 索引情報の収集は、SQLワークロードの現行の処理の基盤にするのではなく、既存の計算済の統計および問合せ計画の分析の基盤にします。このシナリオでは、SQL文は準備されますが、実行はされません。このモードは、接続レベルの取得でのみ実行できます。

取得を完了するには、ttIndexAdviceCaptureEnd組込みプロシージャをコールすることで、同じ接続からの有効な接続レベルの取得、または有効なデータベースレベルの取得は終了します。データベースレベルの取得の完了には、ADMIN権限が必要です。

取得中に接続が失敗すると、次のことが起きます。

  • 取得が接続レベルの取得の場合、取得は終了し、関連するすべてのリソースは解放されます。

  • 取得がデータベースレベルの取得の場合、ADMIN権限を持った別のユーザーが接続してttIndexAdviceCaptureEnd組込みプロシージャを起動し、データベースレベルの取得を終了するまで、取得は続行されます。

取得中に一時領域が一杯になると、有効な取得は終了し、取得中に収集されたデータは保存されます。

ノート:

取得後にttIndexAdviceCaptureDropを実行して一時領域を解放します。「索引アドバイザ用に収集したデータの破棄と結果の最終処理」を参照してください。

次の例では、SQLワークロードの現行の処理に対応した接続レベルにおける索引アドバイザ用の収集を開始します。

call ttIndexAdviceCaptureStart(0,0);

次の例では、接続レベルの取得用の収集を終了します。

call ttIndexAdviceCaptureEnd(0);

ノート:

Oracle TimesTen In-Memory DatabaseリファレンスのttIndexAdviceCaptureStartおよびttIndexAdviceCaptureEndを参照してください。

索引推奨項目およびデータ収集情報の取得

データ収集概要および索引アドバイザ推奨項目を取得するには、ttIndexAdviceCaptureInfoGetおよびttIndexAdviceCaptureOutput組込みプロシージャを使用します。目的のデータのいずれか、または両方を実行します。

  1. ttIndexAdviceCaptureInfoGet組込みプロシージャをコールして、索引アドバイザ用のデータ収集概要情報を取得します。「ttIndexAdviceCaptureInfoGetを使用したデータ収集情報の取得」を参照してください。

  2. ttIndexAdviceCaptureOutput組込みプロシージャをコールして、索引推奨項目を取得します。「ttIndexAdviceCaptureOutputを使用した索引推奨項目の取得」を参照してください。

  3. DBAが索引推奨項目の作成文を評価した後で、目的の索引作成推奨項目を適用します。

ttIndexAdviceCaptureInfoGetを使用したデータ収集情報の取得

ttIndexAdviceCaptureInfoGet組込みプロシージャは、索引アドバイザ用に収集されたデータに関する情報を取得します。

接続レベルの取得とデータベース・レベルの取得のどちらでも、1行のみが返されます。

ノート:

データベースレベルの取得による行は、ADMIN権限を持っているユーザーにのみ返されます。

ttIndexAdviceCaptureInfoGet組込みプロシージャがデータを取得するのは、次の場合です。

  • データの取得が開始されて、終了していない場合。

  • 前の取得が開始されて停止されていて、データが削除されていない場合。

ノート:

進行している取得がないか、データが存在していない場合、行は返されません。

返される行には、次の情報が含まれています。

  • 取得の状態: 取得が完了すると0が返されます。取得が引き続き進行中の場合は1が返されます。

  • 該当する場合は接続識別子。

  • この取得に設定されている取得レベルおよびモード。

  • 取得間隔の間に準備されて実行された文の数。

  • 取得が開始されて停止された時刻。

次に、363の準備済の文および369の実行済の文について完了した接続レベルの取得に関する取得情報を示します。 

Command> call ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 363, 369, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

ノート:

Oracle TimesTen In-Memory DatabaseリファレンスのttIndexAdviceCaptureInfoGetを参照してください。

ttIndexAdviceCaptureOutputを使用した索引推奨項目の取得

ttIndexAdviceCaptureOutput組込みプロシージャは、指定したレベル(接続レベルまたはデータベースレベル)で最後に記録された取得から索引推奨項目のリストを取得します。

リストには、推奨索引ごとにCREATE文が含まれています。

接続レベルの取得について索引推奨項目をリクエストするには、取得を開始した同じ接続内でcaptureLevelを0に設定したttIndexAdviceCaptureOutputを実行します。データベースレベルの取得の場合、ユーザーがADMIN権限を持っている接続でcaptureLevelを1に設定したttIndexAdviceCaptureOutputを実行します。

返される行には、次のものが含まれています。

  • stmtCount - SQLワークロードを加速するために索引が役立つ回数。

  • createStmt - 推奨索引を作成するために使用できる文。これらの文のすべてのデータベース・オブジェクト名は完全に修飾されます。

次の例は、HR.PURCHASE表でPURCHASE_i1という索引に対応したCREATE文を示しています。この索引は、このSQLワークロードに対して4回役立ちます。 

CALL ttIndexAdviceCaptureOutput();
< 4, create index PURCHASE_i1 on HR.PURCHASE(AMOUNT); >
1 row found.

ノート:

Oracle TimesTen In-Memory DatabaseリファレンスのttIndexAdviceCaptureOutputを参照してください。

索引アドバイザ用に収集したデータの破棄と結果の最終処理

DBAによって承認された新しい索引に対してCREATE文を適用した後、索引アドバイザ用に収集した取得データを破棄できます。ttIndexAdviceCaptureDrop組込みプロシージャは、接続レベルまたはデータベースレベルの取得にできる、指定したcaptureLevel用に収集された既存のデータを削除します。 
Call ttIndexAdviceCaptureDrop(0);

接続レベルとデータベースレベルの両方の取得を削除するには、この組込みプロシージャを2回コールする必要があります。同じレベルで取得が進行中の間は、この組込みプロシージャを起動することはできません。

ノート:

Oracle TimesTen In-Memory DatabaseリファレンスのttIndexAdviceCaptureDropを参照してください。

索引推奨項目がそれ以上ない状態でSQLワークロードが実行されるようになるまで、「索引アドバイザを実行するための準備」と「索引推奨項目およびデータ収集情報の取得」で示されている手順を繰り返すことができます。新しい索引が適用された表の統計を更新し続けることができ、新しい索引が推奨されるかどうかを確認するために索引アドバイザを再実行できます。

索引アドバイザの組込みプロシージャの使用例

索引アドバイザの組込みプロシージャでは、SQLワークロードのデータ収集のフローが示され、索引のアドバイスが提供されます。 

Command> call ttOptUpdateStats();

Command> call ttIndexAdviceCaptureStart();

Command> SELECT employee_id, first_name, last_name FROM employees;
< 100, Steven, King >
< 101, Neena, Kochhar >
< 102, Lex, De Haan >
< 103, Alexander, Hunold >
< 104, Bruce, Ernst >
...
< 204, Hermann, Baer >
< 205, Shelley, Higgins >
< 206, William, Gietz >
107 rows found.

Command> SELECT MAX(salary) AS MAX_SALARY 
FROM employees 
WHERE employees.hire_date > '2000-01-01 00:00:00';
< 10500 >
1 row found.

Command> SELECT employee_id, job_id FROM job_history 
WHERE (employee_id, job_id) NOT IN (SELECT employee_id, job_id 
FROM employees);
< 101, AC_ACCOUNT >
< 101, AC_MGR >
< 102, IT_PROG >
< 114, ST_CLERK >
< 122, ST_CLERK >
< 176, SA_MAN >
< 200, AC_ACCOUNT >
< 201, MK_REP >
8 rows found.

Command> WITH dept_costs AS 
(SELECT department_name, SUM(salary) dept_total 
FROM employees e, departments d 
WHERE e.department_id = d.department_id 
GROUP BY department_name), 
avg_cost AS 
(SELECT SUM(dept_total)/COUNT(*) avg 
FROM dept_costs) 
SELECT * FROM dept_costs 
WHERE dept_total > 
(SELECT avg FROM avg_cost) 
ORDER BY department_name;
< Sales, 304500 >
< Shipping, 156400 >
2 rows found.

Command> call ttIndexAdviceCaptureEnd();

Command> call ttIndexAdviceCaptureInfoGet();
< 0, 1, 0, 0, 9, 6, 2012-07-27 11:44:08.136833, 2012-07-27 12:07:35.410993 >
1 row found.

Command> call ttIndexAdviceCaptureOutput();
< 1, create index EMPLOYEES_i1 on HR.EMPLOYEES(SALARY); >
< 1, create index EMPLOYEES_i2 on HR.EMPLOYEES(HIRE_DATE); >
2 rows found.

Command> call ttIndexAdviceCaptureDrop();