index(4)管理带约束的b树索引

1.主键列上建立索引
这里我改变了书中介绍的顺序，首先介绍建表同时建立主键约束的情况，这种在测试中最常见。

SQL> create table t0115 (id int primary key,name varchar(20));

Table created.

SQL> select constraint_name,index_name from dba_constraints where table_name='T0115';

CONSTRAINT_NAME 	       INDEX_NAME
------------------------------ ------------------------------
SYS_C0010933		       SYS_C0010933
建立主键约束时如果不定义约束名称，数据库会为约束和其建立的索引自动分配一个名称，索引继承所在表的表空间存储参数。如果需要指定约束和索引的名称，需要建表时指定primary key名称。
SQL> create table t0115 (id int constraint t0115_pk primary key,name varchar(20));

Table created.

SQL> select constraint_name,index_name from dba_constraints where table_name='T0115';

CONSTRAINT_NAME 	       INDEX_NAME
------------------------------ ------------------------------
T0115_PK		       T0115_PK
这种写法有一个问题，只能定义一列主键，如果需要建立复合主键采用out-of-line写法。
SQL> create table t0115 (id int,name varchar(20),constraint t0115_pk primary key(id,name));

Table created.

SQL> select table_name,column_name from dba_cons_columns a,(select constraint_name from dba_constraints where table_name='T0115' and constraint_type='P')b where a.constraint_name=b.constraint_name;

TABLE_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
T0115
ID

T0115
NAME

第二种方式是将建表和建立主键约束分开来，这是生产环境中通常采用的做法。

SQL> create table t0115 (id int,name varchar(20));

Table created.

SQL> alter table t0115 add constraint t0115_pk primary key (id);

Table altered.

第三种方式是将建立索引和建立主键约束分开来，这种方式个人认为并不具备太大意义，书中介绍了两种场景，①需要删除或禁用约束但保留索引，这个需求可以通过alter table tab_name drop constraint con_name keep index实现；②主键定义列与索引列不同，这种情况非常少见，因为手动建立的唯一索引与主键约束建立的索引并不冲突。

SQL> create table t0115 (id int,name varchar(20));
SQL> create unique index t0115_index on t0115(id);
SQL> alter table t0115 add constraint t0115_pk primary key (id);

2.唯一键列上建立索引
唯一约束和主键约束在一些功能上是类似的，比如都可以检查键值的唯一性（主键约束可以看成是唯一约束和非空约束的结合），下面的表简单说明了两者的异同。

#	主键约束	唯一约束
共同点	键值唯一性，建立约束同时建立唯一索引	同
区别	不允许NULL	可以存在NULL值
区别	唯一主键	可以同时存在多个唯一约束

主键列上建立索引的三种方法，同样适用于唯一约束，这里不再详述。主要介绍一下单独建立唯一索引（但不建立唯一约束）的问题，这个问题解释了唯一索引和唯一约束的区别，这也是大家刚刚接触ORACLE数据库经常会有的疑问。通常情况下，唯一索引（并不依赖唯一约束）和唯一约束都用来检查列数据的唯一性，但对于有外键引用的数据列，只能使用唯一约束而不能用唯一索引。

SQL> create table t0115 (id int primary key,name varchar(20) constraint t0115_uk unique);

Table created.

SQL> create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(name) references t0115(name));

Table created.

SQL> drop table t0116;

Table dropped.

SQL> alter table t0115 drop constraint t0115_uk keep index; 

Table altered.

SQL> select index_name from dba_indexes where table_name='T0115';

INDEX_NAME
------------------------------
SYS_C0010942
T0115_UK

SQL> create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(name) references t0115(name));
create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(name) references t0115(name))
                                                                                                               *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

可以看到在表t0115 name列建立唯一约束，表t0116可以引用父表的对应字段，删除约束只保留索引后，建立外键报错“父表上没有匹配的主键或唯一键”。
唯一索引另一个值得关注的问题是违反唯一性的报错信息：

SQL> insert into t0115 values (1,'SXD');

1 row created.

SQL> insert into t0115 values (2,'SXD');
insert into t0115 values (2,'SXD')
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T0115_UK) violated
大名鼎鼎的ORA-00001违反唯一约束检查，但这个报错信息很容易引起困惑，对于没有唯一约束的情况，根据TEST.T0115_UK检查dba/user_ constraint视图是查不到信息的，这时候需要去dba/user_ indexes查看。
SQL> select constraint_name from dba_constraints where constraint_name='T0115_UK';

no rows selected

SQL> select index_name from dba_indexes where index_name='T0115_UK';

INDEX_NAME
------------------------------
T0115_UK

3.外键列上建立索引
和主键/唯一约束自带索引不同，外键列的索引需要手动建立，建立的方法也很简单，主要关注一下外键索引的作用：①改善使用外键连接父表与子表的查询性能，这个很好理解，索引可能帮助在子表中更快定位记录；②避免子表插入/删除数据时父表的全表锁定。对于第二点，做个实验验证一下。

SQL> create table t0115 (id int primary key,name varchar(20) constraint t0115_uk unique);
create table t0116 (id int primary key,name varchar(20),constraint t0116_fk foreign key(id) references t0115(id));
insert into t0115 values (1,'SXD');
insert into t0115 values (2,'SYK');
insert into t0116 values (1,'SXD');
insert into t0116 values (2,'SYK');
Table created.

SQL> 
Table created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> 
1 row created.

SQL> insert into t0116 values (2,'SYK');
insert into t0116 values (2,'SYK');insert into t0116 values (2,'SYK')
                                  *
ERROR at line 1:
ORA-00911: invalid character


SQL> insert into t0116 values (2,'SYK');

1 row created.

SQL> commit;

Commit complete.

SQL> delete from t0116 where id=1;

1 row deleted.

SQL> delete from t0115 where id=2;
delete from t0115 where id=2
*
ERROR at line 1:
ORA-02292: integrity constraint (TEST.T0116_FK) violated - child record found
当子表删除数据行，父表（非关联）数据行也被锁定。
SQL> select b.sid,b.sql_id from v$transaction a,v$session b where a.ADDR=b.TADDR;

       SID SQL_ID
---------- -------------
       140 d8ahshuqcs3n8

SQL> select sql_text from v$sql where sql_id='3rq7g6tbr3dhj';

SQL_TEXT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
delete from t0115 where id=2

SQL> select * from v$lock where sid=140 and type in ('TM','TX');

ADDR		 KADDR			 SID TY        ID1	  ID2	   LMODE    REQUEST	 CTIME	    BLOCK
---------------- ---------------- ---------- -- ---------- ---------- ---------- ---------- ---------- ----------
00007FAD1C976230 00007FAD1C976290	 140 TM      73981	    0	       3	  0	  4137		0
00007FAD1C976230 00007FAD1C976290	 140 TM      73984	    0	       3	  0	  4137		0
000000008F0EF648 000000008F0EF6C0	 140 TX      65536	  749	       6	  0	  4137		0

删除父表数据行的事务产生等待，此时140会话持有3个锁，t0115/t0116表锁和t0115行锁。这里有两点需要说明：
1.外键引起的父表锁定不是表级别的，TM行共享（RS）锁允许其他事务插入、更新、删除除TX锁定外的行。
2. 子表插入、更新数据行，父表关联数据行将被锁定；子表删除数据行，父表关联数据行依然可以操作。