RE: (not) dropping indexes with PK constraints in 10g
Date: Thu, 20 Aug 2009 09:13:37 -0400
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,
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
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
WHERE obj# = ( SELECT object_id
FROM user_objects WHERE object_name = 'PK_PKTEST' );
My German Oracle Blog: http://oraculix.wordpress.com/
http://www.freelists.org/webpage/oracle-l Received on Thu Aug 20 2009 - 08:13:37 CDT