主键与其索引对应的关系

今天一同事讨论到，他发现一个表有主键约束，且主键并没有没disable掉，但没有唯一索引。我第一反应是这不可能。

1. 创建一张表，指定ID为主键（不指定主键名）：

create table t_pk_uk_idx_test (id number primary key, name varchar2(100));

1.1 查看本表的约束状态：

SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE, STATUS, DEFERRABLE,VALIDATED FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'T_PK_UK_IDX_TEST';

TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     VALIDATED

------------------------------ ------------------------------ --------------- -------- -------------- -------------

T_PK_UK_IDX_TEST               SYS_C0015447                          P               ENABLED  NOT DEFERRABLE VALIDATED

1.2 查看建立表的语句：

SELECT DBMS_METADATA.GET_DDL('TABLE','T_PK_UK_IDX_TEST','HNYD') FROM DUAL;

/*

  CREATE TABLE "HNYD"."T_PK_UK_IDX_TEST"

   (  "ID" NUMBER,

  "NAME" VARCHAR2(100),

   PRIMARY KEY ("ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "DBADATA1"  ENABLE

   ) SEGMENT CREATION IMMEDIATE

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "DBADATA1"

*/

1.3 查看被建立约束的语句：

SELECT DBMS_METADATA.GET_DDL('CONSTRAINT','SYS_C0015447','HNYD') FROM DUAL;

  /*

  ALTER TABLE "HNYD"."T_PK_UK_IDX_TEST" ADD PRIMARY KEY ("ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "DBADATA1"  ENABLE

  */

1.4 查看被建立索引的语句：

SELECT DBMS_METADATA.GET_DDL('INDEX','SYS_C0015447','HNYD') FROM DUAL;

  /*

  CREATE UNIQUE INDEX "HNYD"."SYS_C0015447" ON "HNYD"."T_PK_UK_IDX_TEST" ("ID")

  PCTFREE 10 INITRANS 2 MAXTRANS 255

  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1

  BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)

  TABLESPACE "DBADATA1"

  */

如果通过create table t_pk_uk_idx_test (id number primary key, name varchar2(100)); 这种方式不指定主键名，在建立表时，系统会默认分配一个名字，并建立同名的一个唯一索引，一个主键约束。并没有指定“not null" ，但主键约束肯定不允许ID列有空值。

如果尝试删除这个唯一索引会报错：

SQL> drop index  "HNYD"."SYS_C0015447" ;

drop index  "HNYD"."SYS_C0015447"

                   *

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key --无法删除用于强制唯一/主键的索引。

SQL> ALTER TABLE "HNYD"."T_PK_UK_IDX_TEST" disable PRIMARY KEY;

Table altered

SQL> drop index "HNYD"."SYS_C0015447" ;

drop index "HNYD"."SYS_C0015447"

                  *

ERROR at line 1:

ORA-01418: specified index does not exist

 这样Disable后，对应的索引也被删除，但约束还在。

SQL> SELECT TABLE_NAME,CONSTRAINT_NAME,CONSTRAINT_TYPE, STATUS, DEFERRABLE,VALIDATED FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'T_PK_UK_IDX_TEST';

TABLE_NAME                     CONSTRAINT_NAME                CONSTRAINT_TYPE STATUS   DEFERRABLE     VALIDATED

------------------------------ ------------------------------ --------------- -------- -------------- -------------

T_PK_UK_IDX_TEST               SYS_C0015447                   P               DISABLED NOT DEFERRABLE NOT VALIDATED

再次enable后，索引建立起来了，但发现索引的OBJECT_ID前后不一样了，说明索引的确是被删除了。

2. 那么整个过程到底发生了什么？ 做一个10046 看下

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

Session altered.

SQL> create table hnyd.t_pk_uk_idx_test1 (id number primary key, name varchar2(100));

Table created.

SQL> alter session set events '10046 trace name context off';

Session altered.

SQL> select value from v$diag_info WHERE name = 'Default Trace File';

VALUE

--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/trac/trac2/trace/trac2_ora_79986.trc

 tkprof /u01/app/oracle/diag/rdbms/trac/trac2/trace/trac2_ora_79986.trc /tmp/79986.txt

--前面的数字表未行号：

  28 PARSING IN CURSOR #140461753592416 len=79 dep=0 uid=0 oct=1 lid=0 tim=1470392199306885 hv=1413582164 ad='849bb510' sqlid='99a5     901a433an'

  29 create table hnyd.t_pk_uk

  30 END OF STMT

503 insert into obj$(owner#,name,namespace,obj#,type#,ctime,mtime,stime,status,remoteowner,linkname,subname,dataobj#,flags,oid$,sp     are1,spare2,spare3) values(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17,:18)

504 END OF STMT

505 PARSE #140461752366488:c=1000,e=648,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=4,plh=0,tim=1470392199365433

506 BINDS #140461752366488:

507  Bind#0

508   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

509   oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0

510   kxsbbbfp=7fbfcce95600  bln=22  avl=03  flg=05

511   value=113

512  Bind#1

513   oacdty=01 mxl=32(17) mxlc=00 mal=00 scl=00 pre=00

514   oacflg=10 fl2=0001 frm=01 csi=873 siz=32 off=0

515   kxsbbbfp=850f86e6  bln=32  avl=17  flg=09

516   value="T_PK_UK_IDX_TEST1"

con$是视图dba_constraint的基表, 存放约束编号和约束名称。

 867 PARSING IN CURSOR #140461752215744 len=60 dep=1 uid=0 oct=2 lid=0 tim=1470392199379315 hv=1878790044 ad='84b4fa48' sqlid='8nhg     2pdrzs3ww'

868 insert into con$(owner#,name,con#,spare1)values(:1,:2,:3,:4)

869 END OF STMT

870 PARSE #140461752215744:c=0,e=481,p=0,cr=0,cu=2,mis=1,r=0,dep=1,og=4,plh=0,tim=1470392199379314

871 BINDS #140461752215744:

872  Bind#0

873   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

874   oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0

875   kxsbbbfp=7fbfcce95600  bln=22  avl=03  flg=05

876   value=113

877  Bind#1

878   oacdty=01 mxl=32(12) mxlc=00 mal=00 scl=00 pre=00

879   oacflg=10 fl2=0001 frm=01 csi=873 siz=32 off=0

880   kxsbbbfp=852d51fe  bln=32  avl=12  flg=09

881   value="SYS_C0015449"

 895 =====================

896 PARSING IN CURSOR #140461752211872 len=88 dep=1 uid=0 oct=9 lid=0 tim=1470392199381070 hv=1310241531 ad='84a712c8' sqlid='7q23     u0x71jcrv'

897 CREATE UNIQUE INDEX "HNYD"."SYS_C0015449" on "HNYD"."T_PK_UK_IDX_TEST1"("ID") NOPARALLEL

898 END OF STMT

这里并没结束，后面还有很多对表、对象等基表的操作关联等。

cdef$也dba_constraint 的基表之一；

2049 insert into cdef$(obj#,con#,type#,intcols,condlength,condition,robj#,rcon#,match#,refact,enabled,cols,defer,mtime,spare1,spare     2,spare3)values(:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,0,null,:7),decode(:8,0,null,:8),decode(:9,0,nu     ll,:9),decode(:10,0,null,:10), decode(:11,0,null,:11),:12, decode(:13,0,null,:13),:14,:15,:16,:17)

2050 END OF STMT

到这里只能说明主键约束与唯一索引有一定的关系，在建立主键时，若这个字段上没有索引，会尝试在这上面建立唯一索引。

3. 假如这个字段上有索引呢。重新创建一个表，并创建一个普通的组合索引。

SQL> create table t_pk_uk_idx_test2 (id number, name varchar2(100));

Table created

SQL> create index idx_id_name_normal on t_pk_uk_idx_test2(id,name);

Index created

在ID列上增加主键约束。

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

Session altered.

SQL> alter table HNYD.t_pk_uk_idx_test2 add constraint constraint_c1 primary key(ID);

alter session set events '10046 trace name context off';

Table altered.

SQL>

Session altered.

SQL> select value from v$diag_info WHERE name = 'Default Trace File';

VALUE

--------------------------------------------------------------------------------

/u01/app/oracle/diag/rdbms/trac/trac2/trace/trac2_ora_85746.trc

 29 select count(FA#) from SYS_FBA_TRACKEDTABLES where OBJ# = :1 and bitand(FLAGS, :2)=0

50 LOCK TABLE "HNYD"."T_PK_UK_IDX_TEST2" IN SHARE MODE  NOWAIT

79 alter table HNYD.t_pk_uk_   

515 PARSING IN CURSOR #139746758027104 len=60 dep=1 uid=0 oct=2 lid=0 tim=1470394710583377 hv=1878790044 ad='84b4fa48' sqlid='8nhg     2pdrzs3ww'

516 insert into con$(owner#,name,con#,spare1)values(:1,:2,:3,:4)

517 END OF STMT

518 PARSE #139746758027104:c=1000,e=424,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=4,plh=0,tim=1470394710583375

519 BINDS #139746758027104:

520  Bind#0

521   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

522   oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0

523   kxsbbbfp=7f1953e20f50  bln=22  avl=03  flg=05

524   value=113

525  Bind#1

526   oacdty=01 mxl=32(13) mxlc=00 mal=00 scl=00 pre=00

527   oacflg=10 fl2=0001 frm=01 csi=873 siz=32 off=0

528   kxsbbbfp=710a6596  bln=32  avl=13  flg=09

529   value="CONSTRAINT_C1"

--是否违反约束校验

569 PARSING IN CURSOR #139746758025304 len=260 dep=1 uid=0 oct=3 lid=0 tim=1470394710608559 hv=1153179406 ad='710a6188' sqlid='fks     n1dj2bs7sf'

570  select /*+ all_rows ordered dynamic_sampling(2) */ A.rowid, :1, :2, :3 from "HNYD"."T_PK_UK_IDX_TEST2" A, (select /*+ all_row     s */ "ID" from "HNYD"."T_PK_UK_IDX_TEST2" A where( "ID" is not null) group by  "ID" having count(1) > 1) B where( "A"."ID" = "     B"."ID")

571 END OF STMT

872 select REGEXP_SUBSTR(:1,'\s+rename\s+to\s+',1,1,'i') from dual

873 END OF STMT

874 PARSE #139746756289296:c=0,e=346,p=0,cr=0,cu=0,mis=1,r=0,dep=3,og=1,plh=0,tim=1470394710643058

875 BINDS #139746756289296:

876  Bind#0

877   oacdty=01 mxl=128(80) mxlc=00 mal=00 scl=00 pre=00

878   oacflg=13 fl2=206001 frm=01 csi=873 siz=128 off=0

879   kxsbbbfp=7f1953c45e60  bln=128  avl=80  flg=05

880   value="alter table HNYD.t_pk_uk_idx_test2 add constraint constraint_c1 primary key(ID)"

1286 PARSING IN CURSOR #139746756266776 len=350 dep=1 uid=0 oct=2 lid=0 tim=1470394710656978 hv=453737599 ad='84a1d8a0' sqlid='dmh1     7scdhqz3z'

1287 insert into cdef$(obj#,con#,type#,intcols,condlength,condition,robj#,rcon#,match#,refact,enabled,cols,defer,mtime,spare1,spare     2,spare3)values(:1,:2,:3,decode(:4,0,null,:4),decode(:5,0,null,:5),:6,decode(:7,0,null,:7),decode(:8,0,null,:8),decode(:9,0,nu     ll,:9),decode(:10,0,null,:10), decode(:11,0,null,:11),:12, decode(:13,0,null,:13),:14,:15,:16,:17)

1288 END OF STMT

cdef$此处应该就是约束的定义表：

1291  Bind#0

1292   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

1293   oacflg=00 fl2=0001 frm=00 csi=00 siz=96 off=0

1294   kxsbbbfp=7f1953e26b40  bln=22  avl=04  flg=05

1295   value=94495 --------------------------表的OBJECT_ID

1296  Bind#1

1297   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

1298   oacflg=00 fl2=0001 frm=00 csi=00 siz=0 off=24

1299   kxsbbbfp=7f1953e26b58  bln=22  avl=04  flg=01

1300   value=15450----------------------------约束的ID

1352  Bind#16

1353   oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

1354   oacflg=00 fl2=0001 frm=00 csi=00 siz=24 off=0

1355   kxsbbbfp=7f1953e26a20  bln=22  avl=04  flg=05

1356   value=94496 -- idx_id_name_normal的OBJECT_ID. 与约束关联的索引

SELECT con#,obj#,enabled FROM sys.cdef$ WHERE con#=15450;

select owner,object_id,object_name from dba_objects where object_id in (94495,94496)

查看此主键约束与表、索引的关联

SQL> SELECT con#,obj#,enabled FROM sys.cdef$ WHERE con#=15450;

      CON#       OBJ#    ENABLED

---------- ---------- ----------

     15450      94495      94496

SQL> select owner,object_id,object_name from dba_objects where object_id in (94495,94496)  ;

OWNER                           OBJECT_ID OBJECT_NAME

------------------------------ ---------- --------------------------------------------------------------------------------

HNYD                                94496 IDX_ID_NAME_NORMAL

HNYD                                94495 T_PK_UK_IDX_TEST2

--约束类型： sys.cdef$ 里面type#=2

SQL> SELECT CONSTRAINT_NAME,CONSTRAINT_TYPE,TABLE_NAME FROM DBA_CONSTRAINTS WHERE TABLE_NAME = 'T_PK_UK_IDX_TEST2';

CONSTRAINT_NAME                CONSTRAINT_TYPE TABLE_NAME

------------------------------ --------------- ------------------------------

CONSTRAINT_C1                  P               T_PK_UK_IDX_TEST2

--尝试删除这个普通索引

SQL> drop index HNYD.IDX_ID_NAME_NORMAL;

drop index HNYD.IDX_ID_NAME_NORMAL

                *

ERROR at line 1:

ORA-02429: cannot drop index used for enforcement of unique/primary key

---索引并非唯一的

SQL> select index_name,index_type,table_name,uniqueness from dba_indexes WHERE index_name = 'IDX_ID_NAME_NORMAL';

INDEX_NAME                     INDEX_TYPE                  TABLE_NAME                     UNIQUENESS

------------------------------ --------------------------- ------------------------------ ----------

IDX_ID_NAME_NORMAL             NORMAL                      T_PK_UK_IDX_TEST2              NONUNIQUE

---约束名与约束编号

SQL> SELECT owner#,name,con# FROM sys.con$ oc WHERE name='CONSTRAINT_C1';

    OWNER# NAME                                 CON#

---------- ------------------------------ ----------

       113 CONSTRAINT_C1                       15450

此时查看表的定义为：

  CREATE TABLE "HNYD"."T_PK_UK_IDX_TEST2"

   (        "ID" NUMBER,

        "NAME" VARCHAR2(100),

         CONSTRAINT "CONSTRAINT_C1" PRIMARY KEY ("ID")

  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

  TABLESPACE "DBADATA1"  ENABLE

   ) SEGMENT CREATION DEFERRED

  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255

NOCOMPRESS LOGGING

  TABLESPACE "DBADATA1" 

所以认为这个主键约束，利用现有的索引，管理表中的数据（其实键约束并不一定需要有索引，比如外键，但外键没有索引可能造成严重的锁问题）。名称与索引名并不对就。单个字段的主键也可以利用组合索引（本例就是利用的组合索引）。

平常说的主键非空、唯一，这是对的。反正我是很长一段时间误解了，以为索引就要是唯一索引的，其实并不是。因为主键对数据约束了，所数据具有唯一性，主键对应的索引定义并不一定要唯一，有可用的索引即可，但建立结束后，表里面的数据受约束的约束。

可能主键上索引的目的之一，应该是利用其对应的索引，在DML操作时，不用造成全表的锁定（当插入数据时，当a会话插入数据后不提交，b会话执行同样的语句，会产生行锁，本例分别在两个会话执行insert into HNYD.T_PK_UK_IDX_TEST2  select 1, 'a' from dual; 即可模拟）。

怎么测试呢：

??a. 拟将索引设置为不可用状态，再对数据操作，但失败了，主键不可用后，对表无法进行DML操作

SQL> alter index IDX_ID_NAME_NORMAL unusable;

Index altered

SQL> delete hnyd.T_PK_UK_IDX_TEST2;

delete hnyd.T_PK_UK_IDX_TEST2

*

ERROR at line 1:

ORA-01502: index 'HNYD.IDX_ID_NAME_NORMAL' or partition of such index is in

unusable state

 b. 约束的是否有效状态判断是decode(c.enabled, NULL, 'DISABLED', 'ENABLED')), 所以也不能对将约束定义里面的存放索引对象ID清空。

 c.将sys.cdef$ 里面enable字段改成一个不合法的值。并flush共享池，不过这直接导致这张表不能执行任何语句了，包括查询。

SQL> insert into HNYD.T_PK_UK_IDX_TEST2

  2    select 2, 'a' from dual;

insert into HNYD.T_PK_UK_IDX_TEST2

                 *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01426: numeric overflow

 d.将索引从数据字典里面修改。

SQL> update sys.ind$ set obj#=-1 where  obj#=94496;

1 row updated.

SQL> insert into HNYD.T_PK_UK_IDX_TEST2

  2    select 2, 'a' from dual;

insert into HNYD.T_PK_UK_IDX_TEST2

                 *

ERROR at line 1:

ORA-00604: error occurred at recursive SQL level 1

ORA-01426: numeric overflow

SQL> update sys.ind$ set obj#=94496 where  obj#=-1;

1 row updated.

SQL> insert into HNYD.T_PK_UK_IDX_TEST2

  2    select 2, 'a' from dual;

insert into HNYD.T_PK_UK_IDX_TEST2

*

ERROR at line 1:

ORA-00001: unique constraint (HNYD.CONSTRAINT_C1) violated

测试失败。没找到办法让主键约束处理可用状态，但其对应的索引失效、者无对应的索引情况下进行DML操作。

如果利用主键做为等值条件访问，执行计划问题。

--如前所述，虽然这个索引与主键关联，但如下图。

再创建一个唯一索引

SQL> create unique index hnyd.pk_pkupidxtest2_id on hnyd.T_PK_UK_IDX_TEST2(id);

Index created.

SQL> alter table T_PK_UK_IDX_TEST2   drop  constraint CONSTRAINT_C1 ;

Table altered

--这里删除约束，并不会删除关联的索引

SQL> alter table T_PK_UK_IDX_TEST2

  2    add  constraint CONSTRAINT_C1 primary key (ID) using index pk_pkupidxtest2_id;

Table altered

---

再回到前面的问题，如果主键指定的索引不带主键对应的列会是什么效果。（步骤略）

a. 删除约束和所有索引

b. 创建name列上的索引

c.增加主键约束，指向name列的索引。

SQL> create index idx_pkukidx_name on T_PK_UK_IDX_TEST2(name);

Index created

SQL>   alter table T_PK_UK_IDX_TEST2

  2    add  constraint pk_nothing_id primary key (ID) using index idx_pkukidx_name;

SQL> alter table T_PK_UK_IDX_TEST2  add   constraint pk_nothing_id primary key (ID) using index idx_pkukidx_name;

alter table T_PK_UK_IDX_TEST2  add   constraint pk_nothing_id primary key (ID) using index idx_pkukidx_name

*

ERROR at line 1:

ORA-14196: Specified index cannot be used to enforce the constraint.

至此：

1. 主键约束，约束了数据的合法性（唯一，非空）

2. 主键约束一定需要与合法索引关联（从测试看，需要这个索引带主键字段），并不要求索引列和主键列相同，也不需要唯一索引，可以手工指定。若不指定且没有索引可选，会自己建立与主键名一样的唯一索引。

3. 索引在数据访问的时候快速定位到数据，如果主键上无索引（从外键无索引联想）任何DML操作都会将全表锁住。

4. 因为有了主键约束，基对应列上的索引，最好建立成唯一的，唯一索引可以走“index unique scan" 速度最快，而不是索引。
5.主键和索引是完全不同的概念，一个是约束，一个是索引。主键和唯一索引没什么可比性。

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

转载于:http://blog.itpub.net/27349469/viewspace-2123074/