Re: INDEXING large tables?

Maybe you can trick the thing by using the following technique: 1. split the table fragments into separate tables (alter fragment on table soandso detach dbs_soandso2 soandso2 ) 2. create the index on the table soandso (with all fragmenting expressions) (now containing only the first fragment) 3. attach the remaining fragments one by one (alter fragment on table soandso attach soandso2 as frag-expression) sorry no large table/or time to test this yet. so long Andreas Zeugswetter Chuck Ludwigsen wrote: > 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. > > > - > ------------------------------------------------------------------------- > > >