没有连接的Oracle IN子句对性能的影响是什么?

本文介绍了没有连接的Oracle IN子句对性能的影响是什么?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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

I have a query in this form that will on average take ~100 in clause elements, and at some rare times > 1000 elements. If greater than 1000 elements, we will chunk the in clause down to 1000 (an Oracle maximum).

SQL的格式为

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

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

The tables I am selecting from are huge and will contain millions more rows than what is in my in clause. My concern is that the optimizer may elect to do a table scan (our database does not have up to date statistics - yeah - I know ...)

是否存在可以强制使用主键的提示-无需知道主键的索引名，也许类似于.../* + DO_NOT_TABLE_SCAN */?

Is there a hint I can pass to force the use of the primary key - WITHOUT knowing the index name of the primary Key, perhaps something like ... /*+ DO_NOT_TABLE_SCAN */?

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

Are there any creative approaches to pulling back the data such that

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

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

推荐答案

如果表上的统计信息准确，那么当您使用优化器时，优化程序将不太可能选择执行表扫描而不是使用主键索引WHERE子句中只有1000个硬编码元素.最好的方法是收集(或设置)对象的准确统计信息，因为这应该使好事情自动发生，而不是尝试进行大量的体操运动以解决不正确的统计信息.

If the statistics on your table are accurate, it should be very unlikely that the optimizer would choose to do a table scan rather than using the primary key index when you only have 1000 hard-coded elements in the WHERE clause. The best approach would be to gather (or set) accurate statistics on your objects since that should cause good things to happen automatically rather than trying to do a lot of gymnastics in order to work around incorrect statistics.

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

If we assume that the statistics are inaccurate to the degree that the optimizer would be lead to believe that a table scan would be more efficient than using the primary key index, you could potentially add in a DYNAMIC_SAMPLING hint that would force the optimizer to gather more accurate statistics before optimizing the statement or a CARDINALITY hint to override the optimizer's default cardinality estimate. Neither of those would require knowing anything about the available indexes, it would just require knowing the table alias (or name if there is no alias). DYNAMIC_SAMPLING would be the safer, more robust approach but it would add time to the parsing step.

如果您要在IN子句中使用可变数量的硬编码参数构建SQL语句，则可能会通过使用不可共享的SQL和迫使数据库花费大量时间分别对每个变量进行硬解析.如果您创建了一个可共享的SQL语句，该语句可以被解析一次，则效率会大大提高.根据您的IN子句值的来源，可能看起来像

If you are building up a SQL statement with a variable number of hard-coded parameters in an IN clause, you're likely going to be creating performance problems for yourself by flooding your shared pool with non-sharable SQL and forcing the database to spend a lot of time hard parsing each variant separately. It would be much more efficient if you created a single sharable SQL statement that could be parsed once. Depending on where your IN clause values are coming from, that might look something like

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

If you got yourself down to a single sharable SQL statement, then in addition to avoiding the cost of constantly re-parsing the statement, you'd have a number of options for forcing a particular plan that don't involve modifying the SQL statement. Different versions of Oracle have different options for plan stability-- there are stored outlines, SQL plan management, and SQL profiles among other technologies depending on your release. You can use these to force particular plans for particular SQL statements. If you keep generating new SQL statements that have to be re-parsed, however, it becomes very difficult to use these technologies.

这篇关于没有连接的Oracle IN子句对性能的影响是什么?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！