Re: Detaching Indexes

"Michael Hoffman" <mrh@panix.com> wrote in message news:c06c0p$90u$1@reader2.panix.com... > Thanks Art & Malc! I expected that to be the case, so before posting these > numbers, I onunloaded & onloaded the database. In the past, we've used > this method to reorg the entire database in one fell swoop. In this case, > the numbers did not change much at all. No, this is beacuse onunload/onload simply writes out and reads back the binary pages. So if you had a page made up, say, 50% of data and 50% of now-unused index space, that's what you'll end up with after onunload/onload too, because the internal strucute of each page is unaffacted. However, a rebuild of the tables through alter fragment or some other metod *will* re-block the data, and your space will be freed up.

Thanks Neil. I've used the unonload / onload to defragment tables, which usually leads to freeing up unused space due to deleted rows. I've run both unload / load and "alter index to cluster" to reorg most of the tables. The space has definitely been released and the database is now much smaller!! Thanks ALL for you help! Michael Hoffman In <c06d2s$13c4fv$1@ID-162943.news.uni-berlin.de> article, Neil Truby mentioned that: : "Michael Hoffman" <mrh@panix.com> wrote in message : news:c06c0p$90u$1@reader2.panix.com... : > Thanks Art & Malc! I expected that to be the case, so before posting : these : > numbers, I onunloaded & onloaded the database. In the past, we've used : > this method to reorg the entire database in one fell swoop. In this case, : > the numbers did not change much at all. : No, this is beacuse onunload/onload simply writes out and reads back the : binary pages. So if you had a page made up, say, 50% of data and 50% of : now-unused index space, that's what you'll end up with after onunload/onload : too, because the internal strucute of each page is unaffacted. : However, a rebuild of the tables through alter fragment or some other metod : *will* re-block the data, and your space will be freed up.

It may be that onunload/onload for a reorg will free entire unused extents only. If an extent is partially used the process will save and restore it. That would explain why sometimes doing a reorg this way seems to release unused space. Art S. Kagel Michael Hoffman wrote: > Thanks Neil. I've used the unonload / onload to defragment tables, which > usually leads to freeing up unused space due to deleted rows. > > I've run both unload / load and "alter index to cluster" to reorg most of the > tables. The space has definitely been released and the database is now much > smaller!! > > Thanks ALL for you help! > > Michael Hoffman > > In <c06d2s$13c4fv$1@ID-162943.news.uni-berlin.de> article, Neil Truby mentioned that: > : "Michael Hoffman" <mrh@panix.com> wrote in message > : news:c06c0p$90u$1@reader2.panix.com... > : > Thanks Art & Malc! I expected that to be the case, so before posting > : these > : > numbers, I onunloaded & onloaded the database. In the past, we've used > : > this method to reorg the entire database in one fell swoop. In this case, > : > the numbers did not change much at all. > > : No, this is beacuse onunload/onload simply writes out and reads back the > : binary pages. So if you had a page made up, say, 50% of data and 50% of > : now-unused index space, that's what you'll end up with after onunload/onload > : too, because the internal strucute of each page is unaffacted. > > : However, a rebuild of the tables through alter fragment or some other metod > : *will* re-block the data, and your space will be freed up. > >