RE: Index full-same problem/diff situation

By any chance, did you change the "NEXT SIZE" on the fragmented table *after* you built the index? We ran into a bug (sorry, don't remember the bug #) that caused this scenario to result in an index extent *equal* in size to the data extent, instead of being proportional. You can use "oncheck -pe" to see if your index extents are the same size as your data extents. I believe the bug was fixed in 7.24.UC6 or some such. The workaround was to drop and re-create the index *after* you have changed the "NEXT SIZE". HTH ================================ Paul A. Mosser, Open Systems DBA Informix Certified DBSA Wells Fargo & Co. Tempe, Arizona paul.mosser@wellsfargo.com ================================ -----Original Message----- From: Angela Neal [mailto:angela.neal@attws.com] Sent: Monday, May 10, 1999 4:45 PM To: Mark Collins Cc: informix-list@iiug.org Subject: Re: Index full-same problem/diff situation Thanks for the quick response! There are approx 45 load jobs running every hour. Composit key is based on an 2 ID#s, Date and Time stamps. Both extents per dbspace are fully used. The index uses not only its initally allocated space but also grabs all initial FREE space in the chunks. When I calculated the extent sizes I tried to make both the data and index extent fit proportionately into a single chunk, so each chunk would contain the appropriate number of data & index pages. This has worked for other fragmented tables and tables in their own dbspace. Given the way data is loaded into this table, could your suggestion of decreasing the fill factor make a difference, even when the index is created before data is loaded? Mark Collins wrote: > > 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. -- Angela Neal Database Administration AT&T Wireless Services Inc. 425.580.4373 (desk) 425.580.2485 (fax)