Oracle索引
1.简介
- 索引是数据库对象之一,用于加快数据的检索,类似于书籍的目录.在数据库中索引可以减少数据库程序查询结果时需要读取的数据量,类似于在书籍中我们利用索引可以不用翻阅整本书就可以找到想要的信息.
- 索引是建立在表上的可选对象;索引的关键在于通过一组排序后的索引键来取代默认的全表扫描方式.从而提高检索效率.
- 索引在逻辑上和物理上都与相关的表和数据无关,当创建或删除一个索引时,不会影响基本的表.
- 索引一旦建立,在表上进行DML操作时(例如:在执行插入,修改或者删除等相关操作时),Oracle会自动管理索引,索引删除,不会对表产生影响,降低DML操作效率
- 索引对用户是透明的,无论表上是否有索引,sql语句的用法不变
- Oracle创建主键时会自动在列上创建索引 主键=唯一索引
2.索引的原理
- 若没有索引,在搜索某个记录时(例如查找name='wish')需要搜索索引所有的记录,因为不能保证只有一个wish,必须全部搜索一遍
- 若name上建立索引,Oracle会对全表进行一次搜索,将每条记录的name值升序排列,然后构建索引条目(namae和rowid),存储到索引段中,查询name为wish时即可直接查找对应的地方
- 创建了索引并不一定就会使用,Oracle自动统计表的信息后,决定是否使用索引,表中的数据很少时使用全表扫描速度已经很快,没有必要使用索引
3.索引的使用
创建索引语法
CREATE [UNIQUE][PBITMAP] INDEX 索引名 --unique表示唯一索引
ON 索引名 (列1 [ASC|DESC]),列2[ASC|DESC], --bitmap表示创建位图索引
... | [express] )
[TABLESPACE tablespace_name]
[[PCTFREE n1]] --指定索引在数据块中的空闲时间
[STORAGE (INITIAL n2)]
[NOLOGGING] --表示创建和重建索引时允许对表做DML操作,默认情况下不应该使用
[NOLINE]
[NOSORT]; --表示创建索引时不进行排序,默认不使用,如果数据已经按照该索引顺序排序排列的可以使用
修改索引
重命名索引
alter index 索引名 rename to 新索引名;
合并索引(表使用一段时间以后会在索引中产生碎片,此时索引的效率会降低,可以选择重建索引或合并索引,合并索引的方式更好一些,无需额外的存储空间,代价较低)
alter index 索引名 coalesce;
重建索引
方式一 : 删除原来的索引,重新建立索引
方式二 :
alter index 索引名 rebuild;
删除索引
drop index 索引名;
查看索引
select index_name,index_type,tablespace_name,uniquess
from all_indexes where table_name=表名;
4.索引的分类
B树索引(默认索引,保存经过排序过的索引列和对应的rowid值)
1)说明:
- Oracle中最常用的索引 : B树索引就是一颗二叉树;叶子节点(双向链表)包含索引列和指向表中每个匹配行的ROWID值
- 所有叶节点具有相同的深度,因而不管查询条件怎样,查询速度基本一样
- 能够适应精确查询,模糊查询和比较查询
2)分类 :
- UNIQUE , NON-UNIQUE(默认) , REVERSE KEY(数据列中的数据反向存储的)
3)创建语法 :
create index 索引名 on 表名(列名);
4)适合使用场景
列基数(列中不重复值的个数)大的时候适合使用B树索引
位图索引
1)说明 :
- 创建位图索引时,Oracle会扫描整张表,并未索引列的每个列的每个取值建立一个位图(位图中,多表中每一行 使用一位(bit,0或者1)来标识该行是否包含位图索引列的取值,如果为1,表示对应的rowid所在的记录包含该位图索引列的值),最后通过位图索引中的映射函数完成位到行的ROWID的转换
2)创建语法
create bitmap index 索引名 on 表名(列名);
3)适合场景
对于列基数小的适合建立位图索引(如:性别等)
单列索引和复合索引
注意
即如果索引建立在多个列上,它的第一个列被where子句引用时,优化器才会使用该索引,即至少包含组合索引的第一列
函数索引
1)说明
- 当经常要访问一些函数或者表达式时,可以将其存储到索引中,这样下次访问的时候,该值已经计算出来了,可以加快查询速度
- 函数索引既可以使用B树索引,也可以使用位图索引;当函数结果不确定时采用B树索引,结果是固定的某几个值时使用位图索引
- 函数索引中可以使用len , trim , substr , upper(每行返回独立的结果),不能使用聚合函数如 : sum , max , min , avg等
2)例子 :
create index 索引名 on 表名(函数(列名));
create index fbi on student (upper(name));
select * from student where upper(name) ='WISH';
索引的建立原则总结
- 如果有两个或两个以上的索引,其中有一个唯一性索引,而其它是非唯一,这种情况下Oracle将使用唯一性索引而完全忽略非唯一性索引
- 至少要包含组合索引的第一列（即如果索引建立在多个列上，只有它的第一个列被where子句引用时，优化器才会使用该索引）
- 小表(数据较少的表)不要建立索引
- 对于基数大的列适合建立B树索引，对于基数小的列适合建立位图索引
- 列中有很多空值，但经常查询该列上非空记录时应该建立索引
- 经常进行连接查询的列应该创建索引
- 使用create index时要将最常查询的列放在最前面
- LONG（可变长字符串数据，最长2G）和LONG RAW（可变长二进制数据，最长2G）列不能创建索引
- 限制表中索引的数量（创建索引耗费时间？，并且随数据量的增大而增大；索引会占用物理空间；当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度）
注意事项 : - 通配符在搜索词首出现时，oracle不能使用索引
- 不要在索引列上使用not，可以采用其他方式代替如下：（oracle碰到not会停止使用索引，而采用全表扫描）
- 索引上使用空值比较将停止使用索引
- 索引列不能使用函数
- 索引列不能计算
