Oracle 在WHERE子句中显式提供值，与使用子查询相比，显示出更好的性能

嗨

我是oracle的新手，不确定如何提供liveSQL链接。

我有两张表要加入
huge_table包含大约10亿行
big_table包含大约10000万行
和小表包含999行提供条件过滤

要匹配huge_table.num_id列的small_table.num_id

我注意到的问题是，如果我显式地提供where子句中的值，它将比使用子查询更快。

具体在看解释计划结果时
此查询的速度更快，基数为100，成本为6

SELECT h.col_required, b.col_required, h.num_id
FROM 
huge_table h,
big_table b

WHERE h.row_id = b.xx_id
      AND b.status = 'up'
      AND h.num_id in ('num1', 'num2',... 'num100')  -- Explicitly providing the values that in 
                                                     -- small_table

这种带有子查询的查询在1445704基数和660293成本方面要慢得多

SELECT h.col_required, b.col_required, h.num_id
FROM 
huge_table h,
big_table b

WHERE h.row_id = b.xx_id
      AND b.status = 'up'
      AND h.num_id in ('SELECT num_id FROM small_table)  -- Using sub query 
                                                         -- provide the values

我还尝试使用WHERE exits或INNER JOIN small_table都提供了类似的结果与子查询。


我的问题是，如果有可能在不明确提供where子句中的值的情况下具有良好的性能？


谢谢

几点:

-使用子查询，您必须访问另一个表。对于数据库来说，这是更多的工作。尽管对于一张小表来说，这种努力应该可以忽略不计

-当您提供文字值列表时，优化器会确切知道您要搜索的值。而对于子查询，它必须根据小表的统计数据进行猜测。

这可能导致子查询与列表中的估计值存在很大差异。

您在查询的估计行中有很大的差异 (100 vs 1445704)。所以你几乎可以肯定每个人都有不同的计划。

如果您共享查询计划，我们可以进一步研究。

有关如何获取execution计划，请参阅:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

确保这包括E行、A行和缓冲区列!