Setting the PCTFREE and FREEPAGE for your indexes depends upon how much insert and delete activity is going to occur against those indexes. For indexes that have little or no inserts and deletes, you could use a small PCTFREE with no free pages.
Note: Updates that change key columns are actually inserts and deletes.
For indexes with heavy changes, consider larger amounts of free space. Adding free space may increase the number of index levels, and then increase the amount of I/O for random reads. If you do not have enough free space, you might experience an increased frequency of index page splits. When DB2 splits a page, it looks for a free page in which to place one of the split pages. If it does not find a page nearby, it searches the index for a free page. For large indexes, this search could lead to CPU and locking problems. We recommend that you set a predictive PCTFREE that anticipates growth over a period such that you do not split pages. You should then monitor the frequency of page splits to determine when to REORG the index or establish a regular REORG policy for that index.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|