为什么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.

我怀疑这与逻辑数据库限制表示，例如，一个表中的列不能超过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屋！