- 追加された行はこの色です。
- 削除された行はこの色です。
reorgはデータベース内の不要な領域除去やデータの再配置、インデックスの再作成によりパフォーマンスの向上を行う。
:統計情報|オプティマイザーがデータに到達するために最適なアクセスパスを判断する元となる情報
#contentsx
***データベースの統計情報の更新および統計情報の確認 [#r849b955]
reorgが必要なテーブルは、reorgchkの結果欄、REORGに「*」が表示される。
db2 => reorgchk update statistics on table all
db2 => reorgchk current statistics on table all
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
SCHEMA NAME CARD OV NP FP ACTBLK TSIZE F1 F2 F3 REORG
----------------------------------------------------------------------------------------
Table: DBNAME.TABLENAME
DBNAME TABLENAME 2.7e+07 0 4e+05 4e+05 - 2.42e+08 0 13 100 -*-
Table: SCHEMA.TABLENAME
SCHEMA TABLENAME 2.7e+07 0 4e+05 4e+05 - 2.42e+08 0 13 100 -*-
〜中略〜
Table: SYSIBM.SYSXMLSTATS
SYSIBM SYSXMLSTATS - - - - - - - - - ---
----------------------------------------------------------------------------------------
Index statistics:
F4: CLUSTERRATIO or normalized CLUSTERFACTOR > 80
F5: 100 * (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) / ((NLEAF - NUM EMPTY LEAFS -1) * (INDEXPAGESIZE - 96) > MIN(50, (100- PCTFREE))
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (indexPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
SCHEMA NAME CARD LEAF ELEAF LVLS ISIZE NDEL KEYS F4 F5 F6 F7 F8 REORG
-------------------------------------------------------------------------------------------------
Table: SCHEMA.TABLE
SYSIBM SQL080811073550260 3e+07 2e+05 0 4 20 0 3e+07 56 87 5 0 0 *----
〜中略〜
Table: SYSIBM.SYSXMLSTATS
SYSIBM IBM184 - - - - - - - - - - - - -----
-------------------------------------------------------------------------------------------------
CLUSTERRATIO or normalized CLUSTERFACTOR (F4) will indicate REORG is necessary
for indexes that are not in the same sequence as the base table. When multiple
indexes are defined on a table, one or more indexes may be flagged as needing
REORG. Specify the most important index for REORG sequencing.
Tables defined using the ORGANIZE BY clause and the corresponding dimension
indexes have a '*' suffix to their names. The cardinality of a dimension index
is equal to the Active blocks statistic of the table.
***tableのreorg [#he12305d]
db2 => reorg table SCHEMA.TABLENAME use 利用するテンポラリテーブルスペース名
DB20000I The REORG command completed successfully.
***INDEXの再生成 [#cd28386f]
db2 => reorg indexes all for table SCHEMA.TABLENAME
DB20000I The REORG command completed successfully.
***統計情報の再収集 [#ve74310e]
db2 => runstats on table SCHEMA.TABLENAME on key columns and indexes all
DB20000I The RUNSTATS command completed successfully.
***再バインドの実施 [#c5281681]
$ db2rbind DBNAME -l LOGFILE ALL
Rebind done successfully for database 'DBNAME'.
----
#counter
