Heavily fragmented indexes

The database I am loading is suffering a huge amount of index fragmentation in spite of all the data fitting comfortably into one extent per table. The problem seems to be exacerbated by the schema having a large number of VARCHARS, such that the size of the data (and hence the extent sizes I have chosen) is far less than the extent size dbimport would have calculated - it seems IDS's index extent sizing is much too small. What is the best practise in such a situation? I thought of giving each table or index its own dbspace such that index extents would mostly just append, but I'm reluctant to do that given the number of tables involved. Any suggestions? Thanks Andy Kent

It's 10.0 on Redhat with RAID 1+0 SANs by the way. We can't go to 11 yet because of application certification issues.

ANDY KENT wrote: > The database I am loading is suffering a huge amount of index fragmentation in > spite of all the data fitting comfortably into one extent per table. The > problem seems to be exacerbated by the schema having a large number of > VARCHARS, such that the size of the data (and hence the extent sizes I have > chosen) is far less than the extent size dbimport would have calculated - it > seems IDS's index extent sizing is much too small. > Index extent sizing is based on a ratio of the keysize to the rowsize times the table's extent sizing. Your problem is that you had to reduce the presumed extent sizing of the table during the load because dbexport overstates the extent sizing for tables with VARCHARs if the columns mostly contain less than maximum length data. However, when you index a VARCHAR (not a good thing to do BTW) the engine stores the maximum length of the column in the key padded with spaces, so the calculated extent size of the index based on your reduced table extents was too small for the expanded keys. Nothing you can do for now except rebuild the indexes so that they are contiguous in one or a few extents. You don't need one dbspace per index, just a few dbspaces. If you rebuild each index into a clean dbspace one at a time they will be initially contiguous. To reduce future fragmentation set the FILLFACTOR lower than the default of 90% for indexes that are expected to grow. Then they can grow across the initially allocated extent(s) rather than adding new ones. Art S. Kagel Oninit > What is the best practise in such a situation? I thought of giving each table > or index its own dbspace such that index extents would mostly just append, but > I'm reluctant to do that given the number of tables involved. Any suggestions? > > Thanks > Andy Kent > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========

On the face of it the FILLFACTOR option sounds like a good one, but won't that just cause a whole lot more extents with pages (say) 20% full once more data is added to the table? It's a migration of an existing database so it's easy enough for us to get it to append the index extents at load time. It's what happens after the initial build and go-live that is the concern. Andy

ANDY KENT wrote: > On the face of it the FILLFACTOR option sounds like a good one, but won't that > just cause a whole lot more extents with pages (say) 20% full once more data > is added to the table? > The key is to build the indexes in a relatively clean dbspace one at a time so that any additional extents are compressed by the engine into one or a few much larger extents. The FILLFACTOR will prevent or at least delay the adding of more extents later because the engine will use the empty space within the existing index pages first followed by using empty pages in the existing extent(s) before extending the index further. > It's a migration of an existing database so it's easy enough for us to get it > to append the index extents at load time. It's what happens after the initial > build and go-live that is the concern. > Right, hence the lower FILLFACTOR to initially create the indexes with enough empty space on each page to accomodate growth. The index will initially be slightly less efficient than it otherwise would by, but over time it will not lose efficiency as the number of keys grows because new pages will not be added as frequently and so the index pages will have more/better locality to each other for range scans. I see from your post today that you are missing something about how FILLFACTOR works, I'll reply there.... Stay tuned. Art S. Kagel > Andy > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > See you at the IIUG Informix 2008 Conference > The Power Conference for Informix Professionals > April 27 - 30, 2008 Marriott Overland Park (Kansas City), Kansas > http://www.iiug.org/conf > Registration Now Open!! > > ================================================================================ =========== > Please access the attached hyperlink for an important electronic communications disclaimer: > > http://www.oninit.com/home/disclaimer.php > > ================================================================================ =========== > > > ================================================================================ =========== Please access the attached hyperlink for an important electronic communications disclaimer: http://www.oninit.com/home/disclaimer.php ================================================================================ ===========