インデックス設計 - lookworld @ 簡単メモ～

インデックス設計

インデックスは、アクセスパターンを基にwhere句の結合キー項目、検索キー項目、ソートキー項目にインデックスを定義します。大量の挿入処理が発生するテーブルについては検索処理とのバランスからトレードオフを考慮し、チューニングの段階で最終調整を行います。



5.3.1.容量概算手順

インデックス容量を以下の算出式に従って算出し、余裕率50%（索引は行が表から削除されても領域を開放しないため）を上乗せした値をinitialエクステントサイズとしinitialエクステントサイズの10%をnextエクステントサイズとします。また、PCTFREEは10で、手順はB-Treeベースとします。


０．前提項目　LOC_IDとLOCATION_NAMEの２列に対して、ユニーク・インデックスI_LOCを表領域INDEXに作成します。

SQL>DESC LOCATION


名前 タイプ
------------------------------- -------
LOC_ID NUMBER(10)
LOCATION_NAME CHAR(30)
PROVINCE_ID VARCHAR2(2)
LOC_TYPE VARCHAR2(2)



　

１．動的パフォーマンス表V$PARAMETERよりDB_BLOCK_SIZEを調査します。

SVRMGR> select value from v$parameter where name = 'db_block_size'; 

VALUE

------------------------------------------------------------

4096



--- ここからは机上で実施 ---

２．ブロックサイズ・ヘッダーを求めます。

ブロックサイズ・ヘッダー　＝　固定長ヘッダ　+　可変長トランザクション・ヘッダー

　　　　　　　　　　　　　　　　＝　113 + (24 * INITRANS)

＝ 113 + (24 * 2) 

＝ 161



３．使用できる領域を計算します。

(ブロックサイズ) - (ブロックサイズヘッダー)

=4096 - 161 = 3935 Byte



４．利用可能データ領域を計算します。

ceil( 3935 * (１－(10/100)))　=　3542　Byte　(切上げ)


　

５．索引行サイズ（IROWSZ）を計算します。

索引行サイズ　＝　行ヘッダー　+　バイト長を加えた索引列データ領域サイズの和


バイト長を加えた索引列データ領域サイズの和　

＝　（索引定義列値　+　オーバーヘッド）+（索引定義列値　+　オーバーヘッド）+（　…　）


オーバーヘッド＝１

＝　７　+　３１　（LOC_ID　＝　６　+　１　＝　７　、　LOCATION_NAME ＝　３０　+　１　＝　３１）

＝　３８　


索引行サイズ　（IROWSZ）

＝　（3　*　UB1）　+　３８

＝　４１Byte　



６．索引行あたりに使用される領域サイズ（RAVG）を計算します。

max (UB1 * 3 + UB4 + SB2, IROWSZ) + SB2

max (1 * 3 + 4 + 2, 41) + 2 = 49

max (9,41) + 2 = 43 Bytes



７．全体の索引値を計算します。

エントリヘッダ　+　ROWID長　+　F　+　V　+　RAVG

　=　２　+　６　+　２　+　０　+　４３

F：127B以下の列１に対して ＋１

　=　５３(RNU)


V：128B以上の列１に対して ＋１



８．ユニークインデックスなので、さらに＋１します。

　　　　　　　=　５３　＋　１　

　　　　　　　=　５４　（RUI）



９．　４で計算した値に対して、ユニークの場合は（RUI）を、非ユニークの場合は(RNU)で、ブロックあたりの平均索引エントリ数を計算します。

ユニークインデックス　　=　floor( 3542 / 54 )　　=　６５　row per data block　　　　　　　　

非ユニークインデックス　= floor( 3542 / 53 )　　=　６６　row per data block　　　　　　　　floor()：切り捨て



以上により、ブロックあたり、ユニークインデックスでは６５個、非ユニークインデックスでは６６個格納できることが分かります。


１０．　９のユニークインデックスの場合における必要なブロック数とバイト数を計算します。ここで、索引を構成する列でNullでない行が10,000件あるとします。

必要ブロック数　＝　ceil（1.05*Nullでない索引の件数　/ ブロックあたりの平均索引エントリ数）

＝　ceil (1.05 * 10000 / 65)　＝　１６２

必要バイト数　　＝　必要ブロック数　/ ブロック・サイズ

　　　　　　　　　　　＝　162　*　4096 ＝　６６３,５５２　Byte


　


5.3.2.エクステント・PCTUSEDの算出

インデックスにはテーブルと違いPCTFREEがありません。エクステントは最小・最大値を設定します。



5.3.3.効率的なインデックスの利用

インデックスを効果的に利用する方法は、「データベース開発標準　２．１　インデックスの利用」を参照します。



5.3.4.設定指針

インデックス作成における指針を示します。


・minextents = 1, maxextents =249 , pctuserd = 5とします。

・INITIAL * 1.05, NEXT(INITIAL * 1.05) *　0.1



SQL>create unique index I_LOC on LOCATION(loc_id, location_name)

　　2>tablespace INDEX 

　　3>storage(initial 643K next 68K pctincrease 0) nologging;


　

詳細については、「Oracle８Server　管理者ガイド」の付録「スキーマ・オブジェクトの領域の見積もり」を参照してください。