如何使用 NOT IN 子句添加超过 1000 个值-数据库问题

how to add more than 1000 values with NOT IN clause(如何使用 NOT IN 子句添加超过 1000 个值)

本文介绍了如何使用 NOT IN 子句添加超过 1000 个值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我想在 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.

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