首先我们来看看最常见的B树索引，B树索引适用于几乎所有的场合，也是系统中使用最为广泛的索引形式。实际上我们所说的普通索引，反转键索引、降序索引、函数索引等都是B树结构的，其物理存储结构是完全相同的。与之相对应的位图索引是完全不同的存储结构，位图索引不是树状结构，没有枝节点，只有叶节点。对于B树索引的操作可以进行索引唯一性扫描、索引范围扫描、快速索引全扫描和索引全扫描，而对于位图索引的访问方式只有一种，就是索引全扫描。在使用位图索引的时候，只有对索引完全扫描一遍，才能找到所有的所需要的行。
从昨天的知识点我们知道，索引是一个树状的结构，组织形式是一颗扩展了的B树，和普通B树不同的是，这棵B树的所有叶节点上有一条双向链，称为叶节点链。这条双向链是根据索引键值的大小进行排序的。这条双向链的存在十分关键，这是实现索引范围扫描的最关键的技术。当进行索引范围扫描时，首先通过B树的定位算法，从根开始，找到范围扫描起始键值的位置，然后从这个位置开始，通过叶节点链按照升序或者降序的方式扫描相关的叶节点，直到找到超出范围扫描范围的键值为止。
最为普通的索引是按照键值升序排列的，索引树的右面的枝叶的键值总比左边的大。而如果我们设计了降序索引，那么情况正好倒过来，索引左面的枝叶的键值总比右面的大。
函数索引是一种特殊的B树索引，引入函数索引的目的是解决那些在使用过程中，必须在字段上做函数运算的情况。一般情况下，我们在编程时都会建议开发人员不要在WHERE条件中的表字段上使用函数，因为这样我们无法为其设计索引。不过事实上，我们无法杜绝这样的函数的存在。比如说，我们必须从某个人字段取第二和第三位进行比较：WHERE substr(id,2,3)='ID'，如果这样的查询条件放弃函数的话，程序员的处理将十分复杂。函数索引为这种情况提供了很好的帮助，如果这个查询条件使用索引效果较好的话，我们可以在ID字段上创建一个以函数substr(id,2,3）为键的B树索引。事实上，在绝大多数应用系统中,函数索引都是不可避免的，不过不幸的是，在我做过的优化项目中，我基本上没有看到过用户在使用这种索引。
接下来我们来讨论一下反转键索引（reverse key index），这是一种十分著名的索引，反转键索引是在存储键值的时候，先将键值进行翻转。比如'1234'存储在索引中的键值是'4321'。设计反转键索引的目的是解决索引的热块冲突问题。索引块出现热块冲突是在性能优化时经常会碰到的问题，比如一个主键是通过sequence生成的，那么主键索引就可能成为热块。这种情况下，如果我们确定针对主键的查询不存在或者很少有索引范围扫描，那么我们可以考虑使用反转键索引来解决主键的热块冲突问题。反转键索引解决索引热块冲突的原理很简单，就是通过键值的反转，打乱索引数据块中的数据组织，从而将热点数据分散到不同的索引数据块中。
不过除了解决热块冲突的问题外，DBA界还流传着反转键索引可以解决 like '%abc'无法随用索引的问题。粗想起来，还确实是这么回事，like '%abc'这样的条件，由于通配符在第一个字符，因此这样的查询条件，无法进行索引范围扫描，因此一般情况下使用全表扫描比较合适。不过在某些情况下，如果表十分巨大，这种全表扫描成本太高，如果能使用索引就好了。而反转键索引正好在存储键值的时候是反转过来的，1abc,2abc在索引键里的存储为abc1,abc2，这种情况下，做就可以通过范围扫描将符合条件的记录找出来吗？这个解释似乎是很合理，我也曾经被这个理论所蒙蔽过一段时间，直到有一天我自己做了一个实验，才发现问题远非那么简单。下面我们来回顾一下这个实验。首先我们创建测试表：
DROP TABLE TINDEX;
CREATE TABLE TINDEX as SELECT DISTINCT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,STATUS,TEMPORARY,TIMESTAMP,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234456' abc
from dba_objects;
INSERT INTO TINDEX SELECT DISTINCT OBJECT_NAME,OBJECT_ID,OBJECT_TYPE,CREATED,STATUS,TEMPORARY,TIMESTAMP,'ABCDEFGHIJKLMNOPQRSTUVWXYZ1234456' abc
from dba_objects;
create index idx_tindex_name on tindex(object_name ) reverse;
然后做一次表分析：
exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'tindex', estimate_percent=>30, -
cascade=>true, degree=>2);
似乎一切都准备好了，下面我们来测试一下反转键索引是否真的能够解决like语句的问题：
SQL> set autotrace traceonly
select * froM tindex where object_name like '%TINDEX';
SQL>
Execution Plan
----------------------------------------------------------
Plan hash value: 2264840918
----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5297 | 553K| 103 (3)| 00:00:01 |
|* 1 | TABLE ACCESS FULL| TINDEX | 5297 | 553K| 103 (3)| 00:00:01 |
----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("OBJECT_NAME" LIKE '%TINDEX')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
1717 consistent gets
0 physical reads
0 redo size
939 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
似乎索引并没有被自动使用，我们使用hint强制索引看看：
SQL> select /*+ INDEX(TINDEX idx_tindex_name ) */ * froM tindex where object_name like '%TINDEX';
Execution Plan
----------------------------------------------------------
Plan hash value: 2021627753
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 5297 | 553K| 5548 (1)| 00:00:54 |
| 1 | TABLE ACCESS BY INDEX ROWID| TINDEX | 5297 | 553K| 5548 (1)| 00:00:54 |
|* 2 | INDEX FULL SCAN | IDX_TINDEX_NAME | 5287 | | 520 (1)| 00:00:05 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("OBJECT_NAME" LIKE '%TINDEX')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
520 consistent gets
0 physical reads
0 redo size
939 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
虽然使用了索引，但是扫描方式是全索引扫描，而不是我们期待的索引范围扫描。看样子反转键索引并不能解决这个问题，我以前是被忽悠了。于是我继续做实验：
SQL> create index idx_tindex_func on tindex(reverse(object_name));
Index created.
SQL> exec DBMS_STATS.GATHER_TABLE_STATS(ownname=>'scott', tabname=>'tindex', estimate_percent=>30, -
> cascade=>true, degree=>2);
PL/SQL procedure successfully completed.
SQL> select * froM tindex where reverse(object_name) like 'XEDNIT%';
Execution Plan
----------------------------------------------------------
Plan hash value: 1286384425
-----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 4 | 436 | 7 (0)| 00:00:01 |
| 1 | TABLE ACCESS BY INDEX ROWID| TINDEX | 4 | 436 | 7 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | IDX_TINDEX_FUNC | 4 | | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')
filter(REVERSE("OBJECT_NAME") LIKE 'XEDNIT%')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
6 consistent gets
0 physical reads
0 redo size
939 bytes sent via SQL*Net to client
400 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
2 rows processed
这才是我们所需要的效果，通过reverse函数，然后将%TINDEX反转为XEDNIT%，才真正的解决了这个问题，这个SQL的开销是原SQL的几百分之一。实际上这种解决方案只能在修改应用的前提下实现，不如使用反转键索引这么简单，而且用途广泛。不过我们也终于通过实验纠正了一个错误的，流传甚广的误解。我在网上通过google查阅了大量的关于此话题的英文资料，终于明白了这个误解的来源，最初的时候，网上确实有一篇文章，介绍使用reverse函数解决这个问题，后来这篇文章在被转载的时候
SELECT *
FROM customer
WHERE Cust_Name LIKE '%Vilas%'
修改为：
SELECT *
FROM customer
WHERE reverse(Cust_Name) LIKE '%saliV%';
被错误的写成了：
SELECT *
FROM customer
WHERE Cust_Name LIKE '%saliV%';
以此版本为蓝本，终于引发了通过反转键索引优化like操作这个错误的观点。由此可见，网络上的知识，不经过自己验证就全盘吸收是多么危险。
不过以Oracle反转键索引的存储结构，确实具备对Like条件做范围扫描的基础，只不过这样的扫描，和以往Oracle提供的任何一种索引扫描技术都不相同，是一种全新的索引扫描方式。也许在Oracle 12或者13里，真的会出现类似的功能呢，Oracle的美工新版本给人带来的惊喜一直都是出乎大多数人的意外的。
扫一扫
1884
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
