Re: INDEXING large tables?

: Joe Lumbley wrote: : > : > Has anyone been able to successfully index **very** : > large tables in OnLine 7.22 (or better). OK, I'll answer my own posting. Informix can index large tables, its just that you have to have everything set just right, and it's not well-documented. The sort was failing on 381 million rows, even though I had 20 gigabytes of DBSORTSPACE, laid out as 10 dbspaces of 2 gig apiece. When I went to 5 dbspaces, each consisting of two 2-gig chunks, everything went well, and the sort completed in less than 6 hours! I noticed some really interesting items here, and maybe somebody can point us to where this may be documented (just may be a new chapter in my Debuggers Survival Guide). The PSORT creates temp files in the dbspaces. In the first scenario, 10 x 2-gig dbsortspaces, the temp files started out at 32 pages long and grew incrementally, eventually crashing the index when the total sort space usage hit about 65% after 11 hours. In the 5 x 4-gig dbspaces scenario, the files started out at 576 pages and eventually used up about 36% of my total sort dbspaces, and finished after 6 hours. In the first instance, the sort kept blowing up because it was trying to write a table into one of the 10 2-gig spaces. If that particular chunk didn't have enough space to hold the data, the index build died, even though I other sort/temp space available. The sort was smart enough to go to a second chunk in each of the 5 new tempspaces if those chunks were available. I suspect that the sensitivity to number of sort spaces relates to the number of cpus and cpuvps in the system. I had 6 cpuvps trying to use 10 sort dbspaces in the failing instance and 6 cpuvps trying to use 5 sort dbspaces in the instance that succeded. FYI, and thanks for all the response by e-mail from y'all. 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. ---------------------------------------------------------------------------