提问人：Binomi 提问时间：11/15/2023 最后编辑：Paul WBinomi 更新时间：11/16/2023 访问量：86
SQL在硬编码时使用索引，但在使用子选择时不使用
SQL using Index when hardcoded, but not when using a subselect
问：
我想选择表t中的所有数据，该表具有特定名称作为列条目。表 t 具有列名的索引。
当我执行指定 where 子句中硬编码的名称时，将使用索引并超快地计算结果。
但是当我确实使用与另一个（小）表的联接时，索引不会被使用，而且速度非常慢。
硬编码：
SELECT t.*
FROM table t
WHERE date = xyz
AND name in ('hardcoded_name_1','hardcoded_name_2', ...)
AND status = 1;
加入我真的很想做：
SELECT t.*
FROM table t
JOIN (SELECT name2 from t2) on name = name2
WHERE date = xyz
AND status = 1;
这里的表 t 很大，表 t2 非常小。
连接的执行计划：
SELECT STATEMENT ALL_ROWS
4 NESTED LOOPS
2 PARTITION RANGE ALL
1 TABLE ACCESS STORAGE FULL TABLE t.name
3 INDEX UNIQUE SCAN INDEX (UNIQUE) t2.name
答：
0赞
Ali Malek
11/15/2023
#1
问题在于您正在使用，这会导致优化器可能无法有效地使用索引。因为它在某种程度上类似于拥有一个与第一个表无关的临时表。subquery
为了提高性能，您需要使用直接连接，例如：
SELECT t.*
FROM table t
JOIN t2 ON t.name = t2.name2
WHERE date = xyz AND status = 1;
另外，请确保您有 fk，这有助于您的加入变得更快。
评论
0赞
Binomi
11/16/2023
你好阿里·马利克。谢谢你的建议。遗憾的是，查询速度与其他联接一样慢。我发现 t2.name2 也是我看到在执行计划中使用的索引。
0赞
BobC
11/16/2023
能否提供两次执行的执行计划（或 SQL Monitor 报告）。
0赞
Binomi
11/16/2023
这是连接的执行计划： SELECT 语句 ALL_ROWS 4 嵌套循环 2 分区范围 全部 1 表访问存储 全表 t.name 3 索引 唯一扫描索引（唯一） t2.name
0赞
Bogdan Dincescu
11/16/2023
#2
尝试使用一些提示：
SELECT /*+ leading(t2) use_nl(t) index(t ix_t_name) */ t.*
FROM table t
JOIN t2 on name = name2
WHERE date = xyz AND status = 1
;
ix_t_name该索引的实际名称。
评论
0赞
Binomi
11/16/2023
我试过了，但不幸的是它并没有使我的查询更快
0赞
BobC
11/16/2023
查看执行计划的完整输出（包括估计的基数）会很有用。同时，您可以尝试哈希连接吗？SELECT /*+ 超前导（t2） use_hash（t） / t。FROM 表 t JOIN t2 on name = name2 WHERE date = xyz AND status = 1
2赞
Thorsten Kettner
11/16/2023
#3
您想要选择与名称列表匹配的行。在极端情况下，可能会发生没有一行与任何名称匹配或所有行都匹配的情况。
在第一个查询中，优化程序确切地知道要查找的名称数量和名称。这有助于它决定预期的匹配数量。此外，您使用的是子句，因此即使列表中有重复值，主表的行也只会被选中一次。优化器认为这是使用索引执行此操作的最快方法。IN
在第二个查询中，您更改了两个内容：
- 您已将硬编码值替换为 select。DBMS 现在必须知道 t2 表，以便估计预期的匹配项数。桌子上有统计数据吗？它们是最新的吗？只运行整个表而不是使用索引听起来像是获得稳定运行时间的好方法。
- 您已将子句替换为联接。这意味着，从理论上讲，您可以在结果中获得 t 行数倍。只有当 t2.name2 上存在唯一键时，DBMS 才会看到它保证不会有任何重复项。
IN
我不知道你为什么切换到联接。您需要加入吗？有没有你想从 t2 中选择的数据？照原样，看起来您仍然只需要查找一下：
SELECT t.*
FROM table t
WHERE date = xyz
AND name in (SELECT name2 from t2)
AND status = 1;
然后，您可能希望再次收集两个表的统计信息，最好在名称列上使用直方图，以便 DBMS 充分了解您的表，从而获得最佳执行计划。
最适合查询的索引似乎是这样的
create index idx1 on t (date, name, status);
或者也许
create index idx2 on t (name, status, date);
或具有这三列的其他索引。索引应按其选择性顺序排列列。一个名字的行百分比是多少？一个日期的行百分比是多少？状态 1 的行百分比是多少？百分比越小，色谱柱的选择性越强。将最具选择性的列设为索引中的第一个列。
评论
0赞
Binomi
11/16/2023
嗨，托尔斯滕。感谢您的全面回答。首先你是对的 - 在这种情况下我不需要连接，但我也尝试了带有子查询的选择，执行时间与连接大致相同。不幸的是，我无法更改表 t 的索引。表 t 是一个非常大的表，而表 t2 非常小，只有 30 行左右。所以理想情况下，我想告诉 Oracle：1. 从 t2 中获取所有只有几个名字，然后是 2。取这几个名称，然后选择 t 中的相应列
0赞
Thorsten Kettner
11/16/2023
我不明白为什么你不能在 t 上添加索引。您是否收集了统计数据，以便 Oracle 知道 t2 有多小，并且其中只有几个名字？与 t2 中的名称匹配的 t 行的百分比是多少？
0赞
Binomi
11/16/2023
我无法更改索引，因为我只阅读了 t 上的授权。如何收集这些统计数据？t2 的所有名称都包含在 t 中。 t2 有 40 行。将 where 语句应用于 t 后，t 大约包括 400.000。匹配主要在 t 的 40 行上，因此 t 中所有行的 0.00001=0.001% 与 t2 中的名称匹配。
0赞
Thorsten Kettner
11/16/2023
所以，是的，如果 Oracle 知道它的表，它应该使用索引。这是关于以下内容的文档：docs.oracle.com/database/121/ARPLS/d_stats.htm#ARPLS68582。您甚至可以使用该参数在名称列上获取频率直方图。希望这有助于找到一些例子。DBMS_STATS.GATHER_TABLE_STATSmethod_opt
0赞
Binomi
11/16/2023
我会尝试的，谢谢
评论
datestatusnamedatestatusnamenamet2t1