ITPub博客

首页 > Linux操作系统 > Linux操作系统 > 删除主键,唯一约束,什么时候会自动删除列上的索引?

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

原创 Linux操作系统 作者:wei-xh 时间:2012-07-11 21:45:20 0 删除 编辑
你可能经常会有这样的顾虑,在删除唯一约束或者主键约束的时候,附带的索引会不会被删除掉?
现在的团队有一个规范,但凡是增加主键,都需要先手工创建索引,再增加主键。给出的原因是:这样删除主键的时候,索引就不会被删除掉了。
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/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论
Oracle ACE组成员,DBGeeK用户组发起人。曾在DTCC、ORACLE技术嘉年华、Gdevops等公开场合做过数据库技术专题分享,2017年应Oracle邀请在世界最大的数据库会议OOW上做技术分享。组织翻译了《拨云见日,解密Oracle ASM内核》一书。

注册时间:2009-07-04

  • 博文量
    422
  • 访问量
    2278228