レコードの挿入・更新・削除を繰り返していくと、エクステントやブロックの使用効率が低下し、ディスク領域を圧迫したり、パフォーマンスに悪影響を及ぼしたりします。
そのため、無駄な領域を確保しているテーブルは再編成して領域使用効率を改善したほうがよいということになります。
テーブルがどれだけの領域を使っていて、どれだけ空いていて、本当はどれだけ必要なのかを調べるにはテーブルの統計情報を利用します。
テーブルの統計情報はUSER_TABLES、DBA_TABLES、ALL_TABLESのいずれかのテーブルを検索すると参照することができます。
以下にSCOTTが所有するテーブルの統計情報を取得するSQLの例を示します。
(権限の都合上、sysユーザーで実行）
SELECT
TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN,
BLOCKS,
EMPTY_BLOCKS,
CASE AVG_ROW_LEN
WHEN null THEN null
WHEN 0 THEN 0
ELSE ( CEIL( NUM_ROWS / TRUNC( ( ( BLOCK_SIZE - KCBH - UB4 - KTBBH
- ( INI_TRANS - 1 ) * KTBIT - KDBH )
* ( 100 - PCT_FREE ) / 100 - KDBT ) / AVG_ROW_LEN ) ) + 1 )
END EST_BLOCKS
FROM DBA_TABLES
,(SELECT TYPE_SIZE KCBH FROM V$TYPE_SIZE WHERE TYPE = 'KCBH')
,(SELECT TYPE_SIZE UB4 FROM V$TYPE_SIZE WHERE TYPE = 'UB4')
,(SELECT TYPE_SIZE KTBBH FROM V$TYPE_SIZE WHERE TYPE = 'KTBBH')
,(SELECT TYPE_SIZE KTBIT FROM V$TYPE_SIZE WHERE TYPE = 'KTBIT')
,(SELECT TYPE_SIZE KDBH FROM V$TYPE_SIZE WHERE TYPE = 'KDBH')
,(SELECT TYPE_SIZE KDBT FROM V$TYPE_SIZE WHERE TYPE = 'KDBT')
,(SELECT VALUE BLOCK_SIZE FROM V$PARAMETER WHERE NAME = 'db_block_size')
WHERE OWNER = 'SCOTT'
ORDER BY BLOCKS DESC;
TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS EST_BLOCKS
-------------------- ---------- ----------- ---------- ------------ ----------
DEPT 4 23 60 68 2
EMP 12 41 60 68 2
SALGRADE 5 13 60 68 2
BONUS 0 0 0 128 0
- NUM_ROWS
- 行数
- AVG_ROW_LEN
- 平均行サイズ
- BLOCKS
- 使用ブロック数
- EMPTY_BLOCKS
- 空きブロック数
- EST_BLOCKS
- 行数と平均行サイズから算出した推定必要ブロック数。 ※あくまでも推定なので多少の誤差は生じます。
なお、推定必要ブロック数を算出するには、V$TYPE_SIZEやV$PARAMETERから算出に必要な値を取得しなければなりませんが、上記のように実行時に直接取得すると検索に非常に時間がかかります。
そのため、プラットフォームが決まっている場合やブロックサイズを統一している場合は、以下のようにこれらの値を決め撃ちにして、さらに先に計算できるところはしておいたほうが検索が速くなっていいでしょう。
SELECT
TABLE_NAME,
NUM_ROWS,
AVG_ROW_LEN,
BLOCKS,
EMPTY_BLOCKS,
CASE AVG_ROW_LEN
WHEN null THEN null
WHEN 0 THEN 0
ELSE ( CEIL( NUM_ROWS / TRUNC( ( ( 8106 - ( INI_TRANS - 1 ) * 24 )
* ( 1 - PCT_FREE / 100 ) - 4 ) / AVG_ROW_LEN ) ) + 1 )
END EST_BLOCKS
FROM DBA_TABLES
WHERE OWNER = 'SCOTT'
ORDER BY BLOCKS DESC;
TABLE_NAME NUM_ROWS AVG_ROW_LEN BLOCKS EMPTY_BLOCKS EST_BLOCKS
-------------------- ---------- ----------- ---------- ------------ ----------
DEPT 4 23 60 68 2
EMP 12 41 60 68 2
SALGRADE 5 13 60 68 2
BONUS 0 0 0 128 0
このようにして算出した推定必要ブロック数が使用ブロック数よりも著しく小さい場合は、領域が無駄に使われている可能性が高いため、再編成の候補となります。
ただ、よほどシビアなシステムでない限りは、使用ブロック数が小さければそれほど気にしなくてもいいかもしれません。
システム特性に応じて再編成の目安とするしきい値を設定し、それを上回ったり、下回ったりしたら再編成するなどのルールを決めて運用するのがいいでしょう。