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? Interesting. Have you considered using PSORT_DBTEMP? Okay, okay. I only asked. :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com FAQ http://www.iiug.org |///// / //| | +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |If it's slow, the users complain. |/// / ////| | Fax: +27 11 807 2594 |If it's fast, the users keep quiet.|// / /////| |Cell: +27 83 250 2325 |Therefore, "No news: travels fast"!|/ ////////| +----------------------+-----------------------------------+-----------+