Oracle索引详解(一)
### --索引介绍
??索引对于Oracle学习来说,非常重要,在数据量巨大的状况下,使用恰到好处的索引,将会使得数据查询时间大大减少,于2017/12/25暂时对Oracle中的索引进行一个大致的了解。
索引的创建语法
索引的特点
索引的不足
比较适合建立索引的列的特点
不适合建立索引的列的特点
限制索引(建立了索引,但是无法使用)
查询索引
组合索引
Oracle rowid
选择性
群集因子
二元高度
快速全表扫描
跳跃式扫描
索引的创建语法
create or replace unique|bitmap index . on .
(| asc|desc ,
| asc|desc ...)
tablespace
storage
logging|nologging
compute statistics
nocompress|compress
nosort|reverse
partition|global partition
unique|bitmap : unique表示唯一值索引,bitmap表示位图索引,为空则默认为B-tree索引
column_name|expression asc|desc , ... :可以单列索引,也可以多列进行联合索引,当为
tablespace : 制定存放索引的表空间(当表和索引在不同的表空间的时候,效率更高)
storage : 可以设置表空间的存储参数
logging|nologging : 是否对索引产生redolog(对于大表来说,可以设置为nologging从而来减少空间占用,提高效率)
compute statistics : 设置为创建索引时,收集统计信息
nocompress|compressnn : 是否使用“键压缩”(使用键压缩可以删除一个键列中出现的重复值)
nosort|reverse : nosort表示与表中相同的顺序进行创建索引,reverse表示使用与表中相反的顺序进行创建索引
partition|nopartition|global partition : 可以在分区表上和未分区表上对创建的索引进行分区
索引的特点
大大加快检索数据的速度
通过创建唯一性索引,可以保证数据库表中每一行数据的唯一性
加速表与表之间的连接
查询语句汇总含有分组或者排序的语句时,速度更快
查询的过程中,使用索引,使用优化隐藏器,从而提高系统的性能
索引的不足
创建和维护索引,比较耗费时间,随着数据量的增大而增大
创建索引,占一定的物理空间(聚簇索引,占用空间会更大)
在对表进行增删改的时候,索引相应的也需要进行动态的更新
比较适合建立索引的列的特点
经常需要搜索的列上
主键,一般建立唯一性索引,保持数据的唯一性
外键,提高表与表之间连接的速度
需要排序的列上
where子句后边经常出现的字段
经常需要根据范围进行搜索的列上,比如日期
不适合建立索引的列的特点
很少进行搜索的列上
列取值比较少的列上
blob类型的列上
修改频率比较高的列上
限制索引(建立了索引,但是无法使用)
使用不等于<> 、 != ,(不等于操作符一定会进行全表扫描)
使用is null 、 is not null (只要索引中出现一个null,那么这个索引就报废了。所以在建立索引的时候,一定要将准备建立索引的列设置为not null)
使用函数(where子句中含有trunc()、add_months()之类)的时候,sql优化器会自动忽略掉索引
where子句中,进行了数据类型不匹配的比较,比如(where row_num = ‘1‘)的时候,生气了优化器会限制索引的使用
查询索引
dba_indexes
user_indexes
uesr_ind_columns
组合索引
索引中,包含不止一个列。
在Oracle9i之前,需要先使用前导索引,才能使用组合索引。
Oracle rowid
??实体表中,每一行都有rowid,通过每一行的rowid,Oracle提供了访问单行数据的能力。
选择性
??user_indexes中distinct_keys,选择性越高,那么索引返回的值就越少。
群集因子
??user_indexes中的clustering factor 越接近 leaf block的值的话,说明表中的数据越有序。
二元高度
??dba_indexes 的 Blevel列查看对应索引的二元高度,二元高度随着表的大小以及被索引的列中,值的范围的狭窄程度而变化。重建索引可以降低二元高度。
快速全表扫描
??允许Oracle执行一个全局索引的扫描操作,快速全表扫描可以快速读取B-tree索引上的所有树叶块。
跳跃式扫描
create index TT_index on TT(teamid,areacode);
select /*+ index(tt TT_index )*/ count(areacode) from tt;