Как поместить более 1000 значений в предложение Oracle IN [дубликат]

я почти уверен, что вы можете разделить значения на несколько INs с помощью или:

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

вы можете попробовать использовать следующий формат:

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

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

откуда вы получаете список идентификаторов в первую очередь? Поскольку они являются идентификаторами в вашей базе данных, они пришли из какого-то предыдущего запроса?

когда я видел это в прошлом было так:-

1. отсутствует ссылочная таблица, и правильным способом было бы добавить новую таблицу, поместить атрибут в эту таблицу и присоединиться к ней
2. список идентификаторов извлекается из базы данных, а затем используется в последующей инструкции SQL (возможно, позже или дальше другой сервер или что-то еще). В этом случае ответ заключается в том, чтобы никогда не извлекать его из базы данных. Либо хранить во временной таблице, либо просто написать один запрос.

Я думаю, что могут быть лучшие способы переделать этот код, который просто получает этот оператор SQL для работы. Если вы предоставите более подробную информацию вы можете получить некоторые идеи.

использовать ...из таблицы.(.. :

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; 
/ 

Это один из редких случаев, когда вам нужна подсказка, иначе Оракл не будет использовать индекс на столбце 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

я оказался здесь в поисках решения, а также.

в зависимости от количества элементов высокого класса, которые вам нужно запросить, и предполагая, что ваши элементы уникальны, вы можете разделить запрос на пакеты запросов 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 запросов. Если это так, то вам, вероятно, следует более серьезно взглянуть на использование решения глобальных временных таблиц, приведенного выше, как наиболее "правильное решение. Кроме того, если ваши элементы не уникальны, вам также нужно будет разрешить повторяющиеся результаты в ваших партиях.

Да, очень странная ситуация для Oracle.

Если вы укажете 2000 идентификаторов внутри предложения IN, это не удастся. это не удается:

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

но если вы просто поместите 2000 идентификаторов в другую таблицу (например, временную таблицу), она будет работать это работает:

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

что вы можете сделать, на самом деле можно разделить записи на множество 1000 записей и выполнить их по группам.

вместо IN статья, вы можете попробовать использовать JOIN С другой таблицей, которая извлекает идентификатор. таким образом, нам не нужно беспокоиться о пределе. просто мысль с моей стороны.

вот некоторый код Perl, который пытается обойти ограничение, создав встроенное представление, а затем выбрав из него. Текст инструкции сжимается с помощью строк по двенадцать элементов каждый вместо выбора каждого элемента из двух по отдельности,а затем распаковывается путем объединения всех столбцов. UNION или UNION ALL IN decompression не должны иметь никакого значения здесь, так как все это происходит внутри, в котором будет наложена уникальность, прежде чем присоединиться к ней в любом случае, но в сжатии используется 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 whose_base in ($bases_list_expr)
UPDATE

вместо SELECT * FROM table1 WHERE ID IN (1,2,3,4,...,1000);

используйте этот :

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

*обратите внимание, что вы должны быть уверены, что идентификатор не ссылается на другие внешние идентификаторы, если это зависимость. Чтобы убедиться, что доступны только существующие идентификаторы:

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

Ура