oracle 何时使用倒序索引

使用倒序索引提升ORDER BY DESC性能

使用倒序索引（INDEX DESC），可以大幅提升带有order by desc子句的SQL语句性能。

举例

1、表名：test_t，有一字段名为object_id

2、总数据量：580000行，segment_size：72MB

3、Where条件（Owner=’SYS’ and Object_id>50000）的行数：32472行

4、SQL语句：select * from test_t where owner='SYS' and object_id >50000 order by object_id desc

5、希望借助倒序索引，提升order by object_id desc的性能

希望能帮到你。

本文只讲最最平常最最简单的索引 就是以create index ix on tx(a b c)形式创建的索引 而不讲位图索引 反向键索引 倒序索引 基于函数的索引等等 其实呢 只要是基于B树的索引 不管是在Oracle Mysql 还是其它数据库中 原理应当都是一样的

索引最重要的一个性质应该就是有序 索引中的每一项 是从左到右 从小到大 以严格的顺序排列好的

下面的讨论都以上面的索引ix(a b c)为例

把这棵索引的叶子节点画到纸上 大概是这样的

a a a an

b b b bn

c c c cn

上面这个 ×n的矩阵 每一列代表了一条记录 同时这一列记录 也对应了表里的唯一一条记录 当然 在Oracle里 对于non unique索引 需要补上rowid 才是真正唯一的 上面的索引相当于create unique index ix on tx(a b c rowid)我们把这个细节忽略掉

把每一列看作一个向量 vi = (ai bi ci)

有序的含义就是

vi <vj iff i <j

vi <vj这么定义

(ai <aj) or (ai = aj and bi <bj) or (ai = aj and bi = bj and ci <cj)

从这个基本性质 我们可以得到一些其它性质(为了打字方便 ai+k表示a(i+k) 而不是a(i)+k)

) 如果ai ai+ …… ai+k 都是相等的 那么

bi <= bi+ <= …… <= bi+k

) 如果ai ai+ …… ai+k是相等的 而且bi bi+ …… bi+k也是相等的 那么

ci <= ci+ <= …… <ci+k

但是从 ai ai+ …… ai+k相等 我们得不到

ci <= ci+ <= …… <= ci+k这个结论

索引相关的很多问题 都和上面提到的这几个性质有关系

下面来看几个常见的查询:

q ) select * from tx where a = :va and b = :vb

q ) select * from tx where b = :vb and c = :vc

q ) select * from tx where a = :va and c = :vc

q ) select * from tx where a = :va order by b

q ) select * from tx where a = :va order by b c

q ) select * from tx where a = :va order by c

q ) select * from tx where a = :va order by b c desc

q ) select * from tx where a = :va order by b desc c desc

q ) select * from tx where a = :va and b <= :vb

qa) select * from tx where a = :va and b >= :vb

qb) select * from tx where a = :va and c >= :vc

qc) select * from tx where a = :va and b >= :vb order by c

大家可以考虑一下这些查询各自会以怎样的方式执行 不同查询之间有什么区别?

同样 为什么在索引字段上作了函数运算之后 索引不可用?

考虑下面这个语句:

select * from tx where f(a) = :vfa

首先 在字段 a上作了函数运算之后 排序的规则是否仍旧一样? a <b 与 f(a) <f(b)是否等价?

其次 就算f(a)和a的排序规则一样 但是索引块中存的a 但是你传给它的是经过了函数运算的值:vfa 只有oracle知道函数f的反函数inv_f 并在vfa上做inv_f(:vfa)计算之后 才能通过索引的B树结果进行查找

当然 现实中f可能不是显示的 而是隐式的 如传入参数和字段类型不匹配的情况下 Oracle可能在字段上作函数运算 从语句上可能看不出索引字段上被做了函数运算 但Oracle内部已经在字段上运用了函数 这样也会导致索引不可用 这种情况下用hint强制使用索引也是没用的

通过dbms_xplan display_cursor可以或许可以查看到这种隐式类型转换

通过v$sql_bind_metadata应当可以查看到每个绑定变量的类型

通过v$sql_bind_capture这个视图甚至可以看到每个绑定变量具体的值 不要把bind_capture和bind peek搞混哦 而且这里bind_cature也不会每绑定一次变量就capture一次 不然对执行量非常高 绑定频繁的语句 capture以同样频率进行的话 开销可能还是有点大的

上面讲到了索引的有序性 下面来讲讲索引另外一个有趣的性质 其实 我们完全可以把索引看作一张表 这张表包含和主表一样多的记录(如果不考虑null) 只不过每条记录只有主表的部分字段 开个玩笑 我们是不是可以把索引叫做有序视图呢?或者精确一点 有序物化视图:)

那么 我在执行一些查询的时候 如果所有字段都包含在索引中 是不是只要访问索引就可以了呢?

这些字段可以出现在select列表中 where条件中 order by字段中 也可以出现在两个表连接时的连接条件中

那么 根据业务的需求 我们是不是可以设计或调整索引以减少对主表的访问呢?或者 是不是可以适当的调整应用的设计或实现来满足索引呢?

同时 考虑到索引的有序性 是不是可以利用索引来避免排序呢?

当然 我们不能忽略null的存在 如果一条记录在索引中的所有字段上都是null的 那么oracle是不会索引这条记录的 比如如果记录ri的ai bi ci字段都是null的 索引中是找不到这条记录的 这会有什么问题呢?首先表中的记录和索引中的记录从数量上来说就不一样了

考虑一下Oracle会怎样执行下面这个查询:

select count(*) from tx

这个呢 hint起作用了吗?

select /*+ parallel(tx ) */ count(*) from tx

大家可以测试一下 怎样把count(*)这个 *** 作并行化 从这里或许可以得到一些Oracle怎么处理hint的提示

最后 讲一下Oracle CBO计算索引访问成本的公式

cost =

blevel +

ceiling(leaf_blocks * effective index selectivity) +

ceiling(clustering_factor * effective table selectivity)

这个公式相信很多地方可以找到(我是从cost base oracle fundamentals这本书里copy出来的) 简单说一下我自己对这个公式的理解

blevel是索引树的高度

leaf_blocks是索引的页子节点的个数

effective index selectivity (eis)怎么算呢?

还是举几个例子

where a = :va and b = :vb c = :vc

这里eis是 (selectivity a) * (selectivity b) * (selectivity c)

where a = :va and c = :vc

这里eis是 selectivity a

where b = :vb and c = :vc

这里eis是

where a = :va and b >= :vb and c = :vc

这里eis是 (selectivity a) * (selectivity range b)

就是说按索引字段的顺序 第一个不在where条件中出现的字段 或者第一个做了范围运算的字段 之后出现的字段的selectivity是不能乘到effective index selectivity里去的

简单的说 ceiling(leaf_blocks * effective index selectivity)表示的是Oracle需要访问的索引叶子节点的个数

clustering_factor表示的是按索引的顺序 从头走到尾 需要访问多少次数据块 这里需要考虑到Oracle的一个优化 如果连续n条记录在同一个表块中 那么oracle认为只需要访问一次数据块

那么clustering_factor的值的范围就很容易确定了 cf >= table blocks and cf <= rows in index

effective table selectivity 这个计算就容易了 把索引中所有字段的selectivity乘起来就可以了

如果查询中还有其它条件 比如 d = :vd and e = :ve 但是d e这些字段又不在索引中 那么在这些列上的过滤条件 需要回表后把这些值取出来才能判断 所以d e这些列的selectivity是不能乘到effective table selectivity里去的

ceiling(clustering_factor * effective table selectivity)表示需要回表的次数

所以上面索引访问的cost就是走某个索引 需要访问的数据块的个数

当然 前面的讨论忽略了index skip scan这种情况 因为本人对index skip scan也不是很明白

什么情况下会走skip scan?

select * from tx where a = :va and c = :vc 是不是会在c这个字段上也作一个skip scan呢?

lishixinzhi/Article/program/Oracle/201311/16730

索引用于快速找到特定一些值的记录。如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录。表越大，资源消耗越大。如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。

大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储，MEMORY (HEAP)表支持哈希索引。

字符串默认都是自动压缩前缀和后缀中的空格。

通常，如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY 表)的独特之处在后面会讨论到。

想要尽快找到匹配 WHERE 子句的记录。

根据条件排除记录。如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引。

做表连接查询时从其他表中检索记录。

想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。优化程序会在检查索引的

key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# = constant 子句。这样的话，

MySQL会为 MIN() 或 MAX() 表达式分别单独做一次索引查找，并且将它替换成常数。当所有的表达式都被替换成常数后，查询就立刻返回。如下：

SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10

对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时(如 ORDER

BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。

有些时候，查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果：

SELECTkey_part3FROMtbl_nameWHEREkey_part1=1

假设有如下 SELECT 语句：

如果在 col1 和 col2 上有一个多字段索引的话，就能直接取得对应的记录了。

---