topic Re: How do you move a partitionned index from one TS to another ? in Operating System - HP-UX

How do you move a partitionned index from one TS to another ?

Nicolas Dumeige — Tue, 16 Mar 2004 09:04:20 GMT

Hello,

How do you move a partitionned index from one TS to another ?

Thanks

Re: How do you move a partitionned index from one TS to another ?

Jean-Luc Oudart — Tue, 16 Mar 2004 09:15:00 GMT

What about
alter index rebuild tablespace

Regards,
Jean-Luc

Re: How do you move a partitionned index from one TS to another ?

Thierry Poels_1 — Tue, 16 Mar 2004 09:18:29 GMT

Hi,

Move the index partition to a new tablespace with the following command:

alter index index_name rebuild partition part_name tablespace tsname;

(hope you're talking about Oracle ;-)
good luck,
Thierry.

Re: How do you move a partitionned index from one TS to another ?

Nicolas Dumeige — Tue, 16 Mar 2004 09:26:40 GMT

The second answer is better.

As you can see Jean-Luc, Oracle refused to move the whole index at once.

14:55:43 RSTX01> alter index ISLDRST_CPTEXT_RST_SAVE rebuild tablespace RST;
alter index ISLDRST_CPTEXT_RST_SAVE rebuild tablespace RST
*
ERROR at line 1:
ORA-14086: a partitioned index may not be rebuilt as a whole

Do I really need to specify every partition_name ?

Thanks

Re: How do you move a partitionned index from one TS to another ?

Thierry Poels_1 — Tue, 16 Mar 2004 09:45:32 GMT

hi,

yep, normally every partition resides in a different tablespace.
But you can combine them into one "alter index" statement:

alter index I1
rebuild partition P1 tablespace T1 storage (...)
rebuild partition P2 tablespace T2 storage (...);

good luck,
Thierry.

Re: How do you move a partitionned index from one TS to another ?

Jean-Luc Oudart — Tue, 16 Mar 2004 10:00:05 GMT

I just wanted to give general idea.
not using partioning.

from the documentation :
Using Alter Index to Rebuild a Partition
The ALTER INDEX...REBUILD PARTITION statement rebuilds one partition of an index. It cannot be used on a composite-partitioned table. At the same time as you recreate the index, you can move the partition to a new tablespace or change attributes.

For composite-partitioned tables, use ALTER INDEX...REBUILD SUBPARTITION to rebuild a subpartition of an index. You can move the subpartition to another tablespace or specify a parallel clause. The following statement rebuilds a subpartition of a local index on a table and moves the index subpartition is another tablespace.

ALTER INDEX scuba
REBUILD SUBPARTITION bcd_types
TABLESPACE tbs23 PARALLEL (DEGREE 2);

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Also there was a bug on 8i where the statistics were lost. Bug 3018073
Don't know your version.

Regards,
Jean-Luc

Re: How do you move a partitionned index from one TS to another ?

Nicolas Dumeige — Tue, 16 Mar 2004 10:12:39 GMT

Merci Ã vous deux pour votre aide.

J'ai utilisÃ© le script suivant pour reconstruire / dÃ©placer mon index :

select 'ALTER INDEX '||INDEX_NAME||' REBUILD PARTITION '||PARTITION_NAME||' TABLESPACE RST'
from USER_IND_PARTITIONS
where INDEX_NAME in (SELECT index_name FROM user_indexes WHERE table_name='TSLDRST_SAV