ITPUX技术网

交流 . 资讯 . 分享
Make progress together!
Linux操作系统下Oracle11g R2 RAC 安装配置教程
Oracle数据库培训-备份恢复-性能优化-集群容灾
Oracle数据库DBA高级工程师培训视频

约束和索引的关系

内容发布:707174135| 发布时间:2017-3-5 19:27:09
很多人比较纠结的约束和索引的关系  
zz: jeanron100

最近有不少朋友留言或者微信问我一个问题,出乎我的意料,问

题竟然都是很相似的,所以我统一答复一下。
之前写了一篇文章 一个清理和查询都要兼顾的简单方案,看来

很多朋友还是很关心这个分区索引中的唯一性约束是怎么建立的

。我举个例子,当然内容中会贯穿说到你们的疑问,而且很可能

你么对于约束和索引还是存在这一些误解。
首先我们创建一个测试表,为了简单起见,也没有注意很多的命

名规则了。
create table test (id number,name varchar2(30))
partition by range(id)
(
partition p1 values less than(1),
partition p100 values less than (100),
partition p200 values less than(200)
);
我们就举unique约束的例子吧。
添加约束有很多种语法来实现。比如下面的三种
alter table test modify(id unique);
alter table test add constraint con_test_id_uq

unique(id);
alter table test add (constraint con_test_id_uq

unique(id));
首先我们来简单说明一个地方,就是generated的含义。
创建一个唯一性约束
alter table test modify(id unique);
这个时候查看约束的时候,发现约束名是系统自动生成的。
SQL>  select constraint_name,table_name from

user_constraints where table_name='TEST';
CONSTRAINT_NAME                TABLE_NAME
------------------------------ ----------------------

--------
SYS_C0031909                   TEST
那么对应的index的情况如何呢。发现也是自动生成的,这个时

候字段generated的含义就是约束名是系统自动生成的。
SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';
OWNER                          CONSTRAINT_NAME         

       TABLE_NAME                     GENERATED        

    INDEX_NAME
------------------------------ ----------------------

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

------- ------------------------------
TEST                           SYS_C0031909            

       TEST                           GENERATED NAME   

    SYS_C0031909
好了,我们删除这个约束,继续测试,删除的同时会把约束也一

并删除。
SQL> ALTER TABLE TEST DROP CONSTRAINT SYS_C0031909

CASCADE;
Table altered.
这个时候我们创建一个约束,指定约束名。当然下面两种语法都

没有问题的。这就体现了Oracle语法解析器的强大之处。
alter table test add constraint con_test_id_uq

unique(id);
--alter table test add (constraint con_test_id_uq

unique(id));
当然创建出来约束之后,再来看看约束udev情况。
SQL> select constraint_name,table_name from

user_constraints where table_name='TEST';
CONSTRAINT_NAME                TABLE_NAME
------------------------------ ----------------------

--------
CON_TEST_ID_UQ                 TEST
查看索引的情况,发现这个时候generated是user name,也就是

用户指定的名字。
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';

OWNER                          CONSTRAINT_NAME         

       TABLE_NAME                     GENERATED        

    INDEX_NAME
------------------------------ ----------------------

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

------- ------------------------------
TEST                           CON_TEST_ID_UQ         

       TEST                           USER NAME        

    CON_TEST_ID_UQ
好了,到了大家关注的地方了,这个时候对这个分区表创建唯一

性约束,默认是会创建一个唯一性索引,但是这个索引是一个全

局索引。查看分区索引的数据字典视图,没有任何信息可以佐证


SQL> SELECT INDEX_NAME,PARTITION_NAME FROM

USER_IND_PARTITIONS WHERE INDEX_NAME='CON_TEST_ID_UQ';
no rows selected

好了,这个时候我来矫正一些知识点,首先来说上面两种创建约

束的方式,从规范的角度来说应该是需要使用第二种的,约束名

也是望名知义。小规则还是包含着大智慧。
然后对于创建Unique的本地约束,带有本地索引该怎么做呢,这

个时候需要先创建索引,然后创建约束绑定起来。
首先删除创建的测试约束,开始正式的创建。alter table

test drop constraint con_test_id_uq;
创建本地索引。
create unique index ind_test_uq on test(id) local;
这个时候还没有唯一性约束生成。
SQL>  SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';
no rows selected
然后创建约束,这个时候仔细观察,其实会发现约束名和索引名

是不同的。也是各司其职。
alter table test add constraint con_test_uq unique(id)

using  index ind_test_uq ;
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='TEST';
OWNER                CONSTRAINT_NAME               

TABLE_NAME                     GENERATED      

INDEX_NAME
-------------------- ------------------------------ -

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

----------------------
TEST                 CON_TEST_UQ                    

TEST                           USER NAME      

IND_TEST_UQ
这个时候索引约束就映射起来了。如果直接删除索引,会有下面

的报错。
SQL> DROP INDEX IND_TEST_UQ;
DROP INDEX IND_TEST_UQ
           *
ERROR at line 1:
ORA-02429: cannot drop index used for enforcement of

unique/primary key
对于这类的错误,尤其是生产环境中还是让人非常头痛的。
我们可以drop或者disable约束,然后删除即可。
SQL> ALTER TABLE TEST DROP CONSTRAINT CON_TEST_UQ;
Table altered.

SQL> DROP INDEX IND_TEST_UQ;
Index dropped.
如果你觉得这种方式有些特别,那么我们来看看tom怎么说。在

这一点上,其实Oracle也是建议做一个权衡,还是做到索引和约

束的分离。
Oracle recommends that unique indexes be created

explicitly, and not through enabling a unique

constraint on a table.
Alternatively, you can define UNIQUE integrity

constraints on the desired columns. Oracle enforces

UNIQUE integrity constraints by automatically defining

a unique index on the unique key. However, it is

advisable that any index that exists for query

performance, including unique indexes, be created

explicitly.

当然如果觉得上面的描述有些长,来两句简单的。
A unique constraint does not necessarily create an

index.
A unique constraint does not necessarily create a

UNIQUE index.

如果还觉得不好理解,还有方法。
a) unique did not create an index
b) unique does not need a unique index
如果还不理解,我们做个小的实验。
SQL> create table t( x int, y int );
Table created.

SQL> create index t_idx on t(x,y);
Index created.

SQL> alter table t add constraint t_unique unique(x);
Table altered.

SQL> select index_name,uniqueness,index_type from

user_indexes where table_name ='T';
INDEX_NAME                     UNIQUENES INDEX_TYPE
------------------------------ --------- ------------

---------------
T_IDX                          NONUNIQUE NORMAL
可以看到我们创建的压根不是唯一性索引,但是可以和普通索引

映射起来。
SQL> SELECT

OWNER,CONSTRAINT_NAME,TABLE_NAME,GENERATED,INDEX_NAME

FROM USER_CONSTRAINTS WHERE TABLE_NAME='T'
OWNER                          CONSTRAINT_NAME         

       TABLE_NAME                     GENERATED      

INDEX_NAME
------------------------------ ----------------------

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

------------------------------
TEST                           T_UNIQUE               

       T                              USER NAME      

T_IDX
明白了这一点,其实对于理解约束和索引的关系和关联还是很有

帮助的。
个人觉得为什么索引和约束要分开,有一个很大的原因就是因为

约束disable的情况下,索引会连带删除。
SQL> create table test2(id number,name varchar2(30));
Table created.
SQL>  alter table test2 add constraint con_test2_id_uq

unique(id);
Table altered.
插入一些数据。
SQL> insert into test2 select object_id,object_name

from all_objects;
72888 rows created.
SQL> commit;
Commit complete.
索引和约束的关系如下:
SQL> select constraint_name,status,index_name from

user_constraints where table_name='TEST2';
CONSTRAINT_NAME                STATUS   INDEX_NAME
------------------------------ -------- -------------

-----------------
CON_TEST2_ID_UQ                ENABLED  

CON_TEST2_ID_UQ
把约束置为失效
SQL> alter table test2 disable constraint

con_test2_id_uq;
Table altered.
这个时候再次查看,索引已经没有了踪影,对应的段也不存在了


SQL>  select constraint_name,status,index_name from

user_constraints where table_name='TEST2';
CONSTRAINT_NAME                STATUS   INDEX_NAME
------------------------------ -------- -------------

-----------------
CON_TEST2_ID_UQ                DISABLED
如果这个表非常大,这种操作的影响还是非常的大的,如果是分

区表的全局索引那么影响也是全局性的。简单点说还是杀伤力太

大。个人见解而已。


上一篇:经常使用的Oracle监控语句
下一篇:Sqlplus生产awr报告方法
回复

使用道具 举报

内容发布:yujiebin| 发布时间:2017-3-7 14:20:03
赞!非常好的文章!值得看!
回复 支持 反对

使用道具 举报

1框架
您需要登录后才可以回帖 登录 | 立即注册

本版积分规则

快速回复 返回顶部 返回列表