Oracle

Рано или поздно, каждый специалист, работающий с базами данных ORACLE, наталкивается на ограничение максимального количества элементов для оператора IN:

SELECT *

FROM VOUCHERS
WHERE CLIENT_ID IN (28, 45, 46, 102,...)

В ORACLE для оператора IN в скобках можно перечислять не более 1000 элементов через запятую.

Иногда этого количества может не хватать. Что делать в этом случае?

Во-первых, давай рассмотрим случай когда такого большого количества может не хватать? Не каждый день составляешь запрос и вручную перечисляешь через запятую тысячу элементов, верно? :)

На самом деле, SQL-запросы могут формировать и программы (прямо налету), а не люди. Например, в некотором приложении пользователь нажал кнопку, согласно которой должны выбраться все счета из таблицы VOUCHERS базы данных, но именно счета видимых в данный момент на экране (а список клиентов определённым образом отфильтрован).

И программа динамически строит запрос, как я и написал выше, где в скобках для оператора IN перечисляются идентификаторы видимых на экране клиентов. И далее этот запрос для отчёта или для другой экранной формы должен выбрать данные.

SQL-запросы с большим количеством элементов для оператора IN могут составляться не только программно, но и человеком. Однажды написанный SQL запрос с оператором IN/NOT IN, может со временем, дорабатываться и включать в себя всё новые элементы. Через какое-то время количество элементов у оператора IN/NOT IN может превысить установленный Ораклом лимит и такой запрос не сможет быть выполненным.

Как-то одному из разработчиков ORACLE задали вопрос: почему именно 1.000 элементов и не планируется ли в будущих выпусках ORACLE увеличить этот лимит?
На что разработчик ответил: а какой бы Вы хотели иметь лимит? 2.000, 5.000, или, может быть, 100.000? Вы можете написать SQL более оптимально и не придётся перечислять такое большое количество элементов для оператора IN.

Как можно поправить SQL запрос, убрав ограничение в 1.000 элементов?

Способ первый:

Использование (временной) таблицы. (Временная) таблица (пусть называется TMP_TABLE) заполняется значениями (например, идентификаторами клиентов) и далее они используются в ограничении выборки путём INNER JOIN-а с этой таблицей или её также можно использовать в IN:

Сначала заполняем (временную) таблицу:

INSERT INTO TMP_TABLE
(CLIENT_ID)
VALUES
(28);

INSERT INTO TMP_TABLE
(CLIENT_ID)
VALUES
(45);
...

И далее используем её для ограничения вывода данных из основной таблицы:

SELECT v.*
FROM VOUCHERS v
INNER JOIN TMP_TABLE t
ON v.CLIENT_ID = t.CLIENT_ID

Или так:

SELECT *
FROM VOUCHERS
WHERE CLIENT_ID IN (SELECT CLIENT_ID FROM TMP_TABLE)

Способ второй:

Использование нескольких операторов IN:

SELECT *
FROM VOUCHERS
WHERE ( CLIENT_ID IN (28, 45, 46, 102,...)
OR CLIENT_ID IN (1789, 1800,..) )

Есть и ещё способы обойти ограничения в 1.000 элементов оператора IN в ORACLE, о них ты можешь почитать тут.

Программы, строящие SQL-запросы динамически и с применением IN, контролируют сколько элементов размещается в скобках через запятую. При большом количестве элементов, они будут размещены в нескольких операторах IN, соединённых между собой оператором OR (второй способ).

Буду рад, если оценишь статью лайком и подпишешься на мой канал, если ещё не подписан.

Проверяя работу наших учеников курса "SQL. Базы данных. ORACLE", и даже курса "Программирования в PL/SQL (ORACLE)" иногда встречаю следующее использование функции to_date, которое содержит ошибку. И сейчас напишу почему. Итак, вот конструкция, содержащая ошибку:

to_date(sysdate, 'dd.mm.yyyy')

Функция to_date служит для преобразования ТЕКСТА в дату согласно указанной маске, а в примере выше функции на вход даётся итак дата (sysdate ведь дата, только ещё и со временем). Во-первых, неразумно из итак даты делать дату, иначе будет как в мультфильме про Кунг-фу панду:

-Ты не должен делать из них себя, ты должен делать из них их;

-Как я могу делать из них их, если они итак уже они? :)

Почему же эта конструкция ещё и содержит ошибку. Ошибка в том, что в этой конструкции Ораклу придётся задействовать неявное преобразование.

Чтобы функция to_date смогла отработать, она должна принять на вход ТЕКСТ и преобразовать его в дату, согласно указанной маске. А в примере выше Ораклу подаётся дата, ведь sysdate это итак дата (текущая, со временем). И Оракл сначала неявно преобразовывает ее в текст, а вот как он это выполняет без указанной макси преобразования, может зависеть от региональных настроек представления даты. В итоге дата в текст может быть преобразована совсем другая (будет другой день, месяц или год). И уже из текста, в котором может быть совершенно неожиданно другая дата, to_date сделает дату согласно указанной маске. Результат может быть неожиданным.

Неявного преобразования всегда нужно избегать. Это может привести к скрытым ошибкам. Все работает (ORACLE не показывает ошибку), а данные в результате в самый неподходящий и ответственный момент будут получены не правильные.

Я обычно всегда спрашиваю ученика для чего такая конструкция была сделана и что требовалось здесь получить. В итоге, чаще всего, нужна бывает текущая дата без времени. Текущую дату со временем в ORACLE даёт функция SYSDATE, и чтобы отбросить у неё часы, минуты и секунды (то есть чтобы сделать ее просто датой), можно обернуть её в функцию trunc. Функция trunc сделает из неё дату без времени (усечёт часы, минуты и секунды). То есть конструкцию:

to_date(sysdate, 'dd.mm.yyyy')

лучше заменить на:

trunc(sysdate).

Буду рад лайку этой статье и твоей подписке на мой канал, если ты еще не подписан.