SEO

Hi All,

IDS 7.31.UD6
AIX 4.3

I recently began experimenting with detaching indexes. I was under
the impression that moving the indexes to a different dbspace would decrease
the space used in the main data dbspace.

However, this is what I found:

(4K Pgs) TableData Space Index Space
---------------------------------------------------
Before 392521 Avail 283992 Avail
After 392497 Avail 141299 Avail

As you can see, detaching the indexes used 142693 pgs (570 MB), but only
released 25 Data pages (100 KB). If the index pages were interleaved with
the data, shouldn't I get the same 500 MBs returned in the Tabledata space?

Thanks,
Michael Hoffman

P.s. -- Still awaiting any help on why detaching the indexes seems to be
causing "dbschema" to core dump with a Segmentation Fault.

"Michael Hoffman" <mrh@panix.com> wrote in message
news:c01n4g$pbn$1@reader2.panix.com...
> Hi All,
>
> IDS 7.31.UD6
> AIX 4.3
>
> I recently began experimenting with detaching indexes. I was
under
> the impression that moving the indexes to a different dbspace would
decrease
> the space used in the main data dbspace.
>
> However, this is what I found:
>
> (4K Pgs) TableData Space Index Space
> ---------------------------------------------------
> Before 392521 Avail 283992 Avail
> After 392497 Avail 141299 Avail

How are you measuring the space used and available?

Michael Hoffman <mrh@panix.com> wrote in message news:<c01n4g$pbn$1@reader2.panix.com>...
> Hi All,
>
> IDS 7.31.UD6
> AIX 4.3
>
> I recently began experimenting with detaching indexes. I was under
> the impression that moving the indexes to a different dbspace would decrease
> the space used in the main data dbspace.

<snip>
Did you drop and recreate the tables after the indexes were fragged?

> Thanks,
> Michael Hoffman
>
> P.s. -- Still awaiting any help on why detaching the indexes seems to be
> causing "dbschema" to core dump with a Segmentation Fault.

Never caused us a problem on HPUX, IDS7.31UC2 , sorry......

Malc_p

If you are checking free space in the dbspaces, then yes, simply
detaching the indexes will not automatically release pages to the free
pool. The now unused space within the table IS free for the table to
use for more data pages (run oncheck -pt or -pT to check), but to
release the space back to the common free extent pool you have to
reorg the table to release unused pages and compress partial pages.

Art S. Kagel

Michael Hoffman wrote:
> Hi All,
>
> IDS 7.31.UD6
> AIX 4.3
>
> I recently began experimenting with detaching indexes. I was under
> the impression that moving the indexes to a different dbspace would decrease
> the space used in the main data dbspace.
>
> However, this is what I found:
>
> (4K Pgs) TableData Space Index Space
> ---------------------------------------------------
> Before 392521 Avail 283992 Avail
> After 392497 Avail 141299 Avail
>
> As you can see, detaching the indexes used 142693 pgs (570 MB), but only
> released 25 Data pages (100 KB). If the index pages were interleaved with
> the data, shouldn't I get the same 500 MBs returned in the Tabledata space?
>
> Thanks,
> Michael Hoffman
>
> P.s. -- Still awaiting any help on why detaching the indexes seems to be
> causing "dbschema" to core dump with a Segmentation Fault.
>
>

Neil,
Sorry for not explaining better: the numbers are sums of the output
from 'onstat -d' for the chunks comprising the respective dbspaces.

Michael

In <c02acq$1215fb$1@ID-162943.news.uni-berlin.de> article, Neil Truby
mentioned that:
: "Michael Hoffman" <mrh@panix.com> wrote in message
: news:c01n4g$pbn$1@reader2.panix.com...
: > Hi All,
: >
: > IDS 7.31.UD6
: > AIX 4.3
: >
: > I recently began experimenting with detaching indexes. I was
: under
: > the impression that moving the indexes to a different dbspace would
: decrease
: > the space used in the main data dbspace.
: >
: > However, this is what I found:
: >
: > (4K Pgs) TableData Space Index Space
: > ---------------------------------------------------
: > Before 392521 Avail 283992 Avail
: > After 392497 Avail 141299 Avail

: How are you measuring the space used and available?

"Michael Hoffman" <mrh@panix.com> wrote in message
news:c06b7n$8ra$1@reader2.panix.com...
> Neil,
> Sorry for not explaining better: the numbers are sums of the output
> from 'onstat -d' for the chunks comprising the respective dbspaces.
>
> Michael

In which case it's for the reason Art Kagel and Malc P gave - you need to
drop and re-create the table to free up the space (or use ALTER FRAGMENT ..
INIT IN DBSPACE or some other method of re-organising the table data).

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.

I will reorg the 10 largest tables this weekend and post my results next
week.

Thanks for easing my mind that I wasn't nuts to expect the reclaimed space.
My boss was begining to doubt my skills. ;-)

Michael Hoffman

In <4026A001.9000207@erols.com> article, Art S. Kagel mentioned that:
: If you are checking free space in the dbspaces, then yes, simply
: detaching the indexes will not automatically release pages to the free
: pool. The now unused space within the table IS free for the table to
: use for more data pages (run oncheck -pt or -pT to check), but to
: release the space back to the common free extent pool you have to
: reorg the table to release unused pages and compress partial pages.

: Art S. Kagel

: Michael Hoffman wrote:
: > Hi All,
: >
: > IDS 7.31.UD6
: > AIX 4.3
: >
: > I recently began experimenting with detaching indexes. I was under
: > the impression that moving the indexes to a different dbspace would decrease
: > the space used in the main data dbspace.
: >
: > However, this is what I found:
: >
: > (4K Pgs) TableData Space Index Space
: > ---------------------------------------------------
: > Before 392521 Avail 283992 Avail
: > After 392497 Avail 141299 Avail
: >
: > As you can see, detaching the indexes used 142693 pgs (570 MB), but only
: > released 25 Data pages (100 KB). If the index pages were interleaved with
: > the data, shouldn't I get the same 500 MBs returned in the Tabledata space?
: >
: > Thanks,
: > Michael Hoffman
: >
: > P.s. -- Still awaiting any help on why detaching the indexes seems to be
: > causing "dbschema" to core dump with a Segmentation Fault.
: >
: >

"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.
>
>