oracle键、索引、约束及其区别
键、索引、约束及其区别
今天下午刚好没事，把一些基础性的概念理顺一下，存档，省的麻烦，嘿嘿
一．索引
1. 什么是索引？
索引是对数据库表中一列或多列的值进行排序的一种结构。
在关系型数据库中，索引是一种与表有关的数据库结构，是事实存在的。它可以使对于表的select等等操作更加快速，相当于一本书的目录。
对于一张表，如果我们想要找到某一列符合特定值的记录，第一种方法是全表搜索，匹配，然后把所有符合的记录列出，但是这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。
索引是一个单独的、物理的数据库结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。由此可知，索引是要消耗数据库空间的。
并非所有的数据库都以相同的方式使用索引。作为通用规则，只有当经常查询索引列中的数据时，才需要在表上创建索引。索引占用磁盘空间，并且降低添加、删除和更新行的速度。在多数情况下，索引用于数据检索的速度优势大大超过它的不足之处。但是，如果应用程序非常频繁地更新数据或磁盘空间有限，则可能需要限制索引的数量。
可以使用单列作为索引，也可以使用多列联合作为索引。
2. 索引的优缺点
优点：
（1）大大加快数据的检索速度;
（2）创建唯一性索引，保证数据库表中每一行数据的唯一性;
（3）加速表和表之间的连接;
（4）在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。
缺点：
（1）索引需要占物理空间。
（2）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。
建立索引时的注意事项：
（1） 考虑已在表上创建的索引数量。最好避免在单个表上有很多索引
（2） 检查已在表上创建的索引的定义。最好避免包含共享列的重叠索引
（3） 检查某列中唯一数据值的数量，并将该数量与表中的行数进行比较。比如如果有1000w记录，某字段为性别，只有男，女。也就是说一半的记录都是重复的，这样就要考虑是否还有必要建立索引了。
3. 一些索引类别
（1） 普通索引
也即不加任何限制的索引。可通过以下语句理解。
create table zjj_temp_1 (id number(10),first_name char(10),last_name char(10),age number(3),val number(10,2));
insert into zjj_temp_1 values(1,'junjie','zhang',25,4000);
select * from zjj_temp_1;
此时一条记录已经插入进入了。
create index zjj_temp_index_1 on zjj_temp_1(first_name);
--------建立索引
insert into zjj_temp_1 values(1,'junjie','zhang',25,4000)
--------再次插入一条一模一样的记录
select * from zjj_temp_1;
Ok!两条记录出现了，也即此索引的作用是让你再查找first_name为某一个特定值的记录时速度更快而已，仅此而已。
（2） 唯一索引
一种索引，不允许具有索引值相同的行，从而禁止重复的索引或键值。系统在创建该索引时检查是否有重复的键值，并在每次使用 INSERT 或 UPDATE 语句添加数据时进行检查。
继续分析例子：
drop index zjj_temp_index_1; ----删除上文创建的普通索引。
create unique index zjj_temp_1 on zjj_temp_1(id);
----建立唯一索引
数据库报错了？
是的，说找到重复的关键字。
从上文我们可以看到，zjj_temp_1表中有两条记录，id都是1.
这样是唯一索引是不允许的，所以自然就创建不起来了。
delete from zjj_temp_1 where rownum<2; --删除一行
create unique index zjj_temp_1 on zjj_temp_1(id);
----继续创建，发现这次成功了。
insert into zjj_temp_1 values(2,'junjie','zhang',25,4000);
----成功
insert into zjj_temp_1 values(1,'kesi','ma',25,4000); 失败！！！
耶！就是这样！
（3） 主键索引
数据库表经常有一列或列组合，其值唯一标识表中的每一行。该列称为表的主键。它和唯一索引的共性在于都不允许有重复记录，区别在于，唯一索引是不限制null的，也就是说或可以有一条以上的null值插入，但是主键却限定不能为空。
继续执行语句：
insert into zjj_temp_1 values(null,'kesi','ma',25,4000); ---成功
select * from zjj_temp_1;
这就表明唯一索引是允许有空值的。
Drop index zjj_temp_1; ---删除唯一索引
alter table zjj_temp_1 add constraint zjsy_1 primary key(id); ---建立主键
我们可以发现id有一条记录为空，所以是无法建立主键的。
删除那条空记录就可以了。
（4） 聚簇索引和非聚簇索引
聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。
聚簇是根据码值找到数据的物理存储位置，从而达到快速检索数据的目的。Oracle聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。非聚簇索引的顺序与数据物理排列顺序无关，叶节点仍然是索引节点，只不过有一个指针指向对应的数据块。由于聚簇索引要按照索引排序，所以一个表最多只能有一个聚簇索引，但可以使用多列。
ORACLE中的聚簇表是指两个表有一个字段完全相同，并且在业务中经常会按这个字段为目标连接这两个表，这时建立聚簇表，
两个表公用一个字段，能减少占用空间，并能明显提高连接查询速度。
这两篇都有实际的例子，这里就不再深入讨论了。
建立聚簇索引的思想
1、大多数表都应该有聚簇索引或使用分区来降低对表尾页的竞争，在一个高事务的环境中，对最后一页的封锁严重影响系统的吞吐量。
2、在聚簇索引下，数据在物理上按顺序排在数据页上，重复值也排在一起，因而在那些包含范围检查(between、<、<=、<>、>=)或使用group by或orderby的查询时，一旦找到具有范围中第一个键值的行，具有后续索引值的行保证物理上毗连在一起而不必进一步搜索，避免了大范围扫描，可以大大提高查询速度。
3、在一个频繁发生插入操作的表上建立聚簇索引时，不要建在具有单调上升值的列(如IDENTITY)上，否则会经常引起封锁冲突。
4、在聚簇索引中不要包含经常修改的列，因为码值修改后，数据行必须移动到新的位置。
5、选择聚簇索引应基于where子句和连接操作的类型。
具体的聚簇索引请参考以下文章：
http://blog.sina.com.cn/s/blog_607b68cc0100f5jo.html
http://space.itpub.net/9778796/viewspace-660186
二．键
1. 什么叫键
数据库中的键（key）又称为关键字，是关系模型中的一个重要概念，它是逻辑结构，不是数据库的物理部分。
2. 唯一键
唯一键，即一个或者一组列，其中没有重复的记录。可以唯一标示一条记录。
3. 主键
属于唯一键，是一个比较特殊的唯一键。区别在于主键不可为空。
4. 外键
如果公共关键字在一个关系中是主关键字，那么这个公共关键字被称为另一个关系的外键。由此可见，外键表示了两个关系之间的联系。以另一个关系的外键作主关键字的表被称为主表，具有此外键的表被称为主表的从表。外键又称作外关键字。换而言之，如果关系模式R中的某属性集不是R的主键，而是另一个关系R1的主键则该属性集是关系模式R的外键，通常在数据库设计中缩写为FK。
外键在开发中基本使用不到，主要是数据库用来保证数据的完整性的
举个简单的例子
表A内有列C1
表B内有列C2
将C2的外键指向C1
那么当你向表B插入数据时，C2的内容必须为C1中的一个
还有几个约束需要你设置
如删除或者修改表A中的字段时怎么处理表B中相关联的数据
举例如下：
create table z_laopo (id number(5) primary key,name char(20),age number(3),zhiye char(20)); ----创建老婆表
create table z_nanren
(id number(5) primary key,
name char(20),
age number(3),
laopo_id number(5),
foreign key(laopo_id) references z_laopo(id)
); -----创建男人表，并限定laopo_id为老婆表的外键
insert into z_laopo values (1,'fengjie',18,'accontant');
insert into z_laopo values (2,'cangjingkong',25,'teacher');
----向老婆表插入记录
insert into z_nanren values (1,'nanren_1',24,1)
insert into z_nanren values (2,'nanren_2',24,2)
insert into z_nanren values (3,'nanren_3',24,3)
----向男人表也插入3条记录
前两条没问题，第三条报错：
因为不存在id为3的老婆，也即laopo_id只能再1,2之间选择。除非z_laopo表中有3这个id。
5. 父键
对于有外键关系的2张表，存在外键的表所参照的表叫主表，而存在外键的表叫从表，上例中z_nanren为从表，z_laopo为主表。Lao_id为外键，z_laopo表的id为父键。
三．约束
像主键、唯一等等其实都是一种约束。
看一下语句就能明白了
create table z_test1(a char(10)); ---创建表
alter table z_test1 add constraint PK_Z_TEST1 primary key(a);
----添加主键
create table z_test2(a char(10)); ---创建表
alter table z_test2 add constraint UQ_Z_TEST1 unique(a); ----添加唯一约束
其中主键约束比唯一约束更严格，不能为空。
四．键、索引、约束的区别
一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。
(1) 主键索引和主键有什么关系？
主键索引是创建主键的时候系统自动创建的索引，主键要求不重复，不为空，但是他如何判断有效率呢？当然是建索引了，老是全表遍历还不疯掉。
所以建立主键会自动的建立主键索引。
(2) 主键和唯一键的区别在于唯一键可以为空，主键不可以
(3) 建立唯一约束和唯一索引又什么区别？
同理，建立唯一约束的时候，也会自动的创建唯一索引。建立唯一索引可以说是唯一约束的一种手段。
基本上，实现起来是没有什么区别的。如果实在理解不了，就当一样好了。
(4) 聚簇索引和非聚簇索引有何区别？
这个上边已经讲和很详细了，还附有两篇文章，这里就不说了。
(5) 约束和主键有什么区别？
约束一般有主键约束，外键约束，唯一约束等。
分别为primary key，foreign key，unique 其中主键约束只是约束的一种。
其实他们是不同概念的东西。
Ok，基本就整理成这样吧!
原文地址：http://blog.sina.com.cn/s/blog_82ee2ee60100xwl4.html
这篇文章的意义已经理解，但是他举得例子不错，保存下来，下次想看的时候可以参考一下。
一般，我们看到术语“索引”和“键”交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。索引和键的混淆通常是由于数据库使用索引来实施完整性约束。
接下来我们看看数据库中的主键约束、唯一键约束和唯一索引的区别。
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for Linux: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> create table test (
2 id int,
3 name varchar2(20),
4 constraint pk_test primary key(id))
5 tablespace users;
Table created.
SQL> select constraint_name, constraint_type from user_constraints;
CONSTRAINT_NAME C
------------------------------ -
PK_TEST P
在test表中，我们指定了ID列作为主键，Oracle数据库会自动创建一个同名的唯一索引：
SQL> select index_name, index_type, uniqueness, tablespace_name
2 from user_indexes
3 where table_owner='SCOTT'
4 and table_name = 'TEST';
INDEX_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME
-------------------- -------------------- --------- ------------------------------
PK_TEST NORMAL UNIQUE USERS
此时，如果我们再试图在ID列上创建一个唯一索引，Oracle会报错，因为该列上已经存在一个唯一索引：
SQL> create unique index idx_test_uk on test(id);
create unique index idx_test_uk on test(id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
即使创建非唯一索引也不行：
SQL> create index idx_test_id on test(id);
create index idx_test_id on test(id)
*
ERROR at line 1:
ORA-01408: such column list already indexed
那么唯一键约束的情况是怎样的呢？
SQL> drop table test purge;
Table dropped.
SQL> create table test(
2 id int,
3 name varchar2(20),
4 constraint uk_test unique(id));
Table created.
SQL> select constraint_name, constraint_type from user_constraints;
CONSTRAINT_NAME C
------------------------------ -
UK_TEST U
查看此时的索引情况：
SQL> select index_name, index_type, uniqueness, tablespace_name
2 from user_indexes
3 where table_owner='SCOTT'
4 and table_name = 'TEST';
INDEX_NAME INDEX_TYPE UNIQUENES TABLESPACE_NAME
-------------------- -------------------- --------- ------------------------------
UK_TEST NORMAL UNIQUE USERS
Oracle同样自动创建了一个同名的唯一索引，而且也不允许再在此列上创建唯一索引或非唯一索引。
我们知道，主键约束要求列值非空（NOT NULL），那么唯一键约束是否也要求非空呢？
SQL> insert into test values(1, 'Sally');
1 row created.
SQL> insert into test values(null, 'Tony');
1 row created.
SQL> insert into test values(null, 'Jack');
1 row created.
SQL> select * from test;
ID NAME
---------- --------------------
1 Sally
Tony
Jack
从实验结果来看，唯一键约束并没有非空要求。
接下来我们看看唯一索引对列值的非空要求有什么不同。
SQL> drop table test purge;
Table dropped.
SQL> create table test(
2 id int,
3 name varchar2(20));
Table created.
SQL> create unique index idx_test_id on test (id);
Index created.
SQL> insert into test values(1, 'Sally');
1 row created.
SQL> insert into test values(null, 'Tony');
1 row created.
SQL> insert into test values(null, 'Jack');
1 row created.
SQL> select * from test;
ID NAME
---------- --------------------
1 Sally
Tony
Jack
通过实验，我们看出唯一索引与唯一键约束一样对列值非空不做要求。
如果我们让主键约束或者唯一键约束失效，Oracle自动创建的唯一索引是否会受到影响？
SQL> drop table test purge;
Table dropped.
SQL> create table test(
2 id int,
3 name varchar2(20),
4 constraint uk_test unique(id));
Table created.
SQL> select index_name, index_type, uniqueness from user_indexes;
INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
UK_TEST NORMAL UNIQUE
SQL> alter table test disable constraint uk_test;
Table altered.
SQL> select index_name, index_type, uniqueness from user_indexes;
no rows selected
当主键约束或者唯一键约束失效时，Oracle会删除隐式创建的唯一索引。
如果我们先创建唯一索引，再创建主键或者唯一键约束，情况又会怎样呢？
SQL> drop table test purge;
Table dropped.
SQL> create table test(
2 id int,
3 name varchar2(20));
Table created.
SQL> create unique index idx_test_id on test (id);
Index created.
SQL> select index_name, index_type, uniqueness
2 from user_indexes
3 where table_owner = 'SCOTT'
4 and table_name = 'TEST';
INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
IDX_TEST_ID NORMAL UNIQUE
SQL> alter table test add constraint uk_test unique (id);
Table altered.
SQL> select index_name, index_type, uniqueness
2 from user_indexes
3 where table_owner = 'SCOTT'
4 and table_name = 'TEST';
INDEX_NAME INDEX_TYPE UNIQUENES
------------------------------ --------------------------- ---------
IDX_TEST_ID NORMAL UNIQUE
SQL> select constraint_name, constraint_type
2 from user_constraints
3 where table_name = 'TEST';
CONSTRAINT_NAME C
------------------------------ -
UK_TEST U
SQL> alter table test disable constraint uk_test;
Table altered.
SQL> select constraint_name, constraint_type, status
2 from user_constraints
3 where table_name = 'TEST';
CONSTRAINT_NAME C STATUS
------------------------------ - --------
UK_TEST U DISABLED
SQL> select index_name, index_type, uniqueness, status
2 from user_indexes
3 where table_owner = 'SCOTT'
4 and table_name = 'TEST';
INDEX_NAME INDEX_TYPE UNIQUENES STATUS
------------------------------ --------------------------- --------- --------
IDX_TEST_ID NORMAL UNIQUE VALID
实验结果表明，先创建的唯一索引不受约束失效的影响。
总结如下：
（1）主键约束和唯一键约束均会隐式创建同名的唯一索引，当主键约束或者唯一键约束失效时，隐式创建的唯一索引会被删除；
（2）主键约束要求列值非空，而唯一键约束和唯一索引不要求列值非空；
（3）相同字段序列不允许重复创建索引；
