「oracle索引」各种Oracle索引类型介绍

oracle索引

逻辑上：

Single column 单行索引
concatenated 多行索引
unique 唯一索引
NonUnique 非唯一索引
Function-based函数索引
Domain 域索引

物理上：

Partitioned 分区索引
NonPartitioned 非分区索引
B-tree：
Normal 正常型B树
Rever Key 反转型B树 
Bitmap 位图索引

索引结构：

B-tree：

适合与大量的增、删、改（OLTP）；

不能用包含OR操作符的查询；

适合高基数的列（唯一值多）

典型的树状结构；

每个结点都是数据块；

大多都是物理上一层、两层或三层不定，逻辑上三层；

叶子块数据是排序的，从左向右递增；

在分支块和根块中放的是索引的范围；

Bitmap:

适合与决策支持系统；

做UPDATE代价非常高；

非常适合OR操作符的查询；

基数比较少的时候才能建位图索引；

树型结构：

索引头

开始ROWID，结束ROWID（先列出索引的最大范围）

BITMAP

每一个BIT对应着一个ROWID，它的值是1还是0，如果是1，表示着BIT对应的ROWID有值

1. b-tree索引

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

2. 位图索引(bitmap index)

位图索引特定于该列只有几个枚举值的情况，比如性别字段，标示字段比如只有0和1的情况。

3. 基于函数的索引

比如经常对某个字段做查询的时候是带函数操作的，那么此时建一个函数索引就有价值了。

4. 分区索引和全局索引

这2个是用于分区表的时候。前者是分区内索引，后者是全表索引

5. 反向索引（REVERSE）

这个索引不常见，但是特定情况特别有效，比如一个varchar(5)位字段(员工编号)含值（10001,10002,10033,10005,10016..）

这种情况默认索引分布过于密集，不能利用好服务器的并行

但是反向之后10001,20001,33001,50001,61001就有了一个很好的分布，能高效的利用好并行运算。

6.HASH索引

HASH索引可能是访问数据库中数据的最快方法，但它也有自身的缺点。集群键上不同值的数目必须在创建HASH集群之前就要知道。需要在创建HASH集群的时候指定这个值。使用HASH索引必须要使用HASH集群。

oracle索引总结

简介

1.说明

1）索引是数据库对象之一，用于加快数据的检索，类似于书籍的索引。在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。

2）索引是建立在表上的可选对象；索引的关键在于通过一组排序后的索引键来取代默认的全表扫描检索方式，从而提高检索效率

3）索引在逻辑上和物理上都与相关的表和数据无关，当创建或者删除一个索引时，不会影响基本的表；

4）索引一旦建立，在表上进行DML操作时（例如在执行插入、修改或者删除相关操作时），oracle会自动管理索引，索引删除，不会对表产生影响

5）索引对用户是透明的，无论表上是否有索引，sql语句的用法不变

6）oracle创建主键时会自动在该列上创建索引

索引原理

若没有索引，搜索某个记录时（例如查找name='wish'）需要搜索所有的记录，因为不能保证只有一个wish，必须全部搜索一遍
若在name上建立索引，oracle会对全表进行一次搜索，将每条记录的name值哪找升序排列，然后构建索引条目（name和rowid），存储到索引段中，查询name为wish时即可直接查找对应地方
创建了索引并不一定就会使用，oracle自动统计表的信息后，决定是否使用索引，表中数据很少时使用全表扫描速度已经很快，没有必要使用索引

索引使用（创建、修改、删除、查看）

1.创建索引语法

CREATE [UNIQUE] | [BITMAP] INDEX index_name  --unique表示唯一索引
ON table_name([column1 [ASC|DESC],column2    --bitmap，创建位图索引
[ASC|DESC],…] | [express])
[TABLESPACE tablespace_name]
[PCTFREE n1]                                 --指定索引在数据块中空闲空间
[STORAGE (INITIAL n2)]
[NOLOGGING]                                  --表示创建和重建索引时允许对表做DML操作，默认情况下不应该使用
[NOLINE]
[NOSORT];                                    --表示创建索引时不进行排序，默认不适用，如果数据已经是按照该索引顺序排列的可以使用

2.修改索引

1）重命名索引

alter index index_sno rename to bitmap_index;

2)合并索引（表使用一段时间后在索引中会产生碎片，此时索引效率会降低，可以选择重建索引或者合并索引,合并索引方式更好些，无需额外存储空间，代价较低）

alter index index_sno coalesce;

3)重建索引

方式一：删除原来的索引，重新建立索引

方式二：alter index index_sno rebuild;

3.删除索引

drop index index_sno;

4.查看索引

select index_name,index-type, tablespace_name, uniqueness from all_indexes where table_name ='tablename';

 -- eg:    
create index index_sno on student('name');
select * from all_indexes where table_name='student';

索引分类

1. B树索引（默认索引，保存讲过排序过的索引列和对应的rowid值）

1）说明：

1.oracle中最常用的索引；B树索引就是一颗二叉树；叶子节点（双向链表）包含索引列和指向表中每个匹配行的ROWID值

2.所有叶子节点具有相同的深度，因而不管查询条件怎样，查询速度基本相同

3.能够适应精确查询、模糊查询和比较查询

2）分类：

UNIQUE,NON-UNIQUE(默认),REVERSE KEY（数据列中的数据是反向存储的）

3）创建例子

craete index index_sno on student('sno');

4）适合使用场景：

列基数（列不重复值的个数）大时适合使用B数索引

2. 位图索引

1）说明：

1.创建位图索引时，oracle会扫描整张表，并为索引列的每个取值建立一个位图（位图中，对表中每一行使用一位（bit，0或者1）来标识该行是否包含该位图的索引列的取值，如果为1，表示对应的rowid所在的记录包含该位图索引列值），最后通过位图索引中的映射函数完成位到行的ROWID的转换

2)创建例子

create bitmap index index_sno on student(sno);

3) 适合场景：

对于基数小的列适合简历位图索引（例如性别等）

3.单列索引和复合索引（基于多个列创建）

1) 注意：

即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引，即至少要包含组合索引的第一列

4. 函数索引

1)说明：

1. 当经常要访问一些函数或者表达式时，可以将其存储在索引中，这样下次访问时，该值已经计算出来了，可以加快查询速度

2. 函数索引既可以使用B数索引，也可以使用位图索引；当函数结果不确定时采用B树索引，结果是固定的某几个值时使用位图索引

3. 函数索引中可以随你用len、trim、substr、upper（每行返回独立结果），不能使用如sum、max、min、avg等

2）例子：

create index fbi  on student (upper(name));
select * from student where upper(name) ='WISH';

索引建立原则总结

1. 如果有两个或者以上的索引，其中有一个唯一性索引，而其他是非唯一，这种情况下oracle将使用唯一性索引而完全忽略非唯一性索引

2. 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）

3. 小表不要简历索引

4. 对于基数大的列适合建立B树索引，对于基数小的列适合简历位图索引

5. 列中有很多空值，但经常查询该列上非空记录时应该建立索引

6. 经常进行连接查询的列应该创建索引

7. 使用create index时要将最常查询的列放在最前面

8. LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引

9.限制表中索引的数量（创建索引耗费时间，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）

注意事项

1. 通配符在搜索词首出现时，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%';

2. 不要在索引列上使用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

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

select * from student where score is not null;

ORACLE索引介绍和使用

１．什么是索引？

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

2.使用索引的目的：

加快查询速度
减少I/O操作
消除磁盘排序（索引能加快排序速度）

3.何时使用索引：

查询返回的记录数排序表<40%，对非排序表<7%
表的碎片较多（频繁增加、删除）

4．索引的种类

非唯一索引（最常用）
唯一索引
位图索引
局部有前缀分区索引
局部无前缀分区索引
全局有前缀分区索引
散列分区索引
基于函数的索引

5．管理索引的准则

在表中插入数据后创建索引
在用SQL*Loader或import工具插入或装载数据后，建立索引比较有效；

6.索引正确的表和列

经常检索排序大表中40%或非排序表7%的行，建议建索引；
为了改善多表关联，索引列用于联结；
列中的值相对比较唯一；
取值范围（大：B*树索引，小：位图索引）；
Date型列一般适合基于函数的索引；
列中有许多空值，不适合建立索引

7.为性能而安排索引列

经常一起使用多个字段检索记录，组合索引比单索引更有效；
把最常用的列放在最前面，例：dx_groupid_serv_id(groupid,serv_id)，在where条件中使用groupid或groupid,serv_id，查询将使用索引，若仅用到serv_id字段，则索引无效；

合并/拆分不必要的索引。

限制每个表索引的数量

一个表可以有几百个索引（你会这样做吗？），但是对于频繁插入和更新表，索引越多系统cpu，I/O负担就越重；
建议每张表不超过5个索引。

删除不再需要的索引

索引无效，集中表现在该使用基于函数的索引或位图索引，而使用了B*树索引；
应用中的查询不使用索引；
重建索引之前必须先删除索引，若用alter index … rebuild重建索引，则不必删除索引。

索引数据块空间使用

创建索引时指定表空间，特别是在建立主键时，应明确指定表空间；
合理设定pctfress，注意：不能给索引指定pctused；
估计索引的大小和合理地设置存储参数，默认为表空间大小，或initial与next设置成一样大。

考虑并行创建索引

对大表可以采用并行创建索引，在并行创建索引时，存储参数被每个查询服务器进程分别使用，例如：initial为1M，并行度为8，则创建索引期间至少要消耗8M空间；

考虑用nologging创建索引

对大表创建索引可以使用nologging来减少重做日志；
节省重做日志文件的空间；
缩短创建索引的时间；
改善了并行创建大索引时的性能。

8.怎样建立最佳索引?

明确地创建索引

create index index_name on table_name(field_name)
　　tablespace tablespace_name
　　pctfree 5
　　initrans 2
　　maxtrans 255
　　storage
　　(
　　minextents 1
　　maxextents 16382
　　pctincrease 0
　　);

创建基于函数的索引:

常用与UPPER、LOWER、TO_CHAR(date)等函数分类上，例：

create index idx_func on emp (UPPER(ename)) tablespace tablespace_name;

创建位图索引:

对基数较小，且基数相对稳定的列建立索引时，首先应该考虑位图索引，例：

create bitmap index idx_bitm on class (classno) tablespace tablespace_name;

明确地创建唯一索引

可以用create unique index语句来创建唯一索引，例：

create unique index dept_unique_idx on dept(dept_no) tablespace idx_1;

创建与约束相关的索引

可以用using index字句，为与unique和primary key约束相关的字段创建索引，例如：

alter table table_name
　　add constraint PK_primary_keyname primary key (field_name)
　　using index tablespace tablespace_name；

如何创建局部分区索引

基础表必须是分区表；
分区数量与基础表相同；
每个索引分区的子分区数量与相应的基础表分区相同；
基础表的子分区中的行的索引项，被存储在该索引的相应的子分区中,例如:

　　Create Index TG_CDR04_SERV_ID_IDX On TG_CDR04(SERV_ID)
　　Pctfree 5
　　Tablespace TBS_AK01_IDX
　　Storage (
　　MaxExtents 32768
　　PctIncrease 0
　　FreeLists 1
　　FreeList Groups 1
　　)
　　local
　　/

如何创建范围分区的全局索引

基础表可以是全局表和分区表。

create index idx_start_date on tg_cdr01(start_date)
　　global partition by range(start_date)
　　(partition p01_idx vlaues less than (‘0106’)
　　partition p01_idx vlaues less than (‘0111’)
　　…
　　partition p01_idx vlaues less than (‘0401’ ))
　　/

重建现存的索引

重建现存的索引的当前时刻不会影响查询；

重建索引可以删除额外的数据块；

提高索引查询效率；

alter index idx_name rebuild nologging;

对于分区索引：

alter index idx_name rebuild partition partiton_name nologging;

要删除索引的原因

不再需要的索引；
索引没有针对其相关的表所发布的查询提供所期望的性能改善；
应用没有用该索引来查询数据；
该索引无效，必须在重建之前删除该索引；
该索引已经变的太碎了，必须在重建之前删除该索引；
语句：

drop index idx_name;
drop index idx_name drop partition partition_name;

8.建立索引的代价

基础表维护时，系统要同时维护索引，不合理的索引将严重影响系统资源，主要表现在CPU和I/O上；

插入、更新、删除数据产生大量db file sequential read锁等待；

一个表中有几百万条数据，对某个字段加了索引，但是查询时性能并没有什么提高，这主要可能是oracle的索引限制造成的。

oracle的索引有一些索引限制，在这些索引限制发生的情况下，即使已经加了索引，oracle还是会执行一次全表扫描，查询的性能不会比不加索引有所提高，反而可能由于数据库维护索引的系统开销造成性能更差。

常见的索引限制问题：

9、使用不等于操作符(<>, !=)

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

select * from dept where staff_num <> 1000;

但是开发中的确需要这样的查询，难道没有解决问题的办法了吗？

有！

通过把用 or 语法替代不等号进行查询，就可以使用索引，以避免全表扫描：上面的语句改成下面这样的，就可以使用索引了。

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

10、使用 is null 或 is not null

使用 is null 或is nuo null也会限制索引的使用，因为数据库并没有定义null值。如果被索引的列中有很多null，就不会使用这个索引（除非索引是一个位图索引，关于位图索引，会在以后的blog文章里做详细解释）。在sql语句中使用null会造成很多麻烦。

解决这个问题的办法就是：建表时把需要索引的列定义为非空(not null)

11、使用函数

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

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

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

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

12、比较不匹配的数据类型

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

select * from dept where dept_id = 900198;

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

select * from dept where dept_id = '900198';

13、使用like子句

使用like子句查询时，数据需要把所有的记录都遍历来进行判断，索引不能发挥作用，这种情况也要尽量避免。

Like 的字符串中第一个字符如果是‘%’则用不到索引

Column1 like ‘aaa%’ 是可以的
Column1 like ‘%aaa%’用不到

14．使用IN

尽管In写法要比exists简单一些，exists一般来说性能要比In要高的多

用In还是用Exists的时机

当in的集合比较小的时候,或者用Exists无法用到选择性高的索引的时候，用In要好，否则就要用Exists

例：

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…’;--才能用到索引

假定TEST表的dt字段是date类型的并且对dt建了索引。

如果要查‘20041010’一天的数据.下面的方法用不到索引

Select * from test where to_char(dt,’yyyymmdd’) =‘20041010’;

而以下将会用到索引。

select * from test where dt >=to_date(‘20041010’,’yyyymmdd’) and dt < to_date(‘20041010’,’yyyymmdd’) + 1

15．如果能不用到排序，则尽量避免排序。

用到排序的情况有

集合操作。Union ,minus ,intersect等,注：union all 是不排序的。

order by
Group by
Distinct
In

有时候也会用到排序

确实要排序的时候也尽量要排序小数据量,尽量让排序在内存中执行，有文章说，内存排序的速度是硬盘排序的1万倍。

在排序的字段上创建索引，让排序在内存中执行，加快排序速度。

16.在基于CBO的优化器（花费）下，表的统计数据过期。也可能导致不使用索引。

解决：执行表分析。获取表的最新信息。

17.获取的数据量过大，全部扫描效率更高

18.索引字段的值分散率太低，值太集中，如类型字段都是1,2，状态类型Y-有效/N-无效。这类型的字段最好别建索引。

尽管在这些字段上建立了索引，但对全表数据区分度不大。最后还是会全表扫描。

各种Oracle索引类型介绍