如何将超过1000个值放入一个 Oracle IN 子句中

有没有办法绕过 Oracle 10g 对静态 IN 子句中1000个项目的限制？我有一个逗号分隔的列表，其中包含许多我想在 IN 子句中使用的 ID，有时这个列表可能超过1000个项，这时 Oracle 会抛出一个错误。查询类似于..。

select * from table1 where ID in (1,2,3,4,...,1001,1002,...)
371051 次浏览
小开
最佳答案

将这些值放在一个临时表中，然后在 id 所在的位置执行选择(从 temtable 中选择 id)

小开

You may try to use the following form:

select * from table1 where ID in (1,2,3,4,...,1000)
union all
select * from table1 where ID in (1001,1002,...)
小开

使用... 从表(... :

create or replace type numbertype
as object
(nr number(20,10) )
/


create or replace type number_table
as table of numbertype
/


create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
open p_ref_result for
select *
from employees , (select /*+ cardinality(tab 10) */ tab.nr from table(p_numbers) tab) tbnrs
where id = tbnrs.nr;
end;
/

这是少数需要提示的情况之一，否则 Oracle 将不会在列 id 上使用索引。这种方法的一个优点是 Oracle 不需要一次又一次地对查询进行硬解析。使用临时表大多数时候比较慢。

编辑1 简化过程(感谢 jimmyorr) + 示例

create or replace procedure tableselect
( p_numbers in number_table
, p_ref_result out sys_refcursor)
is
begin
open p_ref_result for
select /*+ cardinality(tab 10) */ emp.*
from  employees emp
,     table(p_numbers) tab
where tab.nr = id;
end;
/

例如:

set serveroutput on


create table employees ( id number(10),name varchar2(100));
insert into employees values (3,'Raymond');
insert into employees values (4,'Hans');
commit;


declare
l_number number_table := number_table();
l_sys_refcursor sys_refcursor;
l_employee employees%rowtype;
begin
l_number.extend;
l_number(1) := numbertype(3);
l_number.extend;
l_number(2) := numbertype(4);
tableselect(l_number, l_sys_refcursor);
loop
fetch l_sys_refcursor into l_employee;
exit when l_sys_refcursor%notfound;
dbms_output.put_line(l_employee.name);
end loop;
close l_sys_refcursor;
end;
/

这将产生:

Raymond
Hans
小开

你从哪里得到的身份证明名单？因为它们是数据库中的 ID，所以它们来自以前的某个查询吗？

当我过去看到这种情况时，原因是:-

  1. 缺少一个引用表，正确的方法是添加新表，在该表上放置一个属性并连接到它
  2. 从数据库中提取 id 列表，然后在后续的 SQL 语句中使用(可能稍后使用，也可能在其他服务器或其他服务器上使用)。在这种情况下，答案是永远不要从数据库中提取它。要么存储在临时表中，要么只编写一个查询。

我认为可能有更好的方法来重写这段代码，只是让这个 SQL 语句工作。如果你提供更多的细节，你可能会得到一些想法。

小开

我几乎可以肯定，您可以使用 OR 跨多个 IN 分割值:

select * from table1 where ID in (1,2,3,4,...,1000) or
ID in (1001,1002,...,2000)
小开

我也是来找解决办法的。

根据您需要查询的高端项目数量，并假设您的项目是唯一的，您可以将查询分成1000个项目的批处理查询，然后将结果合并到您的终端(这里是伪代码) :

//remove dupes
items = items.RemoveDuplicates();


//how to break the items into 1000 item batches
batches = new batch list;
batch = new batch;
for (int i = 0; i < items.Count; i++)
{
if (batch.Count == 1000)
{
batches.Add(batch);
batch.Clear()
}
batch.Add(items[i]);
if (i == items.Count - 1)
{
//add the final batch (it has < 1000 items).
batches.Add(batch);
}
}


// now go query the db for each batch
results = new results;
foreach(batch in batches)
{
results.Add(query(batch));
}

这可能是一个很好的权衡方案，您通常没有超过1000个项目-因为有超过1000个项目将是您的“高端”边缘情况下的场景。例如，如果您有1500个条目，两个查询(1000,500)就不会那么糟糕。这还假设每个查询本身并不特别昂贵。

如果预期项目的典型数量要大得多(比如说，在100000个范围内) ，需要100个查询，那么这个 不会是合适的。如果是这样，那么您可能应该更认真地考虑使用上面提供的全局临时表解决方案作为最“正确”的解决方案。此外，如果项目不是唯一的，还需要解决批处理中的重复结果。

小开

而不是 SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1000);

Use this :

SELECT * FROM table1 WHERE ID IN (SELECT rownum AS ID FROM dual connect BY level <= 1000);

* 注意，如果这是一个依赖项，则需要确保 ID 不引用任何其他外部 IDS。为了确保只有现有的身份证可用，然后:

SELECT * FROM table1 WHERE ID IN (SELECT distinct(ID) FROM tablewhereidsareavailable);

干杯

小开

Instead of using IN clause, can you try using JOIN with the other table, which is fetching the id. that way we don't need to worry about limit. just a thought from my side.

小开

是的，对于先知来说，情况很奇怪。

if you specify 2000 ids inside the IN clause, it will fail. 这种做法失败了:

select ...
where id in (1,2,....2000)

but if you simply put the 2000 ids in another table (temp table for example), it will works 下面的问题:

select ...
where id in (select userId
from temptable_with_2000_ids )

你所能做的，实际上可以把记录分成许多1000条记录，然后一组一组地执行它们。

小开 
select column_X, ... from my_table
where ('magic', column_X ) in (
('magic', 1),
('magic', 2),
('magic', 3),
('magic', 4),
...
('magic', 99999)
) ...
小开

下面是一些 Perl 代码，它们试图通过创建内联视图并从中进行选择来绕过这个限制。通过使用由12个项组成的行来压缩语句文本，而不是单独从 DUAL 中选择每个项，然后通过将所有列联合在一起来解压缩语句文本。解压缩中的 UNION 或 UNION ALL 在这里应该没有区别，因为它们都在一个 IN 中，在加入之前都会施加唯一性，但在压缩中，UNION ALL 用于防止许多不必要的比较。由于我过滤的数据都是整数，引用不是问题。

#
# generate the innards of an IN expression with more than a thousand items
#
use English '-no_match_vars';
sub big_IN_list{
@_ < 13 and return join ', ',@_;
my $padding_required = (12 - (@_ % 12)) % 12;
# get first dozen and make length of @_ an even multiple of 12
my ($a,$b,$c,$d,$e,$f,$g,$h,$i,$j,$k,$l) = splice @_,0,12, ( ('NULL') x $padding_required );


my @dozens;
local $LIST_SEPARATOR = ', '; # how to join elements within each dozen
while(@_){
push @dozens, "SELECT @{[ splice @_,0,12 ]} FROM DUAL"
};
$LIST_SEPARATOR = "\n    union all\n    "; # how to join @dozens
return <<"EXP";
WITH t AS (
select $a A, $b B, $c C, $d D, $e E, $f F, $g G, $h H, $i I, $j J, $k K, $l L FROM     DUAL
union all
@dozens
)
select A from t union select B from t union select C from t union
select D from t union select E from t union select F from t union
select G from t union select H from t union select I from t union
select J from t union select K from t union select L from t
EXP
}

人们会这样使用它:

my $bases_list_expr = big_IN_list(list_your_bases());
$dbh->do(<<"UPDATE");
update bases_table set belong_to = 'us'
where id in ($bases_list_expr)
UPDATE

提交答案