如何使用 NOT IN 子句添加超过 1000 个值

2021-12-30 00:00:00 sql oracle11g oracle

我想在 NOT IN 子句中使用逗号分隔的 ID.我正在使用 oracle 11g.

I have comma delimited id's that I want to use in NOT IN clause.. I'm using oracle 11g.

select * from table where ID NOT IN (1,2,3,4,...,1001,1002,...)

结果

ORA-01795: maximum number of expressions in a list is 1000

我不想使用临时表.正在考虑这样做

I don't want to use temp table. am trying considering doing this

select * from table1 where ID NOT IN (1,2,3,4,…,1000) AND 
ID NOT IN (1001,1002,…,2000)

有没有其他更好的解决方法来解决这个问题?

Is there any other better workaround to this issue?

推荐答案

你说你不想,但是:使用临时表.这就是正确的解决方案.

You said you don't want to, but: use a temporary table. That's the correct solution here.

在 Oracle 中查询解析的开销很大,这就是将数千个标识符放入一个巨大的 SQL 块中时会得到的结果.此外,还有定义不明确的限制 关于您要命中的查询长度.另一方面,对表进行反联接... Oracle 擅长于此.将数据批量加载到表中,Oracle 也很擅长.使用临时表.

Query parsing is expensive in Oracle, and that's what you'll get when you put thousands of identifiers into a giant blob of SQL. Also, there are ill-defined limits on query length that you're going to hit. Doing an anti-JOIN against a table, on the other hand... Oracle is good at that. Bulk loading data into a table, Oracle is good at that too. Use a temp table.

IN 限制为一千个条目是一种完整性检查.你击中它的事实意味着你正在尝试做一些疯狂的事情.

Limiting IN to a thousand entries is a sanity check. The fact that you're hitting it means you're trying to do something insane.

相关文章