3 索引と索引構成表
索引は、表の行へのアクセスを高速化できるスキーマ・オブジェクトです。索引構成表は、索引構造に格納された表です。
この章の構成は、次のとおりです。
索引の概要
索引は、表または表クラスタに関連するオプションの構造であり、索引によってデータ・アクセスを高速化できる場合があります。
索引は、関連するオブジェクトのデータから論理的にも物理的にも独立したスキーマ・オブジェクトです。そのため、索引を削除または作成しても、索引付けされた表に物理的な影響はありません。
注意:
索引を削除しても、アプリケーションは引き続き機能します。ただし、索引設定されていたデータへのアクセスは低速になる場合があります。
たとえば、ある人事管理マネージャが複数の段ボール箱を棚に置いて管理しているとします。従業員情報を収めたフォルダが、それらの箱にランダムに収納されています。従業員Whalen(ID 200)のフォルダは、1つ目の箱の下から10フォルダ目であり、King(ID 100)のフォルダは3つ目の箱の一番下にあります。マネージャがフォルダを見つけるには、1つ目の箱の一番下から一番上まですべてのフォルダを確認し、目的のフォルダが見つかるまで次の箱で同じことを繰り返します。アクセスを高速化するために、マネージャは従業員全員のIDとフォルダの位置を順にリストした索引を作成できます。
ID 100: Box 3, position 1 (bottom)
ID 101: Box 7, position 8
ID 200: Box 1, position 10
.
.
.
同様に、マネージャは従業員の姓や部門IDなどの個別の索引を作成できます。
この項では、次の項目について説明します。
索引の利点
索引の有無によって、SQL文の表現を変更する必要はありません。
索引は、単一行のデータへの高速なアクセス・パスです。それは実行のスピードにのみ影響を与えます。索引の付いたデータ値では、索引はその値を含んでいる行の位置を直接示すポインタとして機能します。
索引が表の1つ以上の列に存在する場合、データベースで、ランダムに分散している行の小さなセットを表から取得できる場合があります。索引は、ディスクI/Oを削減するための様々な手段のうちの1つです。ヒープ構成表に索引がない場合、そのデータベースでは、値の検索に全表スキャンを実行する必要があります。たとえば、索引付けされていないhr.departments表の位置2700の問合せ
では、データベースは、すべてのブロックのすべての行を検索する必要があります。データ量が増加すると、この方法では適切に対応できなくなります。
通常、次のいずれかの場合は、列に索引を作成することを検討します。
索引の使用可能性と可視性
索引は、使用可能(デフォルト)または使用不可、参照用(デフォルト)または非参照用にできます。
これらのプロパティは次のように定義されます。
-
使用可能性
使用禁止索引はオプティマイザによって無視され、DML操作によって管理されません。使用禁止索引により、バルク・ロードのパフォーマンスが向上します。索引を削除し、後から再作成するかわりに、索引を使用禁止にし、後から再構築できます。使用禁止索引と索引パーティションは、領域を使用しません。使用可能索引を使用禁止にすると、データベースによりその索引セグメントが削除されます。
-
可視性
非参照用索引はDML操作により管理されますが、デフォルトではオプティマイザにより使用されません。索引を非表示にするのは、使用禁止または削除の代替手段です。非参照用索引は、索引を削除する前に削除をテストする場合や、アプリケーション全体に影響を与えることなく一時的に索引を使用する場合に特に役立ちます。
関連項目:
オプティマイザによって実行計画が選択される方法の詳細は、「オプティマイザの概要」を参照
キーと列
キーとは、索引を作成できる列または式の集合です。
索引とキーという用語は同じ意味で使用されることがありますが、これらの用語には違いがあります。索引は、データベース内に格納される構造体であり、ユーザーはSQL文を使用して管理します。一方、キーは厳密に論理的な概念です。
次の文は、サンプル表oe.orders
のcustomer_id
列に索引を作成します。
CREATE INDEX ord_customer_ix ON orders (customer_id);
この文では、customer_id
列が索引キーです。索引自体はord_customer_ix
という名前です。
注意:
主キーと一意キーには自動的に索引が作成されますが、外部キーにも索引を作成できます。
関連項目:
-
「データ整合性」
-
CREATE INDEX
の構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照
コンポジット索引
コンポジット索引(連結索引とも呼ばれる)は、表の中の複数の列に対して作成される索引です。
列をコンポジット索引で、データを取得する問合せに最も有用な順序に配置します。列は、表内で隣り合っている必要はありません。
SELECT
文のWHERE
句でコンポジット索引のすべての列または列の先頭部分を参照する場合には、コンポジット索引によってデータの検索速度を向上させることができます。そのため、定義で使用する列の順序は重要です。通常、最も頻繁にアクセスされる列が最初になります。
たとえば、アプリケーションが頻繁にemployees
表のlast_name
、job_id
およびsalary
列への問合せを発行するとします。また、last_name
はカーディナリティが高く、表の行数に比べて個別値の数が多いとします。次のような列の順序で、索引を作成します。
CREATE INDEX employees_ix
ON employees (last_name, job_id, salary);
3つの列すべて、last_name
列のみ、またはlast_name
およびjob_id
列のみにアクセスする問合せは、この索引を使用します。この例では、last_name
列にアクセスしない問合せは索引を使用しません。
注意:
先頭列のカーディナリティが非常に低い場合などには、データベースでは、この索引のスキップ・スキャンが使用されることがあります(「索引スキップ・スキャン」を参照)。
次の条件のいずれかを満たす場合、複数の索引を同じ表で同じ列順に存在させることができます。
-
索引のタイプが異なります。
たとえば、同じ列でビットマップ索引やBツリー索引を作成できます。
-
索引で異なるパーティション・スキーマが使用される。
たとえば、ローカルでパーティション化された索引や、グローバルにパーティション化された索引を作成できます。
-
索引の一意性プロパティが異なります。
たとえば、同じセットの列で一意と非一意の両方の索引を作成できます。
たとえば、パーティション化されていない索引、グローバル・パーティション索引、ローカルでパーティション化された索引を、同じ表の列のために同じ順序で存在させることができます。一度に表示できるのは、同じ順序で同数の列を持つ1つの索引のみです。
この機能により、既存の索引を削除し、別の属性で再作成する必要なく、アプリケーションを移行できます。また、索引キーが増え続け、新しいエントリが同じセットの索引ブロックに挿入されることになる場合、この機能はOLTPデータベースで役立ちます。このような「ホット・スポット」を軽減するには、パーティション化されていない索引からグローバル・パーティション索引に、索引を進化させることができます。
同じセットの列の索引が、タイプやパーティショニング・スキームで違いがない場合、これらの索引では異なる列順列を使用する必要があります。たとえば、次のSQL文では有効な列順列を指定しています。
CREATE INDEX employee_idx1 ON employees (last_name, job_id);
CREATE INDEX employee_idx2 ON employees (job_id, last_name);
関連項目:
コンポジット索引の使用方法の詳細は、『Oracle Database SQLチューニング・ガイド』を参照
一意索引と非一意索引
索引には、一意索引と非一意索引の2種類があります。一意索引を使用すると、表の複数行のキー列に重複した値が入らないことが保証されます。
たとえば、アプリケーションでは、同じ従業員IDを持つ2人の従業員がいないことが必要になります。一意索引の場合、データ値ごとにrowidが1つ存在します。リーフ・ブロックのデータはキーのみによってソートされます。
非一意索引では、索引付けされた1つ以上の列に重複値が許可されます。たとえば、employees
表のfirst_name
列に、複数のMike
値が含まれていてもかまいません。非一意索引の場合、ROWIDはソートされた順序でキーに含まれるため、非一意索引は索引キーとROWIDによってソートされます(昇順)。
Oracle Databaseでは、すべてのキー列がNULLの表の行には、索引は作成されません。ただし、ビットマップ索引の場合や、クラスタ・キーの列値がNULLの場合は例外です。
索引のタイプ
Oracle Databaseは、パフォーマンスの機能性を補足する複数の索引体系を提供しています。
Bツリー索引は標準索引タイプです。これらは高度に選択的な索引(各索引エントリに複数行が対応)および主キーに適しています。Bツリー索引を連結索引として使用すると、索引付けされた列でソートされたデータを取得できます。Bツリー索引には、次の表に示すサブタイプがあります。
表3-1 Bツリー索引のサブタイプ
Bツリー索引のサブタイプ | 説明 | 詳細情報 |
---|---|---|
索引構成表 |
索引構成表はデータ自体が索引であるため、ヒープ構成表とは異なります。 |
|
索引構成表 |
索引構成表はデータ自体が索引であるため、ヒープ構成表とは異なります。 |
|
逆キー索引 |
このタイプの索引では、索引キーのバイトの並びが逆になり、たとえば、103は301として格納されます。バイトの並びを逆にすることにより、索引への挿入が多数のブロックに分散されます。 |
「逆キー索引」 |
降順索引 |
このタイプの索引では、特定の列(1つまたは複数)のデータが降順で格納されます。 |
|
Bツリークラスタ索引 |
このタイプの索引では、特定の列(1つまたは複数)のデータが降順で格納されます。 |
次の表に、Bツリー構造を使用しない索引のタイプを示します。
表3-2 Bツリー構造を使用しない索引
タイプ | 説明 | 詳細情報 |
---|---|---|
ビットマップ索引とビットマップ結合索引 |
ビットマップ索引では、索引エントリはビットマップを使用して複数の行を指します。対照的に、Bツリー索引エントリは単一の行を指します。ビットマップ結合索引は、2つ以上の表を結合するためのビットマップ索引です。 |
|
ファンクションベース索引 |
このタイプの索引に含まれる列は、 |
|
アプリケーション・ドメイン索引 |
ユーザーは、このタイプの索引をアプリケーション固有のドメインにあるデータ用に作成します。物理索引は従来の索引構造を使用する必要はなく、表としてOracle Databaseに格納することも、ファイルとして外部に格納することもできます。 |
関連項目:
-
索引の管理方法の詳細は、『Oracle Database管理者ガイド』を参照
-
様々な索引タイプの詳細は、『Oracle Database SQLチューニング・ガイド』を参照
データベースで索引を管理する方法
行が挿入されても、索引付きのデータ検索のパフォーマンスはほとんど一定です。ただし、表に対して数多くの索引が存在すると、データベースの索引も更新する必要があるため、DMLのパフォーマンスは低下します。
索引記憶域
Oracle Databaseでは、索引データは索引セグメントに格納されます。
データ・ブロックで索引データのために使用できる領域は、データ・ブロック・サイズから、ブロック・オーバーヘッド、エントリ・オーバーヘッド、ROWID、および索引が作成される値1つ当たり1バイトの合計を引いたものです
索引セグメントの表領域は、所有者のデフォルト表領域またはCREATE INDEX
文で明示的に指定された表領域です。管理を容易にするために、索引をその表とは別の表領域に格納できます。たとえば、索引のみを含む表領域は再構築できるため、これらの表領域をバックアップしないよう指定することによって、バックアップに必要な時間と記憶域を削減できます。
関連項目:
索引ブロックのタイプ(ルート、ブランチおよびリーフ)と、ブロック内に索引エントリが格納される方法の詳細は、「索引ブロックの概要」を参照
Bツリー索引の概要
Bツリー(バランス・ツリーの略)は、最も一般的なタイプのデータベース索引です。Bツリー索引は、複数の範囲に分割された順序付きの値リストです。Bツリーは、キーを行または行の範囲と関連付けることによって、完全一致や範囲検索など、広範囲の問合せに対して優れた検索パフォーマンスを提供します。
次の図に、Bツリー索引の構造を示します。この例では、employees
表の外部キー列であるdepartment_id
列の索引を示しています。
この項では、次の項目について説明します。
ブランチ・ブロックとリーフ・ブロック
Bツリー索引には、検索用のブランチ・ブロックと、キー値のソート用のリーフ・ブロックの2つのタイプのブロックがあります。Bツリー索引の上位ブランチ・ブロックには、下位レベルの索引ブロックを指す索引データが含まれます。
図3-1では、ルート・ブランチ・ブロックに0-40
のエントリがあり、次のブランチ・レベルの左端のブロックを指しています。このブランチ・ブロックには、0-10
や11-19
などのエントリがあります。これらの各エントリは、それぞれの範囲に該当するキー値を含むリーフ・ブロックを指します。
すべてのリーフ・ブロックは自動的に同じ深さになるため、Bツリー索引はバランスが保たれます。したがって、索引のどの位置からでも、レコード検索にかかる時間はほぼ同じになります。索引の高さは、ルート・ブロックからリーフ・ブロックに達するまでに必要なブロックの数です。ブランチ・レベルは高さから1を引いた数です。図3-1では、索引の高さは3、ブランチ・レベルは2です。
ブランチ・ブロックには、2つのキーの分岐を決定する際に必要な、最小のキー接頭辞が格納されます。このテクニックを使用すると、データベースでは各ブランチ・ブロックにできるかぎり多くのデータを収納できます。ブランチ・ブロックには、キーを含む子ブロックへのポインタが含まれます。キーおよびポインタの数は、ブロックのサイズによって制限されます。
リーフ・ブロックには、すべての索引付きデータ値と、実際の行を検索するための対応するROWIDが含まれています。各エントリは(キー、ROWID)によってソートされます。リーフ・ブロック内では、キーとROWIDは兄弟関係にある左右のエントリにリンクされます。リーフ・ブロック自体も二重にリンクされます。図3-1では、左端のリーフ・ブロック(0-10
)が2番目のリーフ・ブロック(11-19
)にリンクされます。
注意:
文字データを持つ列の索引は、データベース・キャラクタ・セットにおける文字のバイナリ値を基盤にしています。
索引スキャン
索引スキャンの場合、データベースでは文に指定された索引付けされた列値を使用し、索引を読み込むことによって行を検索します。データベースでは、索引をスキャンして値を検索する場合、n回のI/Oでこの値を見つけます(nは、Bツリー索引の高さ)。これがOracle Database索引の基本原理です。
SQL文が索引付けされた列のみにアクセスする場合、データベースでは表ではなく索引から値を直接読み取ります。文が索引付けされた列に加えて索引付けされていない列にもアクセスする場合、データベースではROWIDを使用して表の中の行を検索します。通常、データベースでは索引ブロックと表ブロックを交互に読み取って表データを取得します。
関連項目:
索引スキャンの詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
全索引スキャン
全索引スキャンの場合、データベースでは索引全体を順に読み取ります。全索引スキャンは、SQL文の述語(WHERE
句)が索引の列を参照している場合、および述語が指定されていない場合に使用できます。全スキャンでは、データが索引キーによって順序付けられるため、ソートが不要になります。
例3-1 全索引スキャン
アプリケーションにより、次の問合せが実行されるとします。
SELECT department_id, last_name, salary FROM employees WHERE salary > 5000 ORDER BY department_id, last_name;
この例では、department_id
、last_name
およびsalary
が索引のコンポジット・キーです。Oracle Databaseでは、ソートされた順序(部門IDおよび姓の順序)で索引を読み取り、salary属性でフィルタ処理して索引の全スキャンを実行します。これにより、データベースでは、問合せに含まれている列より多くの列を含むemployees
表よりも小さいデータ・セットがスキャンされ、データのソートが不要になります。
全スキャンによって次のような索引エントリが読み取られます。
50,Atkinson,2800,rowid 60,Austin,4800,rowid 70,Baer,10000,rowid 80,Abel,11000,rowid 80,Ande,6400,rowid 110,Austin,7200,rowid . . .
高速全索引スキャン
高速全索引スキャンは、データベースが特定の順序に従わずに索引ブロックを読み取る全索引スキャンで、表にアクセスすることなく、索引自体の中のデータにアクセスします。
高速全索引スキャンは、次に示す2つの条件が満たされている場合に全表スキャンの代替となるスキャンです。
-
問合せに必要なすべての列が索引に含まれていること。
-
すべてNULLの行が問合せの結果セットに現れないこと。この結果を保証するためには、索引内の少なくとも1つの列に次のいずれかが存在する必要があります。
-
NOT NULL
制約。 -
問合せの結果セットにNULLが含まれないように列に適用された述語
-
例3-2 高速全索引スキャン
アプリケーションにより、ORDER BY
句を含まない次の問合せが発行されるとします。
SELECT last_name, salary FROM employees;
last_name
列にはNOT NULL制約があります。姓と給与が索引のコンポジット・キーである場合、高速全索引スキャンでは索引エントリを読み取って、要求された情報を取得できます。
Baida,2900,rowid Atkinson,2800,rowid Zlotkey,10500,rowid Austin,7200,rowid Baer,10000,rowid Austin,4800,rowid . . .
索引レンジ・スキャン
索引レンジ・スキャンは、条件に索引の1つ以上の先頭列が指定された索引の順序付きスキャンで、索引キーには0、1またはそれ以上の値を指定できます。
条件は、1つ以上の式および論理(ブール)演算子の組合せを指定したものです。これにより、TRUE
、FALSE
またはUNKNOWN
の値が戻されます。
データベースでは一般的に、索引レンジ・スキャンを選択的なデータへのアクセスに使用します。選択性は、問合せで選択される表の中の行の割合で、0は行がないことを表し、1はすべての行を表します。選択性は、問合せの述語(WHERE last_name LIKE 'A%'
など)や述語の組合せに関連します。述語は、値が0に近付くほど選択性が高くなり、値が1に近付くほど選択性が低く(非選択性が高く)なります。
たとえば、姓がA
で始まる従業員を問い合せるとします。last_name
列に索引が作成されており、次のエントリがあるとします。
Abel,rowid
Ande,rowid
Atkinson,rowid
Austin,rowid
Austin,rowid
Baer,rowid
.
.
.
last_name
列が述語に指定されており、それぞれの索引キーに複数のROWIDが考えられるため、データベースではレンジ・スキャンを使用できます。たとえば、Austinという姓の従業員が2人いた場合、2つのROWIDがキーAustin
に関連付けられます。
索引レンジ・スキャンは、10から40のIDを持つ部門の問合せのように、両側に境界がある場合と、40より大きいIDの問合せのように片側のみに境界がある場合があります。索引をスキャンするには、データベースではリーフ・ブロックを後方または前方に移動します。たとえば、10から40のIDを求めるスキャンでは、最小キー値10以上を含む最初の索引リーフ・ブロックが検索されます。次に、40より大きい値が見つかるまで、スキャンはリンクされたリーフ・ノード・リストを水平に進みます。
索引の一意スキャン
索引レンジ・スキャンとは対照的に、索引の一意スキャンでは、索引キーに関連付けられたROWIDは0個または1個である必要があります。
データベースでは、述語が等価演算子を使用して一意
索引のキーのすべての列を参照する場合に、一意スキャンが実行されます。索引の一意スキャンでは、2つ目のレコードがないことがわかっているため、最初のレコードが見つかるとただちに処理が停止します。
例として、ユーザーが次の問合せを実行するとします。
SELECT *
FROM employees
WHERE employee_id = 5;
employee_id
列が主キーで、次のようなエントリで索引が作成されているとします。
1,rowid
2,rowid
4,rowid
5,rowid
6,rowid
.
.
.
この場合、データベースでは、索引の一意スキャンを使用してIDが5の従業員のROWIDが検索されます。
索引スキップ・スキャン
索引スキップ・スキャンでは、コンポジット索引の論理副索引を使用します。個別の索引を検索する場合と同様に、データベースでは単一の索引をスキップします。
コンポジット索引の先頭列に含まれる個別値が少数であり、索引の先頭以外のキーに多数の個別値がある場合は、スキップ・スキャンが有効です。コンポジット索引の先頭列が問合せの述語に指定されていない場合、データベースでは、索引スキップ・スキャンを選択できます。
例3-3 コンポジット索引のスキップ・スキャン
sh.customers
表の顧客に対して次の問合せを実行するとします。
SELECT * FROM sh.customers WHERE cust_email = 'Abbey@company.example.com';
customers
表にはcust_gender
という列があり、この列の値はM
またはF
です。列(cust_gender
、cust_email
)にコンポジット索引が存在するとします。次の例は、索引エントリの一部を示しています。
F,Wolf@company.example.com,rowid F,Wolsey@company.example.com,rowid F,Wood@company.example.com,rowid F,Woodman@company.example.com,rowid F,Yang@company.example.com,rowid F,Zimmerman@company.example.com,rowid M,Abbassi@company.example.com,rowid M,Abbey@company.example.com,rowid
cust_gender
はWHERE
句に指定されていませんが、データベースでは、この索引のスキップ・スキャンを使用できます。
スキップ・スキャンでは、論理副索引の数は、先頭列の個別値の数によって決定されます。前述の例では、先頭列に2つの可能な値があります。データベースでは、索引を、キーF
を持つ1つの副索引と、キーM
を持つ2つ目の副索引に論理的に分割します。
電子メールがAbbey@company.example.com
の顧客のレコードを検索する場合、最初に値F
を持つ副索引が検索され、次に値M
を持つ副索引が検索されます。その結果、データベースでは問合せが次のように処理されます。
SELECT * FROM sh.customers WHERE cust_gender = 'F' AND cust_email = 'Abbey@company.example.com' UNION ALL SELECT * FROM sh.customers WHERE cust_gender = 'M' AND cust_email = 'Abbey@company.example.com';
関連項目:
スキップ・スキャンの詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
索引クラスタ化係数
索引クラスタ化係数は、姓などの索引付きの値を対象に、行の順序性を測定した指標です。順序性がよいほど、クラスタ化係数は小さくなります。
クラスタ化係数は、索引を使用した表全体の読取りに必要な入出力回数のおおよその目安として使用できます。
-
クラスタ化係数が高いほど、大規模な索引のレンジ・スキャン実行時の入出力回数が増えます。索引エントリはランダムな表ブロックを指しているため、データベースは索引が示すデータを取得するために、同じブロックの読取りが何回も必要になることがあります。
-
クラスタ化係数が低いほど、大規模な索引のレンジ・スキャン実行時の入出力回数が減ります。同じ範囲の中にある索引キーは同じブロックを指していることが多いため、データベースは同じブロックを何度も読み取る必要がありません。
クラスタ化係数は、索引スキャンと深い関連性があります。これは、クラスタ係数で次の事項を判断できるからです。
-
データベースが大規模なレンジ・スキャンに索引を使用するかどうか
-
索引キーに対する表の編成の度合い
-
索引キーに従って行を順序付ける必要がある場合に、索引構成表、パーティション化、表クラスタのいずれを使用するのがよいか
例3-4 クラスタ化係数
employees
表が2つのデータ・ブロックに分かれているとします。表3-3は、2つのデータ・ブロック内の行を示しています(省略記号は省略したデータを示します)。
表3-3 Employees表内にある2つのデータ・ブロックの内容
データ・ブロック1 | データ・ブロック2 |
---|---|
|
|
行は姓(太字の部分)の順序に従ってブロックに格納されています。たとえば、データ・ブロック1は、最後の行から先頭に向ってAbel、Andeと続き、先頭のSteven Kingまでアルファベット順に並んでいます。データ・ブロック2は、最後の行から先頭に向ってKochar、Kumarと続き、先頭のZlotkeyまでアルファベット順に並んでいます。
姓の列に索引が付けられているとします。それぞれの名前エントリが、ROWIDに相当します。索引エントリは概念的には次のようになります。
Abel,block1row1
Ande,block1row2
Atkinson,block1row3
Austin,block1row4
Baer,block1row5
.
.
.
社員IDの列に別の索引が付けられているとします。索引エントリは概念的には次のようになり、社員IDは、2つのブロック全体にわたり、ほぼランダムな場所に分散しています。
100,block1row50
101,block2row1
102,block1row9
103,block2row19
104,block2row39
105,block1row4
.
.
.
次の文では、ALL_INDEXES
ビューを問い合せて、次の2つの索引のクラスタ化係数を検索します。
SQL> SELECT INDEX_NAME, CLUSTERING_FACTOR
2 FROM ALL_INDEXES
3 WHERE INDEX_NAME IN ('EMP_NAME_IX','EMP_EMP_ID_PK');
INDEX_NAME CLUSTERING_FACTOR
-------------------- -----------------
EMP_EMP_ID_PK 19
EMP_NAME_IX 2
EMP_NAME_IX
のクラスタ化係数は低く、同じリーフ・ブロック内の隣接する索引エントリは、同じデータ・ブロック内の行を指す傾向があります。EMP_EMP_ID_PK
のクラスタ化係数は高く、同じリーフ・ブロック内の隣接する索引エントリが、同じデータ・ブロック内の行を指すことはほとんどありません。
関連項目:
ALL_INDEXES
の詳細は、『Oracle Databaseリファレンス』を参照してください。
逆キー索引
逆キー索引はBツリー索引の一種であり、列の順序は保ちながら、各索引キーのバイトを物理的に逆にします。
たとえば、索引キーが20
であり、このキーに対して標準Bツリー索引で格納される16進数の2バイトがC1,15
である場合、逆キー索引では、バイトが15,C1
として格納されます。
キーを逆にすると、Bツリー索引の右側にあるリーフ・ブロックの競合の問題が解決されます。この問題は、複数のインスタンスが同じブロックを繰り返し変更するOracle Real Application Clusters(Oracle RAC)データベースで特に深刻です。たとえば、orders
表では、オーダーの主キーは順次キーです。クラスタ内の1つのインスタンスが注文20を追加し、別のインスタンスがオーダー21を追加し、各インスタンスが索引の右側にある同じリーフ・ブロックにキーを書き込みます。
逆キー索引では、バイト順を逆にすることにより、挿入が索引のすべてのリーフ・キー全体に分散されます。たとえば、標準キー索引では隣接する20や21などのキーは、離れた別々のブロックに格納されます。したがって、順次キーの挿入のためのI/Oは、より等分に分散されます。
索引のデータは格納されるときに列キーでソートされないため、逆キー配列を使用すると、場合によっては索引レンジ・スキャン問合せを実行できなくなります。たとえば、20より大きいオーダーIDを求める問合せを発行した場合、データベースではこのIDを含むブロックから開始できず、リーフ・ブロックが水平に処理されます。
昇順索引と降順索引
昇順索引では、Oracle Databaseのデータは昇順に格納されます。デフォルトでは、文字データは値の各バイトに含まれるバイナリ値に従って順序付けられ、数値データは最小の数から最大の数へ、日付は古い値から新しい値への順になります。
昇順索引の例として、次のSQL文を考えてみます。
CREATE INDEX emp_deptid_ix ON hr.employees(department_id);
Oracle Databaseでは、hr.employees
表をdepartment_id
列でソートします。department_id
および対応するROWID値を、0
から始まる昇順で昇順索引にロードします。索引を使用するとき、Oracle Databaseでは、ソートしたdepartment_id
値が検索され、関連付けられたROWIDを使用して要求されたdepartment_id
値を持つ行が検索されます。
CREATE INDEX
文にDESC
キーワードを指定すると、降順索引を作成できます。この場合、索引は指定された列(1つまたは複数)にデータを降順に格納します。表3-3のemployees.department_id
列の索引が降順である場合、250
を含むリーフ・ブロックがツリーの左側となり、0
のブロックが右側になります。降順索引でのデフォルトの検索は、最大値から最小値への順です。
降順索引は、問合せで一部の列を昇順、他の列を降順でソートする場合に有効です。たとえば、last_name
列およびdepartment_id
列に対して、次のようにコンポジット索引を作成したとします。
CREATE INDEX emp_name_dpt_ix ON hr.employees(last_name ASC, department_id DESC);
ユーザーがhr.employees
に対して、昇順(AからZへ)の姓と降順(大きいIDから小さいIDへ)の部門IDを問い合せる場合、データベースではこの索引を使用してデータを検索するため、データをソートする余分な手順が不要になります。
関連項目:
-
昇順および降順の索引検索の詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
-
CREATE INDEX
のASC
およびDESC
オプションの詳細は、『Oracle Database SQL言語リファレンス』 を参照してください
索引の圧縮
索引の領域を削減するために、Oracle Databaseでは様々な圧縮アルゴリズムを使用できます。
接頭辞圧縮
Oracle Databaseでは、接頭辞圧縮(キー圧縮とも呼ばれます)を使用して、Bツリー索引または索引構成表の主キー列値の一部を圧縮できます。接頭辞圧縮によって、索引に使用される領域が大幅に減少します。
圧縮されていない索引エントリには、1つの要素があります。接頭辞圧縮を使用する索引エントリには、グループ化要素である接頭辞エントリと、一意要素またはほぼ一意要素である接尾辞エントリの2つの要素があります。データベースでは、圧縮するために、接頭辞エントリが索引ブロック内の接尾辞エントリ間で共有されます。
注意:
一意要素を持つようにキーを定義しなければ、データベースによってグループ化要素にROWIDが追加され、一意要素が提供されます。
デフォルトでは、一意索引の接頭辞は最後のキー列を除くすべてのキー列で構成され、非一意索引の接頭辞はすべてのキー列で構成されます。次のように、oe.orders
表の2つの列に、コンポジット、一意索引を作成するとします。
CREATE UNIQUE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status );
前述の例では、索引キーはonline,0
です。ROWIDはエントリのキー・データ部分に格納され、キー自体の一部ではありません。
注意:
1つの列に一意索引を作成した場合、共通の接頭辞が存在しないため、Oracle Databaseでは接頭辞キー圧縮を使用できません。
一方、同じ列に非一意索引を作成するとします。
CREATE INDEX orders_mod_stat_ix ON orders ( order_mode, order_status );
また、order_mode
列およびorder_status
列で繰返し値が発生するとします。索引ブロックに、次の例に示すエントリがあるとします。
online,0,AAAPvCAAFAAAAFaAAa online,0,AAAPvCAAFAAAAFaAAg online,0,AAAPvCAAFAAAAFaAAl online,2,AAAPvCAAFAAAAFaAAm online,3,AAAPvCAAFAAAAFaAAq online,3,AAAPvCAAFAAAAFaAAt
前述の例では、キー接頭辞は、online,0
と同様にorder_mode
とorder_status
の値を連結した値で構成されます。AAAPvCAAFAAAAFaAAa
と同様に、接尾辞はROWIDにあります。ROWID自体がデータベース内で一意であるため、ROWIDによって索引エントリ全体が一意になります。
前述の例の索引がデフォルトの接頭辞圧縮によって作成された場合(COMPRESS
キーワードで指定された)、online
,0
やonline
,3
のような重複するキー接頭辞が圧縮されます。その結果、データベースでは次のように圧縮されます。
online,0 AAAPvCAAFAAAAFaAAa AAAPvCAAFAAAAFaAAg AAAPvCAAFAAAAFaAAl online,2 AAAPvCAAFAAAAFaAAm online,3 AAAPvCAAFAAAAFaAAq AAAPvCAAFAAAAFaAAt
接尾辞エントリ(ROWID)は、索引行の圧縮版を形成します。各接尾辞エントリは、接尾辞と同じ索引ブロックに格納されている接頭辞エントリを参照します。
別の方法として、接頭辞圧縮を使用する索引を作成するときに接頭辞の長さを指定できます。たとえば、COMPRESS 1
と指定した場合、接頭辞はorder_mode
となり、接尾辞はorder_status,rowid
となります。索引ブロックの例の値では、索引によって接頭辞online
の重複した発生が除外されており、これは、概念上次のように表すことができます。
online 0,AAAPvCAAFAAAAFaAAa 0,AAAPvCAAFAAAAFaAAg 0,AAAPvCAAFAAAAFaAAl 2,AAAPvCAAFAAAAFaAAm 3,AAAPvCAAFAAAAFaAAq 3,AAAPvCAAFAAAAFaAAt
索引では、特定の接頭辞がリーフ・ブロックごとに1回のみ格納されます。圧縮されるのは、Bツリー索引のリーフ・ブロックのキーのみです。ブランチ・ブロックの場合、キーの接尾辞は切り捨てることができますが、キーは圧縮されません。
関連項目:
-
圧縮型索引の使用方法の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
パーティション索引で接頭辞圧縮を使用する方法の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照してください。
-
CREATE INDEX
のkey_compression
句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
拡張索引圧縮
Oracle Database 12cリリース1 (12.1.0.2)以降、拡張圧縮索引により、ヒープ構成表でサポートされる索引の従来の接頭辞圧縮が改善されました。
拡張索引圧縮の利点
接頭辞圧縮は、サポートされる索引のタイプ、圧縮率および使い勝手に関して制限があります。すべてのブロックに固定重複キー排除を使用する接頭辞圧縮とは異なり、拡張索引圧縮ではブロックごとに適応重複キー排除を使用します。拡張索引圧縮の主な利点は次のとおりです。
-
データベースは、列内レベル接頭辞、重複キー排除、ROWID圧縮などのアルゴリズムを使用して、各ブロックの最適な圧縮を自動的に選択します。接頭辞圧縮とは異なり、拡張索引圧縮ではユーザーがデータの特性を知る必要はありません。
-
高度圧縮は、非一意索引と一意索引の両方で動作します。接頭辞圧縮は一部の非一意索引で適切に動作しますが、先頭列に繰り返しが少ない索引では圧縮率が低くなります。
-
圧縮された索引は、圧縮されていない索引と同じように使用できます。索引は、同じアクセス・パス(一意キー検索、レンジ・スキャン、高速全スキャン)をサポートします。
-
索引は、親表または格納されている表領域から拡張圧縮を継承できます。
拡張索引圧縮の動作
拡張索引圧縮は、ブロック・レベルに作用し、各ブロックを最適に圧縮できます。データベースでは次の方法が使用されます。
-
索引作成時に、リーフ・ブロックが一杯になると、データベースでは自動的にブロックを最適レベルに圧縮します。
-
DMLの結果として索引ブロックを再編成する際に、データベースで受信索引エントリのための十分な領域を作成できる場合、ブロック分割は行われません。ただし、拡張索引圧縮を使用しない場合、DML中にブロックが一杯になると、索引ブロックの分割が常に行われます。
拡張索引圧縮HIGH
Oracle Database 12cリリース2 (12.2)より前のリリースでは、拡張索引圧縮の唯一のフォームは低圧縮(COMPRESS ADVANCED LOW
)でした。現在は高圧縮(COMPRESS ADVANCED HIGH
)も指定できるようになり、これはデフォルトでもあります。HIGH
オプションによる拡張索引圧縮には、次の利点があります。
-
ほとんどの場合、圧縮率が高くなり、索引にアクセスする問合せのパフォーマンスも向上します
-
拡張低より複雑な圧縮アルゴリズムを採用します
-
特殊なディスク上形式である圧縮単位でデータを格納します
注意:
HIGH
圧縮を適用すると、すべてのブロックが圧縮されます。LOW
圧縮を適用すると、データベースでは一部のブロックが非圧縮のままになることがあります。どの程度のブロックが非圧縮のままになっているかは、統計を使用して特定できます。
例3-5 拡張高圧縮を使用した索引の作成
この例はhr.employees
表の索引に対する拡張索引圧縮を使用可能にします。
CREATE INDEX hr.emp_mndp_ix
ON hr.employees(manager_id, department_id)
COMPRESS ADVANCED;
次の問合せにより、圧縮のタイプが示されます。
SELECT COMPRESSION FROM DBA_INDEXES WHERE INDEX_NAME ='EMP_MNDP_IX';
COMPRESSION
-------------
ADVANCED HIGH
関連項目:
-
拡張索引圧縮を有効化する方法については、『Oracle Database管理者ガイド』を参照してください。
-
CREATE INDEX
のkey_compression
句の詳細は、『Oracle Database SQL言語リファレンス』を参照してください -
ALL_INDEXES
の詳細は、『Oracle Databaseリファレンス』を参照してください。
ビットマップ索引の概要
ビットマップ索引では、各索引キーのビットマップがデータベースに格納されます。従来型のBツリー索引では、1つの索引エントリは単一の行を指します。ビットマップ索引では、各索引キーに複数の行へのポインタが格納されます。
ビットマップ索引は、主にデータ・ウェアハウス用または問合せが多数の列を非定型方式で参照する環境用に設計されています。ビットマップ索引が適している状況として、次のような状況があります。
-
索引付けされた列は、カーディナリティが低く、表の行数に比べて個別値の数が少なくなります。
-
索引付けされた表は、読取り専用であるか、DML文による重要な変更の対象外です。
データ・ウェアハウスの例として、sh.customer
表にcust_gender
列があるとすると、この列に指定される値は、M
とF
の2つのみです。特定の性別の顧客の数に対する問合せが一般的だとします。このような場合、customer.cust_gender
列はビットマップ索引の候補になります。
ビットマップ内の各ビットは、使用可能なROWIDに対応しています。ビットが設定されている場合は、対応するROWIDを持つ行にはキー値が含まれます。マッピング機能がビット位置を実際のROWIDに変換するため、別の内部表現が使用されていても、ビットマップ索引はBツリー索引と同じ機能を提供します。
単一の行の索引付けされた列が更新された場合、データベースによって、更新された行にマッピングされた個別のビットではなく索引キー・エントリ(たとえば、MまたはF)がロック
されます。キーは多数の行を指すため、索引付きデータに対するDMLは、通常、これらの行をすべてロックします。このため、ビットマップ索引は多くのOLTPアプリケーションに適していません。
関連項目:
-
パフォーマンスのためにビットマップ索引を使用する方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
-
データ・ウェアハウスでビットマップ索引を使用する方法の詳細は、『Oracle Databaseデータ・ウェアハウス・ガイド』を参照してください。
例: 単一表でのビットマップ索引
次の例では、sh.customers
表のいくつかの列がビットマップ索引の候補になっています。
次の問合せについて考えてみます。
SQL> SELECT cust_id, cust_last_name, cust_marital_status, cust_gender
2 FROM sh.customers
3 WHERE ROWNUM < 8 ORDER BY cust_id;
CUST_ID CUST_LAST_ CUST_MAR C
---------- ---------- -------- -
1 Kessel M
2 Koch F
3 Emmerson M
4 Hardy M
5 Gowen M
6 Charles single F
7 Ingram single F
7 rows selected.
cust_marital_status
およびcust_gender
列のカーディナリティは低く、cust_id
およびcust_last_name
のカーディナリティは低くありません。このため、ビットマップ索引はcust_marital_status
およびcust_gender
に適切といえます。その他の列には、ビットマップ索引は効果がありません。この場合は、一意のBツリー索引を使用する方が、表示と検索が効率的になります。
表3-4に、前述の例に示したcust_gender
列の出力に対するビットマップ索引を示します。これは、各性別に対応する2つのビットマップからなります。
表3-4 1つの列のサンプル・ビットマップ
値 | 行1 | 行2 | 行3 | 行4 | 行5 | 行6 | 行7 |
---|---|---|---|---|---|---|---|
|
1 |
0 |
1 |
1 |
1 |
0 |
0 |
|
0 |
1 |
0 |
0 |
0 |
1 |
1 |
マッピング関数はビットマップの各ビットをcustomers
表のROWIDに変換します。各ビットの値は、表内の対応する行の値に依存しています。たとえば、customers
表の最初の行で性別がM
であるため、M
値のビットマップには、最初のビットとして1
が含まれます。ビットマップcust_gender='M'
の行2、6、および7のビットは、これらの行が値としてM
を含まないため、0
になります。
注意:
Bツリー索引とは異なり、ビットマップ索引は、完全にNULL値からなるキーを含む場合があります。NULLの索引作成は、集計関数COUNT
による問合せなど、一部のSQL文に使用できます。
顧客の人口統計を調べているアナリストが、女性顧客のうち独身者または離婚者はどれくらいいるかを尋ねてきたとします。この質問は、次のSQL問合せで表現できます。
SELECT COUNT(*)
FROM customers
WHERE cust_gender = 'F'
AND cust_marital_status IN ('single', 'divorced');
ビットマップ索引を使用すると、表3-5に示すように、結果のビットマップから1
の値の数を数え、この問合せを効率よく処理できます。基準に該当する顧客を識別するときは、Oracle Databaseでは結果のビットマップを使用して表にアクセスできます。
表3-5 2つの列のサンプル・ビットマップ
値 | 行1 | 行2 | 行3 | 行4 | 行5 | 行6 | 行7 |
---|---|---|---|---|---|---|---|
|
1 |
0 |
1 |
1 |
1 |
0 |
0 |
|
0 |
1 |
0 |
0 |
0 |
1 |
1 |
|
0 |
0 |
0 |
0 |
0 |
1 |
1 |
|
0 |
0 |
0 |
0 |
0 |
0 |
0 |
|
0 |
0 |
0 |
0 |
0 |
1 |
1 |
ビットマップ索引は、WHERE
句に指定されたいくつかの条件に対応する索引を効率的にマージします。すべての条件ではなく一部の条件のみを満たす行は、表自体がアクセスされる前に除外されます。これによってレスポンス時間が短縮されます(多くの場合は大幅に改善されます)。
ビットマップ結合索引
ビットマップ結合索引は、2つ以上の表を結合するためのビットマップ索引です。
索引は、表列の値ごとに、対応する行のROWIDを索引付きの表に格納します。対照的に、標準ビットマップ索引は単一表に対して作成されます。
ビットマップ結合索引は、制限を事前に実行するため、結合するデータのボリュームを削減する効率的な方法です。ビットマップ結合索引が役に立つ場合の例として、ユーザーが特定の職種の従業員数を頻繁に問い合せるとします。一般的な問合せは次のようになります。
SELECT COUNT(*)
FROM employees, jobs
WHERE employees.job_id = jobs.job_id
AND jobs.job_title = 'Accountant';
この問合せでは、通常、jobs.job_title
の索引を使用してAccountant
の行を検索し、次にjob_idの行を検索し、employees.job_id
の索引を使用して一致する行を検索します。表のスキャンではなく索引自体からデータを取り出すには、次のようにビットマップ結合索引を作成できます。
CREATE BITMAP INDEX employees_bm_idx
ON employees (jobs.job_title)
FROM employees, jobs
WHERE employees.job_id = jobs.job_id;
次の図に示すように、索引キーはjobs.job_title
であり、索引付きの表はemployees
です。
概念的には、次の問合せ(サンプル出力を示しています)に示すSQL問合せのjobs.title
列の索引はemployees_bm_idx
です。索引のjob_title
キーは、employees
表の行を指します。経理担当者の数を求める問合せでは、索引を使用すると、索引自体に要求された情報が含まれるため、employees
およびjobs
表へのアクセスを回避できます。
SELECT jobs.job_title AS "jobs.job_title", employees.rowid AS "employees.rowid"
FROM employees, jobs
WHERE employees.job_id = jobs.job_id
ORDER BY job_title;
jobs.job_title employees.rowid
----------------------------------- ------------------
Accountant AAAQNKAAFAAAABSAAL
Accountant AAAQNKAAFAAAABSAAN
Accountant AAAQNKAAFAAAABSAAM
Accountant AAAQNKAAFAAAABSAAJ
Accountant AAAQNKAAFAAAABSAAK
Accounting Manager AAAQNKAAFAAAABTAAH
Administration Assistant AAAQNKAAFAAAABTAAC
Administration Vice President AAAQNKAAFAAAABSAAC
Administration Vice President AAAQNKAAFAAAABSAAB
.
.
.
データ・ウェアハウスでは、結合条件は、ディメンション表の主キー列とファクト表の外部キー列の間の等価結合(等価演算子を使用する)になります。ビットマップ結合索引は、格納に関して、事前に結合をマテリアライズするマテリアライズド結合ビューよりもはるかに効率的な場合があります。
関連項目:
ビットマップ結合索引の詳細は、Oracle Databaseデータ・ウェアハウス・ガイドを参照してください
ビットマップ記憶域構造
Oracle Databaseでは、Bツリー索引構造を使用して各索引付きキーのビットマップを格納します。
たとえば、jobs.job_title
がビットマップ索引のキー列である場合、1つのBツリーに索引データが格納されます。リーフ・ブロックには個々のビットマップが格納されます。
例3-6 ビットマップ記憶域の例
jobs.job_title
列に一意の値であるShipping Clerk
、Stock Clerk
、およびその他いくつかの値があるとします。この索引のビットマップ索引エントリには、次のコンポーネントがあります。
-
索引キーとしての職種
-
ROWIDの範囲として小さいROWIDと大きいROWID
-
範囲内の特定のROWIDのビットマップ
その結果、この索引の索引リーフ・ブロックには次のようなエントリが含まれる場合があります。
Shipping Clerk,AAAPzRAAFAAAABSABQ,AAAPzRAAFAAAABSABZ,0010000100
Shipping Clerk,AAAPzRAAFAAAABSABa,AAAPzRAAFAAAABSABh,010010
Stock Clerk,AAAPzRAAFAAAABSAAa,AAAPzRAAFAAAABSAAc,1001001100
Stock Clerk,AAAPzRAAFAAAABSAAd,AAAPzRAAFAAAABSAAt,0101001001
Stock Clerk,AAAPzRAAFAAAABSAAu,AAAPzRAAFAAAABSABz,100001
.
.
.
ROWID範囲が異なるため、同じ職種が複数エントリに出現します。
セッションで、1人の従業員のジョブIDをShipping Clerk
からStock Clerk
に更新します。このような場合、セッションは古い値(Shipping Clerk
)と新しい値(Stock Clerk
)の索引キー・エントリに排他的にアクセスする必要があります。Oracle Databaseでは、UPDATE
がコミットされるまで、これら2つのエントリで指し示された行がロックされます(ただし、Accountant
またはその他のキーで指し示された行はロックされません)。
ビットマップ索引のデータは1つのセグメント.に格納されます。Oracle Databaseでは、各ビットマップを1つ以上の断片に格納します。各断片は単一データ・ブロックの一部を占めます。
関連項目:
「ユーザー・セグメント」では、様々なセグメントのタイプと、セグメントの作成方法について説明します
ファンクション索引の概要
ファンクション索引では、1つ以上の列を含むファンクションや式の値が計算され、索引に格納されます。ファンクション索引はBツリー索引またはビットマップ索引のいずれかです。
索引付きファンクションは、算術式、あるいはSQL関数、ユーザー定義のPL/SQLファンクション、パッケージ・ファンクションまたはCコールアウトを含む式のいずれかです。たとえば、ファンクションでは2つの列の値を加算できます。
関連項目:
-
ファンクション索引の作成方法の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
ファンクション索引の使用方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
-
ファンクション索引の制約および使用上の注意は、『Oracle Database SQL言語リファレンス』を参照してください
ファンクション索引の使用方法
ファンクション索引は、WHERE
句にファンクションを含む文を効率的に評価します。データベースでは、ファンクションが問合せに含まれている場合にのみ、ファンクション索引を使用します。ただし、INSERT
文とUPDATE
文の処理中には、データベースでは文を処理するために従来どおりファンクションを評価する必要があります。
例3-7 算術式に基づく索引
たとえば、次のようなファンクション索引を作成したとします。
CREATE INDEX emp_total_sal_idx ON employees (12 * salary * commission_pct, salary, commission_pct);
データベースでは、次(部分的なサンプル出力を示しています)に示すような問合せを処理するときに、この索引を使用できます。
SELECT employee_id, last_name, first_name, 12*salary*commission_pct AS "ANNUAL SAL" FROM employees WHERE (12 * salary * commission_pct) < 30000 ORDER BY "ANNUAL SAL" DESC; EMPLOYEE_ID LAST_NAME FIRST_NAME ANNUAL SAL ----------- ------------------------- -------------------- ---------- 159 Smith Lindsey 28800 151 Bernstein David 28500 152 Hall Peter 27000 160 Doran Louise 27000 175 Hutton Alyssa 26400 149 Zlotkey Eleni 25200 169 Bloom Harrison 24000
例3-8 UPPERファンクションに基づく索引
SQLファンクションUPPER(
column_name
)
またはLOWER(
column_name
)
でファンクション索引を定義すると、大文字/小文字を区別しない検索が容易になります。たとえば、employees
のfirst_name
列に大文字/小文字が含まれるとします。hr.employees
表に次のようなファンクション索引を作成します。
CREATE INDEX emp_fname_uppercase_idx ON employees ( UPPER(first_name) );
emp_fname_uppercase_idx
索引によって、次のような問合せが容易になります。
SELECT * FROM employees WHERE UPPER(first_name) = 'AUDREY';
例3-9 表の特定の行の索引付け
ファンクション索引は、表の特定の行のみに索引を付ける場合にも役に立ちます。たとえば、sh.customers
表のcust_valid
列には、値としてI
またはA
があるとします。A
の行のみに索引を付けるには、A
以外のすべての行に対してNULL値を戻すファンクションを作成できます。次のような索引を作成できます。
CREATE INDEX cust_valid_idx ON customers ( CASE cust_valid WHEN 'A' THEN 'A' END );
関連項目:
-
言語索引の詳細は、『Oracle Databaseグローバリゼーション・サポート・ガイド』を参照してください。
-
SQL関数の詳細は、『Oracle Database SQL言語リファレンス』を参照してください
ファンクション索引による最適化
WHERE
句に式を含む問合せの場合、オプティマイザでは、ファンクション索引に対する索引レンジ・スキャンを使用できます。
範囲スキャンのアクセス・パスは、述語の選択性が高い場合、つまり選択される行が比較的少ない場合に特に効果を発揮します。例3-7では、式12*salary*commission_pct
の索引が作成されていれば、オプティマイザは索引レンジ・スキャンを使用できます。
また、仮想列は、式から導出されるデータへのアクセスを高速化するために役立ちます。たとえば、仮想列annual_sal
を12*salary*commission_pct
として定義し、annual_sal
にファンクション索引を作成できます。
オプティマイザは、SQL文の式を解析し、文の式ツリーとファンクション索引を比較して、式のマッチングを実行します。この比較は大文字/小文字が区別され、ブランクは無視されます。
関連項目:
-
統計の収集の詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
-
表への仮想列の追加方法の詳細は、Oracle Database管理者ガイドを参照してください。
アプリケーション・ドメイン索引の概要
アプリケーション・ドメイン索引は、アプリケーション固有のカスタマイズされた索引です。
拡張索引作成機能を使用すると、次のことが可能です。
-
カスタマイズされた複合データ型(文書、空間データ、イメージ、ビデオ・クリップなど)の索引への対応(「非構造化データ」を参照)
-
特殊な索引作成方法の利用
アプリケーション固有の索引管理ルーチンを索引タイプ・スキーマ・オブジェクトとしてカプセル化し、オブジェクト型の表の列や属性のドメイン索引を定義できます。拡張索引作成機能により、アプリケーション固有の演算子を効率的に処理できます。
ドメイン索引の構造と内容は、 カートリッジと呼ばれるアプリケーション・ソフトウェアによって制御されます。データベースは、ドメイン索引の作成、メンテナンス、および検索のために、このアプリケーションと対話します。索引構造そのものは、索引構成表としてデータベースに格納するか、ファイルとして外部に格納できます。
関連項目:
Oracle Databaseの拡張性アーキテクチャ内のデータ・カートリッジの使用方法の詳細は、『Oracle Databaseデータ・カートリッジ開発者ガイド』を参照してください。
索引構成表の概要
索引構成表は、一種のBツリー索引構造に格納される表です。一方、ヒープ構成表では、行は収まる位置に挿入されます。
索引構成表では、行は表の主キーに定義された索引に格納されます。Bツリーの各索引エントリには、非キー列値も格納されます。したがって、索引はデータであり、データは索引です。アプリケーションは、ヒープ構成表と同じように、SQL文を使用して索引構成表を操作します。
たとえば、索引構成表について、人事管理マネージャが複数の段ボール箱を棚に置いて管理しているとします。それぞれの箱には1、2、3、4というように番号のラベルが付いていますが、箱は棚の中で順番に並んではいません。それぞれの箱には、次の箱が棚のどの位置にあるかを示すポインタがあります。
それぞれの箱には従業員レコードが入っているフォルダが収納されています。フォルダは従業員IDによってソートされています。従業員KingのIDは100で、それが最小のIDであるため、Kingのフォルダは箱1の一番下にあります。従業員101のフォルダは100の上、102は101の上というように、箱1がいっぱいになるまでフォルダが収められています。この並びにおける次のフォルダは、箱2の一番下になります。
この例の場合、フォルダを従業員IDで並べると、専用の索引をメンテナンスすることなく、フォルダを効率的に検索できます。ユーザーが従業員107、120、および122のレコードを要求していると想定します。1つの手順で索引を検索し、別の手順でフォルダを取り出すかわりに、マネージャは、フォルダを順番に検索し、個々のフォルダを見つかり次第取り出すことができます。
索引構成表は、主キーまたはそのキーの有効な接頭辞の使用により、表の行に対するアクセスを高速化します。リーフ・ブロックの行に非キー列が存在することにより、追加のデータ・ブロックI/Oが回避されます。たとえば、従業員100の給与は索引行自体に格納されます。さらに、行が主キーの順序で格納されるため、主キーまたは接頭辞によるレンジ・アクセスでは最小限のブロックI/Oが行われます。もう1つの利点は、個別の主キー索引の空間オーバーヘッドが回避されることです。
索引構成表は、関連したデータ断片をまとめて格納する必要がある場合や、データを特定の順序で物理的に格納する必要がある場合に役立ちます。通常、このタイプの表は情報取得、空間データおよびOLAPアプリケーションに使用されます。
関連項目:
-
「OLAP」
-
索引構成表の管理方法の詳細は、『Oracle Database管理者ガイド』を参照してください。
-
索引構成表を使用してパフォーマンスを向上させる方法の詳細は、Oracle Database SQLチューニング・ガイドを参照してください。
-
CREATE TABLE ... ORGANIZATION INDEX
の構文およびセマンティクスについては、Oracle Database SQL言語リファレンス を参照してください
索引構成表の特性
データベース・システムは、Bツリー索引構造を操作することで、索引構成表に対するすべての操作を実行します。
次の表は、索引構成表とヒープ構成表の違いをまとめたものです。
表3-6 ヒープ構成表と索引構成表の比較
ヒープ構成表 | 索引構成表 |
---|---|
ROWIDが行を一意に識別します。主キー制約はオプションで定義できます。 |
主キーが行を一意に識別します。主キー制約を定義する必要があります。 |
|
|
ROWIDによって個々の行に直接アクセスできます。 |
個々の行には、主キーによって間接的にアクセスします。 |
順次全表スキャンは、特定の順序ですべての行を戻します。 |
全索引スキャンまたは高速全索引スキャンは、特定の順序ですべての行を戻します。 |
他の表とともに表クラスタに格納できます。 |
表クラスタには格納できません。 |
|
LOB列は格納できますが、 |
仮想列を格納できます(サポートされているのはリレーショナル・ヒープ表のみです)。 |
仮想列は格納できません。 |
図3-3に、索引構成表departments
の構造を示します。リーフ・ブロックには表の行が主キーの順序に従って格納されています。たとえば、最初のリーフ・ブロックの最初の値は、部門ID 20
、部門名Marketing
、マネージャID 201
、および場所ID 1800
を示しています。
例3-10 索引構成表のスキャン
索引構成表では、すべてのデータが同じ構造に格納され、ROWIDを格納する必要がありません。図3-3に示すように、索引構成表のリーフ・ブロック1には、主キーの順序に従った次のようなエントリが含まれます。
20,Marketing,201,1800
30,Purchasing,114,1700
索引構成表のリーフ・ブロック2には次のようなエントリが含まれます。
50,Shipping,121,1500
60,IT,103,1400
主キーの順序になっている索引構成表の行をスキャンすると、ブロックが次の順序で読み取られます。
-
ブロック1
-
ブロック2
例3-11 ヒープ構成表のスキャン
ヒープ構成表でのデータ・アクセスを索引構成表でのデータ・アクセスと比較するため、ヒープ構成表departments
のセグメントのブロック1に、次のように行が格納されているとします。
50,Shipping,121,1500
20,Marketing,201,1800
ブロック2には同じ表の行が次のように格納されています。
30,Purchasing,114,1700
60,IT,103,1400
このヒープ構成表のBツリー索引リーフ・ブロックには、次のエントリが格納されています(最初の値は主キーであり、2つ目の値はROWIDです)。
20,AAAPeXAAFAAAAAyAAD
30,AAAPeXAAFAAAAAyAAA
50,AAAPeXAAFAAAAAyAAC
60,AAAPeXAAFAAAAAyAAB
主キーの順序になっている表の行をスキャンすると、表セグメント・ブロックが次の順序で読み取られます。
-
ブロック1
-
ブロック2
-
ブロック1
-
ブロック2
したがって、この例のブロックI/Oの数は、索引構成の例での数の倍になります。
関連項目:
-
「表の編成」
(ヒープ構成表の詳細) - (セグメントとデータ・ブロックの関係の詳細)
行オーバーフロー領域付きの索引構成表
索引構成表を作成するとき、別のセグメントを行オーバーフロー領域として指定できます。
索引構成表では、行全体を格納しているBツリー索引エントリは大きくなる場合があり、エントリを格納する別のセグメントが役立ちます。これに対し、Bツリー・エントリはキーとROWIDで構成されるため、通常は小さくなります。
行オーバーフロー領域を指定した場合、データベースでは索引構成表の行を次の部分に分割できます。
-
索引エントリ
この部分には、主キー列すべての列値、行のオーバーフロー部分を指す物理ROWID、およびオプションで一部の非キー列が含まれます。この部分は索引セグメントに格納されます。
-
オーバーフロー部分
この部分には、残りの非キー列の列値が含まれます。この部分はオーバーフロー記憶域セグメントに格納されます。
関連項目:
-
CREATE TABLE
のOVERFLOW
句を使用して行オーバーフロー領域を設定する方法の詳細は、『Oracle Database管理者ガイド』を参照してください -
CREATE TABLE ... OVERFLOW
の構文およびセマンティクスについては、『Oracle Database SQL言語リファレンス』を参照してください
索引構成表の2次索引
2次索引は、索引構成表の索引です。
ある意味では、2次索引は索引の索引です。これは独立したスキーマ・オブジェクトであり、索引構成表とは別に格納されます。
Oracle Databaseでは、索引構成表に対して論理ROWIDと呼ばれる行識別子を使用します。論理ROWIDは、表の主キーをbase64でエンコードした表現です。論理ROWIDの長さは、主キーの長さに応じて異なります。
索引リーフ・ブロック内の行は、挿入によってブロック内またはブロック間で移動できます。索引構成表の行は、ヒープ構成の行のように移行しません。索引構成表の行には永続的な物理アドレスがないため、データベースでは主キーに基づいて論理ROWIDを使用します。
たとえば、departments
表が索引構成表であるとします。location_id
列には各部門のIDが格納されます。表には次のように行が格納され、最後の値が場所IDになります。
10,Administration,200,1700
20,Marketing,201,1800
30,Purchasing,114,1700
40,Human Resources,203,2400
location_id
列の2次索引には、次のような索引エントリが含まれる場合があります。カンマに続く値が論理ROWIDです。
1700,*BAFAJqoCwR/+
1700,*BAFAJqoCwQv+
1800,*BAFAJqoCwRX+
2400,*BAFAJqoCwSn+
2次索引は、主キーでも主キーの接頭辞でもない列を使用して、索引構成表への高速で効率的なアクセスを可能にします。たとえば、IDが1700より大きい部門名の問合せでは、2次索引を使用してデータ・アクセスを高速化できます。
関連項目:
-
ROWIDの使用および
ROWID
疑似列の詳細は、「ROWIDデータ型」を参照 - (行の移行の理由と、移行によって入出力回数が増える理由の詳細)
-
索引構成表に2次索引を作成する方法の詳細は、『Oracle Database管理者ガイド』を参照
-
索引構成表パーティションに2次索引を作成する方法の詳細は、『Oracle Database VLDBおよびパーティショニング・ガイド』を参照
論理ROWIDと物理推測
2次索引では論理ROWIDを使用して表の行を検索します。
論理ROWIDには、索引エントリの最初の作成時の物理ROWIDである物理推測が含まれます。Oracle Databaseは、物理推測を使用して、主キー検索をバイパスし、索引構成表のリーフ・ブロックを直接検証できます。行の物理位置が変化した場合、論理ROWIDは失効した物理推測が含まれていても引き続き有効です。
ヒープ構成表の場合、2次索引によるアクセスでは、行を含むデータ・ブロックをフェッチするために、2次索引のスキャンと付加的なI/Oが必要になります。索引構成表の場合、2次索引によるアクセスは、次のように物理推測を使用するかどうかと、その正確さに応じて異なります。
-
物理推測を使用しない場合、アクセスでは2次索引のスキャンとその後に主キー索引のスキャンという、2つの索引スキャンが行われます。
-
物理推測を使用する場合は、アクセスは物理推測の正確さに応じて異なります。
-
正確な物理推測を使用する場合、アクセス時には、2次索引スキャンと追加のI/Oによって行を含むデータ・ブロックがフェッチされます。
-
不正確な物理推測を使用する場合、アクセス時には2次索引スキャンとI/Oによって間違ったデータ・ブロック(物理推測が示すブロック)がフェッチされてから、主キー値による索引構成表の索引一意スキャンが実行されます。
-
索引構成表のビットマップ索引
索引構成表の2次索引は、ビットマップ索引にできます。ビットマップ索引では、各索引キーのビットマップが格納されます。
索引構成表にビットマップ索引が存在する場合、すべてのビットマップ索引はヒープ構成のマッピング表を使用します。マッピング表には索引構成表の論理ROWIDが格納されます。マッピング表の各行には対応する索引構成表の行の論理ROWIDが格納されます。
データベースでは、検索キーを使用してビットマップ索引にアクセスします。キーが見つかると、ビットマップのエントリは物理ROWIDに変換されます。ヒープ構成表の場合、この物理ROWIDは実表へのアクセスに使用されます。索引構成表の場合、この物理ROWIDはマッピング表へのアクセスに使用され、それにより、索引構成表へのアクセスに使用される論理ROWIDが取得されます。次の図に、departments_iot
表の問合せ用の索引アクセスを示します。
注意:
索引構成表で行を移動すると、その索引構成表に作成されたビットマップ索引の使用禁止状態が変化します。
関連項目:
物理ROWIDと論理ROWIDの相違点の詳細は、「行断片のROWID」を参照