如何使用NOT IN子句添加1000个以上的值 [英] how to add more than 1000 values with NOT IN clause

问题描述

我有一个逗号分隔的ID，我想在NOT IN子句中使用. 我正在使用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块中时，这将是您获得的.此外，还有未定义的限制您要命中的查询长度.另一方面，对表进行反JOIN操作... 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.

这篇关于如何使用NOT IN子句添加1000个以上的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！