Re: INDEXING large tables?

Mark D. Stock wrote: > > Joe Lumbley wrote: > > > > Has anyone been able to successfully index **very** > > large tables in OnLine 7.22 (or better). > > [SNIP] > > 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!!!! > > [SNIP] > > 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 > > Interesting. Have you considered using PSORT_DBTEMP? > Yes. I have indexed 34-120 Million rows with key sizes of 20-105 bytes. Several things. Yes, Informix uses all sort-temp spaces evenly and writes out fixed sized sort-work files. This means that if any of the temp dbspaces or PSORT_DBTEMP filesystems fills the sort/index-sort fails. The sort-space with the least free space limits the size of the sort. If you have four or more filesystems with sufficient free space such that none will fill up during the sort (ie 5 with at least 4GB free each, OR 4 filesystems with at least 5GB free each) you can use PSORT_DBTEMP to force the sort to use filesystem space. If your OS does not support large filesystems and you cannot create 10-12 2GB filesystems then expanding the DBSPACETEMP dbspaces is your only option, note that each of the sort-work files is probably treated as a contiguous file so some space will be wasted at the end of each chunk so allocate more temp space than you think you need. Also note that the PSORT_MAXALLOC, PSORT_NPROCS, and PDQPRIORITY variables can have a dramatic effect on HUGE index builds. Art S. Kagel