Re: Index full-same problem/diff situation

First a few questions. It appears that you are inserting data into your table with a batch load process. Is that correct? If so, is this a once-a-week load, or do you perform several loads, like maybe one an hour? You state that you are have two chunks per dbspace, and that the table and index fill the first chunk. How large is the second chunk? Does the index (or table) grab an extent in that second chunk? It should. I suspect that your index is filling because your data is being loaded in random order, relative to the key. Because you have created the index prior to loading the table, each insert creates an index entry immediately. As more rows are loaded, the index pages begin to split as the B+ tree grows to accomodate the additional keys. When you specified an extent size for the table and then created the index, Informix gave the index an extent which is about the same ratio to the table extent as the length of the key is to the length of the row, based on whatever fillfactor was in effect. Because the B+ tree splits reduce the number of keys per page, you run out of index space. You may be able to increase the size of the index extent by lowering the fillfactor. If this is a once-a-week load, then there are two options to pursue. One is to create the table, load the data, then create the index(es). This is the only real option if you have more than one index. If there is only one index, you could sort the data in ascending key order prior to loading the table. Informix has an (undocumented) index behavior that prevents splitting an index page if the new row to be inserted is higher than any other key in the database. This works great for serial columns and indexes built on date/time inserted. If this is a frequent load process, then the only thing I could recommend (based on the above assumptions) is to add space. This is also true if the data is being inserted by several users concurrently. > Hi, > > Just read the other threads about full indexes. My scenario's a bit > different and I'd appreciate some input on how to deal with this > situation. > > I have a table fragmented across multiple dbspaces (1 wk data/dbspace). > Each dbspace has 2 chunks. Table was sized so that a single data and its > index extent claimed almost the entire chunk. The problem is that the > index pages allocated (ex:48652 pgs) are completely consumed way before > the data pages are used (ex:178203 free pgs). This in turn causes data > loads to fail. The number of rows actually loaded into the same number > of index pages also differs from week to week. > > When I rebuild the index after it decides its full, a large number of > pages are free'd up and I can continue loading my data. I have already > allocated additional space and the problem persists, just leaving more > data pages empty. Rebuilding the index weekly or allocating more space > aren't very appealing options. > > Any suggestions?? Mark Collins mcollins@us.dhl.com The truth shall set you free, but a good lie will keep you out of jail in the first place.