Oracle笔记--索引(Index)创建与使用

简介

当我们在某本书中查找特定的章节内容时，可以先从书的目录着手，找到该章节所在的页码，然后快速的定位到该页。这种做法的前提是页面编号是有序的。如果页码无需，就只能从第一页开始，一页页的查找了。
数据库中索引(Index)的概念与目录的概念非常类似。如果某列出现在查询的条件中，而该列的数据是无序的，查询时只能从第一行开始一行一行的匹配。创建索引就是对某些特定列中的数据排序，生成独立的索引表。在某列上创建索引后，如果该列出现在查询条件中，Oracle会自动的引用该索引，先从索引表中查询出符合条件记录的ROWID，由于ROWID是记录的物理地址，因此可以根据ROWID快速的定位到具体的记录，表中的数据非常多时，引用索引带来的查询效率非常可观。

适用索引的场景：

• 如果表中的某些字段经常被查询并作为查询的条件出现时，就应该考虑为该列创建索引。
• 当从很多行的表中查询少数行时，也要考虑创建索引。有一条基本的准则是：当任何单个查询要检索的行少于或者等于整个表行数的10%时，索引就非常有用。

说明

• 索引是数据库对象之一，用于加快数据的检索，类似与书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
• 索引是建立在表上的可选对象。索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率。
• 索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表。
• 索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响。
• 索引对用户是透明的，无论表上是否有索引，sql语句的用法不变。
• oracle创建主键时会自动在该列上创建索引。

创建语法

CREATE [UNIQUE] | [BITMAP] INDEX <schema>.<index_name> 
      ON <schema>.<table_name>
           (<column_name> | <expression> ASC | DESC,
            <column_name> | <expression> ASC | DESC,...)
     TABLESPACE <tablespace_name>
     STORAGE <storage_settings>
     LOGGING | NOLOGGING
     COMPUTE STATISTICS
     NOCOMPRESS | COMPRESS<nn>
     NOSORT | REVERSE
     PARTITION | GLOBAL PARTITION<partition_setting>
说明：
	1） UNIQUE | BITMAP：指定UNIQUE为唯一值索引，BITMAP为位图索引，省略为B-Tree索引。
    2）<column_name> | <expression> ASC | DESC：可以对多列进行联合索引，当为expression时即“基于函数的索引”
    3）TABLESPACE：指定存放索引的表空间(索引和原表不在一个表空间时效率更高)
	4）STORAGE：可进一步设置表空间的存储参数
	5）LOGGING | NOLOGGING：是否对索引产生重做日志(对大表尽量使用NOLOGGING来减少占用空间并提高效率)
	6）COMPUTE STATISTICS：创建新索引时收集统计信息
	7）NOCOMPRESS | COMPRESS<nn>：是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
	8）NOSORT | REVERSE：NOSORT表示与表中相同的顺序创建索引，REVERSE表示相反顺序存储索引值
	9）PARTITION | NOPARTITION：可以在分区表和未分区表上对创建的索引进行分区

修改语法

 1. 修改索引
 	Alter index old_index_name rename to new_index_name;
 2. 合并索引
 	表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引，合并索引方式会更好些，无需额外空间，代价较低。
 	Alter index index_name coalesce;
 3. 重建索引
 	方式一：删除原来的索引，重新建立。
 		Drop Index index_name;
 	方式二：
 		Alter index index_name rebuild [online];
 4. 查看索引
 	--查看表的所有索引
 	select * from all_indexes where table_name = 'tablename';
 	--查看该表的所有索引列
 	select* from all_ind_columns where table_name = 'tablename';

主键约束、唯一约束和唯一索引的区别

一般，我们看到属于“索引”和"键"交换使用，但实际上这两个是不同的。索引是存储在数据库中的一个物理结构，键纯粹是一个逻辑概念。键代表创建来实施业务规则的完整性约束。
索引和键的混淆通常是由于数据库使用索引来实施完整性约束。

1. 主键约束：

  Alter table TABLE_NAME add constraint 约束名称 primary key(列名);

创建主键约束后，Oracle会自动创建一个同名的唯一索引。
如果再试图在主键列上建立一个唯一索引(或者普通索引)，Oracle都会报错。

2. 唯一约束：

  Alter table TABLE_NAME add constraint 约束名称 unique(列名);

创建唯一约束之后，同样Oracle会自动创建一个同名的唯一索引，同样不允许再此列上创建唯一索引和非唯一索引。
唯一约束没有非空的要求。

3. 唯一索引：

  Create unique index 索引名字 on 表名(列名);

唯一索引对列值非空不做要求。

4. 使约束失效：

  Alter table 表名 disable constraint 约束名；

当主键约束或者唯一键约束失效时，Oracle会删除隐式创建的唯一索引。
如果先创建唯一索引，后创建主键或唯一键约束，然后使约束失效，则先创建的唯一索引不受约束失效的影响。

使用索引注意事项

• 通配符在搜索词首出现时，oracle不能使用索引，eg:

--我们在name上创建索引；
create index index_name on student('name');

--下面的方式oracle不适用name索引
select * from student where name like '%wish%';
--如果通配符出现在字符串的其他位置时，优化器能够利用索引；如下：
select * from student where name like 'wish%';

• 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）

select * from student where not (score=100);
select * from student where score <> 100;
--替换为
select * from student where score>100 or score <100

• 索引上使用空值比较将停止使用索引， eg：

select * from student where score is not null;