Oracle 学习（二）---索引

文章引用参考          

http://www.cnblogs.com/djcsch2001/articles/1823459.html

索引的创建

create (unique) index index_name on table (column)

[ASC|DESC] [TABLESPACE tablespace] 
index_name为索引的名称
ASC|DESC 为升序降序排列
TABLESPACE 表空间

创建索引的标准语法:

CREATE INDEX 索引名 ON 表名 (列名) 

     TABLESPACE 表空间名; 

创建唯一索引:

CREATE unique INDEX 索引名 ON 表名 (列名) 

     TABLESPACE 表空间名; 

创建组合索引:

CREATE INDEX 索引名 ON 表名 (列名1,列名2) 

     TABLESPACE 表空间名; 

创建反向键索引:

CREATE INDEX 索引名 ON 表名 (列名) reverse

     TABLESPACE 表空间名; 

先来说一下unique的使用和差别

添加unique后表示为唯一索引

区别：唯一索引unique index和一般索引normal index最大的差异是在索引列上增加一层唯一约束。添加唯一索引的数据列可以为空，但是只要尊在数据值，就必须是唯一的。

联系：1）unique index就是额外添加唯一性的约束。该约束严格的保证索引列的取值是唯一的，这在一些数据列上的业务约束是很重要的功能。比如一个数据列，不能作为主键，而且允许为空，但是业务上要求唯一特性，这个时候用唯一性索引就是最好的旋转。2）性能上两者并无很大区别。

using index用法：

create primary key …. Using index ：创建主键的时候同时将其设为索引，名称与主键名称相同。

在创建一个PK的时候，会自动创建一个与之对应的唯一索引。（因为某个字段如果被设置为Unique便会自动被设为索引）。如果不特别指定，这个索引的表空间和表的表空间是一样的，但是不建议将两者放在一起。

create table test_table(name varchar(20));//不声明tablespace 默认分配一个表空间
alter table test_table add (id primary key) using index tablespace tablespace1;//没有声明名称默认为primary key相同的名称

2.建立索引的目的

建立索引的目的是：
l 提高对表的查询速度；
l 对表有关列的取值进行检查。

但是，对表进行insert,update,delete处理时，由于要表的存放位置记录到索引项中而会降低一些速度。
注意：一个基表不能建太多的索引；
      空值不能被索引
      只有唯一索引才真正提高速度,一般的索引只能提高30%左右。

 

索引的修改

ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n] 
REBUILD 
[STORAGE n]

其中：
REBUILD 是根据原来的索引结构重新建立索引，实际是删除原来的索引后再重新建立。

ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n] 
REBUILD 
[STORAGE n]

提示：DBA经常用 REBUILD 来重建索引可以减少硬盘碎片和提高应用系统的性能。

例：
alter index pk_detno rebuild storage(initial 1m next 512k);

ALTER INDEX emp_ix REBUILD REVERSE;//Reverse 为反转函数

Oracle8i 的新功能可以对索引的无用空间进行合并，它由下面命令完成：

ALTER INDEX . . . COALESCE;

索引的删除

DROP INDEX [schema.]indexname

 

例如：

sql> drop index test_index;

注：当表结构被删除时，有其相关的所有索引也随之被删除。

PS:

//将test_table name 字段转化为大写函数 (虽然我还不知道他的具体应用价值在哪里 = =)

Create index upper_index on test_table(upper(name));

 select * from test_table where upper(name) like 'JOH%';

 

ALTER [UNIQUE] INDEX [user.]index
[INITRANS n]
[MAXTRANS n] 
REBUILD 
[STORAGE n]