IDS Forum

Re: Table and indexes extent size

Posted By: DAVE GRIFFEN
Date: Wednesday, 5 August 2009, at 4:17 p.m.

In Response To: Table and indexes extent size (PAULO RAFAEL PADILLA)

>>Is there any tip to set a FILLFACTOR value for this case?
>>Does FILLFACTOR have performance effects or space waste?

Here are some things to consider before deciding on a FILLFACTOR for any index creation...
What is the growth rate of your table relative to current size?
Are index values for future records predictable? If they are predictable, will they be evenly distributed across current leaf pages or will they be localized to a small % of leaf pages?
If you specify FILLFACTOR 70 for an index, every index page will be left with 30% free space. If the majority of index pages will remain unchanged, then that 30% is wasted. This can also impact performance as more pages will need to be read and buffered. The more partially filled pages consuming buffers, the higher your BTR, etc.

Example 1 - An Index on a serial column where all records will be systematically assigned a value. This is the ultimate example of predictability with a singular insert point as every record will be added to the end of the index. Anything less than a FILLFACTOR 100 is a waste of space.

Example 2 - A multi part Index on a sales table (store #, sales_date, transaction_number). For any store #, I can predict that we will insert records of Aug 5 before those of Aug 6 and transaction_number 220 before transaction_number 221. So my # of insertion points into the index will be limited to the # of stores. If my index has 100,000 leaf pages and there are 1000 stores, then 99% of current leaf pages will not change. A very high FILLFACTOR will be best.
NOTE: Ongoing inserts into an index such as this can also lead to 50% wasted space as a remnant of page splits, regardless of FILLFACTOR. Periodic drop and recreates may be needed to keep wasted space to a minimum. Having an index fragment per store could also help. Although, I don't know if a 1000 index fragments is really advisable. IDS does have special split logic when the new record is a high key for the index or index fragment.

Example 3 - An index on a customer table (last_name, first_name). Predicting the names of your next 1000 customers is practically impossible. Betting that the % of John Smiths will be close to your current % of John Smiths is reasonable. Set the FILLFACTOR to accomodate your anticipated growth rate.

Example 4 - An index on a customer table (customer #). If customer # is assigned systematically, then this could be much like Example 1. If customer # = telephone #, then this is more like Example 3.

The best way to determine a good FILLFACTOR is to know your data and it's source.
Dave Griffen

Messages In This Thread

• Table and indexes extent size
  PAULO RAFAEL PADILLA -- Monday, 3 August 2009, at 6:40 p.m.
  • Re: Table and indexes extent size
    Art Kagel -- Monday, 3 August 2009, at 6:51 p.m.
    • Re: Table and indexes extent size
      PAULO RAFAEL PADILLA -- Tuesday, 4 August 2009, at 12:44 p.m.
      • Re: Table and indexes extent size
        JOHN MILLER -- Wednesday, 5 August 2009, at 2:19 p.m.
  • Re: Table and indexes extent size
    DAVE GRIFFEN -- Wednesday, 5 August 2009, at 4:17 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.