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

＜続・INDEXに関する検証その１＞ペンネームつけまい＆モンキーターン

— 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

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