sql - Oracle SQL - 选择不按预期使用索引
问题描述
所以我已经超过 5 年没有使用 Oracle 并且我已经没有实践了。我一直在使用 SQL Server。
我正在查看一些现有的查询并尝试改进它们,但它们的反应非常奇怪。根据解释计划,他们没有加快速度,而是进行全表扫描而不使用索引。
在原始查询中,在 where 语句中完成了两个表之间的等值连接。我们将它们称为表 A 和 B。我使用了一个解释计划,然后是 SELECT * FROM table(DBMS_XPLAN.DISPLAY (FORMAT=>'ALL +OUTLINE')); 它告诉我表 A 是由本地索引查询的。
按本地索引 ROWID 访问表
SELECT A.*
FROM TableA A, TableB B
WHERE A.SecondaryID = B.ID;
我尝试更改查询并将 TableA 与新表(表 C)连接起来。表 C 是表 B 的子集,有 700 条记录,而不是 100K。然而,解释计划告诉我,现在通过完整查找查询表 A。
CREATE TableC
AS<br>
SELECT * FROM TableB WHERE Active='Y';
SELECT A.*
FROM TableA A, TableC C
WHERE A.SecondaryID = C.ID;
下一步,我保留了表 A 和 C 之间的连接,但使用了一个提示来告诉它使用表 A 上的索引。但是它仍然会进行完整的查找。
SELECT /*+ INDEX (A_NDX01) */ A.*
FROM TableA A, TableC C
WHERE A.SecondaryID = C.ID;
因此,我尝试将连接更改为表 A 的简单选择,并使用 IN 语句与表 C 进行比较。仍然是全表扫描。
SELECT A.*
FROM TableA A
WHERE A.SecondaryID in (SELECT ID FROM TableC);
最后,我把前面的语句改成 subselect 拉取前 1000 条记录,它使用了索引。奇怪的是,表 C 中只有 700 条记录。
SELECT A.*
FROM TableA A
WHERE A.SecondaryID in (SELECT ID FROM TableC WHERE rownum <1000
)
我想知道是否有人可以帮助我弄清楚发生了什么?
我最好的猜测是,由于 TableC 是一个新表,也许优化器不知道其中有多少条记录,这就是为什么它只在知道少于 1000 条记录时才使用索引的原因?
我尝试在我的模式上运行 dbms_stats.gather_schema_stats 但它没有帮助。
谢谢您的帮助。
解决方案
作为一般规则,使用索引并不一定会让您的查询总是更快。
提示是优化器使用路径的指令,它并不意味着优化器会选择服从提示指令。在这种情况下,优化器会认为 TableA 上的索引查找在
SELECT A.*
FROM TableA A, TableB B
WHERE A.SecondaryID = B.ID;
SELECT /*+ INDEX (A_NDX01) */ A.*
FROM TableA A, TableC C
WHERE A.SecondaryID = C.ID;
SELECT A.*
FROM TableA A
WHERE A.SecondaryID in (SELECT ID FROM TableC);
在内部,它可能已将所有这些语句(IN)转换为一个连接,当考虑 tableA 和 tableC 中的数据时决定使用全表扫描。
当您执行 rownum 条件时,此计划转换未完成。这是因为当查询块中有 rownum 时,视图合并将不起作用。
我相信这就是你做的时候发生的事情
SELECT A.*
FROM TableA A
WHERE A.SecondaryID in (SELECT ID FROM TableC WHERE rownum <1000)
看看下面的链接
推荐阅读
- matlab - 关于使用 NN 进行数据拟合任务的数据准备的困惑
- artifactory - 从旧的 Artifactory 实例迁移到新的实例时,复制 $ARTIFACTORY_HOM/data/filestore 有什么意义?
- html - css:如何在不增加整个文本行高的情况下增加一个字符的字体大小?
- kotlin - 如何从 getParcelableArrayListExtra 获取数据
在科特林 - javascript - 返回自定义数组值
- c# - 如何在 asp.net core 2.2 中实现 Cookie 基本身份验证和 jwt?
- ajax - symfony + AJAX 如何上传嵌入在 javascript JSON 对象中的文件?
- c++ - 为什么我收到错误:“有多个默认构造函数”?
- python-3.x - Scrapy混合并重复来自不同页面的项目字段
- c# - foreach 循环不显示正确的结果