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

Question

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

select * from table1 where ID in (1,2,3,4,...,1001,1002,...)

Answer 1

将值放入临时表中，然后执行 select where id in (select id from temptable)

Answer 2

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

Answer 3

我几乎确定您可以使用 OR 将值拆分到多个 IN：

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

Answer 4

您可以尝试使用以下表格：

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

Answer 5

你首先从哪里获取 id 列表？ 由于它们是数据库中的 ID，它们是否来自之前的某些查询？

当我过去看到这种情况时，这是因为：-

1. 缺少参考表，正确的方法是添加新表，在该表上添加一个属性，然后加入
2. 从数据库中提取的 id 列表，然后在后续 SQL 语句中使用（可能稍后或在另一台服务器上或其他任何地方）。 在这种情况下，答案是永远不要从数据库中提取它。 要么存储在临时表中，要么只编写一个查询。

我认为可能有更好的方法来重写这段代码，让这个 SQL 语句正常工作。 如果您提供更多详细信息，您可能会得到一些想法。

Answer 6

使用 ...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; 
/ 

这是极少数需要提示的情况之一，否则 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

Answer 7

我也来到这里寻找解决方案。

根据您需要查询的高端项目数量，并假设您的项目是唯一的，您可以将查询拆分为 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 个查询，则此不是合适的。 如果是这样，那么您可能应该更认真地考虑使用上面提供的全局临时表解决方案作为最“正确”的解决方案。 此外，如果您的项目不是唯一的，您还需要解决批次中的重复结果。

Answer 8

是的，甲骨文的情况非常奇怪。

如果您在 IN 子句中指定 2000 个 id，则会失败。
这会失败：

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

但是如果你只是将 2000 个 id 放入另一个表（例如临时表）中，它就会起作用
下面的查询：

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

你可以做什么，实际上可以将记录分成很多 1000 条记录，然后逐组执行它们。

Answer 9

下面是一些 Perl 代码，尝试通过创建内联视图然后从中进行选择来解决该限制。 通过使用每行包含 12 个项目的行来压缩语句文本，而不是单独从 DUAL 中选择每个项目，然后通过将所有列合并在一起来解压缩。 解压缩中的 UNION 或 UNION ALL 在这里应该没有区别，因为它全部进入 IN 内部，无论如何，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

Answer 10

您可以尝试对另一个正在获取 id 的表使用 JOIN，而不是使用 IN 子句。 这样我们就不用担心限制了。 只是我的一个想法。

Answer 11

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

使用：

SELECT * FROM table1 WHERE ID IN (SELECT rownum AS ID FROM 双连接 BY level <= 1000);

*请注意，如果这是依赖项，您需要确保该 ID 不引用任何其他外部 IDS。 为了确保只有现有的 ID 可用，请执行以下操作：

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

干杯