RE: (not) dropping indexes with PK constraints in 10g

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_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] 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/

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Thu Aug 20 2009 - 08:13:37 CDT