Доброго времени суток!
Немножко погонял 2.1.0.17735 RC1 и обнаружил
следующие интересные (для меня) вещи.
Возможно, они заинтересуют кого-нибудь еще.
Насколько я понимаю, все это верно и для 2.0.x
Итак, начнем
CREATE TABLE TEST_TABLE3 (
ID1 INTEGER,
ID2 INTEGER
);
COMMIT;
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (3, 7);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (9, 8);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (6, 3);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (7, 7);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (4, 9);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (3, 5);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (9, 2);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (10, 8);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (7, 2);
INSERT INTO TEST_TABLE3 (ID1, ID2) VALUES (4, 5);
COMMIT;
CREATE INDEX TEST_TABLE3_ID1 ON TEST_TABLE3 (ID1);
CREATE INDEX TEST_TABLE3_ID2 ON TEST_TABLE3 (ID2);
COMMIT;
Пример 1.
select Count(*) from test_table3 t1 inner join test_table3 t2
on t1.ID1 = t2.ID2
where t2.id2 = 3
PLAN JOIN (T1 INDEX (TEST_TABLE3_ID1), T2 INDEX (TEST_TABLE3_ID2))
Честно скажу, не сразу сообразил, что оптимизатор превращает
условие t1.ID1 = t2.ID2 and t2.id2 = 3 в t1.ID1 = t2.ID2 and t1.id1 = 3
В данном случае, это немного хуже варианта с
select Count(*) from test_table3 t2 inner join test_table3 t1
on t1.ID1 = t2.ID2
where t2.id2 = 3
с планом
PLAN JOIN (T2 INDEX (TEST_TABLE3_ID2), T1 INDEX (TEST_TABLE3_ID1))
- 4 и 3 индексных чтения соответственно.
Впрочем, в реальных условиях для разных таблиц, нужно сильно
постараться, чтобы потерять в производительности - например, если
селективности индексов совпадают, но по ID1 идет перекос в сторону 3,
а в ID2 - в сторону 5.
Это, естественно, не баг, а особенность реализации.
Пример 2.
Оптимизатор хорошо обрабатывает условия с равенствами и неравенствами
в where
select Count(*) from test_table3 t1 inner join test_table3 t2
on t1.ID1 = t2.ID2
where (t2.id1 = t1.id2)
план
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2, TEST_TABLE3_ID1))
select Count(*) from test_table3 t1 inner join test_table3 t2
on t1.ID1 = t2.ID2
where (t2.id1 >= t1.id2)
план
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2, TEST_TABLE3_ID1))
Но стоит немного усложнить выражение:
select Count(*) from test_table3 t1 inner join test_table3 t2
on t1.ID1 = t2.ID2
where (t2.id1 = t1.id2-1)
или
select Count(*) from test_table3 t1 inner join test_table3 t2
on t1.ID1 = t2.ID2
where (t2.id1 > t1.id2-1)
как получаем
PLAN JOIN (T2 NATURAL, T1 INDEX (TEST_TABLE3_ID1))
хотя в этих случаях, на первый взгляд, ничто не мешает
задействовать индекс по t2.id1
Пример 3.
Итак, условия в where при inner-связке оптимизатор более-менее обрабатывает.
Как насчет left?
Поскольку таблица всего одна, далее будем использовать сокращение
U / I - U безиндексных и I индексных чтения из таблицы TEST_TABLE3
Оказывается, условие в where не всегда учитывается должным образом
Например,
select * from test_table3 t1 left join test_table3 t2
on t1.ID1 = t2.ID2
where t2.id2 = 0
с планом
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2))
требует всего 10 / 0 чтений - т.е. условие
в where было принято во внимание, и не потребовалось 10 раз читать по
индексу T2.
Но если взять
select * from test_table3 t1 left join test_table3 t2
on t1.ID1 = t2.ID2
where t2.id2 = 3
с планом
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2)),
то получим 10 /10 чтений - т.е. оптимизатор условие в Where
проигнорировал.
Чтобы этого не случилось, нужно включить t2.id2 = 3 в условие связки
select * from test_table3 t1 left join test_table3 t2
on t1.ID1 = t2.ID2 and t2.id2 = 3
where t2.id2 is not null
Получаем 10 / 2 чтения.
Что ж, это еще одна причина не писать кривых запросов (а запрос, и в
самом деле, кривой), используя left там, где требуется inner.
Пример 4
Так же хорошо оптимизируется нововведение is not distinct from,
как и обычный предикат равенства?
И
select * from test_table3 t1 left join test_table3 t2
on t1.ID1 = t2.ID2,
и
select * from test_table3 t1 inner join test_table3 t2
on t1.ID1 is not distinct from t2.ID2
выполняются с одинаковым планом:
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2))
требуя 10 / 8 чтений.
Но для
select * from test_table3 t1 left join test_table3 t2
on t1.ID1 = t2.ID2 and t2.id2 = 3
получаем
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2, TEST_TABLE3_ID1))
и 10/2 чтений
а для
select * from test_table3 t1 left join test_table3 t2
on t1.ID1 is not distinct from t2.ID2 and t2.id2 = 3
PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2))
и 10/10 чтений.
NULL-ов в тестовой таблице нет, поэтому по идее разницы быть не должно.
Скорее всего, внутри сервера t1.ID1 is not distinct from t2.ID2
раскладывается в (t1.ID1 = t2.id2) OR (t1.ID1 is null and t2.ID2 is
null). Попробуем выполнить эту замену в самом запросе
Оказывается,
select * from test_table3 t1 left join test_table3 t2
on ((t1.ID1 = t2.id2) OR (t1.ID1 is null and t2.ID2 is null)) and
(t2.id2 = 3)
при плане PLAN JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE4_ID2,
TEST_TABLE4_ID2))
требует аж 10/16 чтений
Вручную приводим ее к более оптимальному виду
select * from test_table3 t1 left join test_table3 t2
on ((t1.ID1 = t2.id2) AND (t2.id2 = 3)) OR ((t1.ID1 is null and
t2.ID2 is null) and (t2.id2 = 3))
и получаем те же 10/10 чтений.
Поскольку проверка первой части условия связки требует всего 2 индексных
чтения, а проверка второй - 0, то можно выдвинуть предположение о
проблемах с обработкой OR в данном случае. Тем паче, что похожий запрос
select * from test_table3 t1 inner join test_table3 t2
on t1.ID1 is not distinct from t2.ID2 and t2.id1 =3
оптимизируется нормально - 10/2.
Пример 5
Сортировка и join
Для запроса
select first 1 *
from test_table3 t1 left join test_table3 t2
on 1=1
order by t1.id1
получаем план PLAN JOIN (T1 ORDER TEST_TABLE3_ID1, T2 NATURAL)
и 1/1 чтение
Но для эквивалентного ему
select first 1 *
from test_table3 t1 cross join test_table3 t2
order by t1.id1
сортировка по индексу не прокатывает
PLAN SORT (JOIN (T2 NATURAL, T1 NATURAL))
и 110/0 чтений
и смена порядка таблиц в запросе не поможет.
Те же проблемы у inner
select first 1 *
from test_table3 t1 inner join test_table3 t2
on t2.id2 is null
order by t1.id1
PLAN SORT (JOIN (T1 NATURAL, T2 INDEX (TEST_TABLE3_ID2)))
10/0
Здесь, скорее всего, требуется учитывать наличие first 1 при
оптимизации, чтобы избежать полной сортировки. Но вряд ли этот особый
случай будет обрабатываться и в будущем.
К счастью, это можно обойти с помощью derived tables.
Например,
select * from
(select first 1 *
from test_table3 t1
order by t1.id1) dt
inner join
test_table3 t2
on t2.id2 is null
PLAN JOIN (DT T1 ORDER TEST_TABLE3_ID1, T2 INDEX (TEST_TABLE3_ID2))
1/0
Вот, собственно, и все.
Буду рад замечаниям и поправкам.
С уважением, Евгений
