在看《收获,不止SQL优化》一书,并根据书中例子进行实践,整理成笔记
@[toc]
一、Oracle索引简介
在看《收获,不止SQL优化》一书,并根据书中例子进行实践,整理成笔记
1.1 索引分类
Oracle索引分为BTree索引、位图索引、反向索引、函数索引、全文索引等等。
1.2 索引数据结构
Oracle索引中最常用的是BTree索引,所以就以BTree索引为例,讲一下BTree索引,BTree索引数据结构是一种二叉树的结构,索引由根块(Root)、茎块(Branch)、叶子块(Leaf)组成,其中叶子块主要存储索引列具体值(Key Column Value)以及能定位到数据块具体位置的Rowid,茎块和根块主要保存对应下级对应索引
1.3 索引特性
索引特性:
- 索引本身是有序的
- 索引本身能存储列值
1.4 索引使用注意要点
(1)、仅等值无范围查询时,组合的顺序不影晌性能
123456789101112drop table t purge;create table t as select * from dba objects;update t set object_id=rownum ;commit;create index idx_id_type on t(object_id, object_type) ;create index idx_type_id on t(object_type , object_id) ;set autotrace off;alter session set statistics_level=all ;select /*+index(t idx_id_type)*/ * from t where object_id=20 and object_type='TABLE';select * from table(dbms_xplan.display cursor(null , null , 'allstats last'));select /*+index(t,idx_type id)*/ * from t where object_id=20 and object_type= 'TABLE';select * from table(dbms_xplan.display cursor(null , null , 'allstats last'));(2)、范围查询时,组合索引最佳顺序一般是将等值查询的列置前
|
|
- (3)、Oracle不能同时在索引根的两段寻找最大值和最小值
|
|
笛卡尔乘积写法:
- (4)、索引最新的数据块一般是在最右边
1.5、索引的缺点
- 热快竞争:索引最新的数据块一般在最右边,而访问也一般是访问比较新的数据,所以容易造成热快竞争
- 更新新增问题:索引本身是有序的,所以查询时候很快,但是更新时候就麻烦了,新增更新索引都需要保证排序
1.6、索引失效
索引失效分为逻辑失效和物理失效
- 逻辑失效
逻辑失效是因为一些sql语法导致索引失效,比如加了一些函数,而索引列不是函数索引 - 物理失效
物理失效是真的失效,比如被设置unusable属性,分区表的不规范操作也会导致索引失效等等情况
|
|
二、索引分类介绍
索引分类:BTree索引、位图索引、函数索引、反向索引、全文索引
2.1、位图索引
位图索引:位图索引储存的就是比特值
环境准备,位图索引性质适用于count时,效率最高
不用索引的情况:
创建位图索引:
再次查询,走位图索引查询:
注意要点:
位图索引更新列容易造成死锁,所以查询比较多列才适合建位图索引,更新比较多的列就尽量不要建索引
1.2、函数索引
函数索引:就是将一个函数计算的结果存储在行的列中
环境准备:
不走索引的查询:
创建函数索引:
走函数索引的查询:
注意要点:
自定义函数时要加上deterministic 关键字,不然不能建立函数索引
建立一个自定义函数:
尝试建立函数索引:
提示:ORA-30553:函数不能确定
用deterministic 关键字,就可以建立函数索引
在自定义函数代码更新时,对应的函数索引也要重建,否则不能用到原来的函数索引
1.3、反向索引
反向索引:反向索引其实也是BTree索引的一种特例,不过在列中字节会反转的(反向索引是为了避免热快竞争,比如索引列中存储的列值是递增的,比如250101,250102,按照BTree索引的特性,一般是按照顺序存储在索引右边的,所以容易形成热快竞争,而反向索引可以避免这种情况,因为反向索引是这样存储的,比如101052,201052,这样列值就距离很远了,避免了热快竞争)
反向索引不能用到范围查询
|
|
范围查询,发现不走反向索引查询
1.4、全文索引
全文索引:所谓Oracle全文索引是通过Oracle词法分析器(lexer)将所有的表意单元term存储dr$开头的表里并存储term出现的位置、次数、hash值等等信息,Oracle提供了basic_lexer(针对英语)、chinese_vgram_lexer(汉语分析器)、chinese_lexer(新的汉语分析器)。
- basic_lexer:是一种适用于英文的分析器,根据空格或者标点符号将词元分离,不管对于中文来说是没有空格的,所以这种分析器不适合中文
- chinese_vgram_lexer:这是一种原先专门的中文分析器,支持所有的汉字字符集,比如zhs16gbk单点。这种分析器,分析过程是按字为单元进行分析的,举个例子,“索引本身是有序的”,按照这种分析器,会分成词元“索”、“索引”、“引本”、“本身”、“身是”、“是有”、“有序”、“序的”、“的”这些词元,然后你发现像“序的”这些词在中文中基本是不成立的,不过这种Oracle分析器本身就不认识中文,所以只能全部分析,很明显效率是不好的
- chinese_lexer:这是一种新的中文分析器,前面提到chinese_vgram_lexer这种分析器虽然支持所有的中文字符集,但是效率不高,所以chinese_lexer是对其的改进版本,这种分析器认识很多中文词汇,能够比较快查询,提高效率,不过这种分析器只能支持utf-8字符集
Oracle的全文索引具体可以采用通配符查找、模糊匹配、相关分类、近似查找、条件加权和词意扩充等方法
环境准备
设置词法分析器
解锁ctxsys用户,同时给你的测试账号(我这里用scott)授权使用ctx_ddl
建立全文索引
注意要点:更新数据时候记得执行全文索引同步命令,否则将看不到更新数据
|
|