Re: INDEXING large tables?

Joe Lumbley wrote: > > : Joe Lumbley wrote: > : > > : > Has anyone been able to successfully index **very** > : > large tables in OnLine 7.22 (or better). Oops, that was the problem. I'm on 7.13.UC1. Sorry. > 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! So the newer versions of Informix can handle multiple chunks within a temp dbspaces? That's good news . . . our tables aren't getting any smaller <G>. > 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. Would this have something to do with the amount of data being manipulated? Trying to move large chunks of (index) data around and not finding enough room? John Carlson WH Smith, Inc. Again, these ideas and questions are my own, not necessarily that of my company.