SELECT * FROM ( SELECT * FROM cr_proposalsearch ps WHERE UPPER(ps.customerpostcode) like 'MK3%' ORDER BY ps.ProposalNumber DESC ) WHERE ROWNUM <= 200
计划显示它使用索引CR_PROPOSALSEARCH_I1,它是两列的索引：PROPOSALNUMBER& UPPER(CUSTOMERNAME),执行时需要0.985秒：
如果我摆脱了ROWNUM条件,那么该计划就是我所期望的,它在0.343秒内执行：
索引XIF25CR_PROPOSALSEARCH在CR_PROPOSALSEARCH(UPPER(CUSTOMERPOSTCODE))上;
怎么会？
编辑：我收集了cr_proposalsearch表的统计信息,现在两个查询计划都显示它们使用XIF25CR_PROPOSALSEARCH索引.
解决方法
当您执行这样的前n个查询时,并不一定意味着Oracle将获取所有行,对它们进行完全排序,然后返回最高行.执行计划中的COUNT STOPKEY操作表明Oracle只会在找到您要求的行数之前执行基础操作.
优化器已计算出完整查询将获取并排序77K行.如果它将此计划用于top-n查询,则必须执行大量的那些行才能找到前200个(它不一定要对它们进行完全排序,因为它不关心确切的顺序通过顶部的行;但它必须查看所有这些行).
top-n查询的计划使用另一个索引来避免必须排序.它按顺序考虑每一行,检查它是否与谓词匹配,如果是,则返回它.当它返回200行时,就完成了.它的计算表明,这对于获得少量行更有效. (当然,这可能不对;你还没有说过这些查询的相对表现是什么.)
如果优化器在请求所有行时选择此计划,则必须按降序读取整个索引,从ROWID获取表中的每一行,以检查谓词.这将导致大量额外的I / O并检查许多不会返回的行.因此,在这种情况下,它决定使用customerpostcode上的索引更有效.
如果逐渐增加从top-n查询返回的行数,您可能会找到计划从第一个切换到第二个的临界点.仅仅从两个计划的成本来看,我猜这可能是大约1,200行.
