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

  • From: "Bobak, Mark" <Mark.Bobak@xxxxxxxxxxxx>
  • To: "uwe@xxxxxxxxxxxx" <uwe@xxxxxxxxxxxx>, oracle-l <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 20 Aug 2009 09:13:37 -0400

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


Other related posts: