Table and indexes extent size

Hi all, I get a table which have 6 indexes, each one have an integer field. This table has a right extent size and next size calculated, since it was created it has only one extent, but indexes are asking for its next extent frequently. This table is like a LOG table and it has 10000 inserts per week. Indexes were created with default "FILLFACTOR 90" I want to be sure what I have to consider, so: Is there any tip to set a FILLFACTOR value for this case? Does FILLFACTOR have performance effects or space waste? Thanks in advance!

FILLFACTOR is ONLY effective at the time an index is created. It has no effect on the growth of the index over time. Index extents are auto sized based on the tables extent sizing and the ratio of the row size to the index key length. Often this calculation doesn't quite work as IBM would like it to and indexes develop more extents than the parent table. Until IBM lets us declare explicit extent sizing for indexes, there is not much you can do about it. If you have IDS 11.50xC4 or later you can have the BTREE scanner threads compress partial index pages, that can help. Art Art S. Kagel Oninit (www.oninit.com) IIUG Board of Directors (art@iiug.org) Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on my employer, Oninit, the IIUG, nor any other organization with which I am associated either explicitly or implicitly. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Mon, Aug 3, 2009 at 6:40 PM, PAULO RAFAEL PADILLA < rafaelpadilla@grupomayan.com> wrote: > Hi all, > > I get a table which have 6 indexes, each one have an integer field. > > This table has a right extent size and next size calculated, since it was > created it has only one extent, but indexes are asking for its next extent > frequently. > > This table is like a LOG table and it has 10000 inserts per week. > Indexes were created with default "FILLFACTOR 90" > > I want to be sure what I have to consider, so: > > Is there any tip to set a FILLFACTOR value for this case? > Does FILLFACTOR have performance effects or space waste? > > Thanks in advance! > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001636c5a9bf3b4d6b0470449c2c

Thank you Art, I hope this feature arrive soon... This impact to backups highly, because when we have a L 0 backup and we're getting L 1 backups and this indexes ask for a new extent, backups become very large. Regards

I do not believe the new index extents are causing the backups to be slower. The reason for this is that IDS backups only backup pages which are between the first page of a data or index fragment and the highest used page in that fragment. It does not backup nor examine all pages which have been allocated to a fragment, but rather just the used pages associated with the fragment. Example: If you create a table with an extent size of 10 MB having a rowsize of 100 bytes. Then create an index on this table having and an index key size of 10 bytes followed by and insert just 1 row into this table, then the do a level 0 backup. This backup would only backup the one used data page, the one used index page and the bitmap pages. There would be over 9.9MB of space which the backup will not even examine (i.e. read from disk). Hope this helps, John

>>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