删除主键，唯一约束，什么时候会自动删除列上的索引？

你可能经常会有这样的顾虑，在删除唯一约束或者主键约束的时候，附带的索引会不会被删除掉？
现在的团队有一个规范，但凡是增加主键，都需要先手工创建索引，再增加主键。给出的原因是：这样删除主键的时候，索引就不会被删除掉了。
Oracle是怎么知道这个索引是手工创建的，还是Oracle自动（递归）创建的？如果Oracle可以区别开这两者，貌似就有一个可以猜测的答案：
Oracle在删除主键或者唯一约束的时候，对于自动创建的索引会递归的删除掉，对于手工创建的索引会保留。（这并不是最终的结论，最终的结论在文章的最后）。

其实Oracle可以区别开这两者，查看sql.bsq（一般位于$ORACLE_HOME/RDBMS/ADMIN下)文件，里面有ind$视图的创建语句：
create table ind$                                             /* index table */
( obj#          number not null,                            /* object number */
  /* DO NOT CREATE INDEX ON DATAOBJ#  AS IT WILL BE UPDATED IN A SPACE
   * TRANSACTION DURING TRUNCATE */
  dataobj#      number,                          /* data layer object number */
  ts#           number not null,                        /* tablespace number */
  file#         number not null,               /* segment header file number */
  block#        number not null,              /* segment header block number */
  bo#           number not null,              /* object number of base table */
  indmethod#    number not null,    /* object # for cooperative index method */
  cols          number not null,                        /* number of columns */
  pctfree$      number not null, /* minimum free space percentage in a block */
  initrans      number not null,            /* initial number of transaction */
  maxtrans      number not null,            /* maximum number of transaction */
  pctthres$     number,           /* iot overflow threshold, null if not iot */
  type#         number not null,              /* what kind of index is this? */
                                                               /* normal : 1 */
                                                               /* bitmap : 2 */
                                                              /* cluster : 3 */
                                                            /* iot - top : 4 */
                                                         /* iot - nested : 5 */
                                                            /* secondary : 6 */
                                                                 /* ansi : 7 */
                                                                  /* lob : 8 */
                                             /* cooperative index method : 9 */
  flags         number not null,      
                /* mutable flags: anything permanent should go into property */
                                                    /* unusable (dls) : 0x01 */
                                                    /* analyzed       : 0x02 */
                                                    /* no logging     : 0x04 */
                                    /* index is currently being built : 0x08 */
                                     /* index creation was incomplete : 0x10 */
                                           /* key compression enabled : 0x20 */
                                              /* user-specified stats : 0x40 */
                                            /* secondary index on IOT : 0x80 */
                                      /* index is being online built : 0x100 */
                                    /* index is being online rebuilt : 0x200 */
                                                /* index is disabled : 0x400 */
                                                     /* global stats : 0x800 */
                                            /* fake index(internal) : 0x1000 */
                                       /* index on UROWID column(s) : 0x2000 */
                                            /* index with large key : 0x4000 */
                             /* move partitioned rows in base table : 0x8000 */
                                 /* index usage monitoring enabled : 0x10000 */
                      /* 4 bits reserved for bitmap index version : 0x1E0000 */
  property      number not null,    /* immutable flags for life of the index */
                                                            /* unique : 0x01 */
                                                       /* partitioned : 0x02 */
                                                           /* reverse : 0x04 */
                                                        /* compressed : 0x08 */
                                                        /* functional : 0x10 */
                                              /* temporary table index: 0x20 */
                             /* session-specific temporary table index: 0x40 */
                                              /* index on embedded adt: 0x80 */
                         /* user said to check max length at runtime: 0x0100 */
                                              /* domain index on IOT: 0x0200 */
                                                      /* join index : 0x0400 */
                /* functional index expr contains a PL/SQL function : 0x0800 */
                           /* The index was created by a constraint : 0x1000 */
                              /* The index was created by create MV : 0x2000 */

property列是我们需要关注的。当值为0x1000的时候，就是Oracle自动创建的索引。换算成10进制就是4096。这个property的值有个特点，它的各个可以取的值是按照2的倍数增长的。
property的值可以是多个值的和，比如这个索引是唯一的，且是自动创建，那么这个property的值就是0x01+0x1000 转换为10进制就是1+4096=4097

/* The index was created by a constraint : 0x1000 */
property为0x10000的时候，代表这个索引是ORACLE自动（递归）创建的，非手工创建的。
下面我们做几个实验，来验证什么时候Oracle会递归删除掉约束上的索引：

1）建表的同时，指定主键。
create table wxh_tbd(id number ,primary key(id));
找出对应索引的object_id（略）
select PROPERTY from ind$  where OBJ#='193613';
  PROPERTY
----------
      4097
4097代表4096+1，转化为16进制就是：0x1000+0x01 代表了 oracle自动创建了索引而且是唯一索引
这种情况下如果你：
alter table wxh_tbd drop primary key;
拿IND查看索引
@ ind
NO ROWS

发现索引也没了。

2）手工创建索引（唯一索引）
create table wxh_tbd(id number);
create unique index ttt on wxh_tbd(id);
alter table wxh_tbd add constraint pk_o primary key(id);
select PROPERTY from ind$  where OBJ#='193616';
  PROPERTY
----------
         1
1代表是个唯一索引。
这种情况下如果你：
alter table wxh_tbd drop primary key;

@ind

TABLE_NAME                INDEX_NAME                     COLUMN_NAME          TABLESPACE_NAME INDEX_TYPE
------------------------- ------------------------------ -------------------- --------------- ----------------------
WXH_TBD                   PK_O                           ID                   SYSTEM          NORMAL
发现索引还在，因为这个索引不是ORACLE自动创建的。

3）手工创建索引（非唯一索引）
这种情况我不列出来了，由于也是手工创建的，所以，删除约束后，索引还在。


4）创建表的同时，指定主键，但是语法上特殊了一点点。创建了一个唯一索引
create table wxh_tbd(id number ,primary key (id) using index (create unique index ttt on wxh_tbd(id)));
select PROPERTY from ind$  where OBJ#='193616';
  PROPERTY
----------
      4097
发现这种语法创建出来的索引Oracle也认为是自动创建的。
alter table wxh_tbd drop primary key;
@ind
NO ROWS

结果跟我们预料的一样，索引被级联的删除了。

5）创建表的同时，指定主键，但是语法上特殊了一点点。创建了一个非唯一索引
create table wxh_tbd(id number ,primary key (id) using index (create index ttt on wxh_tbd(id)));
select PROPERTY from ind$  where OBJ#='193619';
  PROPERTY
----------
      4096
由于是非唯一索引，因此值是4096      
@ind

TABLE_NAME                INDEX_NAME                     COLUMN_NAME          TABLESPACE_NAME INDEX_TYPE
------------------------- ------------------------------ -------------------- --------------- ----------------------
WXH_TBD                   TTT                             ID                   SYSTEM          NORMAL

但是结果却出乎我们的意料，索引没有级联删除。


这里可以得出一个结论：
1）对于Oracle自动（递归）创建出来的唯一索引，在进行约束（唯一约束、主键约束）删除的时候，Oracle会级联把索引也删除。特别需要注意
必须满足两个条件，1）索引必须是唯一 2）必须是Oracle自动创建。上面的例子5里，虽然Oracle也认为是自动创建的，但是由于不是唯一索引，因此也不会被Oracle级联删除。

2）对于我们手工创建的索引，在进行约束（唯一约束、主键约束）删除的时候，由于不是Oracle自动创建的，因此Oracle会保留索引。

后记：
1）拿10046跟踪drop primary key,会看到有递归的sql,去 查询ind$表，并且查询了property列（红体字）。类似如下：
select i.obj#,i.ts#,i.file#,i.block#,i.intcols,i.type#,i.flags, i.property,
  i.pctfree$,i.initrans,i.maxtrans,i.blevel,i.leafcnt,i.distkey,i.lblkkey,
  i.dblkkey,i.clufac,i.cols,i.analyzetime,i.samplesize,i.dataobj#,
  nvl(i.degree,1),nvl(i.instances,1),i.rowcnt,mod(i.pctthres$,256),
  i.indmethod#,i.trunccnt,nvl(c.unicols,0),nvl(c.deferrable#+c.valid#,0),
  nvl(i.spare1,i.intcols),i.spare4,i.spare2,i.spare6,decode(i.pctthres$,null,
  null,mod(trunc(i.pctthres$/256),256)),ist.cachedblk,ist.cachehit,
  ist.logicalread
from
ind$ i, ind_stats$ ist, (select enabled, min(cols) unicols,
  min(to_number(bitand(defer,1))) deferrable#,min(to_number(bitand(defer,4)))
  valid# from cdef$ where obj#=:1 and enabled > 1 group by enabled) c where
  i.obj#=c.enabled(+) and i.obj# = ist.obj#(+) and i.bo#=:1 order by i.obj#

2）由于 property值都是由2的倍数值的和组成的，那么一个简单的判定是不是满足递归删除索引的公式就是：
bitand(ind$.property,4097) = 4097

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/22034023/viewspace-735231/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/22034023/viewspace-735231/