Re: tbtape question

At 03:08 PM 1/3/95 CST, Dan Madvig - Bethel College & Sem. wrote: >> >> 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? Dan - The best method, provided enough disk and time, is to 'unload', then drop rebuild and 'load'. This allows you to dump data to flat files and reload. Using tbunload will do binary dumps of pages. So if you have a page with one small row on it and rest is empty, tbload will load that exact same page image. tbunload/tbload is fast, and handy, if you do not need to reduce or eliminate row level fragmentation. It will also help in reducing extents, since these new pages will be loaded contiguously into new extents which were created when table was rebuilt (these would presumably be larger extents, if necessary). There is no perfect solution for reducing fragmentation. As your DB gets large, this becomes even more difficult. I currently have a 70+GB database, and as you can imagine, this becomes an issue. Fortunately, the dynamics of the system, are the damn thing just keeps growing (FAR more inserts and updates then deletes). One thing I might also suggest if you have the luxary, is to create a new table, with the exact same schema. "insert into tab2 select from tab1", build your indexes, drop the tab1 then rename tab2 back to tab1. This obviuosly also requires disk, but not as much time. Hope this is helpful.... Jon ================================================================== Jon Vemo Internet: jvemo@cyberspace.com Bothell, WA USA ------------------------------------------------------------------ Watch out for road-kill on the information superhighway....SPLAT!! ==================================================================