Does UPDATE INDEXES work for local indexes

indexoraclepartitioning

I have two tables in Oracle 11.2. First is partitioned and has local index (non-prefixed):

CREATE TABLE table1 (col1 INT, col2 INT)
PARTITION BY LIST(col1)
(
    PARTITION p1 VALUES(1),
    PARTITION p2 VALUES(2)
);
CREATE INDEX table1_index on table1(col2) LOCAL;
INSERT INTO table1 VALUES(1, 1);
INSERT INTO table1 VALUES(2, 1);

Second table is similar but without partitions and index:

CREATE TABLE table2(col1 INT, col2 INT);
INSERT INTO table2 VALUES(1, 2);

When I exchange one of partition from table1 with table2 then index for this partition will be unusable. To avoid this I can use UPDATE INDEXES, so I do:

ALTER TABLE table1 EXCHANGE PARTITION p1
WITH TABLE table2 WITH VALIDATION UPDATE INDEXES;

But UPDATE INDEXES seem to doesn't work, because when I check status of index for this partition it's unusable.

SELECT partition_name, status FROM user_ind_partitions
WHERE index_name = 'TABLE1_INDEX';

PARTITION_NAME                 STATUS   
------------------------------ -------- 
P1                             UNUSABLE 
P2                             USABLE

For GLOBAL index it works as expected.

The question is: does UPDATE INDEXES work for LOCAL indexes when using EXCHANGE PARTITION?

Best Answer

From the Maintaining Partitions documentation:

You can optionally specify if local indexes are also to be exchanged (INCLUDING INDEXES clause), and if rows are to be validated for proper mapping (WITH VALIDATION clause). [...]

Unless you specify UPDATE INDEXES, the database marks UNUSABLE the global indexes or all global index partitions on the table whose partition is being exchanged. Global indexes or global index partitions on the table being exchanged remain invalidated.

I believe you'll need to create a matching index on table2 and use both INCLUDING INDEXES and UPDATE INDEXES (but don't have a way of testing it right now).

Related Question