rownum 和优化器

提问：

在9i的环境中，现在有个一个表A里面有大概10万行数据，其中有个字段B有10个值，每个值的行数比较平均，可以认为每个值都占1万行。
有条SQL为：
select * from A where B = xxx and rownum <= 100;
这条语句被非常频繁的执行，并且，每次带入的B的值都是不同的。
如果没有ROWNUM的条件，对B建立INDEX很可能更加降低效率，但由于我只要从某个B值的1万行数据中找其中100行，在key stop机制下，oracle 找到第100条记录就会停止，实际上index 访问时仅仅需要访问blevel 和部分 leaf_blocks 及100次表扫描by rowid。
由于以上9i CBO没有考虑rownum这个限制条件的原因，分析执行路径的时候，总是认为全表扫描的成本更低。
问题在于，在以上只取前100行的例子中，对B字段加INDEX并且强制采用INDEX，能否提高性能？

回答：

1。rownum是一个伪字段，它只有在真正取到满足条件的数据后才会被赋值。当优化器在选择路径时，没有任何记录与rownum相关，所以虽然你的本意是只取任意100个B = xxx的记录，但你的语句实际上是按下面的方式被优化器进行优化分析的
select * from A where B = xxx

2。对于该语句是full table scan好 还是 index scan好取决于你的数据分布情况。
   下面是个简单的解释：
    假设10万条数据存放于10个block中，full table scan 会使用10个I/O取得所需数据。
    如果B=xxx的数据只分布于2个block中， index存放于1个block中，那么index scan + access data by rowid会使用3个I/O取得所需数据，I/O消耗明显优于full table scan, 此时优化器会选择index scan
      如果B=xxx的数据也遍布于10个block中，那么index scan + access data by rowid会使用11个I/O取得所需数据，反而不如full table scan,此时优化器会选择full table scan

由此，简单化地对B字段加INDEX并且强制采用INDEX未必会提高性能。