首先来个反面教材来说明情况
先创建约束,会导致该约束占用列索引创建失败
drop table wjiao purge;
create table wjiao
(
id number primary key,
name varchar2(20)
);
HR@wjiao>select constraint_name,status from user_constraints where table_name='WJIAO';
CONSTRAINT_NAME STATUS
------------------------------ --------
SYS_C0011190 ENABLED
HR@wjiao>select index_name,status from user_indexes where table_name='WJIAO';
INDEX_NAME STATUS
------------------------------ --------
SYS_C0011190 VALID
HR@wjiao>create unique index idx_wjiao_id on wjiao(id);
create unique index idx_wjiao_id on wjiao(id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
禁用约束,索引被删除
HR@wjiao>alter table wjiao disable constraint SYS_C0011190;
HR@wjiao>select constraint_name,status from user_constraints where table_name='WJIAO';
CONSTRAINT_NAME STATUS
------------------------------ --------
SYS_C0011190 DISABLED
HR@wjiao>select index_name,status from user_indexes where table_name='WJIAO';
no rows selected
启用约束后,索引产生
HR@wjiao>alter table wjiao enable constraint SYS_C0011190;
HR@wjiao>select index_name,status from user_indexes where table_name='WJIAO';
INDEX_NAME STATUS
------------------------------ --------
SYS_C0011190 VALID
实际生产过程中,禁用约束然后启用很常见,但禁用之后数据库性能可能会急剧下降,因为该时间段内无可用索引
当需要在表上定义主键约束时,首先使用主键约束的列创建唯一索引,然后再添加约束,这通常被视为一种良好的做法。
drop table wjiao purge;
create table wjiao
(
id number,
name varchar2(20)
);
HR@wjiao>create unique index idx_wjiao_id on wjiao(id);
HR@wjiao>alter table wjiao add constraint wjiao_pk primary key(id);
HR@wjiao>select index_name,status from user_indexes where table_name='WJIAO';
INDEX_NAME STATUS
------------------------------ --------
IDX_WJIAO_ID VALID
禁用约束,索引依旧在
HR@wjiao>alter table wjiao disable constraint wjiao_pk;
HR@wjiao>select constraint_name,status from user_constraints where table_name='WJIAO';
CONSTRAINT_NAME STATUS
------------------------------ --------
WJIAO_PK DISABLED
HR@wjiao>select index_name,status from user_indexes where table_name='WJIAO';
INDEX_NAME STATUS
------------------------------ --------
IDX_WJIAO_ID VALID
好处是允许你在必要时禁用和重新启用约束,而不必删除底层索引。当必须在一个大表上执行约束管理时,这可以节省大量的时间。在数据仓库环境中,经常有大量数据要批量加载到表中,禁用约束然后重新启用约束是很常见的操作,因为在这种情况下,在加载之前禁用约束并在加载之后重新启用约束,可以节省大量的整体数据处理时间