PK Nounique CASCADE DROP INDEX keep index

 

PURPOSE
  In Oracle 9i, the DBA has an explicit control over how indexes are affected
  while creating, disabling, or dropping Primary Key (PK) and unique 
  constraints.

  This bulletin explains the different behaviours of indexes associated with
  Primary Key or UNIQUE constraints according to the new clauses used when you 
  execute one of the following commands:
  
     CREATE TABLE ... PRIMARY KEY/UNIQUE
     ALTER TABLE  ... DISABLE PRIMARY KEY/UNIQUE
     ALTER TABLE  ... DROP PRIMARY KEY/UNIQUE


SCOPE & APPLICATION
  It is important for DBAs to know what happens to the indexes when creating,
  disabling or dropping a constraint relying on an index, since indexes may 
  have to be rebuilt after these operations. This can have two consequences:
 
    - Indexes may be missing for the Cost Based Optimizer (CBO) if the DBA 
      thinks that the index was not dropped. This can have a major impact on 
      performance.
    - Index rebuilding takes time.


Explicit control over INDEXES when DISABLING/DROPPING PK, Unique constraints:
=============================================================================

A. Creation of Primary Key/Unique constraints and associated index 
   ----------------------------------------------------------------

   In the following views, depending on the way you created the Primary Key (PK)
   or UNIQUE constraint and its associated index, you get these different 
   combinations:

                                       +-----------------+        +------------+
                                       | DBA_CONSTRAINTS |        | DBA_INDEXES|
                                       +-----------------+        +------------+
                                   -----------------------------   ------------
                                   Constraint_name   Index_name     Index_name
                                   --------------- -------------   ------------
Case 1: Create constraint, and index   PK_EMP_ID     EMP_ID_IX      EMP_ID_IX    
        explicitely within the same
        statement.


Case 2: Create constraint, and index   PK_EMP_ID     PK_EMP_ID      PK_EMP_ID    
        implicitely within the same 
        statement.


Case 3: Create constraint and index    PK_EMP_ID         -          EMP_ID_IX   
        separately within two
        statements.
        Enable the constraint.         PK_EMP_ID     EMP_ID_IX      EMP_ID_IX


-------------------------------------------------------------------------
Case 1: Create constraint and index explicitely within the same statement
-------------------------------------------------------------------------

SQL> drop table <OWNER>.<TABLE_NAME>
Table dropped.

SQL> create table <OWNER>.<TABLE_NAME>
     (emp_id NUMBER
             CONSTRAINT pk_emp_id PRIMARY KEY USING INDEX
             (CREATE INDEX <OWNER>.emp_id_ix ON <OWNER>.<TABLE_NAME>(emp_id)
              TABLESPACE indx),
      ename VARCHAR2(12),
      sal   number);

Table created.


SQL> select index_name,uniqueness from dba_indexes where table_name='<TABLE_NAME>';

    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    EMP_ID_IX                      NONUNIQUE

SQL> select constraint_name,index_name, constraint_type from dba_constraints
     where table_name='<TABLE_NAME>' and constraint_type='P';

    CONSTRAINT_NAME                INDEX_NAME                     C
    ------------------------------ ------------------------------ -
    PK_EMP_ID                      EMP_ID_IX                      P


-------------------------------------------------------------------------
Case 2: Create constraint and index implicitely within the same statement
-------------------------------------------------------------------------

SQL> drop table <OWNER>.<TABLE_NAME>
Table dropped.

SQL> create table <OWNER>.<TABLE_NAME>
     (emp_id NUMBER
             CONSTRAINT pk_emp_id PRIMARY KEY USING INDEX TABLESPACE indx,
      ename VARCHAR2(12),
      sal   number);

Table created.


SQL> select index_name,uniqueness from dba_indexes where table_name='<TABLE_NAME>';

    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    PK_EMP_ID                      UNIQUE

SQL> select constraint_name,index_name, constraint_type from dba_constraints
     where table_name='<TABLE_NAME>' and constraint_type='P';

    CONSTRAINT_NAME                INDEX_NAME                     C
    ------------------------------ ------------------------------ -
    PK_EMP_ID                      PK_EMP_ID                      P
 

--------------------------------------------------------------------
Case 3: Create constraint and index separately within two statements
--------------------------------------------------------------------

SQL> drop table <OWNER>.<TABLE_NAME>
Table dropped.

SQL> create table <OWNER>.<TABLE_NAME>
     (emp_id NUMBER
             CONSTRAINT pk_emp_id PRIMARY KEY  DISABLE,
      ename VARCHAR2(12),
      sal   number);

Table created.


SQL> create index <OWNER>.emp_id_ix on <OWNER>.<TABLE_NAME>(emp_id)
     tablespace indx;
Index created.

SQL> select index_name,uniqueness from dba_indexes where table_name='<TABLE_NAME>';

    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    EMP_ID_IX                      NONUNIQUE

SQL> select constraint_name,index_name, constraint_type from dba_constraints
     where table_name='<TABLE_NAME>' and constraint_type='P';

    CONSTRAINT_NAME                INDEX_NAME                     C
    ------------------------------ ------------------------------ -
    PK_EMP_ID                                                     P

SQL> alter table <OWNER>.<TABLE_NAME> ENABLE constraint pk_emp_id;
Table altered.

SQL> select index_name,uniqueness from dba_indexes where table_name='<TABLE_NAME>';

    INDEX_NAME                     UNIQUENES
    ------------------------------ ---------
    EMP_ID_IX                      NONUNIQUE

SQL> select constraint_name,index_name, constraint_type from dba_constraints
     where table_name='<TABLE_NAME>' and constraint_type='P';

    CONSTRAINT_NAME                INDEX_NAME                     C
    ------------------------------ ------------------------------ -
    PK_EMP_ID                      EMP_ID_IX                      P



B. Disabling PK/UNIQUE constraints: what happens to the associated index 
   ---------------------------------------------------------------------

   In Case 1 where the index was created explicitely within the same statement
   as the constraint, the index is in both cases disassociated from the 
   constraint; depending on the clause "CASCADE DROP INDEX" usage, the index is 
   dropped or not.

   In traditionnal Case 2, the behavior remains the same: using the clause 
   "CASCADE DROP INDEX" or not does not influence the usual behavior: it 
   automatically drops the relying index.
  
   In case 3, disabling the constraint drops the index or not: 
       * if the constraint has never been enabled, it never drops the index.
       * but in most cases, the constraint has been enabled for some time. 
         In this case, the clause "CASCADE DROP INDEX" drops the index.
                   
   
                                       +-----------------+       +------------+
                                       | DBA_CONSTRAINTS |       | DBA_INDEXES|
                                       +-----------------+       +------------+
                                  -----------------------------   ------------
                                  Constraint_name   Index_name     Index_name
                                  --------------- -------------   ------------
Case 1: ALTER TABLE ... DISABLE PK     PK_EMP_ID         -             -        
                CASCADE DROP INDEX;
        or
        ALTER TABLE ... DISABLE PK;    PK_EMP_ID         -         EMP_ID_IX    
                                                                 
 
Case 2: ALTER TABLE ... DISABLE PK     PK_EMP_ID         -             -       
                CASCADE DROP INDEX;
        or 
        ALTER TABLE ... DISABLE PK;    PK_EMP_ID         -             -      


Case 3: ALTER TABLE ... DISABLE PK     PK_EMP_ID         -             -    
                CASCADE DROP INDEX;
        or 
        ALTER TABLE ... DISABLE PK;    PK_EMP_ID         -         EMP_ID_IX



C. Dropping PK/UNIQUE constraints: what happens to the associated index 
   ---------------------------------------------------------------------

   In Case 1, where the index was created explicitely within the same statement
   as the constraint, the index is by default KEPT when the constraint is 
   dropped.
   If you want the index to be dropped, you have to explicitely ask for it 
   through the "DROP INDEX" clause.

   In case 2, the behavior is the opposite: if you want the index to be kept 
   and the constraint dropped, you have to explicitly ask for it with the 
   "KEEP INDEX" clause; otherwise the index is DROPPED by default.

   In Case 3, dropping the constraint drops the index or not: 
       * if the constraint has never been enabled, it never drops the index.
       * but in most cases, the constraint has been enabled for some time. 
         Then the index is by default KEPT when the constraint is dropped. If 
         you want the index to be dropped, you have to explicitly ask for it 
         with the "DROP INDEX" clause.


                                             +-----------------+   +-----------+
                                             | DBA_CONSTRAINTS |   |DBA_INDEXES|
                                             +-----------------+   +-----------+
                                           ----------------------- ------------
                                           Constraint  Index_name   Index_name
                                           ----------- ----------- ------------
Case 1: ALTER TABLE ... DROP PK DROP INDEX;     -            -           -       
Case 1: ALTER TABLE ... DROP PK KEEP INDEX;     -            -       EMP_ID_IX              
Case 1: ALTER TABLE ... DROP PK;                -            -       EMP_ID_IX   
                                                              

Case 2: ALTER TABLE ... DROP PK DROP INDEX;     -            -           -                                                      
Case 2: ALTER TABLE ... DROP PK KEEP INDEX;     -            -       PK_EMP_ID                                                              
Case 2: ALTER TABLE ... DROP PK;                -            -           -       


Case 3: ALTER TABLE ... DROP PK DROP INDEX;     -            -           -   
Case 3: ALTER TABLE ... DROP PK KEEP INDEX;     -            -       EMP_ID_IX   
Case 3: ALTER TABLE ... DROP PK;                -            -       EMP_ID_IX