没有连接的Oracle IN子句对性能有何影响？

小编典典

没有连接的Oracle IN子句对性能有何影响？

sql

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

SQL的形式为

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

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

有没有提示可以强制使用主键-在不知道主键索引名的情况下，也许类似于… / * + DO_NOT_TABLE_SCAN * /？

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

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

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

阅读 226

2021-04-22

共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语句，则使用这些技术将变得非常困难。

2021-04-22