唯一索引&普通索引的一个有趣的区别

在测试过程，却引起我对Oracle的索引的机制有了一次生动的回顾。

测试的本意是参数一个随机数，然后根据随机数取一条记录。

SELECT * FROM phs.t1 WHERE rn = trunc(dbms_random.value(1,100000))

Rn是索引字段 normal betree，根据rownum来建立。

但是初次测试的结果，却出乎我意料，居然查询结果是，无记录或者随机长度记录集。多次测试：

SELECT COUNT(*) FROM phs.t1 WHERE rn = trunc(dbms_random.value(1,100000));

结果竟然是：0,28909，0,0,9870，23012，0,56789，45189，1240…

我花了5分钟就解决了这个问题，但花了三个小时的思考，我终于想了一套说法来描述这里面的关系。

解决1：把sql改为

WITH tab AS(SELECT trunc(dbms_random.value(1,100000)) a FROM dual)
  SELECT * FROM t1 WHERE rn =( SELECT a FROM tab)

解决2：把rn的索引改为，unique

实际看到第二种解决办法，就大概有种明白的感觉了，但是想解释一下又无法说清。说白了，这里面牵扯了Oracle Intenal的东西，这些东西又不是公开的。我只能凭着仅有的资料去猜测。

在normal索引下，SQL的执行计划是RANGE SCAN，而unique索引，则是unique SCAN。
发生这种现象实际和SQL的执行机制以及索引的机制有着密切的联系。
通过查询，可以看出该索引有2747个叶子块，67个branch 块，height 3
SQL> SELECT s.leaf_blocks,s.blevel FROM dba_indexes s WHERE s.index_name = 'INDEX_T1_RN';


LEAF_BLOCKS   BLEVEL
----------- ----------
2747   2


SQL> SELECT SUM(blocks) FROM dba_extents WHERE segment_name='INDEX_T1_RN';


SUM(BLOCKS)
-----------
2816
那么它的结构如图：





SQL的执行步骤如下：
1．SQL匹配，语法语义检查,通过对LIBRARY CACHE中对象的比对，进行匹配。
2．对子查询，视图等进行重新组合和SQL改写，判断对象访问的开销以及结果集的大小,每个对象都独立计算成本以及返回的结果集的大小，判断不同的连接顺序的不同开销
，连接方式和连接顺序被通盘考虑，并且找到开销最小的连接方式这个步骤里面包含了SQL执行计划的优化。产生执行树，执行树被生成后放在LIBRARY CACHE里，当SQL执行的时候，被用来驱动查询.
4．分配绑定变量需要的内存空间，绑定变量的值实现绑定。使用上一步产生的执行计划执行SQL.
5．对于SELECT操作，比普通的SQL多了一个FETCH步骤，在这个步骤中，实际上的DB BLOCK的访问才会产生。在这个阶段，将剔除不需要的数据，把结果放入结果集，传输给客户端。
有了上面的知识，我现在来解释这个现象。
Q:为什么在normal的索引下，sql返回结构集的大小是随机的？在unique下面却能确定唯一记录？
A:首先在normal索引下，优化器会把执行计划解释为RANGE SCAN，因为它认为可能返回多条记录。优化器在进行执行计划编写的时候，首先就会计算 trunc(dbms_random.value(1,100000))，由此来确定选择什么执行计划，假设本次产生的随机数是87905。

进入执行阶段，系统找到87905“开始“所在的索引数据块，注意这个“开始”（也就是说它只关注数据是从那里开始的），随后进行遍历该块的水平链表寻找，执行过滤,找到适合rowid，再去获取数据块。

在normal模式下，由于是范围SCAN，优化器实际把

Rn= trunc(dbms_random.value(1,100000))

拆成了

Rn >= trunc(dbms_random.value(1,100000)) and

Rn <= trunc(dbms_random.value(1,100000))

在unique模式下，仍然是

Rn= trunc(dbms_random.value(1,100000))

所以，在normal模式下，遍历过程发现了87905的记录，但它这个时候它还会判断是否已经达到区间扫描的终点，又会执行Rn <= trunc(dbms_random.value(1,100000))

这个时候，trunc(dbms_random.value(1,100000))被重新计算！！！

那么就会出现下面的分支情况：




1.结果>87905,优化器认为已经达到区间终点（索引是有序的），谓词判断结果 true + false = false,所以返回空结果集。

2.结果小于等于87905,返回该记录，计算出新的谓词例如12346，再次寻找块，遍历水平列表（重复上面的动作），然后又进入分支判断…最终出现分支1的时候终止。

这样就说明为什么会出现这种情况。



在unique模式下，在遍历水平链表的时候，找到当前值就返回，而不用进行区间判断，或者是SCAN多条。所以结果集是正确的。
Q:为什么采用了with语法可以避免这种情况呢？
A:因为with字句在oracle内部被解释为一个内联视图或者临时表，所以trunc(dbms_random.value(1,100000))只会计算一次，之后的计算是针对固定的内联视图的。

oracle 不允许这种写法，rn= sequences.nextval，估计也是出于这个考虑吧。
想到这里，就结束了，其实可以自己写一个random函数，在里面加一个记录点，来证明谓词多次改变，也可以做更详细的sql trace，dump dump…

或者换一个Oracle的人来解释一下，就OK了，但我估计在Oracle china也没几个了解数据库机密的人。唉…