Re: Relocating the partitions of an index on a composite partitioned table.

I agree, it appears to work, and even reports that it works. However, applications depending on those indexes (particularly if it's a PK or UK)
will fail. I did just what you described below on one table that I moved using
alter table move subpartition ...
and then rebuild index subpartion.
The load the following day failed because the PK on the table was unusable. It took dropping and recreating the index to fix.

Rodd

Deepak Sharma wrote:

>You would need to rebuild subpartitions (not
>partition), or am I missing something here?
>
>create table t1
>(
> emp_id number,
> date_key number,
> zone char(1)
>)
>PARTITION BY RANGE ( date_key )
> SUBPARTITION BY LIST (zone)
> SUBPARTITION TEMPLATE (
> subpartition P_N values ('N'),
> subpartition P_S values ('S'),
> subpartition P_E values ('E'),
> subpartition P_W values ('W')
> )
>(
> partition P20051003 values less than (
>20051004 ),
> partition P20051004 values less than (
>20051005 ),
> partition P20051005 values less than (
>20051006 ),
> partition P20051006 values less than (
>20051007 )
>)
>/
>
>create index emp_id_ix on t1(emp_id) local;
>
>select index_name, partition_name, subpartition_name
>from user_ind_subpartitions where index_name =
>'EMP_ID_IX';
>
>EMP_ID_IX P20051003 P20051003_P_N
>EMP_ID_IX P20051003 P20051003_P_S
>...
>
>SQL> alter index EMP_ID_IX rebuild subpartition
>P20051003_P_N tablespace users01;
>
>Index altered.
>
>
>
>- Deepak
>
>
>--- Rodd Holman <Rodd.Holman_at_gmail.com> wrote:
>
>
>
>>Actually I just went through this exercise.
>>With composite partitioned indexes, you need to do a
>>drop and create.
>>Rebuild will not work.
>>
>>Rodd
>>
>>
>
>
>
>
>__________________________________
>Yahoo! Mail - PC Magazine Editors' Choice 2005
>http://mail.yahoo.com
>
>
>

-- 
Rodd Holman
Enterprise Data Systems Engineer
LodgeNet Entertainment Corporation
rodd.holman_at_gmail.com

--
http://www.freelists.org/webpage/oracle-l