続・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
--------
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に関する検証 その1~
by つけまい&モンキーターン