Re: tbtape question

In article <3ecoac$okv@emory.mathcs.emory.edu> naomi@anasazi.com (Naomi Walker) writes: >} >} > >} > At 09:53 PM 12/23/94 -0500, Borg Boy wrote: >} > >Question: Will unloading a database using tbunload, then dropping it, and >} > >loading it back in using tbload defrag the tables? >} > > >} Jon Vemo sez: >} > NO. tbunload performs a binary unload of each page, page for page. Any >} > fragmentation which existed before, will still be there after tbload'ing. >} > >} >} I thought 'tbunload,drop,tbload' was one of the sanctioned methods for >} defragging disc space. What does one use? >} > >tbload/tbload does re-extent tables. What it doesn't do is shrink tables >that have had many rows deleted. For that you have to unload/dbload the >table. > > >-- > Naomi Walker (aka N7FSA) | naomi@anasazi.com > | Phoenix, Arizona > ----------------------------------------------------------------- > Dyslexsics, Untie! | Visualize Whirled Peas Tbload/unload does NOT re-extent tables. If you examine the firstsize, nextsize in systables you will see they are the same as they were. What one of the respondents mentioned, that the reloading is done into contiguous space, is correct. So if you have a table, you offload it, you drop it, you reload it, and IT GOES BACK EXACTLY WHERE IT WAS you gain nothing. If on the other hand you wind up getting gobs of free space and it goes there, this is what (I think - any Informix personnel want to comment) happens. Your contiguous extents are rolled up into 1, with a little bit of data, some index, some data, in the same binary pattern they had before. I have situations where the problem is growing tables, but they are large, 400+ meg, and having lots of little pieces isnt particularly expensive from the disk i/o point of view, since you can search 10 40 meg pieces about as well as 1 400 meg piece, by the time you factor in the complexity of the search and the unix scheduler and the disk speed. What appears to be expensive is that for the first 8 extents you have pointers to disk cached somewhere in shared memory, probably in the table table. After that you seem to have to read disk to follow the forward pointers to get to the rest of data and/or index. We have been tbunloading multiple files at the same time, dropping and cleaning up the dbspaces, and then reloading them, with positive results. I also timed the different methods. Tbunload/tbload is 13 times faster than unload/load from (or dbload, which is much more robust). Jonathan Leffler or Dave Kosenko or any other Informix lurkers - any truth to my guess or am I off the mark ?