Те, кто работает с БД, знают об операциях над множествами. Их несколько: UNION — объединение, есть практически в любой реляционной базе, EXCEPT (по стандарту) и MINUS (Oracle) — вычитание, и INTERSECT — пересечение.
UNION по-умолчанию выполняет DISTINCT-преобразование, то есть, если множества A и B где-то пересекаются, то в результирующем множестве дублирующие строки будут удалены. Или, если говорить еще проще, то к результату выполнения первого запроса прибавятся только те строки из второго, которых нет в первом.
Не всем известно, что оператор UNION можно дополнить ключевым словом ALL, и в этом случае результаты просто склеятся без отсеивания дублей. Очевидно, что даже это несколько ускорит запрос (если вы точно знаете, что строки не повторяются). Но на этом не все.
Приведу пример. В фирме, где я раньше работал, информация об организациях хранилась в двух структурах — новая, с кучей отдельных таблиц на каждое свойство, и старая, состоящая из одной единственной таблицы firms. И там, и там присутствовал столбец firmnumber, являющийся первичным ключом. Часть организаций была только в новой структуре, часть только в старой, а часть — и там, и там. Желательно было брать информацию из новой.
Для отображения информации об организациях я создал представление (VIEW), которое выглядело примерно так:
SELECT firmnumber, (куча огромнейших подзапросов, выполняющих в основном групповую конкатенацию строк)
FROM nw.cont_firm (и еще много отJOINенных таблиц новой структуры)
UNION
SELECT firmnumber, (остальные поля таблицы)
FROM firms f
WHERE NOT EXISTS (SELECT NULL FROM nw.cont_firm f2 WHERE f2.firmnumber = f.firmnumber)
Добавив условие WHERE NOT EXISTS во второй запрос, я изначально сделал оба множества не пересекающимися, то есть множество B дополняло множество A. (Note Bene: не совсем корректно выразился, так как на самом деле строки и так получались разные из-за отличий в полях телефонов, адресов и т. д., собственно поэтому и пришлось писать WHERE NOT EXISTS и не полагаться на UNION).
Я просто знал, что объединить два запроса в один — это UNION.
Но на деле что-то вроде SELECT * FROM vi_firms f WHERE f.firmnumber = 10000 выполнялось порядка десяти секунд, а поиск по адресу вообще занимал не меньше минуты. И объяснить это можно вот чем. Когда вы используете оператор UNION, как уже было сказано, два результата «схлопываются» в один, при этом теряется АБСОЛЮТНО вся информация о том, к каким таблицам какие столбцы относились. Выполнив это преобразование, БД уже не знает, 10000 было взято из новой структуры или из старой. Для нее все становится просто строками без привязки к данным, соответственно, не удастся использовать ни первичные ключи, ни индексы, ни отношения, ни материализованные представления. Каждый раз делается выборка всех данных, устраняются дубли (хоть их и нет, формально операция выполняется), и затем уже по все строкам результата ищется значение 10000 в поле firmnumber.
Я попробовал дописать слово ALL, и запрос выполнился за 0,02 секунды! На этот раз никакая информация о том, какой столбец к какой таблице относится не терялась и поиск велся не по результату, выдаваемому представлением, а по данным, использующимся в самих запросах, в данном случае использовался первичный ключ. Поиск по адресу тоже занял мало времени — где-то полсекунды.
Отсюда вывод — использовать UNION без ALL только если действительно необходимо убрать повторяющиеся значения, и этого никак не сделать с помощью ограничений вроде NOT IN или NOT EXISTS.
Все описанное справедливо для Oracle, но, думаю, для многих других БД тоже, хотя не проверял. Надеюсь, этот материал кому-нибудь поможет.
UNION по-умолчанию выполняет DISTINCT-преобразование, то есть, если множества A и B где-то пересекаются, то в результирующем множестве дублирующие строки будут удалены. Или, если говорить еще проще, то к результату выполнения первого запроса прибавятся только те строки из второго, которых нет в первом.
Не всем известно, что оператор UNION можно дополнить ключевым словом ALL, и в этом случае результаты просто склеятся без отсеивания дублей. Очевидно, что даже это несколько ускорит запрос (если вы точно знаете, что строки не повторяются). Но на этом не все.
Приведу пример. В фирме, где я раньше работал, информация об организациях хранилась в двух структурах — новая, с кучей отдельных таблиц на каждое свойство, и старая, состоящая из одной единственной таблицы firms. И там, и там присутствовал столбец firmnumber, являющийся первичным ключом. Часть организаций была только в новой структуре, часть только в старой, а часть — и там, и там. Желательно было брать информацию из новой.
Для отображения информации об организациях я создал представление (VIEW), которое выглядело примерно так:
SELECT firmnumber, (куча огромнейших подзапросов, выполняющих в основном групповую конкатенацию строк)
FROM nw.cont_firm (и еще много отJOINенных таблиц новой структуры)
UNION
SELECT firmnumber, (остальные поля таблицы)
FROM firms f
WHERE NOT EXISTS (SELECT NULL FROM nw.cont_firm f2 WHERE f2.firmnumber = f.firmnumber)
Добавив условие WHERE NOT EXISTS во второй запрос, я изначально сделал оба множества не пересекающимися, то есть множество B дополняло множество A. (Note Bene: не совсем корректно выразился, так как на самом деле строки и так получались разные из-за отличий в полях телефонов, адресов и т. д., собственно поэтому и пришлось писать WHERE NOT EXISTS и не полагаться на UNION).
Я просто знал, что объединить два запроса в один — это UNION.
Но на деле что-то вроде SELECT * FROM vi_firms f WHERE f.firmnumber = 10000 выполнялось порядка десяти секунд, а поиск по адресу вообще занимал не меньше минуты. И объяснить это можно вот чем. Когда вы используете оператор UNION, как уже было сказано, два результата «схлопываются» в один, при этом теряется АБСОЛЮТНО вся информация о том, к каким таблицам какие столбцы относились. Выполнив это преобразование, БД уже не знает, 10000 было взято из новой структуры или из старой. Для нее все становится просто строками без привязки к данным, соответственно, не удастся использовать ни первичные ключи, ни индексы, ни отношения, ни материализованные представления. Каждый раз делается выборка всех данных, устраняются дубли (хоть их и нет, формально операция выполняется), и затем уже по все строкам результата ищется значение 10000 в поле firmnumber.
Я попробовал дописать слово ALL, и запрос выполнился за 0,02 секунды! На этот раз никакая информация о том, какой столбец к какой таблице относится не терялась и поиск велся не по результату, выдаваемому представлением, а по данным, использующимся в самих запросах, в данном случае использовался первичный ключ. Поиск по адресу тоже занял мало времени — где-то полсекунды.
Отсюда вывод — использовать UNION без ALL только если действительно необходимо убрать повторяющиеся значения, и этого никак не сделать с помощью ограничений вроде NOT IN или NOT EXISTS.
Все описанное справедливо для Oracle, но, думаю, для многих других БД тоже, хотя не проверял. Надеюсь, этот материал кому-нибудь поможет.