Okay, I did 4 different tests, as follows: in each test I created a partitioned table.
Test 1 -- create a unique partitioned index, then create a primary key constraint with the same columns Test 2 -- create a non-unique partitioned index, then create a primary key constraint with the same columns Test 3 -- create a primary key constraint with the "using index" clause, partitioned Test 4 -- same as Test 3 In each case I then loaded valid data via insert and checked that for Test 1 and Test 2 the indexes were being used to enforce the constraint. They were. All partitions were in a USABLE state. I then loaded, via sqlloader direct=true, one row that would cause one of the partitions to become UNUSABLE. I then disabled the primary key constraint. For Test 4 only, I did the disable using the "keep index" clause. Next I checked for the existence of the index. In Test 2 (non-unique index) and Test 4 (keep index clause) the index remained. In Test 1 (unique index) and Test 3 (without the keep clause), the index disappeared. For both Test 2 and Test 4, I still needed to rebuild the index partition after I removed the duplicates to make the partition USABLE again. so.... the conclusion is, if I want not to lose the entire partitioned index, I need either a non-unique index to enforce the constraint or, for 9i only, the keep index clause when I disable the constraint. Since, in our case, the constraints have already been built using the "INDEX TABLESPACE" clause, I'll use the "keep index" clause when I have to remove duplicates. Thanks to all who responded and boy did I have fun proving this out :) Rachel __________________________________________________ Do you Yahoo!? Yahoo! Mail Plus - Powerful. Affordable. Sign up now. http://mailplus.yahoo.com -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Rachel Carmichael INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
