Групповой оператор вхождения
Оператор вхождения имеет вид:<Выражение> В (<Список значений>)
Оператор вхождения является одним из операторов сравнения, определенных в языке запросов 1С:Предприятия, и принимает значение "Истина", если значение его левого операнда присутствует в списке значений из правого операнда. Список значений может задаваться в виде:
- списка выражений;
- параметра, значением которого является коллекция значений;
- вложенного запроса.
Если список значений задан вложенным запросом, то возможно использование обобщенной формы оператора вхождения - группового оператора вхождения
<Выражение 1>, ..., <Выражение N>) В (<Вложенный запрос>)Вложенный запрос должен определять выборку из N колонок.
Групповой оператор вхождения принимает значение "Истина", если запись (<Выражение 1>, ..., <Выражение N>) совпадает с одной из записей выборки, определяемой вложенным запросом. Например, пусть левым операндом является список
1, 2, 3)Если результатом вложенного запроса является выборка:1, 1, 1
2, 2, 2
2, 2, 3
то результатом группового оператора вхождения будет "Ложь", а если результатом вложенного запроса является выборка:1, 1, 1
1, 2, 3
2, 2, 3
то результатом группового оператора вхождения будет "Истина".
Наряду с оператором вхождения ("В") в языке запросов предусмотрен оператор не вхождения ("НЕ В"), результатом которого является отрицание оператора "В" с такими же операндами. Все, сказанное про особенности выполнения оператора "В", относится и к оператору "НЕ В".
Реализация группового оператора вхождения
В отличие от простого оператора вхождения, аналог которого имеется в большинстве СУБД (оператор IN языка SQL), групповой оператор вхождения не имеет точных аналогов в SQL. Поэтому при использовании группового оператора вхождения важно учитывать механизм его перевода в SQL. Оператор вида:
(<Выражение 1>, ..., <Выражение N>) В (
ВЫБРАТЬ <Колонка 1>, ..., <Колонка N>
ИЗ <Источники>
ГДЕ <Условие>
)
при выполнении его на СУБД будет иметь вид:
EXISTS(
SELECT 1
FROM <Источники>
WHERE (<Условие>) AND <Выражение 1> = <Колонка 1> AND ... AND <Выражение N> = <Колонка N>
)
Если вложенный запрос содержит агрегатные функции и/или раздел СГРУППИРОВАТЬ ПО, то групповой оператор вхождения вида:
(<Выражение 1>, ..., <Выражение N>) В (
ВЫБРАТЬ <Колонка 1>, ..., <Колонка N>
ИЗ <Источники>
ГДЕ <Условие 1>
СГРУППИРОВАТЬ ПО <Список группировки>
ИМЕЮЩИЕ <Условие 2>
)
на языке SQL будет записан так:
EXISTS(
SELECT 1
FROM <Источники>
WHERE <Условие 1>
GROUP BY <Список группировки>
HAVING (<Условие 2>) AND <Выражение 1> = <Колонка 1> AND ... AND <Выражение N> = <Колонка N>
)
Замечания о скорости выполнения
Скорость выполнения запроса, содержащего групповой оператор вхождения, зависит от возможностей оптимизатора планов запросов используемой СУБД по эффективной реализации оператора EXISTS с вложенным запросом. Поэтому точно предсказать, будет ли данный запрос выполняться эффективно, в общем случае затруднительно. Однако, можно сформулировать рекомендации, следование которым поможет строить эффективно выполняющиеся запросы.
Короткие таблицы
Использование в запросе группового оператора вхождения не приведет к снижению эффективности, если вложенный запрос будет строить выборку из таблиц с небольшим количеством записей, которое не может неограниченно расти в процессе эксплуатации системы и роста объема накопленной базы данных.
Индексы
Необходимо учитывать, что запрос, вложенный в оператор EXISTS будет выполнен многократно при проверке условия отбора для каждой записи. Поэтому, если таблицы, используемые во вложенном запросе, могут содержать большое количество записей, то поиск в них необходимо ускорить посредством определения подходящих индексов:
- используйте в качестве значений колонок <Колонка 1>, ..., <Колонка N> только наименования полей таблиц (не выражения);
- выберите среди полей, используемых в качестве значений колонок <Колонка 1>, ..., <Колонка N> такое, значения которого реже всего повторяются (наиболее селективное поле), и включите индексирование по этому полю;
- не используйте агрегатные функции и группировки в запросе, вложенном в групповой оператор вхождения;
- включайте наиболее селективные поля в список группового оператора вхождения. Например, при проверке остатков на складах по позициям номенклатуры из табличной части документа "РасходныйОрдерНаТовары", на который указывает параметр "ДокументСсылка", следующий запрос будет выполняться медленно из-за того, что в таблице Документ.РасходныйОрдерНаТовары.Товары по группе полей (Номенклатура, ХарактеристикаНоменклатуры, Качество) невозможно построить индекс.
ВЫБРАТЬ
Док.Номенклатура.Представление КАК НоменклатураПредставление,
Док.Номенклатура.Представление КАК НоменклатураПредставление,
Док.Номенклатура.ЕдиницаХраненияОстатков.Представление КАК ЕдиницаХраненияОстатковПредставление,
Док.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры КАК СерияНоменклатуры,
Док.Качество КАК Качество,
СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент
/ Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3))) КАК ДокументКоличество,
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) КАК ОстатокКоличество
ИЗ
Документ.РасходныйОрдерНаТовары.Товары КАК Док
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрНакопления.ТоварыКПередачеСоСкладов.Остатки(,
Склад = &Склад И (Номенклатура, ХарактеристикаНоменклатуры, Качество) В (
ВЫБРАТЬ
Док.Номенклатура, Док.ХарактеристикаНоменклатуры, Док.Качество
ИЗ
Документ.РасходныйОрдерНаТовары.Товары КАК Док
ГДЕ
Док.Ссылка = &ДокументСсылка
И НЕ Док.Номенклатура.Комплект
) И &ДокументПередачи = ДокументПередачи) КАК Остатки
ПО
Док.Номенклатура = Остатки.Номенклатура
И Док.ХарактеристикаНоменклатуры = Остатки.ХарактеристикаНоменклатуры
И Док.Качество = Остатки.Качество
И ((НЕ Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = Док.СерияНоменклатуры) ИЛИ
(Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = &ПустаяСерия))
ГДЕ
Док.Ссылка = &ДокументСсылка
СГРУППИРОВАТЬ ПО
Док.Номенклатура,
Док.ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры,
Док.Качество,
&ДокументПередачи
ИМЕЮЩИЕ
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) < СУММА(ВЫРАЗИТЬ(Док.Количество *
Док.Коэффициент
/ Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3)))
Следующий запрос отличается тем, что в групповой оператор вхождения добавлено поле "Склад", по которому в таблице "Документ.РасходныйОрдерНаТовары.Товары" имеется индекс. В результате время выполнения запроса существенно уменьшилось.
ВЫБРАТЬ
Док.Номенклатура.Представление КАК НоменклатураПредставление,
Док.Номенклатура.Представление КАК НоменклатураПредставление,
Док.Номенклатура.ЕдиницаХраненияОстатков.Представление КАК ЕдиницаХраненияОстатковПредставление,
Док.ХарактеристикаНоменклатуры КАК ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры КАК СерияНоменклатуры,
Док.Качество КАК Качество,
СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент
/ Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3))) КАК ДокументКоличество,
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) КАК ОстатокКоличество
ИЗ
Документ.РасходныйОрдерНаТовары.Товары КАК Док
ЛЕВОЕ СОЕДИНЕНИЕ
РегистрНакопления.ТоварыКПередачеСоСкладов.Остатки(,
(Склад, Номенклатура, Качество, ХарактеристикаНоменклатуры) В (
ВЫБРАТЬ
Док.Ссылка.Склад, Док.Номенклатура, Док.Качество, Док.ХарактеристикаНоменклатуры
ИЗ
Документ.РасходныйОрдерНаТовары.Товары КАК Док
ГДЕ
Док.Ссылка = &ДокументСсылка
И НЕ Док.Номенклатура.Комплект
) И &ДокументПередачи = ДокументПередачи) КАК Остатки
ПО
Док.Ссылка.Склад = Остатки.Склад
И Док.Номенклатура = Остатки.Номенклатура
И Док.Качество = Остатки.Качество
И Док.ХарактеристикаНоменклатуры = Остатки.ХарактеристикаНоменклатуры
И ((НЕ Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = Док.СерияНоменклатуры) ИЛИ
(Док.СерияУказываетсяПриОтпускеСоСклада И Остатки.СерияНоменклатуры = &ПустаяСерия))
ГДЕ
Док.Ссылка = &ДокументСсылка
СГРУППИРОВАТЬ ПО
Док.Номенклатура,
Док.ХарактеристикаНоменклатуры,
Док.СерияНоменклатуры,
Док.Качество,
&ДокументПередачи
ИМЕЮЩИЕ
ЕСТЬNULL(МАКСИМУМ(Остатки.КоличествоОстаток), 0) < СУММА(ВЫРАЗИТЬ(Док.Количество * Док.Коэффициент
/ Док.Номенклатура.ЕдиницаХраненияОстатков.Коэффициент КАК ЧИСЛО(15,3)))
Временные таблицы
Если запрос, вложенный в групповой оператор вхождения, сложный, содержит агрегатные функции, использует таблицы с большим количеством записей, то вероятно, что исполнение группового оператора вхождения будет крайне не эффективным. В этом случае целесообразно:
- исполнить вложенный запрос отдельно и сложить его результат во временную таблицу;
- определить во временной таблице индекс либо по всем полям, либо по наиболее селективному подмножеству полей;
- использовать полученную временную таблицу в запросе, вложенном в групповой оператор вхождения.