db2 reorg
reorg 효과
1. 데이터 정렬(cluster index)
2. 삭제된 row 및 overflow 된 row의 공간 정리
3. extent 재배치
4. pctree 확보
5. index rebuild -> index tree 최적화
reorg command
# db2 reorgchk current statistics on table SCHEMA.TABLE
# db2 reorg table SCHEMA.TABLE user TEMPTABLESPACE
# db2 reorg table SCHEMA.TABLE index SCHEMA.INDEX
# db2 runstats on table SCHEMA.TABLE with distribution and indexes all
*** CARD는 runstats 후 최신값으로 업데이트
*** clusterratio : 하나의 index를 이용해서 전체 table의 data를 access 할 때 index 순서와 data page가 얼마나 일치
(* db2 reorg indexes all for table SCHEMA.TABLE)
rerog 작업 내역 확인
# db2pd -db DBNAME -reorgs
reorg 기타
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
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, (10
0- PCTFREE))
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (ind
exPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
1. 데이터 정렬(cluster index)
2. 삭제된 row 및 overflow 된 row의 공간 정리
3. extent 재배치
4. pctree 확보
5. index rebuild -> index tree 최적화
reorg command
# db2 reorgchk current statistics on table SCHEMA.TABLE
# db2 reorg table SCHEMA.TABLE user TEMPTABLESPACE
# db2 reorg table SCHEMA.TABLE index SCHEMA.INDEX
# db2 runstats on table SCHEMA.TABLE with distribution and indexes all
*** CARD는 runstats 후 최신값으로 업데이트
*** clusterratio : 하나의 index를 이용해서 전체 table의 data를 access 할 때 index 순서와 data page가 얼마나 일치
(* db2 reorg indexes all for table SCHEMA.TABLE)
rerog 작업 내역 확인
# db2pd -db DBNAME -reorgs
reorg 기타
Table statistics:
F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80
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, (10
0- PCTFREE))
F6: (100 - PCTFREE) * ( FLOOR[ (100 - min(10, pctfree)) / 100 * (indexPageSize - 96) / (ISIZE + 12) ] ** (NLEVELS - 2) ) * (ind
exPageSize - 96) / (KEYS * (ISIZE + 9) + (CARD - KEYS) * 5) < 100
F7: 100 * (NUMRIDS DELETED / (NUMRIDS DELETED + CARD)) < 20
F8: 100 * (NUM EMPTY LEAFS / NLEAF) < 20
댓글
댓글 쓰기