oracle回表查询损耗多,12c比10g索引回表消耗增多的问题

问题是这样的：

在12c中，我们测试了2种情况：

第一种是加了hint，使得12c的执行计划和10g类似，只是由于12c的nlj_batching，多了一次nestloop。但是执行计划本质是相同的，都是索引S_CONTACT_X_U1返回表查询。

第二种是使用了10g的outline hint，OFE=10g的，执行计划完全一样。

但是我们发现，无论是在12c中的哪一种情况，驱动表S_SRV_REQ的索引PA_S_SRV_REQ_1_X的full index scan返回结果差异这么大？

文章最后附在10g和12c环境中的测试经过，以及10g环境中的10046 trace和12c第一种情况的10046 trace。

12c-第一种情况：

12c-第二种情况：

10g数据库的情况：

###################################

另外，根据过滤条件，10g和12c返回结果也差不多。且消耗也差不多！

12c：

10g：

在同事的Fred Liu的帮助下，我们终于找到了答案。(Fred Liu在他近期的微信公共账号也写了这个问题。大家可以关注：『老虎刘谈SQL优化』 )

(1)首先，这个SQL逻辑有问题。可以见我附件10g.txt中的附件，有完整的sql文本

SQL的主要功能是分页，客户这里把rownum和order by写在一层关系中了。所以这个是错误的。

(2)就算是在逻辑错误，为什么10g跑快，A-rows小？

这是因为SQL语句中rownum<=10, 而10g的返回记录是10行，12c的返回记录只有4行。

在rownum<=10的情况下，10g扫描索引的时候，扫描了264行之后，已经够10行了，所以可以返回；而12c中扫描索引，只有4行，也就是说，从开头扫到结尾，扫描全部还没找齐10行。所以需要扫描整个索引。因此消耗就高了。

其实我将SQL语句改写成rownum<=4，这样也可以在3秒多返回记录了。或者我将SQL改成rownum<=8000，那么在10g中运行也很慢了。

(3)修正SQL逻辑后，客户的SQL还是跑的慢，15分钟，发现因为客户写的hint用到的索引不是最优执行计划，使用acct_open_dt这个字段的索引之后(或者不用指定，oracle自己的CBO会选择这个索引)，还要把1/3移动到右边，结果不到1秒就出来了。(注，如果1/3不移动到右边，还是会很慢。)

在这个案例中，我们可以学到的知识点：

1. 要分析SQL语句的逻辑，不能光看执行计划

2. 要注意count stopkey和a-rows的值。不然会想不明白为什么在10g扫描了264行就停止了。

3. 对方说数据源是一样的，不能完全相信。上述例子就可以看到，同样语句实际数据源还是有差异的，一个返回10行一个返回4行，而这个原因导致执行效率差别很大。

4. 索引字段有运算关系，需要把运算放到右边。才能正常使用索引。