続・INDEXに関する検証 その1

投稿日:

<続・INDEXに関する検証 その1> ペンネーム つけまい&モンキーターン

— UPDATEとINDEXの関係 —

以前、「おら!オラ! Oracle -どっぷり検証生活-」宛に、以下の質問が寄
せられた。

教えて下さい。
INDEXとして定義された項目を、同一の値で UPDATEすると INDEXはどうなりま
すか。何も変わらないのでしょうか?それとも内部的に更新されてどこかに影
響が出るのでしょうか?

読者の方々は、どのような結果を予測されたであろうか?

1.DATA(テーブル)および INDEX共に更新(上書き)される
2.DATA(テーブル)および INDEX共に更新(上書き)されない
3.DATA(テーブル)のみが更新(上書き)され、INDEXは更新されない
4.INDEXのみが更新(上書き)され、DATA(テーブル)は更新されない

答え ⇒ 3

実データが格納されている TABLEに対しては、データブロックが変更される、
つまり同じ値に上書きされるのだが、INDEXに対しては物理ライトが発生しな
い、つまり上書きされないことが検証の結果から判明した。

それでは、今回行った検証結果を、順に紹介していこう。

— どっぷり検証生活 START! —

同一の値で UPDATEした場合、INDEXに対する変更は行われないという根拠を、
BLOCK_DUMPの中身や PHYSICAL WRITE(物理書き込み)の回数などを基に、
裏付けを行っていく。

<<<検証用テーブルとインデックスの作成>>>

1.ユーザー SCOTTで EMP_TESTと言うテーブルを作成(中身は EMP表と同じ)
2.表の項目 EMPNOに対して、EMP_TEST_INDEXと言う INDEXを作成
(INDEXは別の表領域に作成)

<<<検証に用いたSQL文>>>

SQL> UPDATE EMP_TEST SET EMPNO = 7369 WHERE EMPNO = 7369 ;
1行が更新されました。

SQL> commit ;
コミットが完了しました。

SQL> alter system checkpoint ;(alter system権限が必要)
システムが変更されました。

注:Oracleは、ユーザがコミットを行った直後はデータ・ファイルへの書き戻
し作業を行わない。あるタイミングでまとめて書き戻す「遅延書き込み」
を行っている。つまり、ある程度まとまった更新データを一度に書き戻す
ことによって、データ・ファイルに対する物理I/Oを削減しているのであ
る。そのため、検証を行う上で、書き戻しが完了していないブロック
(ダーティ・ブロック)がデータベース・バッファ上に残っていては正確
な検証結果が得られないため、チェック・ポイントを発生させる必要があ
る。ちなみに、チェック・ポイントが発生すると、データベース・バッ
ファ上のダーティ・ブロックは、すべてデータ・ファイルへ書き戻される。
なお、チェック・ポイントが発生すると、すべてのデータ・ファイルの
ヘッダーに存在するチェック・ポイント情報やSCNが更新される。

SCN = System Change Number:
トランザクションごとに、シーケンシャルに割り振られる番号。このSCN
を基に、トランザクションを再現し、最新のチェック・ポイントまでの回
復を行う。

<<<BLOCK_DUMPの取得方法>>>

テーブル EMP_TESTの項目 EMPNOを、7369から7369(同じ値)に UPDATEすると、
Oracleブロックに対して、どのような処理が施されるのであろうか。
BLOCK_DUMPを基に、確認して見よう。

注:BLOCK_DUMPは、Oracleのバージョンによって取得方法が異なるので、注意
が必要である。

<Oracle 7の場合>

1.DBA_EXTENTSからファイル番号とブロック番号を取得する

SQL> SELECT  SEGMENT_NAME,
FILE_ID,
BLOCK_ID
FROM  DBA_EXTENTS
WHERE  OWNER = 'SCOTT'
AND  SEGMENT_NAME LIKE 'EMP_TEST%' ;

SEGMENT_NAME       FILE_ID   BLOCK_ID
---------------    --------  --------
EMP_TEST           8         2
EMP_TEST_INDEX     9         2

2.10進数のデータ・ブロック・アドレスを取得する

dbms_utility.make_data_block_addressファンクションを使用することで、簡
単にデータ・ブロック・アドレスを求めることができる。

dbms_utility.make_data_block_address(<ファイル番号>,<ブロック番号>)

引数には、10進数のファイル番号とブロック番号を指定する。実行すると、10
進数のデータ・ブロック・アドレスが返される。

SQL> SELECT dbms_utility.make_data_block_address(9,3) DBA
2> FROM DUAL;

DBA
--------