Существует ли обходное решение для ORA-01795: максимальное количество выражений в списке - 1000 ошибок?

Существует ли обходной путь для

'ORA-01795: maximum number of expressions in a list is 1000 error'

У меня есть запрос, и он выбирает поля, основанные на значении одного поля. Я использую предложение in и есть 10000+ значений

пример:

select field1, field2, field3 
from table1 
where name in 
(
'value1',
'value2',
...
'value10000+'
);

Каждый раз, когда я выполняю запрос, я получаю ORA-01795: maximum number of expressions in a list is 1000 error. Я пытаюсь выполнить запрос в TOAD, никакой разницы, той же ошибки. Как мне изменить запрос, чтобы он работал?

Заранее спасибо

Ответ 1

Просто используйте множественные предложения, чтобы обойти это:

select field1, field2, field3 from table1 
where  name in ('value1', 'value2', ..., 'value999') 
    or name in ('value1000', ..., 'value1999') 
    or ...;

Ответ 2

Недавно я столкнулся с этой проблемой и разобрался с навязчивым способом сделать это, не сводя вместе дополнительные предложения IN

Вы можете использовать Tuples

SELECT field1, field2, field3
FROM table1
WHERE (1, name) IN ((1, value1), (1, value2), (1, value3),.....(1, value5000));

Oracle действительно позволяет > 1000 кортежей, но не простые значения. Подробнее об этом здесь,

https://community.oracle.com/message/3515498#3515498
и
https://community.oracle.com/thread/958612

Это, конечно, если у вас нет возможности использовать подзапрос внутри IN для получения значений, необходимых вам из таблицы temp.

Ответ 3

Некоторые обходные решения:

1- Разделить предложение IN на несколько разделов IN, где литералы меньше 1000 и объединить их с помощью предложений OR:

Разделите исходное предложение "WHERE" из одного условия "IN" в несколько условий "IN":

Select id from x where id in (1, 2, ..., 1000,…,1500);

To:

Select id from x where id in (1, 2, ..., 999) OR id in (1000,...,1500);

2- Использование кортежей: Предел 1000 применяется к наборам отдельных элементов: (x) IN ((1), (2), (3),...). Нет ограничений, если наборы содержат два или более элемента: (x, 0) IN ((1,0), (2,0), (3,0),...):

Select id from x where (x.id, 0) IN ((1, 0), (2, 0), (3, 0),.....(n, 0));

3- Использование временной таблицы:

Select id from x where id in (select id from <temporary-table>);

Ответ 4

Пожалуйста, используйте внутренний запрос внутри in -clause:

select col1, col2, col3... from table1
 where id in (select id from table2 where conditions...)

Ответ 5

Еще один способ:

CREATE OR REPLACE TYPE TYPE_TABLE_OF_VARCHAR2 AS TABLE OF VARCHAR(100);
-- ...
SELECT field1, field2, field3
  FROM table1
  WHERE name IN (
    SELECT * FROM table (SELECT CAST(? AS TYPE_TABLE_OF_VARCHAR2) FROM dual)
  );

Я не считаю его оптимальным, но он работает. Подсказка /*+ CARDINALITY(...) */ была бы очень полезна, поскольку Oracle не понимает мощность переданного массива и не может оценить оптимальный план выполнения.

В качестве другой альтернативы - пакетная вставка во временную таблицу и использование последнего в подзапросе для предиката IN.

Ответ 6

есть еще один способ решить эту проблему. скажем, у вас есть две таблицы Table1 и Table2. и требуется получить все записи таблицы 1, не упомянутые/присутствующие в таблице 2, с использованием запроса Критерии. Так продолжайте, как это...

List list=new ArrayList(); 
Criteria cr=session.createCriteria(Table1.class);
cr.add(Restrictions.sqlRestriction("this_.id not in (select t2.t1_id from Table2 t2 )"));
.
.

., Он будет выполнять всю функцию подзапроса непосредственно в SQL, не включая 1000 или более параметров в SQL, преобразованных в инфраструктуру Hibernate. Это сработало для меня. Примечание. Возможно, вам потребуется изменить часть SQL в соответствии с вашими требованиями.

Ответ 7

Я понимаю, что это старый вопрос и ссылка на TOAD, но если вам нужно кодировать это с помощью С#, вы можете разделить список на цикл for. Вы можете по существу сделать то же самое с Java, используя subList();

    List<Address> allAddresses = GetAllAddresses();
    List<Employee> employees = GetAllEmployees(); // count > 1000

    List<Address> addresses = new List<Address>();

    for (int i = 0; i < employees.Count; i += 1000)
    {
        int count = ((employees.Count - i) < 1000) ? (employees.Count - i) - 1 : 1000;
        var query = (from address in allAddresses
                     where employees.GetRange(i, count).Contains(address.EmployeeId)
                     && address.State == "UT"
                     select address).ToList();

        addresses.AddRange(query);
    }

Надеюсь, это поможет кому-то.

Ответ 8

Оперативное объединение

select * from tableA where tableA.Field1 in (1,2,...999)
union
select * from tableA where tableA.Field1 in (1000,1001,...1999)
union
select * from tableA where tableA.Field1 in (2000,2001,...2999)

Ответ 9

Существует еще один вариант: with синтаксисом. Чтобы использовать пример OPs, это будет выглядеть так:

with data as (
  select 'value1' name from dual
  union all
  select 'value2' name from dual
  union all
...
  select 'value10000+' name from dual)
select field1, field2, field3 
from table1 t1
inner join data on t1.name = data.name;

Я столкнулся с этой проблемой. В моем случае у меня был список данных на Java, где каждый элемент имел item_id и client_id. У меня есть две таблицы в БД с подпиской на позиции соответствующих клиентов. Я хочу получить список всех подписки на предметы или для клиента для этого элемента вместе с идентификатором элемента.

Я попробовал три варианта:

Множественные выборки из Java (с использованием кортежей, чтобы обойти ограничение)
С-синтаксисом
Временная таблица

Вариант 1: множественные выборки из Java

В принципе, я первый

select item_id, token 
from item_subs 
where (item_id, 0) in ((:item_id_0, 0)...(:item_id_n, 0))

затем

select cus_id, token 
from cus_subs 
where (cus_id, 0) in ((:cus_id_0, 0)...(:cus_id_n, 0))

Затем я создаю карту на Java с ключом cus_id как ключ и список элементов как значение, а для каждой найденной подписки клиента я добавляю (в список, возвращенный из первого выбора) запись для всех соответствующих элементов с этим item_id. Это намного более грязный код

Вариант 2: С-синтаксис

Получите все сразу с SQL как

with data as (
  select :item_id_0 item_id, :cus_id_0 cus_id
  union all
  ...
  select :item_id_n item_id, :cus_id_n cus_id )
select I.item_id item_id, I.token token
from item_subs I
inner join data D on I.item_id = D.item_id
union all
select D.item_id item_id, C.token token
from cus_subs C
inner join data D on C.cus_id = D.cus_id

Вариант 3: Временная таблица

Создайте глобальную временную таблицу с тремя полями: rownr (первичный ключ), item_id и cus_id. Вставьте все данные там, затем выполните очень похожий выбор на вариант 2, но привязка во временной таблице вместо данных with data

Представление

Это не полностью научный анализ эффективности.

Я бегу против базы данных разработки, с чуть более 1000 строк в моем наборе данных, для которых я хочу найти подписки.
Я только пробовал один набор данных.
Я не в том же физическом месте, что и мой сервер БД. Это не так далеко, но я замечаю, что если я попробую из дома через VPN, то все это будет намного медленнее, хотя это будет одинаковое расстояние (и это не мой домашний интернет, что проблема).
Я тестировал полный вызов, поэтому мой API вызывает другой (также работает в том же экземпляре в dev), который также подключается к БД для получения начального набора данных. Но во всех трех случаях это одно и то же.

YMMV.

Тем не менее, временная таблица была намного медленнее. Как в двойном так медленно. Я получал 14-15 секунд для варианта 1, 15-16 для вариантов 2 и 30 для варианта 3.

Я попробую их снова из той же сети, что и сервер БД, и проверьте, не изменилось ли это, когда я получаю шанс.