Hi Uwe, From memory, I thought, even back in 8i days, if index supporting PK was unique, then it was dropped when constraint is disabled. If it's non-unique, then it's retained when constraint disabled. Anyhow, assuming you want to retain explicitly created index, it seems to me, your process should do something like this: Alter table xxx disable constraint cons1 keep index; Alter index pk_ind unusable; Alter session set skip_unusable_indexes = true; <do data load here> Alter index pk_ind rebuild nologging; Alter table xxx enable constraint cons1; Hope that helps, -Mark -----Original Message----- From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Uwe Küchler Sent: Thursday, August 20, 2009 9:02 AM To: oracle-l Subject: (not) dropping indexes with PK constraints in 10g Dear fellows of the Oracle, this mail is in continuation of the 2006 thread "disable pk works differently in oracle 9 and oracle 10?". At first I wanted to call the list for further advice, but meanwhile I found some more information that I think is worth sharing: Recently, I ran into the scenario that my client wanted to disable a primary key constraint (PK) to speed up data loads. In versions prior to 10g, this resulted in a drop of the supporting index, as well. Now with 10g, the index remained, resulting in unique constraint violations during the data load. The cause is a "fix" of the quite old bug 1240495, dating back as far as march 2000 and spanning product versions from 8.0 to 9.2. Now cross your fingers: How many of you accepted it as a default behaviour, that the disabling of a PK leads to the dropping of the supporting index? Well - no, it wasn't the expected behaviour! Nowadays, there's probably a gazillion of ETL scripts out there that used to make use of this behaviour - but they won't work with 10g anymore in many cases. Q: When does the index NOT get removed after disabling a PK? A: Only when it is a USER-DEFINED index, created like this: create index, then add constraint ... using index Now some more bad news: Oracle's exp utility dumps the constraint and index DDL *always* in the user-defined form. So, if you created a test DB using exp/imp, the outcome of a "disable constraint" might be different in your clone from that in the source DB. Q: How can I deal with that? A: Use the full syntax to disable or drop constraints: "ALTER TABLE pktest DISABLE CONSTRAINT pk_pktest DROP INDEX;" Q: What happens when I re-enable the constraint? A: The index will be generated like an implicit index. So, if you had a user-defined index with different settings (name, tablespace, ...) before, you'll have to specify that with the USING INDEX clause, again. Q: How can I find out if the supporting index was user-generated or not? A: With access to the data dictionary table IND$: SELECT DECODE( property, 4097, 'implicit', 'user-generated' ) generation FROM SYS.ind$ WHERE obj# = ( SELECT object_id FROM user_objects WHERE object_name = 'PK_PKTEST' ); HTH, Uwe --- My German Oracle Blog: http://oraculix.wordpress.com/ -- //www.freelists.org/webpage/oracle-l -- //www.freelists.org/webpage/oracle-l