Drop Constraint

If you drop a unique or primary key constraint the index that supports it may be dropped at the same time – but this doesn’t always happen. Someone asked me recently if it was possible to tell whether or not an index would be dropped without having to find out the hard way by dropping the constraint. The answer is yes – after all, Oracle has to make a decision somehow, so if we can find out how it makes the decision we can predict the decision.

So here’s my best theory so far – along with the observations that led to it. First, run a trace while dropping a primary key constraint and see if this gives you any clues; on an instance running 10gR2 I noticed the following statement appearing in the trace file immediately after the delete from cdef$ (constraint definitions).

select
        o.owner#, i.property, o.name
from
        obj$ o,
        ind$ i
where
        i.obj# = o.obj#
and     o.obj#=:1
;

Given this clue, the obvious next step is to look in sql.bsq (or dcore.bsq if you’re running 11g) to see what it says about column ind$.property, and we find the following comment:

/* The index was created by a constraint : 0x1000 */

This looks promising, so let’s try a few experiments to see what happens in a few cases of creating indexes and constraints – here’s a starting sample:

create table t1 (n1 number);
alter table t1 add constraint t1_pk primary key (n1);

create table t1 (n1 number);
create unique index t1_pk on t1(n1);
alter table t1 add constraint t1_pk primary key (n1);

In the first case (which generated the index automatically) ind$.property held the value 4097; in the second case, where I created the index explicitly, it held the value 1. (For the less mathematically inclined, 0x1000 = decimal 4096). So it would appear that if we want to know if an index would be dropped as the constraint was dropped, we need only check to see if bitand(ind$.property,4096) = 4096. If the predicate evaluates to true the index will be dropped. (But see comment 3 and the subsequent reply below: the test should almost certainly be bitand(ind$.property,4097) = 4097 )

The exact SQL for the check is left as an exercise to the reader – you will probably want to join ind$ to obj$ to user$.

Footnote: this particular bit of the property column doesn’t seem to be exposed in any of the data dictionary views; but if anyone knows of any other way to see it then I’d be interested to hear about it, especially if you don’t have to have access to the sys schema to see it.

Update Oct 2016

Here’s a little bit of SQL I hacked together while I was checking the behaviour of modifying a primary key to “move” it to a different index. It has to be run by SYS, and it references a schema and set of indexes by name (so it’s far from generic) and reports the two critical bits. If both are non-zero then it’s an index that will be dropped if it’s protecting a primary key or unique constraint and you drop or move the constraint:

column object_name format a32

select
        obj.object_name,
        bitand(ind.property,1)          unique_if_1,
        bitand(ind.property,4096)       sysgen_if_4096
from
        sys.ind$        ind,
        (
                select  object_id, object_name
                from    dba_objects
                where   object_type = 'INDEX'
                and     owner = 'TEST_USER'
                and     object_name in ('T1_I1','T1_PK')
        )       obj
where
        ind.obj# = obj.object_id
;

Don’t forget to check the possible performance implications of running this query on a database with a very large data dictionary.