＜続・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 -------- 37748739
注：上記の SQL文で、ブロック番号の所に「2」ではなく「3」を指定している
ことにお気付きいただけただろうか。EMP_TEST_INDEX（EMP_TEST）は、
EMP表（データ件数14レコード）を元に作成しているため、TABLEおよび
INDEXは共に1ブロックに納まっている。また、TABLEおよび INDEXの先頭
には、必ずセグメント・ヘッダー（1ブロック）が存在するため、実デー
タが格納されているのは2番目のブロック以降ということになる。した
がって、この場合実データが格納されている先頭のブロック番号「3」を
指定しなければならない。
3．BLOCK_DUMPを取得する
以下に、BLOCK_DUMPを取得するための SQL文を示す（Oracle 7の場合）
SQL> ALTER SESSION SET EVENTS 2> 'immediate trace name blockdump level 37748739' ; セッションが変更されました。
初期化パラメータ user_dump_dest で指定したディレクトリに、BLOCK_DUMPの
結果が出力される（日付が最新のファイル）。
＜＜＜Oracle 8以降の場合＞＞＞
1．DBA_EXTENTSからファイル番号とブロック番号を取得する
⇒ ＜Oracle 7の場合＞の「1．DBA_EXTENTSからファイル番号～」を参照
2.BLOCK_DUMPを取得する
以下に、BLOCK_DUMPを取得するための SQL文を示す（Oracle 8の場合）
ALTER SYSTEM DUMP DATAFILE <ファイル番号> BLOCK <ブロック番号> ;
SQL> ALTER SYSTEM DUMP DATAFILE 9 BLOCK 3 ; システムが変更されました。
初期化パラメータ user_dump_dest で指定したディレクトリに、BLOCK_DUMPの
結果が出力される（日付が最新のファイル）。
＜＜＜BLOCK_DUMPの結果＞＞＞
同一の値（7369⇒7369）で UPDATEする前のデータ・ブロック・ダンプの結果
Start dump data blocks tsn: 7 file#: 8 minblk 3 maxblk 3 buffer tsn: 7 rdba: 0x02000003 (8/3) scn:0x0000.00b5af49 seq:0x01 flg:0x02 tail:0xaf490601 ~~~~~~~~~~~~~~~~~~~（1） frmt:0x02 chkval:0x0000 type:0x06=trans data 以下省略
同一の値（7369⇒7369）で UPDATEした後のデータ・ブロック・ダンプの結果
Start dump data blocks tsn: 7 file#: 8 minblk 3 maxblk 3 buffer tsn: 7 rdba: 0x02000003 (8/3) scn:0x0000.00b5af55 seq:0x01 flg:0x02 tail:0xaf550601 ~~~~~~~~~~~~~~~~~~~（2） frmt:0x02 chkval:0x0000 type:0x06=trans data 以下省略
同一の値（7369⇒7369）で UPDATEする前の
インデックス・ブロック・ダンプの結果
Start dump data blocks tsn: 8 file#: 9 minblk 3 maxblk 3 buffer tsn: 8 rdba: 0x02400003 (9/3) scn:0x0000.00b47688 seq:0x01 flg:0x00 tail:0x76880601 ~~~~~~~~~~~~~~~~~~~（3） frmt:0x02 chkval:0x0000 type:0x06=trans data 以下省略
同一の値（7369⇒7369）で UPDATEした後の
インデックス・ブロック・ダンプの結果
Start dump data blocks tsn: 8 file#: 9 minblk 3 maxblk 3 buffer tsn: 8 rdba: 0x02400003 (9/3) scn:0x0000.00b47688 seq:0x01 flg:0x00 tail:0x76880601 ~~~~~~~~~~~~~~~~~~~（4） frmt:0x02 chkval:0x0000 type:0x06=trans data 以下省略
各ブロック・ダンプの結果のSCNに注目していただきたい。
データ・ブロック・ダンプ UPDATE前 ⇒ scn:0x0000.00b5af49（1） UPDATE後 ⇒ scn:0x0000.00b5af55（2） インデックス・ブロック・ダンプ UPDATE前 ⇒ scn:0x0000.00b47688（3） UPDATE後 ⇒ scn:0x0000.00b47688（4）
UPDATE後、「データ・ブロックの SCNは更新されているが、インデックス・ブ
ロックのSCNは更新されていない。」
この結果は、同じ値で更新した場合、データ・ブロックに対しては更新（上書
き）するが、インデックス・ブロックに対しては更新（上書き）しないという
ことを裏付ける結果と言えよう（実データが格納されている部分のダンプを見
ただけでは、上書きされたか否かを確認することはできない）。
実際に、REDOログ・ファイルのダンプを取得して中身を確認してみたが、やは
りインデックス・ブロックに関する更新履歴は存在しなかった（チェック・ポ
イント発生時の、ヘッダーに対する SCNの更新は UPDATE処理とは無関係であ
る）。
＜＜＜PHYSICAL WRITEの結果＞＞＞
では次に、各データ・ファイル（データ、インデックス）に対して、どれだけ
の PHYSICAL WRITE（物理書き込み）が発生したかを確認してみよう。
SQL> SELECT SEGMENT_NAME, FILE_ID, FROM DBA_EXTENTS WHERE OWNER = 'SCOTT' AND SEGMENT_NAME LIKE 'EMP_TEST%' ; SEGMENT_NAME FILE_ID --------------- -------- EMP_TEST 8 EMP_TEST_INDEX 9
データ・ブロックに対する物理書き込みブロック数を調べるSQL
SQL> SELECT PHYBLKWRT FROM V$FILESTAT WHERE FILE# = 8 ;
インデックス・ブロックに対する物理書き込みブロック数を調べるSQL
SQL> SELECT PHYBLKWRT FROM V$FILESTAT WHERE FILE# = 9 ;
上記の SQL文の結果は累計値となっているので、UPDATE文の前後で SQL文を発
行し、差分を求める必要がある。
結果：データ・ブロック = 2 BLOCK WRITE
インデックス・ブロック = 1 BLOCK WRITE
内訳を説明すると、チェック・ポイント時のヘッダーに対する書き込みが、そ
れぞれ1ブロックずつ、UPDATEによる書き込みが、データ・ブロックに対して
のみ1ブロック行われている。この結果からも、インデックス・ブロックに対
する更新処理が行われていない様子が伺える。
次回は、INDEXとして定義された項目が、異なる値で更新された場合の UPDATE
と INDEXの関係について、検証を交えながら説明する。
どのような結果になるか、読者の方々もお盆休みを利用して、考えて（検証）
みてはいかかであろうか。
＜余談＞
INDEXは使い方によっては、何倍ものパフォーマンスを得ることができるが、
使い方を間違えると意味が無くなるだけでなく、パフォーマンスを劣化させて
しまう原因にもつながる恐れがある。
しかし、DBAの運用次第では、すばらしい武器になり得る事は事実である。
頭とINDEXは使いよう！
猛暑 茅ヶ崎にて
～続・INDEXに関する検証 その１～
by つけまい＆モンキーターン
