为什么 oracle IN 子句只对静态数据有 1000 的限制? [英] Why oracle IN clause has limit of 1000 only for static data?

问题描述

Oracle IN 子句对于静态数据的限制为 1000，但它接受来自子查询的无限数据.为什么?

Oracle IN clause has limit of 1000 for static data,but it accepts unlimited data from sub queries. why?

推荐答案

这是对任何 表达式列表:

以逗号分隔的表达式列表最多可以包含 1000 个表达式.

A comma-delimited list of expressions can contain no more than 1000 expressions.

为什么是 1000?据推测，实现需要某种限制，这似乎已经足够了.很可能，或者肯定可能在几十年前设置该限制时，限制的性能原因也是如此，特别是当 IN 被转换为多个 OR在这种情况下优化器的语句(如果您查看执行计划，您可以看到).

Why 1000? Presumably the implementation needs some kind of limit, and that probably seemed like more than enough. There may well be, or certainly may have been when that limit was set decades ago, a performance reason for the limit as well, particularly as the IN is converted to multiple OR statements by the optimiser in this case (which you can see if you look at the execution plan).

我很难想出一个合理的场景，需要接近那个，固定值无论如何都不能作为子查询从其他数据中导出.

I'd struggle to come up with a reasonable scenario that needed to get anywhere near that, with fixed values that couldn't be derived from other data anyway as a subquery.

我怀疑这与逻辑数据库限制有关<例如，/a> 表示一个表中的列不能超过 1000；由于在插入语句中使用表达式列表来列出要插入的列和值，因此表达式列表必须能够匹配它，但可能没有理由超过它.

I suspect it's somewhat related to the logical database limits which say you can't have more than 1000 columns in a table, for instance; since an expression list is used in an insert statement to list both the columns and the values being inserted, the expression list has to be able to match that, but maybe has no reason to exceed it.

当然是猜测……如果不了解软件的内部结构，您就不太可能得到明确的答案.

Speculation of course... without seeing the internals of the software you're unlikely to get a definitive answer.

这篇关于为什么 oracle IN 子句只对静态数据有 1000 的限制?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！