sql – 选择顶行时使用的索引错误

发布时间：2020-12-12 06:25:13 所属栏目：MsSql教程来源：网络整理

导读：我有一个简单的查询,它选择由其他索引列过滤的列之一排序的前200行.令人困惑的是为什么PL / SQL Developer中的查询计划显示只有当我选择所有行时才使用此索引,例如： SELECT * FROM( SELECT * FROM cr_proposalsearch ps WHERE UPPER(ps.customerpostcode) li

我有一个简单的查询,它选择由其他索引列过滤的列之一排序的前200行.令人困惑的是为什么PL / SQL Developer中的查询计划显示只有当我选择所有行时才使用此索引,例如：

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索引.

解决方法

包括ROWNUM会更改优化程序关于哪个是更有效路径的计算.

当您执行这样的前n个查询时,并不一定意味着Oracle将获取所有行,对它们进行完全排序,然后返回最高行.执行计划中的COUNT STOPKEY操作表明Oracle只会在找到您要求的行数之前执行基础操作.

优化器已计算出完整查询将获取并排序77K行.如果它将此计划用于top-n查询,则必须执行大量的那些行才能找到前200个(它不一定要对它们进行完全排序,因为它不关心确切的顺序通过顶部的行;但它必须查看所有这些行).

top-n查询的计划使用另一个索引来避免必须排序.它按顺序考虑每一行,检查它是否与谓词匹配,如果是,则返回它.当它返回200行时,就完成了.它的计算表明,这对于获得少量行更有效. (当然,这可能不对;你还没有说过这些查询的相对表现是什么.)

如果优化器在请求所有行时选择此计划,则必须按降序读取整个索引,从ROWID获取表中的每一行,以检查谓词.这将导致大量额外的I / O并检查许多不会返回的行.因此,在这种情况下,它决定使用customerpostcode上的索引更有效.

如果逐渐增加从top-n查询返回的行数,您可能会找到计划从第一个切换到第二个的临界点.仅仅从两个计划的成本来看,我猜这可能是大约1,200行.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!