index subpartitions go unusable
version 10.2.0.3
when I exchange a data subpartition the other index subpartition in the partition goes unuable. Seems like this is bug ... does this do the same in other versions?
{code} SQL> select idx.index_name, idx.status idx_status, ixp.partition_name,ixp.status part_status, subpartition_name,ixs.status subpart_status from all_indexes idx ,all_ind_partitions ixp ,all_ind_subpartitions ixs where idx.owner = UPPER('U1') AND idx.table_name = UPPER('DELME') AND idx.owner = ixp.index_owner AND idx.index_name = ixp.index_name and idx.index_name = ixs.index_name and ixp.partition_name = ixs.partition_name and ixp.partition_name = 'P200701' INDEX_NAME IDX_STAT PARTITION_NAME PART_STA ------------------------------ -------- ------------------------------ -------- SUBPARTITION_NAME SUBPART_ ------------------------------ -------- DELME_PK N/A P200701 N/A P200701_N USABLE DELME_PK N/A P200701 N/A P200701_Y USABLE 2 rows selected. SQL> alter table DELME exchange subpartition P200701_N with table subpart excluding indexes without validation Table altered. SQL> select idx.index_name, idx.status idx_status, ixp.partition_name,ixp.status part_status, subpartition_name,ixs.status subpart_status from all_indexes idx ,all_ind_partitions ixp ,all_ind_subpartitions ixs where idx.owner = UPPER('U1') AND idx.table_name = UPPER('DELME') AND idx.owner = ixp.index_owner AND idx.index_name = ixp.index_name and idx.index_name = ixs.index_name and ixp.partition_name = ixs.partition_name and ixp.partition_name =0