Detaching a Fragment
I was wondering if anyone can help answer a question about ALTER
FRAGMENT DETACH.
I've looked at the manual, newsgroups, and even tried Tech Support
(I've been waiting a week for a reply) and I can't get a definitive
answer.
A little background
IDS 7.31
HP/UX
table schema
CREATE TABLE table1 (
field1 integer,
field2 integer,
field3 integer,.
.
lots of fields
.
.
fragment_val char(1)
) fragment by expression
fragment_val = 'A' in dbspace1,
fragment_val = 'B' in dbspace2,
fragment_val = 'C' in dbspace3,
fragment_val = 'D' in dbspace4;
create unique index pkindex on table1 (field1,field2,field3) in pkdbs;
create index index1 on table1 ... in index1dbs;
create index index2 on table1 ... in index2dbs;
create index index3 on table1 ... in index3dbs;
create index index4 on table1 ... in index4dbs;
create index index5 on table1 ... in index5dbs;
create index index6 on table1 ... in index6dbs;
alter table table1 add constraint ( primary key (field1,field2,field3));
What I would like to know is:
Can I use 'ALTER FRAGMENT DETACH' with table1?
If not, what do I need to change to be able to use 'ALTER FRAGMENT
DETACH'
If I can, will an index rebuild be necessary? If so, what can I do to
avoid an index rebuild.
Basically I am looking for the most efficient way to delete a large
amount of data from table1 while at the same time avoid locking up
table1 because of an index rebuild or a week long delete.
Thanks,
Andrew
Sent via Deja.com http://www.deja.com/
Before you buy.
Yes you can use ALTER FRAGMENT ... DETACH on this table to detach one of the
existing
fragments into a separate table. You will not have to rebuild the indexes
on the original table but
you will have to build new indexes on the resulting detached table. The
table(s) WILL be locked
during the detach process which will likely not be instantaneous as all of
those indexes need to be
cleaned up. It will, however, be faster than a long delete session.
You MAY find it faster to drop the indexes and rebuild them using
PSORT_NPROCS, PSORT_DBTEMP, and BTAPPENDERS to speed the build if you have
multiple CPUs.
Art S. Kagel
aford6875@my-deja.com wrote:
> I was wondering if anyone can help answer a question about ALTER
> FRAGMENT DETACH.
>
> I've looked at the manual, newsgroups, and even tried Tech Support
> (I've been waiting a week for a reply) and I can't get a definitive
> answer.
>
> A little background
>
> IDS 7.31
> HP/UX
>
> table schema
>
> CREATE TABLE table1 (
> field1 integer,
> field2 integer,
> field3 integer,> .
> .
> lots of fields
> .
> .
> fragment_val char(1)
> ) fragment by expression
> fragment_val = A in dbspace1,
> fragment_val = B in dbspace2,
> fragment_val = C in dbspace3,
> fragment_val = D in dbspace4;
>
> create unique index pkindex on table1 (field1,field2,field3) in pkdbs;
> create index index1 on table1 ... in index1dbs;
> create index index2 on table1 ... in index2dbs;
> create index index3 on table1 ... in index3dbs;
> create index index4 on table1 ... in index4dbs;
> create index index5 on table1 ... in index5dbs;
> create index index6 on table1 ... in index6dbs;>
> alter table table1 add constraint ( primary key (field1,field2,field3));>
> What I would like to know is:
>
> Can I use ALTER FRAGMENT DETACH with table1?
> If not, what do I need to change to be able to use ALTER FRAGMENT
> DETACH
> If I can, will an index rebuild be necessary? If so, what can I do to
> avoid an index rebuild.
>
> Basically I am looking for the most efficient way to delete a large
> amount of data from table1 while at the same time avoid locking up
> table1 because of an index rebuild or a week long delete.
>
> Thanks,
>
> Andrew
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.