1. 在创建主键约束时，如果在主键列上没有索引，则会自动创建一个唯一索引：

SQL>  alter table test add constraint p_test primary key (empno);

Table altered.

SQL>  select table_name,index_name,uniqueness,status from user_Indexes where table_name='TEST';

TABLE_NAME INDEX_NAME UNIQUENES STATUS
---------- ---------- --------- --------
TEST       P_TEST     UNIQUE    VALID

在这种情况下，如果把主键约束disable后，则相应的索引被删除：

SQL> alter session set events '10046 trace name context forever,level 12';

Session altered.

SQL> alter table scott.test disable constraint p_test;

Table altered.

SQL> select table_name,index_name,uniqueness,status from dba_indexes where wner='SCOTT' and table_name='TEST';

no rows selected

SQL>  alter table scott.test enable constraint p_test;

Table altered.

SQL> select table_name,index_name,uniqueness,status from dba_indexes where wner='SCOTT' and table_name='TEST';

TABLE_NAME                     INDEX_NAME                     UNIQUENES STATUS
------------------------------ ------------------------------ --------- --------
TEST                           P_TEST                         UNIQUE    VALID

此时查看10046的trace文件，会发现如下sql：
....
....
=====================
PARSING IN CURSOR #3 len=27 dep=1 uid=0 ct=10 lid=0 tim=1206281434026658 hv=0 ad='b70eb7c8'
drop index "SCOTT"."P_TEST"
END OF STMT
PARSE #3:c=18997,e=18639,p=0,cr=77,cu=0,mis=1,r=0,dep=1,og=4,tim=1206281434026652
BINDS #3:
=====================
....
....
=====================
PARSING IN CURSOR #3 len=74 dep=1 uid=0 ct=9 lid=0 tim=1206281468333327 hv=1800847174 ad='326c9c0c'
CREATE UNIQUE INDEX "SCOTT"."P_TEST" on "SCOTT"."TEST"("EMPNO") NOPARALLEL
END OF STMT
PARSE #3:c=1000,e=822,p=0,cr=1,cu=0,mis=1,r=0,dep=1,og=4,tim=1206281468333320
BINDS #3:
=====================

很显然，在执行disable之后，随之执行了drop index的动作，而enable 之后，随之执行了create unique index的动作。

2.如果在创建主键之前，在主键列上先创建一个索引，则在disable和enable过程中避免drop index 和create unique index的出现：

SQL> alter table scott.test drop constraints p_test;

Table altered.

SQL> drop index idx_test;

Index dropped.

SQL> select owner,table_name,index_name,uniqueness,status from dba_indexes where table_name='TEST';

no rows selected

SQL> create index scott.idx_test on scott.test(empno);

Index created.

SQL>  alter table scott.test add constraint p_test primary key (empno) using index;

Table altered.

SQL>  select owner,table_name,index_name,uniqueness,status from dba_indexes
  2  where table_name='TEST' and wner='SCOTT';

OWNER                          TABLE_NAME                     INDEX_NAME                     UNIQUENES STATUS
------------------------------ ------------------------------ ------------------------------ --------- --------
SCOTT                          TEST                           IDX_TEST                       NONUNIQUE VALID

SQL> alter table scott.test disable constraint p_test;

Table altered.

SQL> select owner,table_name,index_name,uniqueness,status from dba_indexes
  2  where table_name='TEST' and wner='SCOTT';

OWNER                          TABLE_NAME                     INDEX_NAME                     UNIQUENES STATUS
------------------------------ ------------------------------ ------------------------------ --------- --------
SCOTT                          TEST                           IDX_TEST                       NONUNIQUE VALID

SQL> alter table scott.test enable constraint p_test;

Table altered.

SQL> select owner,table_name,index_name,uniqueness,status from dba_indexes
  2   where table_name='TEST' and wner='SCOTT';

OWNER                          TABLE_NAME                     INDEX_NAME                     UNIQUENES STATUS
------------------------------ ------------------------------ ------------------------------ --------- --------
SCOTT                          TEST                           IDX_TEST                       NONUNIQUE VALID

通过现在主键列上创建索引，再创建主键约束的方法，可以避免对大表主键进行disable和enable过程中产生的额外创建索引的工作。