oracle 索引的相关介绍(创建、简介、技巧、怎样查看) .

时间:2022-04-29 06:43:59

一、索引简介 

1、索引相当于目录 
2、索引是通过一组排序后的索引键来取代默认的全表扫描检索方式,从而提高检索效率。 
3、索引的创建要适度,多了会影响增删改的效率,少了会影响查询的效率,索引最好创建在取值分散的列上,避免对同一张表创建过多的索引 
4、索引的使用对用户来说是透明的,由系统来决定什么时候使用索引。 
5、Oracle支持多种类型的索引,可以按列的多少、索引值是否唯一和索引数据的组织形式对索引进行分类,以满足各种表和查询条件的要求。(请见附件) 
a. 单列索引和复合索引 
b.B树索引(create index时默认的类型) 
B树索引中所有叶子节点都具有相同的深度,所以不管查询条件如何,查询速度基本相同。另B树索引能够适应各种查询条件,包括精确查询、模糊查询和比较查询 
--Unique 唯一索引 值唯一,但允许存在null,主键默认存在唯一索引,但列不能为null 
--Non-Unique:非唯一索引,其索引值可以重复,允许为NULL。默认情况下,Oracle创建的索引是非唯一索引 
--Reverse Key:反向关键字索引。通过在创建索引时指定“REVERSE”关键字,可以创建反向关键字索引,被索引的每个数据列中的数据都是反向存储 
的,但仍然保持原来数据列的次序 
c.位图索引(对列值范围少,如性别 政治面貌适用,而不是默认的B树索引) 
c.函数索引 
当需要经常访问一些函数或表达式时,可以将其存储在索引中,当下次访问时,由于该值已经计算出来了,因此,可以大大提高那些在WHERE子句中包含该函数或 
表达式的查询操作的速度; 
函数索引既可以使用B树索引,也可以使用位图索引。 

二、管理索引的原则 

使用索引应该遵循以下一些基本的原则。 
1.小表不需要建立索引。 
2.对于大表而言,如果经常查询的记录数目少于表中总记录数目的15%时,可以创建索引。这个比例并不绝对,它与全表扫描速度成反比。 
3.对于大部分列值不重复的列可建立索引。 
4.对于基数大的列,适合建立B树索引,而对于基数小的列适合建立位图索引。 
5.对于列中有许多空值,但经常查询所有的非空值记录的列,应该建立索引。 
6.LONG和LONG RAW列不能创建索引。 
7.经常进行连接查询的列上应该创建索引。 
8.在使用CREATE INDEX语句创建查询时,将最常查询的列放在其他列前面。 
9.维护索引需要开销,特别时对表进行插入和删除操作时,因此要限制表中索引的数量。对于主要用于读的表,则索引多就有好处,但是,一个表如果经常被更改,则索引应少点。 
10.在表中插入数据后创建索引。如果在装载数据之前创建了索引,那么当插入每行时,Oracle都必须更改每个索引。 

三、LONG类型(可存储2G)主要用于不需要作字符串搜索的长串数据,如果要进行字符搜索就要用varchar2类型,存储这么长的请使用 pstat1.setCharacterStream()方法,资料见附件P26 

四、创建索引语法如下 

CREATE INDEX语句的语法如下: 
CREATE [UNIQUE] | [BITMAP] INDEX index_name 
ON table_name([column1 [ASC|DESC],column2 
[ASC|DESC],…] | [express]) 
[TABLESPACE tablespace_name] 
[PCTFREE n1] 
[STORAGE (INITIAL n2)] 
[NOLOGGING] 
[NOLINE] 
[NOSORT]; 


五、 
1、查看索引信息可以在 all_indexs 表中 
2、查看索引信息及引用的列 all_ind_columns 
3、查看函数索引信息 all_ind_expressions 

4、oracle比较智能,有时候即使创建了索引也不会使用,比如说在数据量比较少的情况下,可能就不会用索引 
5、当进行全表扫描的时候,不用索引效率会更好 
6、查询可能会使用缓存,所以说如果发现执行速度变快了,不一定说明你的sql更优了,有可能是使用到了缓存而已 
7、使用plsql中的“解释计划”功能可以比较执行计划的消耗,进而写出更优的sql.