Oracle чем заменить not in

Обновлено: 02.07.2024

Настройка SQL-запроса

У меня есть текущая таблица (Investor) с записями клиентов и другая, итоговая, таблица (Invdatew), с теми же записями, содержащими дополнительный столбец даты. В конце каждого дня я, с помощью пакета dbms_job, вставляю данные из текущей таблицы в итоговую, добавляя в качестве даты значение sysdate. Но это выполняется настолько долго, что на вставку 36 строк (новые записи за день) уходит примерно три часа. Не мог бы ты помочь мне настроить этот запрос.

Ответ Тома Кайта:

Ну, при отсуствии определений таблиц приходится гадать, но я думаю, что столбец inv задан как допускающий значения NULL, что не позволяет серверу выполнять ряд оптимизаций. Ниже представлен пример, в котором я сравниваю результаты трех способов вставки и их производительность. Они должны вам помочь. Я рекомендую метод антисоединения хешированием (hash anti-join), как, вероятно, наиболее оптимальный. Фактически, все зависит от размера таблиц. Я также предполагаю наличие индекса по таблице invdatew для запроса NOT EXISTS, - если индекса нет, лучше и не пытайтесь его выполнять!

Все три запроса - различны, в том смысле, что если столбец INV в таблице invdatew имеет значения NULL, первый вернет один ответ (ни одной строки), а остальные два могут какие-то строки вернуть. Причины этого описаны здесь:

(см. сокращенный перевод этого обсуждения далее - В.К.)

Так что, я думаю, последние два запроса вам подойдут (если в столбце inv таблицы invdatew окажется значение NULL, вы всегда будете получать НОЛЬ строк, - не то, что нужно, правда?)

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

Этот запрос обрабатывался следующим образом:

Ух ты, вот это разница: вместо 85 секунд процессорного времени - всего .6, вместо 86 секунд выполнения - менее 4 секунд.

Это запрос обрабатывался примерно так:

Намного эффективнее.

Да, не так хорошо, как hash_aj, но. очень и очень близко. Я бы избегал этого подхода по причине большого количества логических операций ввода/вывода, - лучше выполнять 550 операций, чем 10672. Запрос обрабатывался следующим образом:

Надеюсь, вы сможете использовать антисоединение хешированием и запрос будет выполняться не более нескольких минут.

Комментарий В.К. Я позволил себе выкинуть восторги вопрошавшего после того, как, применив антисоединение хешированием, он ускорил выполнение запроса в 270 раз, - теперь результат получается за 40 секунд. Конец комментария

Комментарий читателя от 27 сентября 2002 года

А что это за hash_aj? Как оно работает? Это новая возможность 9i?

Ответ Тома Кайта

Это возможность стоимостного оптимизатора, поддерживается уже некоторое время - пример был для версии 8.1.7:

Оригинал обсуждения этого вопроса можно найти здесь.

IN или EXISTS, NOT IN или NOT EXISTS

Не мог бы ты объяснить отличие IN от EXISTS и NOT IN от NOT EXISTS. Просто я читал, что EXISTS будет работать лучше, чем IN, а NOT EXISTS - лучше, чем NOT IN (читал это в руководстве по настройке производительности сервера Oracle).

Ответ Тома Кайта:

(см. сокращенный перевод этого обсуждения далее - В.К.)

На самом деле, что лучше - зависит от запроса и данных. Учтите, однако, что в общем случае NOT IN и NOT EXISTS - не одно и то же.

просто НЕТ строк, таких что сотрудник не явлется менеджером: все - менеджеры (не так ли?)

Ага, а теперь, оказывается, 9 сотрудников менеджерами не являются. Учитывайте особенности обработки значений NULL в условиях NOT IN!! (вот почему условий NOT IN иногда избегают).

NOT IN может быть не менее эффективно, чем NOT EXISTS, - и даже на несколько порядков лучше, - если можно использовать "антисоединение" (если подзапрос точно не возвращает значений NULL)

Комментарий читателя от 2 октября 2002 года

Отличный ответ. Не мог бы ты объяснить, почему по условию NOT IN запись со значением NULL не выбирается?

Ответ Тома Кайта

Потому что NULL означает. хм. не знаю, что. (Буквально, null означает Неизвестно).

не возвращает ни TRUE, ни FALSE.

(вы могли бы подумать, что хоть один из двух запросов должен вернуть строку, но у булева типа в sql есть третье значение - "не знаю")

Оригинал обсуждения этого вопроса можно найти здесь.

IN и EXISTS

Не мог бы ты представить пример, в каких ситуациях IN лучше, чем EXISTS, и наоборот.

Ответ Тома Кайта:

Запросы с этими условиями выполняются очень по-разному.

обычно обрабатывается как:

Обычно выполняется подзапрос, выбираются уникальные значения, индексируются (или хешируются, или сортируются), а затем результат соединяется с исходной таблицей.

В отличие от запроса:

Который выполняется, скорее, так:

Он всегда приводит к полному просмотру таблицы T1, тогда как первый запрос может использовать индекс по T1(x).

Итак, когда же использовать exists, а когда - in?

Пусть результат выполнения подзапроса

"большой" и получается долго. А таблица T1 - сравнительно маленькая, и (select null from t2 where y = x.x) выполняется очень-очень быстро (есть хороший индекс по t2(y)). Тогда запрос с exists будет выполняться быстрее, потому что время на полный просмотр таблицы T1 и выбор из T2 по индексу может быть меньше, чем время полного просмотра T2 для построения подзапроса, по которому мы получаем уникальные значения.

Если результат подзапроса - небольшой, то условие IN обычно проверяется эффективнее.

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

Комментарий читателя от 28 сентября 2001 года

Не мог бы ты проиллюстрировать свой ответ на примере таблиц emp и dept:

увеличивая и уменьшая количество строк в каждой таблице
удаляя и добавляя индексы для обеих таблиц

Так твое объяснение было бы более убедительным. Сделай это, пожалуйста.

Ответ Тома Кайта

Я не собираюсь использовать EMP и DEPT, поскольку для иллюстрации сказанного пришлось бы генерировать массу данных для этих таблиц (если хотите, сделайте это сами ;)

Я буду использовать таблицы BIG и SMALL для иллюстрации.

Так что в таблице small - 99 строк, а в big - более 133000

Это показывает, что если внешний запрос - "большой", а внутренний - "маленький", IN обычно эффективнее, чем EXISTS.

показывает, что если внешний запрос - "маленький", а внутрениий - "большой", условие WHERE EXISTS может быть весьма эффективным.

Комментарий читателя от 30 сентября 2001 года

А что изменится, если удалить индексы по таблицам small и big?

Ответ Тома Кайта

Попробуйте, и узнаете. Весь необходимый код уже есть - такого рода проверки теперь выполнить будет легко.

Результат, однако, легко предположить.

Запрос "select * from big where object_id in (select object_id from small)", скорее всего, будет один раз сортировать BIG, один раз сортировать SMALL, а потом соединять результаты (это называется соединение сортировкой слиянием - sort merge join).

Запрос "select * from big where exists (select null from small where small.object_id = big.object_id)", скорее всего, будет выполняться путем однократного полного просмотра big, а ДЛЯ КАЖДОЙ СТРОКИ big будет полностью просматриваться таблица small.

(После проверки: я проверил, и оказалось, что простое правило" работает. Большой внешний запрос и маленький внутренний = IN. Маленький внешний запрос и большой внутренний = EXISTS. Помните - это ПРОСТОЕ ПРАВИЛО, а из простых правил есть бесконечно много исключений.

Комментарий В.К. Здесь придется прерваться. Выпуск и так получается слишком большим. Мы еще обязательно вернемся к обсуждению IN, EXISTS и антисоединений хешированием в одном из ближайших выпусков. Конец комментария

Оригинал обсуждения этого вопроса можно найти здесь.

Думаю снова сделать авторский выпуск, посвященный синтаксису. Впрочем, есть еще одна идея, - описать, как из СУБД Oracle обратиться к источнику данных ODBC. Как оказалось, сделать это вовсе не сложно. Тема эта была затронута в гостевой книге сайта OpenXS Initiative.

Выпуск выйдет в конце следующей недели. Следите за новостями на сайте проекта Open Oracle.

Примерно полтора года назад я сдал экзамены на OCP Advanced PL/SQL Developer, далее специфика работы несколько изменилась, и после стандартного производственного использования Oracle я занимался разработкой архитектуры двухуровневой клиент-серверной системы на основе Oracle для нужд компьютерной лингвистики. Далее был этап развития системы и решения наукоемких задач на ее основе, пришлось заниматься использованием иерархических запросов в решении нестандартных задач и другими специфическими вещами. Результатом углубления в специфику стало некоторое «проседание» базы, а значит, наступило время снова просмотреть материалы, использовавшиеся для подготовки к экзаменам.

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

Несколько слов о специфике оператора order by

Как вы думаете, будет ли ошибка в результате выполнения данного запроса?

Таким образом, мы отсортировали таблицу dual по выражению «3X», что бессмысленно, однако в качестве выражения для сортировки можно, например, использовать выражение с функцией substr. Важна сама возможность использования выражений.

Как мы знаем, таблица dual содержит один столбец, вернет ли ошибку такой запрос:

В первом случае мы имеем дело с позиционным указанием колонок – т.е. ссылкой на колонку №2, которой не существует, соответственно, получим ошибку.

Во втором случае мы имеем дело с выражением, т.е. это уже не номер колонки, а сортировка по числу 2, аналогичная сортировке по строке «3X» в запросе №1. Поэтому ошибки не будет.

Посмотрите на следующий пример. Какой порядок строк мы получим в результате выполнения этого запроса? Будет ли ошибка?

Union all не сортирует строки объединяемых множеств (в отличии от union), т.е. без order by мы получим строки в указанном в запросе порядке (union all гарантирует сохранение исходного порядка строк). Теперь настала очередь order by, главный вопрос, что такое «3» в этом случае? Поскольку у нас использованы двойные кавычки ", а не одинарные ', то «3» – это алиас колонки. Как известно, операции с множествами требуют использования сходных типов данных, а имена колонок берутся из первого запроса, поскольку мы явно не указали имя первой колонки, то, по умолчанию, она получила имя выражения, т.е. «3». Работа с такими алиасами показана, например, в запросе №5 (главное не забывать про аппер-кейс).

Сортировка по умолчанию – всегда asc, т.е. результаты запроса №4 сортируются по первой колонке по возрастанию. Результат: строка «2, Х», потом «3, Х».

Повторим эксперимент из запроса №3 на множествах. Каким будет результат запроса?

Поэтому запрос №6 вернет ошибку.

Использование not in

Наверняка, большинство людей, прошедших различные курсы, помнят, что следует избегать оператора not in, а сходную функциональность можно получить, используя операторы in либо exists. Причина такого негативного отношения к not in кроется в специфике его работы с null-значениями.

Определите результат выполнения следующего запроса:

Для начала рассмотрим такой запрос:

Ничего необычного в запросе №2 нет: подзапрос возвращает множество из двух строк со значениями «2» и «null», условие where принимает значение true, весь запрос №2 возвращает 1 строку – стандартное поведение.

null AND false = false
false AND null = false
null AND true = null
true AND null = null
null OR true = true
true OR null = true
null OR false = null
false OR null = null

Таким образом, условие where в запросе №1 преобразуется в null, если хотя бы один операнд null, поэтому весь запрос №1 приблизительно эквивалентен следующему запросу:

Очевидно, что запрос №3 не вернет ни одной строки, соответственно, запрос №1 также не вернет ни одной строки.

Неявное преобразование типов

Тема явного и неявного преобразования типов очень обширна, поэтому, не пытаясь охватить ее в целом, я хотел бы рассмотреть лишь один пример. Пускай сегодня 10.09.11 10:00:00 и Оракл сконфигурирован так, что формат DD.MM.RR распознается по умолчанию, какой из запросов вернет одну строку?

Ответ – оба. Почему так и как это, вообще, возможно? Ответ лежит в механизме неявного преобразования типов.

Рассмотрим запрос №1: в where мы сравниваем дату со строкой, в этом случае Оракл пытается преобразовать строку в дату, если формат строки соответствует одному из форматов даты по умолчанию (формат даты по умолчанию для сессии можно посмотреть в параметре NLS_DATE_FORMAT, выполнив запрос
select * from nls_session_parameters). Если формат строки не отвечает формату даты по умолчанию, то получим ошибку. В нашем случае форматы соответствуют и строка '10.09.11' преобразуется в дату 10.09.11 00:00:00, поскольку sysdate = 10.09.11 10:00:00, то запрос №1 вернет 1 строку.

Перевод второй части статьи «SQL Operators Tutorial – Bitwise, Comparison, Arithmetic, and Logical Operator Query Examples».

В первой части статьи мы рассмотрели такие темы:

В этой части мы рассмотрим:

Операторы для проверки существования (IN / NOT IN)

Если мы хотим проверить, есть ли определенное значение в списке значений, мы можем воспользоваться операторами IN или NOT IN :

Аналогично, для отрицания используется NOT IN :

Частичное совпадение — использование LIKE

Иногда нам нужно найти строки, основываясь на частичном совпадении.

Допустим, мы хотим найти всех пользователей, которые зарегистрировались в нашем приложении при помощи адреса Gmail. Мы можем поискать частичное совпадение в столбце, используя ключевое слово LIKE . Также при этом можно использовать групповой символ — % .

Чтобы поиск не зависел от регистра, нужно заменить LIKE на ILIKE :

Мы также можем использовать столько групповых символов, сколько нам нужно.

Например, поиск %j%o% вернет любой email-адрес, соответствующий шаблону «<все-что-угодно>, за чем следует j, за чем следует <все-что-угодно>, за чем следует o, за чем следует <все-что-угодно>»:

Работа с отсутствующими данными (NULL)

Давайте посмотрим, как быть со столбцами и строками, где нет данных.

Для этого давайте добавим в нашу таблицу users еще один столбец: first_paid_at .

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

Мы могли бы стереть нашу таблицу users , введя DROP TABLE users , и пересоздать ее заново, но таким образом мы удалили бы все данные в таблице.

Чтобы изменить таблицу, не стирая ее и не лишаясь данных, можно использовать ALTER TABLE :

Эта команда возвращает результат ALTER TABLE , так что наш запрос ALTER сработал успешно.

Если мы теперь запросим нашу таблицу users , мы заметим, что теперь в ней появился новый столбец без данных:

Наш столбец first_paid_at пуст, и результат нашего psql-запроса показывает, что это пустой столбец. Технически он не пустой: в нем содержится специальное значение, которое psql просто не показывает в выводе — NULL .

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

Чтобы это продемонстрировать, давайте посмотрим на простой SELECT :

Здесь мы просто выбрали 1 = 1 и 1 = 2 . Как мы и ожидали, результат этих двух предложений — t и f (или TRUE и FALSE ). 1 равен 1, но 1 не равен 2.

Теперь давайте попробуем проделать то же самое с NULL :

Мы могли ожидать, что значением будет FALSE , но на деле возвращается значение NULL .

Чтобы еще лучше визуализировать NULL , давайте при помощи опции \pset посмотрим, как psql отображает NULL-значения:

Если мы запустим этот запрос еще раз, мы увидим в выводе ожидаемый нами NULL :

Итак, 1 не равен NULL , а как насчет NULL = NULL ?

Довольно странно, однако NULL не равен NULL .

NULL лучше представлять себе как неизвестное значение. Равно ли неизвестное значение единице? Мы не знаем, оно же неизвестное. Равно ли неизвестное значение неизвестному значению? Опять же, мы этого не знаем. Это немного лучше поясняет, что такое NULL .

Использование IS NULL и IS NOT NULL

Мы не можем использовать с NULL оператор равенства, но мы можем пользоваться двумя специально созданными для этого операторами: IS NULL и IS NOT NULL .

Эти значения ожидаемы: NULL IS NULL — истина, NULL IS NOT NULL — ложь.

Это все прекрасно и очень интересно, но как это применять на практике?

Что ж, для начала давайте заведем какие-то данные в нашем столбце first_paid_at :

В приведенной выше инструкции UPDATE мы задали значения для столбца first_paid_at у троих разных пользователей: пользователю с ID 1 — текущее время ( NOW() ), пользователю с ID 2 — текущее время минус месяц, а пользователю с ID 3 — текущее время минус год.

Во-первых, давайте найдем пользователей, которые нам уже платили, и пользователей, которые пока этого не делали:

Операторы сравнения при работе с датами и временем

Теперь, когда у нас есть кое-какие данные, давайте используем те же операторы сравнения применительно к новому полю TIMESTAMP .

Попробуем найти пользователей, которые совершили платеж на протяжении последней недели. Для этого мы можем взять текущее время ( NOW() ) и вычесть из него одну неделю при помощи ключевого слова INTERVAL :

Мы также можем использовать другой интервал, например, последние три месяца:

Теперь давайте найдем пользователей, которые совершали платеж в промежутке от одного до шести месяцев назад.

Мы можем скомбинировать наши условия, используя AND , но вместо использования операторов < и > давайте используем ключевое слово BETWEEN :

Проверка существования с использованием EXISTS / NOT EXISTS

Другой способ проверить существование (наличие) значения — использовать EXISTS и NOT EXISTS .

Эти операторы фильтруют строки, проверяя существование или несуществование условия. Это условие обычно является запросом к другой таблице.

Чтобы это продемонстрировать, давайте создадим новую таблицу под названием posts . В этой таблице будут содержаться посты, котоыре пользователь может делать в нашей системе.

Это простая таблица. Она содержит только ID, поле для хранения текста поста ( body ) и ссылку на пользователя, который написал этот пост ( user_id ).

Давайте добавим в новую таблицу некоторые данные:

Согласно добавленным данными, у пользователя с ID 1 есть два поста, у пользователя с ID 2 — один пост, у пользователя с ID 3 — тоже один пост.

Чтобы найти пользователей, у которых есть посты, мы можем использовать ключевое слово EXISTS .

EXISTS принимает подзапрос. Если этот подзапрос возвращает что-либо (даже строку со значением NULL ), база данных включит эту строку в результат.

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

Вот пример выборки пользователей, имеющих посты:

Как и ождилалось, мы получили пользователей с ID 1, 2 и 3.

Наш подзапрос EXISTS проверяет записи в таблице posts, где user_id поста совпадает со столбцом id таблицы users. Мы вернули 1 в нашем SELECT , потому что здесь мы можем вернуть что угодно: база данных просто хочет видеть, что что-то вернулось.

Аналогично, мы можем найти пользователей, у которых нет постов. Для этого нужно заменить EXISTS на NOT EXISTS :

Наконец, мы можем переписать наш запрос и использовать IN или NOT IN вместо EXISTS или NOT EXISTS :

Технически это сработает, но вообще, если вы проверяете существование другое записи, более производительно будет использовать EXISTS . Операторы IN и NOT IN в целом лучше применять для проверки значения в статическом списке, как мы делали ранее:

Поразрядные операторы

Хотя на практике поразрядные операторы используются нечасто, для полноты картины давайте рассмотрим простой пример.

Если мы по какой-то причине хотим посмотреть возраст наших пользователей в бинарном виде и поиграться с перестановкой битов, мы можем использовать поразрядные операторы.

В качестве примера давайте рассмотрим поразрядный оператор «and»: & .

Чтобы осуществить поразрядную операцию, нам сначала нужно преобразовать значения в нашем столбце age из целых чисел в бинарный формат. В данном случае мы использовали ::bit(8) и получили восьмибитовые строки.

Далее мы можем «сложить» результат в бинарном формате с другой строкой в бинарном формате — 11111111 . Поскольку бинарный AND возвращает единицу только если оба бита это единицы, эта добавочная строка делает вывод интересным.

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

Поразрядный оператор «not» (

) немного отличается. Он применяется к одному термину, так же, как и обычный оператор NOT :

И, наконец, самый полезный из поразрядных операторов: конкатенация.

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

Здесь мы для создания значения name сконкатенировали (скомбинировали) first_name , пробел ( ' ' ) и last_name .

Заключение

Итак, мы рассмотрели практически все операторы фильтрации, котоыре вам могут понадобиться в работе!

Есть еще несколько, о которых мы не упоминали, но они либо используются не слишком часто, либо используются точно так же, как те, что мы разобрали, так что у вас не должно возникнуть проблем с ними.

От редакции Techrocks: возможно, вам будет интересна еще одна статья того же автора: SQL JOIN: руководство по объединению таблиц.

Мне кажется, что вы можете сделать то же самое в SQL-запросе, используя либо NOT EXISTS, NOT IN, либо LEFT JOIN, где NULL. Например:

Я не уверен, если я получил все правильный синтаксис, но это общие методы, которые я видел. Почему я должен использовать один над другим? Отличается ли производительность. Какой из них самый быстрый / самый эффективный? (Если это зависит от реализации, когда я буду использовать каждый из них?)

не в и не существует и левое соединение / нуль: SQL-сервера
не в и не существует и левое соединение / нуль: в PostgreSQL
не в и не существует и левое соединение / нуль: Оракул
не в и не существует и левое соединение / нуль: MySQL

на MySQL , NOT EXISTS - это немного менее эффективным!--16-->
на SQL Server , LEFT JOIN / IS NULL менее эффективен
на PostgreSQL , NOT IN менее эффективен
на Oracle , все три методы те же самые.

Если база данных хорошо оптимизирует запрос, два первых будут преобразованы в нечто близкое к третьему.

для простых ситуаций, подобных тем, о которых вы спрашиваете, не должно быть никакой разницы, так как все они будут выполняться как соединения. В более сложных запросах база данных может быть не в состоянии сделать соединение из not in и not exists queryes. В этом случае запросы будут намного медленнее. С другой стороны, соединение может также работать плохо, если есть нет индекса, который можно использовать, поэтому только потому, что вы используете соединение, не означает, что вы в безопасности. Вам нужно будет изучить план выполнения запроса, чтобы определить, могут ли быть какие-либо проблемы с производительностью.

предполагая, что вы избегаете нулей, они все способы написания анти-вступите использование стандартного SQL.

очевидное упущение эквивалентно использованию EXCEPT :

Примечание в Oracle вам нужно использовать MINUS оператор (возможно, лучшее имя):

говоря о собственном синтаксисе, также могут быть нестандартные эквиваленты, которые стоит исследовать в зависимости от продукта, который вы используете, например OUTER APPLY в SQL Server (что-то нравится):

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

перспектива производительности всегда избегайте использования обратных ключевых слов, таких как NOT IN, NOT EXISTS, . Потому что для проверки обратных элементов СУБД нужно пробежаться по всем доступным и отбросить обратную выборку.

Читайте также: