Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question

migrated from stackoverflow.com Oct 26 '12 at 12:19

This question came from our site for professional and enthusiast programmers.

add comment

1 Answer 1

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).

share|improve this answer
1  
Yes, it works, but I don't want to have an index on table2. –  psur Oct 26 '12 at 9:25
1  
I'm pretty sure you don't have a choice on that. Build the "local" index on table2 once you've finished loading it, then exchange including it. The time that will take won't be different from the time it would take for Oracle to automatically rebuild the local indexes. –  Mat Oct 26 '12 at 9:26
1  
+1, it needs INCLUDING INDEXES only (for the local indexes). –  Vincent Malgrat Oct 26 '12 at 9:32
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.