Re: INDEXING large tables?

Joe Lumbley wrote: > > Has anyone been able to successfully index **very** > large tables in OnLine 7.22 (or better). > > I've had a support call into Lenexa for better than 2 weeks > with a system that is essentially down, and Informix doesn't > seem to know what to do to fix it. > > Situation: > > table: 381 million rows (35 bytes wide) fragmented by expression > (using a mod function) into 5 dbspaces. > > index: 3 integers......total size 3x4 + 4 = 16 bytes/row > > temp space: 20 gigabytes. > > Running the index has blown up with "out of sort space" about 15 > times. What I think is happening is: each temp space chunk is 2 gig, > which is the Informix size limit. When the index is sorting it tries > to write to one of the many "buckets" in the temp space (I have 10 of > these 2-gig chunks) > > When it gets close to the end of the index, it hits the limit on one > of the chunks (2gig) and blows up, even though there is additional > temp space in other chunks. Tell me this isn't true!!!! > > If it is true, that means that Informix has an intrinsic limit of > table sizes that it can index. I'm testing this theory now by taking > my 10 2-gig temp chunks and turning them into 5 4-gig temp spaces, > each with 2 chunks of 2 gig apiece. I'm hoping that Informix will > continue to write into the > second chunk of each tempspace. I'm afraid that Informix is looking > for contiguous space and will continue to fail. So my question is... > has anybody actually built **big** indexes as opposed to starting > small and indexing as you go? > > Hopefully wrong, > Joe Joe, if it is barfing at the end of the chunk and ignoring the remaining 18G of space in the TEMP-DBspace, it sounds like a bug. I see some others have suggested using ten 1-chunk DBspaces. Certainly worth a try. I am skeptical because we don't know what it mis-reacted to at the end of the chunk. But I'd try that before the following: It occured to me that you might use: ALTER FRAGMENT ON TABLE whatever whatever_part2;and so on, 4 times until you have detached all 5 fragments as separate tables. Create the necessary index for each table. Hopefully these will be small enough not to barf at the end of a chunk. ALTER FRAGMENT ON TABLE whatever ATTACH whatever whatever_part2 AS (original fragmentation clause for fragment-2). # # # # ##### ##### # # ### # # # # # # # # # # ### # # # # # # # # ### # # # # # ####### # # # # # # # # # # # # # # # # # ### # ##### ##### ##### # # ### After all that, it occurs to me: Are you trying to create the index in a single fragment? Or are you allowing the index to fragment according the same expression as the data? My "solution" (I use the term loosely ;-) attempts to accomplish the same result as the latter, which makes me skeptical about this also. But, as Mr. Spock discovered, sometimes an act of desperation is the only logical action. If the multiple DBspaces in the PSORT_DBTEMP fails, try it. Please post your results! You can later publish it in your next book, "How to Work Around Inevitable Bugs and Actually Get Home to Sleep". -- -- Jake (In pursuit of undomesticated aquatic avians) +----------------------------------------------------------+ |Aside from that, how did you enjoy the play, Mrs. Lincoln?| +----------------------------------------------------------+