| Kop123 |
| Отправлено: 12.01.2005, 14:30 |
|
Не зарегистрирован
|
Как выбрать из таблицы все значения кроме тех, что есть в другой таблице? |
|
| Bond |
| Отправлено: 12.01.2005, 15:16 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 142
|
Использовал IBExpert
table1 (field1 smallint); {1,2,3,4,5,6,7}
table2 (field1 smallint); {3,6}
поля field1 ключевые
Запрос:
| CODE | select "table1"."field1" from "table1"
where "table1"."field1" not in
(select "table2"."field1" from "table2"); |
Результат:
|
|
| xim |
| Отправлено: 12.01.2005, 15:42 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 143
|
В Transact SQL:
| CODE |
select * from table1 t1 where
not exists(select <common_field> from table2 t2 where
t2.<common_field>=t1.<common_field>)
|
|
|
| AVC |
| Отправлено: 12.01.2005, 16:02 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Хотя вариант Bond синтаксически более красивый, сталкивался с ситуациями, когда он выполнялся неоправдано долго и даже приводил к краху.
Поэтому я бы написал так:
Select t1.* --, t2.*
From t1 left outer join t2 on (t2.ID = t1.ID)
Where t2.ID is Null
PS. Для более продуктивного совета желательно уточнять СУБД.
Отредактировано AVC — 12/01/2005, 16:06 |
|
| Bond |
| Отправлено: 12.01.2005, 17:10 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 142
|
 )
Согласен с AVC — именно для этого и есть данный оператор
| CODE | select "table1"."field1" from "table1"
left outer join "table2" on ("table2"."field1" = "table1"."field1")
where "table2"."field1" is null |
Производительность запроса значительно возросла.
Я пользовался только INNER JOIN — спасибо )
|
|
| xim |
| Отправлено: 13.01.2005, 11:56 |
|
Станционный диспетчер
Группа: Участник
Сообщений: 143
|
Под SQL Server-ом инструкции:
| CODE |
select * from table1 t1
where t1.<common_field> not in
(select t2.<common_field> from table2 t2)
&&
(
select * from table1 t1 where
not exists(select <common_field> from table2 t2 where
t2.<common_field>=t1.<common_field>)
||
select * from table1 t1 where
not exists(select null from table2 t2 where
t2.<common_field>=t1.<common_field>)
)
|
имеют одинаковую стоимость запроса (судя по плану) и выполняются быстрее чем инструкция, приведенная AVC, т.к. в тратится дополнительное время на фильтрацию записей не найденных во 2 табл.
2AVC:
Что касается "краховых" ситуаций — хотелось бы посмотреть на кусок скрипта и настройки контекста (сессии)
|
|
| AVC |
| Отправлено: 13.01.2005, 12:41 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
2xim
Я же не утверждаю, что это панацея, а просто уточнил что сталкивался с такими ситуациями в реальной жизни.
Многое зависит от используемого сервера и его настроек.
Пример
Oracle 8i, статистики не собраны
| CODE |
-- Поиск первичных ключей, не используемых как вторичные
-- Найти адреса, на которые нет ссылок в PersonalAccount
-- Работет ЧЕРЕЗВЫЧАЙНО медленно и забивает pool (у клиента) вызывая крах
Select Count(*) From HHC.Address ADR
Where ADR.AddressID not in (Select PA.AddressID From HHC.PersonalAccount PA);
13.01.2005 11:10
Дождаться ответа терпения не хватило — запрос снят через 5 мин
План оптимизации — стоимость 14
SELECT STATEMENT Optimizer Mode=CHOOSE
SORT AGGREGATE
FILTER
INDEX FAST FULL SCAN SYS_C001012
TABLE ACCESS FULL PERSONALACCOUNT
|
| CODE |
-- Около 1 сек при объёмах: PA 41695, ADR 41768
Select Count(*)
From (
Select ADR.AddressID, PA.PersonalAccountID
From HHC.Address ADR, HHC.PersonalAccount PA
Where PA.AddressID (+) = ADR.AddressID
) tmp
Where tmp.PersonalAccountID is Null
13.01.2005 11:30
Время выполнения 359 mc
План оптимизации — стоимость 320
SELECT STATEMENT Optimizer Mode=CHOOSE
SORT AGGREGATE
FILTER
HASH JOIN OUTER
INDEX FAST FULL SCAN SYS_C001012
TABLE ACCESS FULL PERSONALACCOUNT
|
Комментарии излишни?
Сбор статистик может исправить ситуацию.
Все зависит от конкретной ситуации. |
|
| AVC |
| Отправлено: 13.01.2005, 13:47 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
2xim
Дождался таки выполнения первого из запросов 34 мин 25 сек (сравните с 320 мс )
Фрагменты файла трассировки
Запрос с not in
| CODE |
call query rows
Fetch 38263124 1
|
Запрос с Null
| CODE |
call query rows
Fetch 1432 1
|
Отредактировано AVC — 13/01/2005, 13:51 |
|
| olegenty |
| Отправлено: 13.01.2005, 14:27 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
| CODE |
select
*
from
t1
where
not exists (select t2.id from t2 where t2.id = t1.id)
|
т.е. вариант xim — самое то.
недавно на SQL.RU это обсуждалось. вроде как ничего более эффективного не придумали...
по внутренней выборке всегда выбирается не более 1-й записи, что рульнее как чем IN — все записи, так и чем LEFT OUTER JOIN — тоже все.
при наличие индексов по полю, для данного примера id. иначе будут варианты и всё будет зависеть от размеров таблиц.
|
|
| AVC |
| Отправлено: 13.01.2005, 15:00 |
|
Ветеран
Группа: Модератор
Сообщений: 1583
|
Oracle 8i
Фрагмент файла трассировки
| CODE |
-- 1 s
Select Count(*) From HHC.Address adr
Where not exists (Select pa.PersonalAccountID From HHC.PersonalAccount pa Where pa.AddressID = adr.AddressID)
call cpu query rows
Fetch 1.12 115058 1
|
Трассировка запросов показывает, что при условии Oracle8i с теми установками которые стоят у меня вариант с Null самый выгодный
| CODE |
тип cpu зачитано блоков
Null 0.28 1432
Exists 1.12 115058
not in 2075.67 38263124
|
Отредактировано AVC — 13/01/2005, 15:27 |
|
| olegenty |
| Отправлено: 13.01.2005, 15:41 |
|
Ветеран
Группа: Модератор
Сообщений: 2412
|
проверил. для MSSQL это неверно. хотя, возможно тоже есть варианты с опциями...
|
|
