如何将超过 1000 个值放入 Oracle IN 子句中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/400255/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to put more than 1000 values into an Oracle IN clause
提问by Aaron Palmer
Is there any way to get around the Oracle 10g limitation of 1000 items in a static IN clause? I have a comma delimited list of many of IDs that I want to use in an IN clause, Sometimes this list can exceed 1000 items, at which point Oracle throws an error. The query is similar to this...
有什么办法可以绕过 Oracle 10g 静态 IN 子句中 1000 个项目的限制？我有一个逗号分隔的列表，其中包含我想在 IN 子句中使用的许多 ID，有时此列表可能超过 1000 个项目，此时 Oracle 会抛出错误。该查询类似于此...
select * from table1 where ID in (1,2,3,4,...,1001,1002,...)
采纳答案by Otávio Décio
Put the values in a temporary table and then do a select where id in (select id from temptable)
将值放在临时表中，然后执行 select where id in (select id from temptable)
回答by Peter Severin
I am almost sure you can split values across multiple INs using OR:
我几乎可以肯定您可以使用 OR 在多个 IN 之间拆分值：
select * from table1 where ID in (1,2,3,4,...,1000) or
ID in (1001,1002,...,2000)
回答by Sergey11g
select column_X, ... from my_table
where ('magic', column_X ) in (
('magic', 1),
('magic', 2),
('magic', 3),
('magic', 4),
...
('magic', 99999)
) ...
回答by rics
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,...)
回答by WW.
Where do you get the list of ids from in the first place? Since they are IDs in your database, did they come from some previous query?
您首先从哪里获得 ID 列表？由于它们是您数据库中的 ID，它们是否来自之前的某个查询？
When I have seen this in the past it has been because:-
当我过去看到这一点时，是因为：-
- a reference table is missing and the correct way would be to add the new table, put an attribute on that table and join to it
- a list of ids is extracted from the database, and then used in a subsequent SQL statement (perhaps later or on another server or whatever). In this case, the answer is to never extract it from the database. Either store in a temporary table or just write one query.
- 缺少参考表，正确的方法是添加新表，在该表上放置一个属性并加入它
- 从数据库中提取一个 id 列表，然后在随后的 SQL 语句中使用（可能稍后或在另一台服务器上或其他任何地方）。在这种情况下，答案是永远不要从数据库中提取它。要么存储在临时表中，要么只编写一个查询。
I think there may be better ways to rework this code that just getting this SQL statement to work. If you provide more details you might get some ideas.
我认为可能有更好的方法来重新编写这段代码，只是让这个 SQL 语句起作用。如果您提供更多详细信息，您可能会得到一些想法。
回答by tuinstoel
Use ...from table(... :
使用 ...from table(... :
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;
/
This is one of the rare cases where you need a hint, else Oracle will not use the index on column id. One of the advantages of this approach is that Oracle doesn't need to hard parse the query again and again. Using a temporary table is most of the times slower.
这是您需要提示的极少数情况之一，否则 Oracle 将不会使用列 id 上的索引。这种方法的优点之一是 Oracle 不需要一次又一次地硬解析查询。大多数情况下，使用临时表的速度较慢。
edit 1simplified the procedure (thanks to jimmyorr) + example
编辑 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;
/
Example:
例子：
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;
/
This will output:
这将输出：
Raymond
Hans
回答by Mike Atlas
I wound up here looking for a solution as well.
我也在这里寻找解决方案。
Depending on the high-end number of items you need to query against, and assuming your items are unique, you could split your query into batches queries of 1000 items, and combine the results on your end instead (pseudocode here):
根据您需要查询的高端项目数量，并假设您的项目是唯一的，您可以将您的查询拆分为 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));
}
This may be a good trade-off in the scenario where you don't typically have over 1000 items - as having over 1000 items would be your "high end" edge-case scenario. For example, in the event that you have 1500 items, two queries of (1000, 500) wouldn't be so bad. This also assumes that each query isn't particularly expensive in of its own right.
在您通常没有超过 1000 个项目的情况下，这可能是一个很好的权衡 - 因为超过 1000 个项目将是您的“高端”边缘情况。例如，如果您有 1500 个项目，则 (1000, 500) 的两个查询不会那么糟糕。这还假设每个查询本身并不是特别昂贵。
This wouldn'tbe appropriate if your typical number of expected items got to be much larger - say, in the 100000 range - requiring 100 queries. If so, then you should probably look more seriously into using the global temporary tables solution provided above as the most "correct" solution. Furthermore, if your items are not unique, you would need to resolve duplicate results in your batches as well.
如果您的预期项目的典型数量要大得多 - 例如，在 100000 范围内 - 需要 100 次查询，这将是不合适的。如果是这样，那么您可能应该更认真地考虑使用上面提供的全局临时表解决方案作为最“正确”的解决方案。此外，如果您的项目不是唯一的，您还需要解决批次中的重复结果。
回答by Aaron He
Yes, very weird situation for oracle.
是的，oracle 的情况非常奇怪。
if you specify 2000 ids inside the IN clause, it will fail. this fails:
如果您在 IN 子句中指定 2000 个 ID，它将失败。这失败了：
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 below query:
但是如果您只是将 2000 个 ID 放在另一个表（例如临时表）中，它将在查询下工作：
select ...
where id in (select userId
from temptable_with_2000_ids )
what you can do, actually could split the records into a lot of 1000 records and execute them group by group.
您可以做什么，实际上可以将记录拆分为大量 1000 条记录并逐组执行。
回答by Never Sleep Again
Here is some Perl code that tries to work around the limit by creating an inline view and then selecting from it. The statement text is compressed by using rows of twelve items each instead of selecting each item from DUAL individually, then uncompressed by unioning together all columns. UNION or UNION ALL in decompression should make no difference here as it all goes inside an IN which will impose uniqueness before joining against it anyway, but in the compression, UNION ALL is used to prevent a lot of unnecessary comparing. As the data I'm filtering on are all whole numbers, quoting is not an issue.
下面是一些 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
}
One would use that like so:
人们会像这样使用它：
my $bases_list_expr = big_IN_list(list_your_bases());
$dbh->do(<<"UPDATE");
update bases_table set belong_to = 'us'
where whose_base in ($bases_list_expr)
UPDATE
回答by Raju
Instead of using INclause, can you try using JOINwith the other table, which is fetching the id. that way we don't need to worry about limit. just a thought from my side.
除了 usingIN子句，您可以尝试JOIN与正在获取 id 的其他表一起使用吗？这样我们就不用担心限制了。只是我这边的一个想法。
