Re: INDEXING large tables?

Joe, You are right in your suspicions. As I'm sure you know, Informix spawns many internal sort files during an index build. In general, it distributes the sort files fairly well throughout the temp dbspaces listed in DBSPACETEMP. However, if it chooses to put sort file N in temp dbspace X, and that temp space fills up, it will not break that sort file to another temp space. Not even if there is sufficient room in other temp spaces. Of the top of my head, your options seem to be: 1) Have larger temp spaces. But don't forget the 2*2 equals 1*4. If you reduce the total number of temp dbspaces, Informix is going to have to put those files in the remaining ones. 2) Consider a larger number of fragments for the index. That way it will generate more sort files but should distribute them a little more evenly. We have a 50+ million row table with ~150 bytes per row in 12 fragments. With 6 temp spaces of 800 MB each, this builds okay. 3) Evaluate the uniqueness of the first element of the key. If there are few values for the first element of the key, then the sort files are an attempt to sort the secondary keys. I don't know how well Informix will split these, but personal experience tells me there is something not quite efficient going on. Consider re-ordering the keys so that the first element has a high percent of uniqueness. I hope this helps. I read your last book a couple years ago, good luck with the next one. Chuck Ludwigsen Joe Lumbley <jlumbley@netcom.com> wrote in article <jlumbleyEDorBq.6v8@netcom.com>... > > > 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 Lumbley(jlumbley@netcom.com) author of: "INFORMIX DBA Survival Guide" > > Slaving away on "INFORMIX DEBUGGERS Survival Guide", available early 1998 > from Prentice Hall/Informix Press. If you have debugging tips, tools, or > ideas to share, please send me some e-mail. > --------------------------------------------------------------------------- >