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

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

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

Otávio Décio.. 89

将值放在临时表中,然后执行select in where in(从temptable中选择id)

1> Otávio Décio..：

---

将值放在临时表中,然后执行select in where in(从temptable中选择id)

---

@ocdecio - 与JOIN相比,我对Oracle 10g的测试显示了不同(并且显然更糟)的IN解释计划.就个人而言,我会使用JOIN,并建议其他人*测试*不同的方法,以查看性能差异,而不是猜测.

---

我个人将值放入临时表并使用JOIN查询值.不过,我不知道这是否真的有更好的表现.

---

我无法在生产中创建临时表.不看好

---

但如果他有2000个值,他将如何在单个数据库命中中插入临时表？这最好写一些逻辑并拆分成1000,1000条记录并创建动态查询,因为peter severin表示select*from table1,其中ID为(1,2,3,4,...,1000)或ID为(1001, 1002,...,2000)..

2> 小智..：

---

我几乎可以肯定你可以使用OR在多个IN之间拆分值:

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

---

IN子句中的最大值数量是您永远不应受限制的限制之一.

---

人们可以这样做,但这意味着Oracle每次都会看到不同的查询,这意味着需要进行大量的硬解析,这会减慢速度.

3> rics..：

---

您可以尝试使用以下表单:

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

---

如果没有创建临时表的权限,这将成为一个超级解决方法.

4> Sergey11g..：

---

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

---

这是有效的,因为这是一个多值比较IN列表.Oracle实现了这个多值比较IN列表,其限制为<100,000,而不是正常IN列表中的1,000.所以第一个值val1是'magic',这也可能是一个列.第二个值val2是一列.因此IN列表中的值是val1和val2必须匹配的值.因此val1必须等于input1,而val2必须等于输入2.由于val1和input1被硬编码为'magic',我们可以将其视为普通IN列表,但限制为100,000而不是1,000.

---

因为甲骨文只是如此_(令人震惊？)在你尝试之前你会不会相信它并且看到它有效!这打败了创建一个临时表,特别是如果你对vi/vim/subl很好.

---

哇!我不敢相信这会有效,但事实确实如此.

5> WW...：

---

你从哪里获得id的列表？由于它们是数据库中的ID,它们是否来自之前的一些查询？

当我在过去看到这个时,原因是: -

缺少参考表,正确的方法是添加新表,在该表上放置一个属性并加入它

从数据库中提取id列表,然后在后续SQL语句中使用(可能在以后或在其他服务器或其他任何服务器上).在这种情况下,答案是永远不要从数据库中提取它.存储在临时表中或只写一个查询.

我认为可能有更好的方法来重新编写这个代码,只是让这个SQL语句工作.如果您提供更多详细信息,您可能会得到一些想法

6> tuinstoel..：

---

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

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