ITPub博客

首页 > 数据库 > Oracle > Oracle如何管理带约束的B树索引

Oracle如何管理带约束的B树索引

原创 Oracle 作者:eric0435 时间:2020-11-06 17:13:19 0 删除 编辑

管理带约束的B树索引
B树索引与主键和唯一键约束是分不开的。这是因为Oracle使用索引来强制执行主键和唯一键约束。若没有相关联的B树索引,将不能启用主键或唯一约束。

在创建主键或唯一键约束时,你可以选择让Oracle自动创建相应索引。在这种情况下,如果删除或禁用约束,Oracle也将自动删除相应的索引。

单独创建索引和约束也是可以的。当单独创建索引和约束时,允许删了或禁用约束,而不会自动删除相应的索引。如果有大量数据需要处理,你可能希望禁用约束,而不删除相应索引。

因为在定义外键约束时,Oracle不会自动创建索引,所以必须手动在与外键约束相关的列上创建索引。在大多数情况下,在外键列上创建B树索引是有益的,因为它有助于避免锁定问题,并提高通过主键和外键列连接父/子表的查询的性能。

1 在主键列上创建B树索引
主键约束保证在一列(或列的组合)中的值可用于唯一标识表内的记录。每个表只能有一个主键约束。主键约束不能包含空值。主键约束可以被看做是唯一(unique)约束和非空(NOT NULL)约束的结合。为每个表创建主键索引有如下几个很好的理由。
.强制执行了主键列在表内必须是唯一的这一业务需求。是的,在某些情况下,可能有一个不需要主键的表(比如日志表),但在大多数情况下,主键对每个表都是必需的。
.主键中的许多列,在访问应用程序的查询的where子句中被频繁使用。这些列上的索引将会改善查询的性能。
.除非已定义父表主键或唯一键约束,否则Oracle将不允许创建子表的外键约束。因此,如果需要外键约束,就必须使用主键或唯一键约束。

对任何启用的主键,Oracle都需要一个与之对应的索引。有几种技术可用来创建主键约束及其对应的索引。
.首先创建表。然后在单独的alter table语句中添加主键约束。alter table语句同时创建了主键约束和索引。
.在create table 语句中内联(与列一起)或在单独的部分中指定主键约束。
.首先创建表,然后使用create index语句创建包含主键列的索引,最后使用alter table ... add constraint语句添加主键约束。

1.1.使用alter table来创建主键约束和索引
下面介绍的这种技术是创建主键约束和相关联的索引最可取的方法。这种方法允许对表的创建与约束和索引的定义分别进行管理。如果你使用的应用程序包含数千个表,约束和索引,那么将创建表与建立相应的约束和索引分开,可以使用管理和诊断安装问题变得更容易。这不是死板的规定,相反,它是从诊断问题演变而来的一种偏好。

在这个例子中,表和主键约束是分别创建的。首先,创建表时没有定义任何约束。

SQL> create table cust2
  2  (
  3  cust_id number,
  4  first_name varchar2(200),
  5  last_name varchar2(200)
  6  ) tablespace reporting_data;
Table created.

然后添加主键约束

SQL> alter table cust2 add constraint cust2_pk primary key(cust_id) using index tablespace reporting_index;
Table altered.

此代码示例使用alter table ... add constraint语句同时创建主键约束和唯一索引。约束和索引都被命名为CUSTS_PK。

1.2.使用create table创建主键约束和索引
另一种常见的方法是用create table语句创建主键约束和索引。可以内联(和列一起)直接指定一个约束。这种方法的优点是简单。如果在开发或测试环境中进行试验,这种方法是快速且有效的。但这种方法也有一个缺点,它不允许在多个列上定义主键。例如:

SQL> create table cust3
  2  (
  3  cust_id number primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> insert into cust3 values(1,'jing','yong');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST3');
SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
SYS_C0023486                                                                                                                     INDEX                       1    1048576

在这段代码中,Oracle创建了主键约束和相应的唯一索引。Oracle自动生成像SYS_C0023486这样的随机名称(约束和索引也被赋予了相同的名称)。

如果想要明确地对约束和索引提供名称,就可以执行下面这样的语句:

SQL> create table cust4
  2  (
  3  cust_id number constraint cust4_pk primary key,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> insert into cust4 values(1,'jing','yong');
1 row created.
SQL> commit;
Commit complete.
SQL> select a.segment_name,a.segment_type,a.extents,a.bytes from user_segments a,user_indexes b where a.segment_name=b.index_name and b.table_name in('CUST4');
SEGMENT_NAME                                                                                                                     SEGMENT_TYPE          EXTENTS      BYTES
-------------------------------------------------------------------------------------------------------------------------------- ------------------ ---------- ----------
CUST4_PK                                                                                                                         INDEX                       1    1048576

也可以指定放置索引的表空间,如下所示:

SQL> create table cust5
  2  (
  3  cust_id number constraint cust5_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.

还可以在create table语句单独的部分中定义主键约束(不和列在一起)。下面是在单独的部分定义主键约束的例子:

SQL> create table cust6
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  constraint cust6_pk primary key(cust_id) using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;
Table created.

这种技术被称为外联(out-of-line),因为约束的声明与列定义之间是用逗号隔开的。该做法与内联的方法相比,具有可以为主键指定多列的优势。

1.3.分别创建B-tree索引和主键约束
还可以首先创建索引,然后改变表以应用主键约束。为了保持这个例子的完整性,这里也显示了create table语句。

SQL> create table cust7
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create unique index cust7_pk on cust7(cust_id) tablespace reporting_index;
Index created.
SQL> alter table cust7 add constraint cust7_pk primary key(cust_id);
Table altered.

这种方法的优点是,可以独立于索引删除或禁用主键约束。在大型数据库环境中,出于数据加载时性能方面的原因,有时可能想要删除或禁用约束。有时可能需要能删除约束,但不删除索引的灵活性。在大型数据库环境中,重建索引会花费很长的时间并消耗大量的系统资源。

另一种稍微有些牵强的情况是,有可能创建一个与主键约束定义的列不同的列的索引。例如:

SQL> create table cust8
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create index cust8_pk on cust8(cust_id,first_name,last_name) tablespace reporting_index;
Index created.
SQL> alter table cust8 add constraint cust8_pk primary key(cust_id);
Table altered.

建议不要创建与约束列不同的列的主键索引,但这么做是可以的。你应该知道有这种情况,避免在诊断问题时感到困惑。

1.4.查看主键约束和索引的详细信息
用如下语句可以确认某个索引的详细信息:

SQL> select index_name,index_type,uniqueness from user_indexes where table_name='CUST7';
INDEX_NAME                                                                                                                       INDEX_TYPE                  UNIQUENES
-------------------------------------------------------------------------------------------------------------------------------- --------------------------- ---------
CUST7_PK                                                                                                                         NORMAL                      UNIQUE

输出如上所示,为了验证约束的信息,可执行如下查询:

SQL> select constraint_name,constraint_type from user_constraints where table_name='CUST7';
CONSTRAINT_NAME                                                                                                                  CONSTRAINT_TYPE
-------------------------------------------------------------------------------------------------------------------------------- ------------------------------
CUST7_PK                                                                                                                         P

1.5.删除主键约束和索引
创建主键约束时自动创建的索引不能直接删除。在这种情况下,如果像下面这样直接删除索引:

SQL> drop index cust4_pk;

就会收到如下错误信息:

drop index cust4_pk
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key

要删除索引,必须首先删除或禁用主键约束。例如,下面的语句将删除创建约束时自动创建的索引:

SQL> alter table cust4 disable constraint cust4_pk;
Table altered.
SQL> alter table cust4 drop constraint cust4_pk;
Table altered.

或者

SQL> alter table cust4 drop primary key;
Table altered.

在删除或禁用主键约束时,可以选择不删除相关索引。可以使用drop/disable constraint子句的keep index子句来保留索引。例如:

SQL> alter table cust4 drop constraint cust4_pk keep index;
Table altered.

此代码指示Oracle删除约束,但保留索引。如果要处理的是很大的表,那么出于载入或操纵数据时性能方面的原因,可能要禁用或删除约束,而非索引,因为删除与一个大表相关联的索引后,可能需要相当长的时间和大量资源来重新创建它。

要注意的另一个方面是,如果主键或唯一键被已启用的外键引用,而试图删除父表上的约束,如下所示:

SQL> alter table cust1 drop primary key;

就会收到如下错误信息:

alter table cust1 drop primary key
*
ERROR at line 1:
ORA-02273: this unique/primary key is referenced by some foreign keys

在这种情况下,需要先删除或禁用引用的外键,或使用cascade子句在主键约束已经被删除或禁用时,自动删除外键约束。例如:

SQL>alter table cust1 drop constraint cust1_pk cascade;
SQL>alter table cust1 disable constraint cust1_pk cascade;
SQL>alter table cust1 drop primary key cascade;

级联删除约束只删除了所有依赖外键的约束,但不从子表中删除任何数据。

2 在唯一键列上创建B树索引
唯一键约束的主要目的是强制地不属于主键一部分的列的唯一性。如果有非主键列在一个表中是唯一的这种业务需求,那么应该使用唯一约束。例如,你可能有定义在客户表的cust_id列上的主键,但可能还需要一个last_name和first_name列的组合上的唯一键约束。

唯一键与主键有两方面的差别。首先,唯一键可以包含NULL值,其次每个表可以定义多个唯一键(而每个表只能定义一个主键)。

如果需要为某个列创建唯一约束,那么可以通过下列几种不同的方式来实现这一需求。
.使用alter table语句来创建一个唯一约束。这将自动创建一个唯一的B树索引。
.使用create table语句来创建一个唯一约束。这也将自动创建一个唯一的B树索引。
.分别创建B树索引和约束。如果想要在禁用或删除约束时分别管理索引和约束,那么可以使用这种方法。
.只创建唯一B树索引,而不费心去创建唯一键约束。如果索引中的列不能被子表的外键引用,那么可以使用这种方法。

2.1.使用alter table来创建唯一约束和索引
这种方法是我们启用唯一键约束并创建相应索引的首选方法。正如在主键约束和索引部分中提到的,将创建表的语句与创建约束和索引的语句分离,往往更容易诊断安装问题。

下面的示例演示如何创建一个表,然后在非主键列上添加一个唯一键约束。

SQL> create table cust9
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.

接下来,使用alter table语句在cust表的last_name和first_name列的组合上创建一个名为cust9_ux1的唯一约束。

SQL> alter table cust9 add constraint cust9_uk1 unique(last_name,first_name) using index tablespace reporting_index;
Table altered.

此语句创建了该唯一约束。此外,Oracle会自动创建一个具有相同名称的相关索引。

2.2.使用create table创建唯一约束和索引
使用create table方法的优点在于,它简单且可以把索引和约束的创建封装在一个语句中。在用create table语句定义唯一约束时,它可以用内网联方式,也可以用外联方式来定义。

第一个例子显示了如何在一个列上内联地创建唯一键约束和索引。由于内联唯一键约束只可以定义在列上,所以我们添加了SSN列,它以内联内式定义了唯一键约束。

SQL> create table cust10
  2  (
  3  cust_id number constraint cust10_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15) constraint cust10_uk1 unique using index tablespace reporting_index
  7  )
  8  tablespace reporting_data;
Table created.

下一个例子使用外联技术在first_name和last_name列的组合上创建了一个唯一约束:

SQL> create table cust11
  2  (
  3  cust_id number constraint cust11_pk primary key using index tablespace reporting_index,
  4  first_name varchar2(30),
  5  last_name varchar2(30),
  6  ssn varchar2(15),
  7  constraint cust11_uk1 unique(first_name,last_name) using index tablespace reporting_index
  8  )
  9  tablespace reporting_data;
Table created.

外联方式的定义具有允许在多列上创建一个唯一键约束的优势。

2.3.分别创建B树索引和唯一键约束
如果需要分别管理索引和约束,那么可以先创建索引,然后再创建约束。例如:

SQL> create table cust12
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create unique index cust12_uk1 on cust12(first_name,last_name) tablespace reporting_index;
Index created.
SQL> alter table cust12 add constraint cust12_uk1 unique(first_name,last_name);
Table altered.

分别创建索引和约束的好处是,可以在不删除底层索引的情况下删了或禁用约束。在处理大量数据时,可考虑使用这种方法。如果你有任何理由需要禁用约束,然后重新启用它,就可以这样做而不删除索引(因为重新创建大索引可能需要很长一段时间)。

2.4.只创建唯一索引
还可以只创建唯一索引而不添加唯一约束。如果你从来没有计划用外键引用一个唯一键,那么只创建一个唯一索引而不定义唯一约束也是可以的。下面是创建一个无关联约束的唯一索引的例子:

SQL> create table cust13
  2  (
  3  cust_id number,
  4  first_name varchar2(30),
  5  last_name varchar2(30)
  6  )
  7  tablespace reporting_data;
Table created.
SQL> create unique index cust13_uk1 on cust13(first_name,last_name) tablespace reporting_index;
Index created.

在用上述语句明确地创建唯一索引时,Oracle虽然创建了一个唯一索引,但却没有在dba/all/user_constraints中为约束添加条目。为什么这很重要呢?,考虑这种情况:

SQL> insert into cust13 values(1,'JAMES','STARK');
1 row created.
SQL> insert into cust13 values(2,'JAMES','STARK');

下面是被抛出的相应错误消息提示:

insert into cust13 values(2,'JAMES','STARK')
*
ERROR at line 1:
ORA-00001: unique constraint (JY.CUST13_UK1) violated

如果让你来诊断这个问题的话,你首先会检查的地方是dba_constraints,你会按错误消息中显示的名称在其中查找一个约束。然而,没有查到任何信息。

SQL> select constraint_name from dba_constraints where constraint_name='CUST13_UK1';
no rows selected

没有相关记录可能会使用人困惑:向表中插入数据时,抛出的错误消息已经表明违反了唯一约束,但在与约束相关的数据字典视图中却没有它的信息。在这种情况下,必须在dba_indexes中查看已经创建的唯一索引的详细信息。例如:

SQL> select index_name,uniqueness from dba_indexes where index_name='CUST13_UK1';
INDEX_NAME                                         UNIQUENES
-------------------------------------------------- ---------
CUST13_UK1                                         UNIQUE

如果你希望能够使用与约束相关的数据字典视图来报告唯一键约束,就应该也定义一个约束。

2.5.删除唯一键约束和索引
如果索引是创建唯一键约束时自动创建的,那么不能直接删除该索引。在这种情况下,必须删除或禁用唯一键约束,而相关的索引会自动被删除。例如:

SQL> drop index cust11_uk1;
drop index cust11_uk1
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of unique/primary key
SQL>alter table cust11 drop constraint cust11_uk1;

这行语句同时删除约束和索引。如果想保留索引,那么可以指定keep index子句。

SQL>alter table cust drop constraint cust11_uk1 keep index;

如果分别创建索引和唯一键约束,或者如果没有唯一键约束与唯一索引相关联,那么可以直接删除该索引。

3 索引外键列
外键约束确保插入数据到子表时,相应的父表记录存在。这是一个保证数据符合父/子业务关系规则的机制。外键也被称为参照完整性约束。

不同于主键和唯一键约束,Oracle不会自动创建外键列上的索引。因此,必须在定义为外键约束的列的基础上手动创建一个外键索引。在大多数情况下,应该在与外键关联的列上创建索引。这里有两个原因。.Oracle经常可以利用外键列上的索引,来改善使用外键列来连接父表和子表的查询性能。.如果外键列上没有B树索引存在,在往子表插入数据或从子表删除数据时,它会锁定父表中的所有行。对于频繁修改父表和子表的应用程序,这将导致锁定和死锁问题。

首先讨论如何在一个外键列上创建B树索引,然后再介绍用来检测未被索引的外键列的一些技巧。

3.1.在外键列上实现索引
假设有这样的需求:必须为address1表的每条记录分配cust14表中存在的一个相应cust_id列。为了强制执行这种关系,在address1表上创建如下外键约束:

SQL> create table cust14(
  2  cust_id number,
  3  last_name varchar2(30),
  4  first_name varchar2(30)
  5  )
  6  tablespace reporting_data;
Table created.
SQL> create table address1(
  2  address_id number,
  3  cust_id number,
  4  street varchar2(30),
  5  city varchar2(30),
  6  state varchar2(30)
  7  )
  8  tablespace reporting_data;
Table created.
SQL> alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id);
alter table address1 add constraint addr1_fk1 foreign key(cust_id) references cust14(cust_id)
                                                                                     *
ERROR at line 1:
ORA-02270: no matching unique or primary key for this column-list

注意,外键列必须引用父表中定义了主键或唯一键约束的列。否则,就会收到错误提示信息"ORA-02270: 此列列表无匹配的唯一键或主键"。

因为在连接cust14表和address1表时,外键列被广泛使用,且外键列上的索引会提高性能,所以在这种情况下,必须手动创建一个索引。例如,在address1表的cust_id外键列上创建普通的B树索引。

SQL>create index addr1_fk1 on address1(cust_id);

索引名不必与外键名称相同。是否这样做,只是一种个人喜好。我们觉得约束和相应的索引具有相同的名称时,维护环境更容易。

创建索引时,如果不指定表空间名称,Oracle会在用户的默认表空间放置索引。一般情况下,最好明确指定该表的索引的存放位置。例如:

create index addr1_fk1 on address1(cust_id) tablespace reporting_index;

注意,外键列上的索引,并不一定是B树类型的。在数据仓库环境中,经常在星型模式的事实表的外键列上使用位图索引。与B树索引不同,外键列上的位图索引不能解决父/子表锁定的问题。使用星型模式的应用程序通常不从事实表删除或修改子记录,因此在数据仓库环境中,在外键列上使用位图索引,锁定不是什么问题。

3.2.确定外键列是否已经被索引
如果你从头开始创建一个应用程序,那么创建程序代码,并确保每一个外键约束都有相应的索引很容易。但是,如果你继承了一个现成的数据库,就需要审慎地检查外键列是否已经被索引。

你可以使用数据字典视图来验证,外键约束的所有列上是否有相应的索引。其基本思路是检查每个外键约束,看它是否有一个相应的索引,这个任务并不像一开始看上去那么简单。用下面的查询作为例子,它可以用来指导你按正确的途径入手:

SQL> col owner for a30
SQL> col cons_name for a30
SQL> col tab_name for a30
SQL> col cons_column for a30
SQL> col ind_column for a30
SQL> select distinct
  2  a.owner owner,
  3  a.constraint_name cons_name,
  4  a.table_name tab_name,
  5  b.column_name cons_column,
  6  nvl(c.column_name,'***Check index***') ind_column
  7  from dba_constraints a,dba_cons_columns b,dba_ind_columns c
  8  where a.constraint_type='R'
  9  and a.owner=UpPER('&&user_name')
 10  and a.owner=b.owner
 11  and a.constraint_name=b.constraint_name
 12  and b.column_name=c.column_name(+)
 13  and b.table_name=c.table_name(+)
 14  and b.position=c.column_position(+)
 15  order by tab_name,ind_column;
old   9: and a.owner=UpPER('&&user_name')
new   9: and a.owner=UpPER('JY')
OWNER                          CONS_NAME                      TAB_NAME                       CONS_COLUMN                    IND_COLUMN
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
JY                             ADDR_FK1                       ADDRESS                        CUST_ID                        CUST_ID

虽然此查询简单并易于理解,但它并不能在所有情况下都正确地报告出未索引的外键。例如,在多列外键的情况下,以不同于索引列的顺序定义约束也不要紧,只要索引列在该索引中位于前面即可。换句话说,如果约束被定义为col1和col2,那么定义一个先是col2,然后是col1的B树索引也没关系。

另一方面,使用B树索引有助于避免锁定的问题,但位图索引却做不到这点。在这种情况下,查询还应该检查索引类型。

在这些情况下,需要用更复杂的查询来检测与外键列相关的索引问题。下面的例子是一个更复杂的查询,它使用listagg分析函数来比较外键约束列(作为字符串返回一行)与相应的索引列:

这个查询会先提示你输入一个模式名称,然后将显示没有相应的索引的外键约束。此查询还检查了索引类型,位图索引可以在外键列上存在,但它不能防止锁定问题。

SQL> select
  2   case when ind.index_name is not null then
  3     case when ind.index_type in('BITMAP') then
  4        '** Bitmp idx **'
  5     else
  6        'indexed'
  7     end
  8   else
  9     '** Check idx **'
 10   end checker,
 11   ind.index_type,
 12   cons.owner,cons.table_name,ind.index_name,cons.constraint_name,cons.cols
 13   from( select
 14         c.owner,c.table_name,c.constraint_name,
 15         listagg(cc.column_name,',') within group (order by cc.column_name) cols
 16         from dba_constraints c,dba_cons_columns cc
 17         where c.owner=cc.owner
 18         and c.owner=UPPER('&&schema')
 19         and c.constraint_name=cc.constraint_name
 20         and c.constraint_type='R'
 21         group by c.owner,c.table_name,c.constraint_name) cons
 22  left outer join
 23  (select
 24    table_owner,table_name,index_name,index_type,cbr,listagg(column_name,',') within group (order by column_name) cols
 25    from(select
 26         ic.table_owner,ic.table_name,ic.index_name,ic.column_name,ic.column_position,i.index_type,
 27         connect_by_root(ic.column_name) cbr
 28         from dba_ind_columns ic,dba_indexes i
 29         where ic.table_owner=UPPER('&&schema')
 30         and ic.table_owner=i.table_owner
 31         and ic.table_name=i.table_name
 32         and ic.index_name=i.index_name
 33         connect by prior ic.column_position-1=ic.column_position
 34         and prior ic.index_name=ic.index_name)
 35  group by table_owner,table_name,index_name,index_type,cbr) ind
 36  on cons.cols=ind.cols
 37  and cons.table_name=ind.table_name
 38  and cons.owner=ind.table_owner
 39  order by checker,cons.owner,cons.table_name;
Enter value for schema: JY
old  18:        and c.owner=UPPER('&&schema')
new  18:        and c.owner=UPPER('JY')
old  29:        where ic.table_owner=UPPER('&&schema')
new  29:        where ic.table_owner=UPPER('JY')
CHECKER                        INDEX_TYPE                     OWNER                          TABLE_NAME                     INDEX_NAME                     CONSTRAINT_NAME                COLS
------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------ ------------------------------
** Check idx **                                               JY                             ADDRESS                                                       ADDR_FK1                       CUST_ID

表锁和外键
下面用一个简单的例子来演示外键列没有索引时的锁定问题。

首先创建两个表(dept和emp)并用一个外键约束把它们关联起来。

SQL> create table emp(emp_id number primary key,dept_id number);
Table created.
SQL> create table dept(dept_id number primary key);
Table created.
SQL> alter table emp add constraint emp_fk1 foreign key(dept_id) references dept(dept_id);
Table altered.

插入数据

SQL> insert into dept values(10);
1 row created.
SQL> insert into dept values(20);
1 row created.
SQL> insert into dept values(30);
1 row created.
SQL> insert into emp values(1,10);
1 row created.
SQL> insert into emp values(2,20);
1 row created.
SQL> insert into emp values(3,30);
1 row created.
SQL> commit;
Commit complete.

打开两个终端会话。在一个会话中,从子表删除一条记录,但不提交。

SQL> delete from emp where dept_id=10;
1 row deleted.

现在尝试(在另一个会话里)从父表中删除一些不受子表删除操作影响的数据。

SQL> delete from dept where dept_id=30;

对父表数据的删除操作会挂起,直到子表的事务初步提交(或回滚)。如果子表中的外键列上没有常规的B树索引,那么任何时间尝试往子表插入数据或删除子表的数据时,它都会在父表上放置一个全表锁,在子表的事务完成前,该全表锁会一直阻止删除或更新父表的数据。

回滚删除子表数据的操作

SQL> rollback;
Rollback complete.

当回滚删除子表数据的操作后,删除父表数据的操作报错,因为违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

现在额外创建一个子表外键列上的索引,并再次运行前面的操作。

SQL> create index emp_fk1 on emp(dept_id);
Index created.

再次执行删除操作,删除子表的数据

SQL> delete from emp where dept_id=10;
1 row deleted.

在另一个会话中删除父表数据不会挂起会立即报违反完整性约束

SQL> delete from dept where dept_id=30;
delete from dept where dept_id=30
*
ERROR at line 1:
ORA-02292: integrity constraint (JY.EMP_FK1) violated - child record found

这样就能够独立地运行前面列出的两个delete语句。当外键列上有B树索引时,如果从子表中删除数据,Oracle将不会过分地锁定父表中的所有行数据。


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

请登录后发表评论 登录
全部评论
系统架构师 Oracle ACE

注册时间:2011-10-12

  • 博文量
    638
  • 访问量
    6348885