Аргумент SQL ограничивает в Oracle
Кажется, что существует предел 1 000 аргументов в SQL Oracle. Я столкнулся с этим при генерации запросов такой как....
select * from orders where user_id IN(large list of ids over 1000)
Мое обходное решение должно составить временную таблицу, вставить идентификаторы пользователей в это сначала вместо того, чтобы выпустить запрос через JDBC, который имеет гигантский список параметров в В.
Кто-либо знает о более легком обходном решении? Так как мы используем, в спящем режиме, интересно, может ли это автоматически сделать подобное обходное решение прозрачно.
4 ответа
Альтернативным подходом была бы передача массива в базу данных и использование функции TABLE() в пункте IN. Это, вероятно, будет работать лучше, чем временная таблица. Конечно, это будет более эффективно, чем выполнение нескольких запросов. Но вам нужно будет следить за использованием PGA-памяти, если у вас большое количество сеансов, делающих подобные вещи. Кроме того, я не уверен, насколько легко будет передать это в Hibernate.
Примечание: TABLE() функции работают в SQL движке, поэтому им нужно, чтобы мы объявили тип SQL.
create or replace type tags_nt as table of varchar2(10);
/
Следующий пример заполняет массив парой тысяч случайных тегов. Затем он использует массив в пункте IN запроса.
declare
search_tags tags_nt;
n pls_integer;
begin
select name
bulk collect into search_tags
from ( select name
from temp_tags
order by dbms_random.value )
where rownum <= 2000;
select count(*)
into n
from big_table
where name in ( select * from table (search_tags) );
dbms_output.put_line('tags match '||n||' rows!');
end;
/
Пока временная таблица является глобальной временной таблицей (т.е. видимой только для сеанса), это рекомендуемый способ действий (и я бы пошел по этому пути для чего-то большего, чем дюжина аргументов, не говоря уже о тысяче).
Мне интересно, где / как вы составляете этот список из 1000 аргументов. Если это полупостоянная группировка (например, все сотрудники, проживающие в определенном месте), то эта группировка должна быть в базе данных, и соединение должно выполняться там. Базы данных спроектированы и построены для очень быстрого соединения. Намного быстрее, чем возвращать кучу идентификаторов обратно на средний уровень и затем отправлять их обратно в базу данных.
select * from orders
where user_id in
(select user_id from users where location = :loc)
комментарии относительно «если эти идентификаторы есть в вашей базе данных, вместо этого используйте соединения / корреляцию». Однако, если ваш список идентификаторов поступает откуда-то еще, например, результат SOLR, вы можете обойти требование временной таблицы, выполнив несколько запросов, каждый из которых содержит не более 1000 идентификаторов, а затем объединив результаты запроса в памяти. Если вы поместите начальный список идентификаторов в уникальную коллекцию, такую как хэш-набор, вы можете вывести 1000 идентификаторов за раз.
Можно добавить дополнительные предикаты, чтобы разбить список на куски 1000:
select * from orders where user_id IN (<first batch of 1000>)
OR user_id IN (<second batch of 1000>)
OR user_id IN ...