Oracle索引详解

大家好，欢迎来到IT知识分享网。

一、索引介绍

1、什么是索引？

  索引是建立在表的一列或多个列上的辅助对象，目的是加快访问表中的数据；Oracle存储索引的数据结构是B树，位图索引也是如此，只不过是叶子节点不同B数索引；索引由根节点、分支节点和叶子节点组成，上级索引块包含下级索引块的索引数据，叶节点包含索引数据和确定行实际位置的rowid。

2、使用索引的目的

  当查询返回的记录数排序表<40%非排序表 <7%且表的碎片较多（频繁增加、删除）时可以加快查询速度减少I/O操作消除磁盘排序

二、索引的分类及结构

1、逻辑上：

Single column/Concatenated 单行索引/多行索引
Unique/NonUnique 唯一索引/非唯一索引

2、物理上：

B-tree B树索引
Bitmap 位图索引
REVERSE 反向索引
HASHHASH索引
Function-based基于函数的索引
Partitioned/NonPartitioned 分区索引/非分区索引
Domain 域索引

三、各种索引详解

1、 B树索引

  Oracle数据库中最常见的索引类型是b-tree索引，也就是B-树索引，以其同名的计算科学结构命名。CREATE INDEX语句时，默认就是在创建b-tree索引。没有特别规定可用于任何情况。

（1）特点：

（2）技巧：

  索引列的值都存储在索引中。因此，可以建立一个组合(复合)索引，这些索引可以直接满足查询，而不用访问表。这就不用从表中检索数据，从而减少了I/O量。

2、位图索引

（1）特点：

（2）技巧：

3、 反向索引

（1）特点：

  不可以将反转键索引与位图索引或索引组织表结合使用。因为不能对位图索引和索引组织表进行反转键处理。

（2）技巧：

  如果您的磁盘容量有限，同时还要执行大量的有序载入，就可以使用反转键索引。

4、HASH索引

（1）特点：

  可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：

select * from emp where UPPER(job) = 'MGR'; 

下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：

select * from emp where job = 'MGR'; 

  可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：

create index EMP$UPPER_JOB on emp(UPPER(job)); 

5、函数索引

  可以在表中创建基于函数的索引。如果没有基于函数的索引，任何在列上执行了函数的查询都不能使用这个列的索引。例如，下面的查询就不能使用JOB列上的索引，除非它是基于函数的索引：

select * from emp where UPPER(job) = 'MGR'; 

下面的查询使用JOB列上的索引，但是它将不会返回JOB列具有Mgr或mgr值的行：

select * from emp where job = 'MGR'; 

  可以创建这样的索引，允许索引访问支持基于函数的列或数据。可以对列表达式UPPER(job)创建索引，而不是直接在JOB列上建立索引，如：

create index EMP$UPPER_JOB on emp(UPPER(job)); 

6、分区索引和全局索引

7、域索引 Domain

  域索引实际为用户自定义索引，域索引主要对存储在数据库中的媒体，图像数据进行索引，这些数据在oracle中基本上以BLOB类型存储，不同的应用存储格式也不同，oracle不可能提供某一种现成的算法对这些数据进行索引，为了能够对这些类型数据快速访问，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> 

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

1、普通索引

create index index_text_txt on test(txt); 

2、唯一索引

create unique index <index_name> on <table_name>(<coiumn_name>); 

3、位图索引

create bitmap index <index_name> on <table_name>(<column_name>) 

4、组合索引

create index <index_name> on <table_name>(<column_name1><column_name2>) 

5、基于函数索引

create index <index_name> on <table_name>(column_name) reverse; create index <index_name> on <table_name>(upper(column_name)) 

6、反向键索引

create index <index_name> on <table_name>(column_name) reverse; 

7.重置索引

alter index <index_name> rebuild; 

8.删除索引

drop index <index_name> 

五、索引失效细节

1.使用不等于操作符(<>, !=)

  下面这种情况，即使在列dept_id有一个索引，查询语句仍然执行一次全表扫描

select * from dept where staff_num <> 1000; 

select * from dept shere staff_num < 1000 or dept_id > 1000; 

2.使用 is null 或 is not null

3.使用函数

  如果没有使用基于函数的索引，那么where子句中对存在索引的列使用函数时，会使优化器忽略掉这些索引。下面的查询就不会使用索引：

select * from staff where trunc(birthdate) = '01-MAY-82'; 

  但是把函数应用在条件上，索引是可以生效的，把上面的语句改成下面的语句，就可以通过索引进行查找。

select * from staff where birthdate < (to_date('01-MAY-82') + 0.9999); 

4.比较不匹配的数据类型

  比较不匹配的数据类型也是难于发现的性能问题之一。下面的例子中，dept_id是一个varchar2型的字段，在这个字段上有索引，但是下面的语句会执行全表扫描。

select * from dept where dept_id = ; 

  这是因为oracle会自动把where子句转换成to_number(dept_id)=，就是3所说的情况，这样就限制了索引的使用。把SQL语句改为如下形式就可以使用索引

select * from dept where dept_id = ''; 

5.使用like子句

6.使用ｉｎ

select count(*) from person_info where xb in (select xb_id from dic_sex); Select count(*) from n_acntbasic a where shbxdjm =:a and exists(select 1 from person_info where pid=a.pid and …); Select * from person_info where zjhm=3101….;将会对person_info全表扫描 Select * from person_info where zjhm =‘3101…’才能用到索引 

Select * from test where to_char(dt,’yyyymmdd’) =‘’; 而select * from test where dt >=to_date(‘’,’yyyymmdd’) and dt < to_date(‘’,’yyyymmdd’) + 1 将会用到索引。 

7.如果能不用到排序，则尽量避免排序

  用到排序的情况有集合操作。Union ,minus ,intersect等,注：union all 是不排序的。Order by、Group by、Distinct、In 有时候也会用到排序，确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

at last:
博主搭建了一个技术的交流群，用于磨炼提升技术能力.群号： ,欢迎各界大佬前来交流学习。