Как наиболее оптимально вычесть одно множество из другого?
Есть две таблицы
T1(ID int) T2(ID int)
В голову приходит только одно решение
SELECT [ID]
FROM T1
WHERE [ID] NOT IN (SELECT [ID] FROM T2)
Можно как-то оптимизировать?
Me_>Как наиболее оптимально вычесть одно множество из другого?
Me_>Есть две таблицы T1(ID int) T2(ID int)
Me_>В голову приходит только одно решение
Me_>Me_>SELECT [ID]
Me_>FROM T1
Me_>WHERE [ID] NOT IN (SELECT [ID] FROM T2)
Me_>
можно еще
select ID from T1
minus
select ID from T2
(если база держит MINUS. Oracle — поддерживает)
правда, не знаю, будет ли это оптимальнее, и насколько, если да
Здравствуйте, fessa, Вы писали:
F>можно еще
F>F>select ID from T1
F>minus
F>select ID from T2
F>
F>(если база держит MINUS. Oracle — поддерживает)
F>правда, не знаю, будет ли это оптимальнее, и насколько, если да
Забыл указать субд — MS SQL Server 2000
Посмотрел в МСДНе, enterprise serves не поддерживают оператор MINUS, жаль.
Здравствуйте, Me_, Вы писали:
Me_>Как наиболее оптимально вычесть одно множество из другого?
Me_>Есть две таблицы T1(ID int) T2(ID int)
Me_>В голову приходит только одно решение
Me_>Me_>SELECT [ID]
Me_>FROM T1
Me_>WHERE [ID] NOT IN (SELECT [ID] FROM T2)
Me_>
Me_>Можно как-то оптимизировать?
еще вариант:
select ID from T1
where not exists
(
select 1 from t2 where T2.ID=T1.ID
)
— в оракле выдает самый лучшей plan из 3-х запросов,
1 full table scan против 2-х в оригинале и в запросе с использованием MINUS
Здравствуйте, fessa, Вы писали:
F>Здравствуйте, Me_, Вы писали:
Me_>>Как наиболее оптимально вычесть одно множество из другого?
F>еще вариант:
F>F>select ID from T1
F>where not exists
F>(
F> select 1 from t2 where T2.ID=T1.ID
F>)
F>
F>- в оракле выдает самый лучшей plan из 3-х запросов,
F>1 full table scan против 2-х в оригинале и в запросе с использованием MINUS
Вообще-то нельзя сказать, что
not in работает хуже, чем
not exists, они оба хорошо работают при разных условиях.
Not in неэффективен при
null значениях, т.к не позволяет серверу выполнять ряд оптимизаций.
not in в прямом смысле слова никак не обрабатывает
null, т.е. он не возвращает ни true, ни false
Оптимизатор стандартно использует для подзапросов NOT IN алгоритм вложенных циклов, если только параметр инициализации ALWAYS_ANTI_JOIN не имеет значения MERGE или HASH, и не выполнен ряд обязательных условий, позволяющих преобразовать подзапрос NOT IN в антисоединение сортировкой слиянием или хешированием. Можно поместить подсказку MERGE_AJ или HASH_AJ в подзапрос NOT IN, чтобы указать, какой алгоритм должен использовать оптимизатор. Т.е. при наличии null значений
Кайт советует использовать метод антисоединения хешированием (hash anti-join)(хинт
/*+ HASH_AJ */)
select ID
from T1
where id is not null
and id not in (select /*+ HASH_AJ */ ID from T2 where T1.ID=T2.ID)
Антисоединение возвращает строки слева от предиката, для которых нет соответствующей строки справа от предиката. То есть, оно возвращает строки, не соответствующие (NOT IN) подзапросу справа.
Если имеются
null значения, тогда лучше использовать
not exists. Но и not exists лучше использовать при наличии индексов.
NOT IN может быть не менее эффективно, чем NOT EXISTS, — и даже на несколько порядков лучше, — если можно использовать "антисоединение" (если подзапрос точно не возвращает значений NULL).
...
Используйте простое правило:
Большой внешний запрос и маленький внутренний = IN.
Маленький внешний запрос и большой внутренний = EXISTS.
