MitjaT пишет:
> MC>Чем гадать — лучше бы поглядели план запроса на интересующей Вас базе > и запостили сюда.
План запроса тут как раз смотреть бессмысленно. частный случай ничего
не доказывает.
Имело бы смысл рассматривать только все возможные планы на всех
возможных раскладах данных.
Posted via RSDN NNTP Server 2.1 beta
Re[2]: Вопрос на собеседовании (как надо было ответить?)
Здравствуйте, MasterZiv, Вы писали:
MZ>А переписать надо было бы так:
MZ> select T1.F1, case when T2_1.F1 then T2_1.F1 else T2_2.F1 end as T2F1 MZ> from Table1 T1 MZ> left join Table2 T2_1 on T1.F2=T2.F2 MZ> left join Table2 T2_2 on T1.F3=T2.F3
Запросы не эквивалентны.
Пример данных:
table1: ((1, 1, 1), (1, 1, 1))
table2: ((1, 1, 1), (1, 1, 1))
Выводят разное количество строк.
Re[3]: Вопрос на собеседовании (как надо было ответить?)
Овощ пишет:
> MZ> select T1.F1, case when T2_1.F1 then T2_1.F1 else T2_2.F1 end as T2F1 > MZ> from Table1 T1 > MZ> left join Table2 T2_1 on T1.F2=T2.F2 > MZ> left join Table2 T2_2 on T1.F3=T2.F3
тогда
> MZ> from Table1 T1 > MZ> left join Table2 T2_1 on T1.F2=T2.F2 > MZ> left join Table2 T2_2 on T1.F3=T2.F3 and not T1.F2=T2.F2
Posted via RSDN NNTP Server 2.1 beta
Re[2]: Вопрос на собеседовании (как надо было ответить?)
С увлечением наблюдал за дискуссией. Рад за MasterZiv и его правильный
ответ (ниже по ветке).
> плох тем, что JOIN по OR может быть плохо воспринят > оптимизатором и не будет использован индекс при выполнении JOIN-а. > Но это — только один из возможных вариантов > развития событий. Думаю, многие оптимизаторы поймут эту > ситуацию правильно и сами сделают правильную переформулировку запроса.
Небольшая ремарка:
Не поймут и не переформулируют. JOIN по OR не оставляет оптимизатору
шансов — будет использован только NESTED LOOP JOIN, а ему индексы пофигу.
Posted via RSDN NNTP Server 2.1 beta
Всё, что нас не убивает, ещё горько об этом пожалеет.
Re[4]: Вопрос на собеседовании (как надо было ответить?)
MasterZiv пишет: > тогда > >> MZ> from Table1 T1 >> MZ> left join Table2 T2_1 on T1.F2=T2.F2 >> MZ> left join Table2 T2_2 on T1.F3=T2.F3 and not T1.F2=T2.F2
Давай не будем гадать. Ответ правильный, если не считать некоторых
описок. Я просто напишу правильно:
select
T1.F1, IsNull(T2.F1, T3.F1) as F1
from Table1 T1
left join Table2 T2 on T1.F2 = T2.F2
left join Table2 T3 on T1.F3 = T3.F3 and not T1.F2 = T3.F2
order by T1.F1
Posted via RSDN NNTP Server 2.1 beta
Всё, что нас не убивает, ещё горько об этом пожалеет.
Re[5]: Вопрос на собеседовании (как надо было ответить?)
Здравствуйте, Ромашка, Вы писали:
Р>MasterZiv пишет: >> тогда >> >>> MZ> from Table1 T1 >>> MZ> left join Table2 T2_1 on T1.F2=T2.F2 >>> MZ> left join Table2 T2_2 on T1.F3=T2.F3 and not T1.F2=T2.F2
Р>Давай не будем гадать. Ответ правильный, если не считать некоторых Р>описок. Я просто напишу правильно:
Р>
Р>select
Р> T1.F1, IsNull(T2.F1, T3.F1) as F1
Р>from Table1 T1
Р> left join Table2 T2 on T1.F2 = T2.F2
Р> left join Table2 T3 on T1.F3 = T3.F3 and not T1.F2 = T3.F2
Р>order by T1.F1
Р>
Что-то у меня опять не сходится.
Пример данных:
table1: (0, 1, 2)
table2: ((1, 1, 1), (2, 2, 2))
Должно вернуть 2 строки.
Где-то опечатка?
Re[6]: Вопрос на собеседовании (как надо было ответить?)
Р>>select
Р>> T1.F1, IsNull(T2.F1, T3.F1) as F1
Р>>from Table1 T1
Р>> left join Table2 T2 on T1.F2 = T2.F2
Р>> left join Table2 T3 on T1.F3 = T3.F3 and not T1.F2 = T3.F2
Р>>order by T1.F1
Р>>
О>Что-то у меня опять не сходится.
О>Пример данных: О>table1: (0, 1, 2) О>table2: ((1, 1, 1), (2, 2, 2)) О>Должно вернуть 2 строки.
О>Где-то опечатка?
ну так какой тогда правилный ответ?
Re: Вопрос на собеседовании (как надо было ответить?)
Здравствуйте, MitjaT, Вы писали:
MT>Здравствуйте!
MT>Как вам такой письменный вопрос на собеседовании? MT>Как правильно на него ответить?
MT>
MT>Чем плох такой запрос? Как можно его оптимизировать?
MT>select T1.F1, T2.F1
MT>from Table1 T1
MT>left join Table2 T2 on T1.F2=T2.F2 or T1.F3=T2.F3
Зависит от БД, для PostgreSQL наиболее быстрый из всех 3-х предложенных.
Для Oracle чуть чуть медленнее третьего (см листинг pgsql, для оракла не привожу).
drop table if exists table1;
drop table if exists table2;
CREATE TABLE table1(
F1 serial primary key,
F2 integer not null,
F3 integer not null
);
CREATE TABLE table2(
F1 serial primary key,
F2 integer not null,
F3 integer not null
);
create index ix_table1_f2 on table1(f2);
create index ix_table1_f3 on table1(f3);
create index ix_table2_f2 on table2(f2);
create index ix_table2_f3 on table2(f3);
-- Создаем записи которые не будут попадать под условие запросаinsert into table2(f2, f3) select f as f2, f+100000 as f3 from generate_series(0, 90000-1, 1) f;
insert into table1(f2, f3) select f2+200000, f3+300000 from table2 order by f1 limit 2000;
-- Создаем записи которые будут попадать под условие запроса F2 = F2insert into table1(f2, f3) select f as f2, f+100000 as f3 from generate_series(90000, 92000-1, 1) f;
insert into table2(f2, f3) select f as f2, f+200000 as f3 from generate_series(90000, 92000-1, 1) f;
-- Создаем записи которые будут попадать под условие запроса F3 = F3insert into table1(f2, f3) select f+100000 as f2, f as f3 from generate_series(92000, 94000-1, 1) f;
insert into table2(f2, f3) select f+200000 as f2, f as f3 from generate_series(92000, 94000-1, 1) f;
-- Создаем записи которые будут попадать под условие запроса F2 = F2 = F3 = F3insert into table1(f2, f3) select f as f2, f as f3 from generate_series(94000, 96000-1, 1) f;
insert into table2(f2, f3) select f as f2, f as f3 from generate_series(94000, 96000-1, 1) f;
analyse;
explain analyse
select T1.F1, T2.F1
from Table1 T1
left join Table2 T2
on T1.F2 = T2.F2
or T1.F3 = T2.F3 ;
explain analyse
select T1F1, max(T2F1)
from
(select T1.F1 as T1F1, T2.F1 as T2F1
from Table1 T1
inner join Table2 T2
on T1.F2 = T2.F2
union all
select T1.F1, T2.F1
from Table1 T1
inner join Table2 T2
on T1.F3 = T2.F3
union all
select T1.F1, null
from Table1 T1
) as T
group by T1F1 ;
cluster table1 using table1_pkey;
cluster table2 using table2_pkey;
explain analyse
select T1.F1, coalesce(T2_1.F1, T2_2.F1) as T2F1
from Table1 T1
left join Table2 T2_1 on T1.F2=T2_1.F2
left join Table2 T2_2 on (T1.F3=T2_2.F3 and not T1.F2=T2_2.F2);
select count(*) from table1;
select count(*) from table2;
Здравствуйте, Ромашка, Вы писали:
Р>С увлечением наблюдал за дискуссией. Рад за MasterZiv и его правильный Р>ответ (ниже по ветке).
увы, как оказалось — неправильный (((
Р>Небольшая ремарка: Р>Не поймут и не переформулируют. JOIN по OR не оставляет оптимизатору Р>шансов — будет использован только NESTED LOOP JOIN, а ему индексы пофигу.
да вобщем то несложно разбить конкретно это условие объединения на 4 _независимых_ подусловия, каждое из которых можно выполнить по отдельности (а корректность таких преобразований можно проверить математически):
select
T1.F1, T2.F1
from
Table1 T1 inner join Table2 T2
on T1.F2 = T2.F2 and T1.F3 != T2.F3
union all
select
T1.F1, T2.F1
from
Table1 T1 inner join Table2 T2
on T1.F2 != T2.F2 and T1.F3 = T2.F3
union all
select
T1.F1, T2.F1
from
Table1 T1 inner join Table2 T2
on T1.F2 = T2.F2 and T1.F3 = T2.F3
union all
select
T1.F1, null
from
Table1 T1
wherenot exists (select * from Table2 T2 where T1.F2 = T2.F2 or T1.F3 = T2.F3)
здесь тоже возможны сканы таблиц, но по крайней мере без NL-ов. Время выполнения запроса под mssql ~1 сек. Для таблиц созданы кластерные индексы по F1 и простые не составные индексы по F2 и F3. План:
имхо здесь основная сложность в оценке время выполнения запроса и следовательно высок риск промахнуться с выбором оптимального варианта.
Ромашка пишет:
> Небольшая ремарка: > Не поймут и не переформулируют. JOIN по OR не оставляет оптимизатору > шансов — будет использован только NESTED LOOP JOIN, а ему индексы пофигу.
1) вы хотите сказать, что индексы неприменимы при NESTED LOOP JOIN в принципе ?
Нет, это не так.
2) почему вы думаете, что все на свете оптимизаторы не произведут
соотвтствующее эквивалентное преобразование автоматом ?
Это можно утверждать только доказав, что такого преобразования
не существует в принципе.
Но в данном случае есть просто тупо алгоритм выполнения запроса,
который невыразим в SQL и будет использовать индексы.
Это NESTED LOOP JOIN T1 и двух курсоров по Table2, с
позиционированием по разным условиям и индексам.
T1
NESTED LOOP JOIN
(Table2 T2_1 on T1.F2=T2.F2
а если в T2_1 записи не нашлось, то
Table2 T2_2 on T1.F3=T2.F3 )
Posted via RSDN NNTP Server 2.1 beta
Re[7]: Вопрос на собеседовании (как надо было ответить?)
ilya_ny пишет: > ну так какой тогда правилный ответ?
Не претендуя на правильность. Если уж не удается по глупости душевной
уломать сервер мультиплицировать правильно количество строк (пример http://rsdn.ru/forum/message/3294469.aspx
select T1.F1, T2.F1
from Table1 T1
left join (select *
from Table2
where F2 in (select F2 from Table1)
or F3 in (select F3 from Table1)
)T2 on T1.F2=T2.F2 or T1.F3=T2.F3
Posted via RSDN NNTP Server 2.1 beta
Всё, что нас не убивает, ещё горько об этом пожалеет.
Re[4]: Вопрос на собеседовании (как надо было ответить?)
MasterZiv пишет: > 1) вы хотите сказать, что индексы неприменимы при NESTED LOOP JOIN в > принципе ? > Нет, это не так.
Да, это не так. MasterZiv, вы прекрасно понимаете, что я хочу сказать.
Не нужно играть со словами.
> 2) почему вы думаете, что все на свете оптимизаторы не произведут > соотвтствующее эквивалентное преобразование автоматом ? > Это можно утверждать только доказав, что такого преобразования > не существует в принципе.
Или что они неэффективны в принципе. В принципе, я с вами согласен.
> Но в данном случае есть просто тупо алгоритм выполнения запроса, > который невыразим в SQL и будет использовать индексы. > Это NESTED LOOP JOIN T1 и двух курсоров по Table2, с > позиционированием по разным условиям и индексам.
seregaa пишет: > да вобщем то несложно разбить конкретно это условие объединения на 4 > _независимых_ подусловия, каждое из которых можно выполнить по > отдельности (а корректность таких преобразований можно проверить > математически):
Класс, поздравляю. Это, похоже, уже правильный ответ.
Posted via RSDN NNTP Server 2.1 beta
Всё, что нас не убивает, ещё горько об этом пожалеет.
Re[5]: Вопрос на собеседовании (как надо было ответить?)
Ромашка пишет:
> Да, это не так. MasterZiv, вы прекрасно понимаете, что я хочу сказать. > Не нужно играть со словами.
Не, я правда не понял.
>> Но в данном случае есть просто тупо алгоритм выполнения запроса, >> который невыразим в SQL и будет использовать индексы. >> Это NESTED LOOP JOIN T1 и двух курсоров по Table2, с >> позиционированием по разным условиям и индексам. > > Ужас, вы не находите?
Здравствуйте, MasterZiv, Вы писали:
MZ>Но в данном случае есть просто тупо алгоритм выполнения запроса, MZ>который невыразим в SQL и будет использовать индексы.
MZ>Это NESTED LOOP JOIN T1 и двух курсоров по Table2, с MZ>позиционированием по разным условиям и индексам.
MZ>T1 MZ>NESTED LOOP JOIN MZ> (Table2 T2_1 on T1.F2=T2.F2 MZ> а если в T2_1 записи не нашлось, то MZ> Table2 T2_2 on T1.F3=T2.F3 )
Можно уговорить mssql использовать почти такой алгоритм с помощью табличной функции:
create function dbo.getByF2F3 (@f2 int, @f3 int)
returns @results table
(
F1 int
)
as
begin
insert @results select F1 from Table2 where F2 = @f2 or F3 = @f3
return
end;
GO
select
T1.F1, T2.F1
from
Table1 T1
outer apply dbo.getByF2F3(T1.F2, T1.F3) T2
я с вами согласен.
