不带连接的 Oracle IN 子句的性能影响是什么?

Question

我有一个这种形式的查询，平均需要 ~100 个子句元素，在极少数情况下> 1000 个元素。如果大于 1000 个元素，我们将 in 子句分块为 1000（Oracle 最大值）。

该 SQL 采用以下形式：

SELECT * FROM tab WHERE PrimaryKeyID IN (1,2,3,4,5,...)

我从中选择的表很大，并且将包含比我的 in 子句中多数百万行的行。我担心的是优化器可能会选择进行表扫描（我们的数据库没有最新的统计数据 - 是的 - 我知道......

有没有我可以传递的提示来强制使用主键 - 不知道主键的索引名称，也许像 .../*+ DO_NOT_TABLE_SCAN */？

是否有任何创造性的方法来提取数据，以便

1. 我们执行最少的往返次数
2. 我们读取的块数最少（在逻辑 IO 级别？
3. 这会更快吗.

SELECT * FROM tab WHERE PrimaryKeyID = 1
  UNION
SELECT * FROM tab WHERE PrimaryKeyID = 2
  UNION
SELECT * FROM tab WHERE PrimaryKeyID = 2
  UNION ....

Answer 1

如果表上的统计信息是准确的，那么当 WHERE 子句中只有 1000 个硬编码元素时，优化程序不太可能选择执行表扫描而不是使用主键索引。 最好的方法是收集（或设置）关于对象的准确统计数据，因为这应该会导致好事自动发生，而不是试图做很多体操来解决不正确的统计数据。

如果我们假设统计信息不准确，以至于优化程序会认为表扫描比使用主键索引更有效，则可以添加DYNAMIC_SAMPLING提示，强制优化程序在优化语句之前收集更准确的统计信息，或者添加CARDINALITY提示以覆盖优化程序的默认基数估计。 这些都不需要知道任何关于可用索引的信息，它只需要知道表别名（如果没有别名，则知道名称）。 DYNAMIC_SAMPLING将是更安全、更可靠的方法，但它会增加解析步骤的时间。

如果要在 IN 子句中使用可变数量的硬编码参数构建 SQL 语句，则可能会用不可共享的 SQL 淹没共享池并迫使数据库花费大量时间单独硬解析每个变体，从而为自己带来性能问题。 如果您创建了一个可以解析一次的可共享 SQL 语句，效率会高得多。 根据 IN 子句值的来源，可能如下所示

SELECT *
  FROM table_name
 WHERE primary_key IN (SELECT primary_key
                         FROM global_temporary_table);

或

SELECT *
  FROM table_name
 WHERE primary_key IN (SELECT primary_key
                         FROM TABLE( nested_table ));

或

SELECT *
  FROM table_name
 WHERE primary_key IN (SELECT primary_key
                         FROM some_other_source);

如果您使用单个可共享的 SQL 语句，那么除了避免不断重新解析该语句的成本之外，您还可以使用多种选项来强制执行不涉及修改 SQL 语句的特定计划。 不同版本的 Oracle 具有不同的计划稳定性选项 - 根据您的版本，有存储大纲、SQL 计划管理和 SQL 配置文件以及其他技术。 您可以使用它们来强制特定 SQL 语句的特定计划。 但是，如果您不断生成必须重新解析的新 SQL 语句，则使用这些技术将变得非常困难。