Oracle Scratchpad

April 19, 2012

Drop Constraint

Filed under: Indexing,Infrastructure,Oracle — Jonathan Lewis @ 4:22 pm BST Apr 19,2012

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 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.

7 Comments »

  1. Jonathan,
    many people (used to) take it for granted that an index gets dropped when its corresponding constraint is disabled or dropped.
    The fact is that it used to be like this up until 9iR2, although it was considered a bug (#1240495 on MOS). As soon as this “bug” got fixed in 10g, lots of systems relying on that old behaviour (e.g. for disabling a PK for a load) started to fail.
    Interesting to see how you traced those details down.
    Kind regards,
    Uwe

    Comment by Uwe M. K├╝chler — April 19, 2012 @ 5:17 pm BST Apr 19,2012 | Reply

  2. You can use keep/drop option:

    
    SQL> drop table t1;
     
    Table dropped
    SQL> create table t1  (id number);
     
    Table created
    SQL> create index i1 on t1(id);
     
    Index created
    SQL> select count(*) from user_indexes i where i.index_name = 'I1';
     
      COUNT(*)
    ----------
             1
    SQL> alter table t1 add constraint pk1 primary key (id);
     
    Table altered
    SQL> alter table t1 drop constraint pk1 drop index;
     
    Table altered
    SQL> select count(*) from user_indexes i where i.index_name = 'I1';
     
      COUNT(*)
    ----------
             0
    
    
    

    Comment by Valentin Nikotin — April 19, 2012 @ 5:41 pm BST Apr 19,2012 | Reply

  3. Hi Jonathan,

    the following testcase let’s me think that your conclusion might not be fully right.

    BR,
    Martin

    SQL> -- two test tables - index created at same time as constraint is created
    SQL> create table scott.TEST1 (
      2    id number not null
      3  , constraint test1_pk primary key  (id)
      4  );
    
    Table created.
    
    SQL>
    SQL> create table scott.TEST2 (
      2    id number not null
      3  , constraint test2_pk primary key  (id) using index
      4      (create index scott.test2_pk on scott.test2 (id))
      5  );
    
    Table created.
    
    SQL>
    SQL> -- both indexes exist
    SQL> select table_name, index_name
      2  from   dba_indexes
      3  where  owner='SCOTT'
      4    and  table_name in ('TEST1','TEST2')
      5  ;
    
    TABLE_NAME                     INDEX_NAME
    ------------------------------ ------------------------------
    TEST2                          TEST2_PK
    TEST1                          TEST1_PK
    
    SQL>
    SQL> -- according to your posting ind$.property would make think that both indexes
    SQL> -- get dropped when the PK constraint gets dropped
    SQL> select do.object_name, do.object_id, bitand(ind$.property,4096), ind$.property
      2  from   sys.ind$    ind$
      3       , dba_objects do
      4  where  do.owner='SCOTT'
      5     and do.object_name in ('TEST1_PK','TEST2_PK')
      6     and do.object_id=ind$.obj#
      7  ;
    
    OBJECT_NAME                     OBJECT_ID BITAND(IND$.PROPERTY,4096)   PROPERTY
    ------------------------------ ---------- -------------------------- ----------
    TEST1_PK                           180658                       4096       4097
    TEST2_PK                           180660                       4096       4096
    
    SQL> -- let us drop both pk constraints
    SQL> alter table scott.test1 drop primary key;
    
    Table altered.
    
    SQL> alter table scott.test2 drop primary key;
    
    Table altered.
    
    SQL>
    SQL> -- but the output is: No, not both PK constraints get dropped
    SQL> select table_name, index_name
      2  from   dba_indexes
      3  where  owner='SCOTT'
      4    and  table_name in ('TEST1','TEST2')
      5  ;
    
    TABLE_NAME                     INDEX_NAME
    ------------------------------ ------------------------------
    TEST2                          TEST2_PK
    
    SQL>
    SQL> -- Greetings from Austria - Martin
    SQL>
    

    Comment by Martin S. — April 19, 2012 @ 7:28 pm BST Apr 19,2012 | Reply

    • Martin,

      Nicely done.
      Your second test is one that I did before posting – except I specified uniqueness in the ‘create index’ bit and the index then disappeared with the constraint. You haven’t specified uniqueness (bit 0 – value 1).

      So perhaps the requirement is that bitand(property,4097) = 4097 – i.e. automatic and unique.

      Comment by Jonathan Lewis — April 19, 2012 @ 7:51 pm BST Apr 19,2012 | Reply

  4. An “Ahaa !” moment when I read this.
    Thanks.

    Comment by Hemant K Chitale — April 20, 2012 @ 12:28 am BST Apr 20,2012 | Reply

  5. Hi!
    Seems this property can be checked through dbms_metadata.
    Code is based on example from “Expert Indexing in Oracle Database 11g”:

    SQL> drop table testtab2;
    
    Table dropped.
    
    SQL> create table testtab2 (a number, b number);
    
    Table created.
    
    SQL> alter table testtab2 modify (a primary key);
    
    Table altered.
    
    SQL> declare
    l_myHandle number;
    l_transHandle number;
    l_ddl clob;
    begin
      l_myhandle := dbms_metadata.open('INDEX');
      dbms_metadata.set_filter(l_myhandle, 'SYSTEM_GENERATED', false);
      dbms_metadata.set_filter(l_myhandle, 'BASE_OBJECT_SCHEMA', user);
      dbms_metadata.set_filter(l_myhandle, 'BASE_OBJECT_NAME', 'TESTTAB2');
      l_transhandle:=dbms_metadata.add_transform(l_myhandle, 'DDL');
      loop
        l_ddl:=dbms_metadata.fetch_clob(l_myhandle);
        exit when l_ddl is null;
        dbms_output.put_line(l_ddl);
      end loop;
      dbms_metadata.close(l_myhandle);
    end;
    /
      2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  
    PL/SQL procedure successfully completed.
    
    SQL> declare
    l_myHandle number;
    l_transHandle number;
    l_ddl clob;
    begin
      l_myhandle := dbms_metadata.open('INDEX');
      dbms_metadata.set_filter(l_myhandle, 'SYSTEM_GENERATED', true);
      dbms_metadata.set_filter(l_myhandle, 'BASE_OBJECT_SCHEMA', user);
      dbms_metadata.set_filter(l_myhandle, 'BASE_OBJECT_NAME', 'TESTTAB2');
      l_transhandle:=dbms_metadata.add_transform(l_myhandle, 'DDL');
      loop
        l_ddl:=dbms_metadata.fetch_clob(l_myhandle);
        exit when l_ddl is null;
        dbms_output.put_line(l_ddl);
      end loop;
      dbms_metadata.close(l_myhandle);
    end;
    /
      2    3    4    5    6    7    8    9   10   11   12   13   14   15   16   17   18  
      CREATE UNIQUE INDEX "SYS"."SYS_C0088374" ON "SYS"."TESTTAB2" ("A") 
      PCTFREE
    10 INITRANS 2 MAXTRANS 255 
      STORAGE(INITIAL 16384 NEXT 16384 MINEXTENTS 1
    MAXEXTENTS 505
      PCTINCREASE 50 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL
    DEFAULT)
      TABLESPACE "SYSTEM" 
    
    
    PL/SQL procedure successfully completed.
    
    SQL>
    

    Comment by Andrew — April 20, 2012 @ 12:52 pm BST Apr 20,2012 | Reply

    • nice idea, Andrew !

      however, if you change
      alter table testtab2 modify (a primary key);
      into
      alter table testtab2 add constraint te_pk primary key (a);

      the index is still SYSTEM_GENERATED, but the first block with “dbms_metadata.set_filter(l_myhandle, ‘SYSTEM_GENERATED’, false);” delivers ( falsely ?)
      CREATE UNIQUE INDEX “SOKRATES”.”TE_PK” ON “SOKRATES”.”TESTTAB2″ (“A”)
      ….

      ( looks like a bug to me, my version is 11.2.0.2.0 )

      However, in both cases
      bitand(ind$.property,4097)
      gives 4097 to me.

      Comment by Matthias Rogel — April 20, 2012 @ 2:09 pm BST Apr 20,2012 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme. Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,906 other followers