Однократное выполнение VIEW при многократном ее JOIN-е в запросе

← →
evvcom © (2005-11-28 00:15) [0]

Всем привет!
Я сегодня с вопросом.
1. Решил оптимизировать одну свою процедуру. Выделил из запроса один сложный древовидный select с несколькими джойнами во вьюху, к которой потом обращаюсь из запроса несколько раз. Думал этот select во вьюхе отработает один раз, и потом во внешнем запросе джойниться будет результат работы этой вьюхи. Да не тут-то было! Смотрю план, а там, как было в запросе 3 обращения к этой вьюхе, так и джойнятся 3 точных копии плана этой вьюхи.

2. Когда-то раньше на одном из серверов той же версии смотрел планы запроса типа
with subquery as (select ...) select ... from subquery sq1 ... join subquery sq2 on ...
и в плане наблюдал скан таблицы SYS<XXXXXXXX> и join ее с другим сканом этой же SYS<XXXXXXXX> таблицы. Т.е. Oracle выполнял подзапрос subquery, результат помещал во временную им же созданную таблицу и использовал в дальнейшем ее.

3. Я попробовал переписать свой запрос аналогичным образом:
with v1 as (select * from MyView) select ... from v1 o ... join v1 r on ...
Сам запрос нормально отрабатывает и возвращает нужные данные, а вот при команде показать план, сервер ругается:
ORA-00604: ошибка на рекурсивном SQL-уровне 1
ORA-00907: отсутствует правая скобка

Завтра на работе попробую запрос на других серваках, возможно у них настройки какие-то другие, и где-то может и план построится и построится по-другому. Может быть. Но интересно, что, какие параметры влияют на построение плана как в п.2? Может кто знает?
Да... Покопался еще в хинтах, показалось, что NO_MERGE может подойти. Но ничего не изменилось.

Версия: Oracle 9.2.0.6

← →
evvcom © (2005-11-28 09:42) [1]

Попробовал еще на 2-х аналогичных серверах. Результат тот же с точностью до миллилитра!

← →
Sergey13 © (2005-11-28 10:01) [2]

Я может не очень въехал в вопрос, но я обычно стараюсь "деревяшку" сделать подзапросом и джоинить уже результат этого подзапроса.
Типа
Select * from (select * from t1 start ... connect by...) s, t2,t3 where s.id=t2.id....

← →
Desdechado © (2005-11-28 10:32) [3]

вьюхи очень плохо оптимизируются, Оракл почему-тоне умеет нормально с ними планы строить :(
если в ХП хочешь что-то оптимизировать, сделай вложенные FOR x IN( select...)

Короче рыл, рыл и нарыл. Все то же самое обнаружили уже многие, но в патчах этого похоже еще почему-то не поправили. Т.е. если употребляется with sq, в нем любой outer join (c inner join баг не проявляется) и во внешнем селекте джойнятся 2 и более этих sq, то при explain plan видим ошибку 604 и 907.

> Sergey13 © (28.11.05 10:01) [2]

У тебя деревяшка используется 1 раз, у меня 2 и более.

> если в ХП хочешь что-то оптимизировать, сделай вложенные
> FOR x IN( select...)

какой FOR? Мне на клиента НД надо вернуть. Т.е. должен быть результирующий select.

Ну и почему вьюху решил? Потому что этот запрос используется из нескольких мест. Я не заметил, что вьюха как-то плохо оптимизируется. Когда делаю select * from MyView ... join MyTableN on ... видно, что оптимизатор джойнит эту MyTableN где-то в середине джойнов, присутствующих во вьюхе, т.е. query transformation нормально отрабатывает. Ну а мне потребовалось, чтобы select во вьюхе отработал один раз, а потом этот результат несколько раз джойнился. Т.е. в плане присутствовала строка TEMP TABLE TRANSFORMATION и RECURSIVE EXECUTION. О, как это называется.
Вывод. Использую значит with v1 as (select * from MyView) select * from v1 o ... join v1 r on ... Только во вьюхе на время отладки плана меняю все outer join на inner, после отладки возвращаю outer. Сам-то запрос нормально отрабатывает!

Однократное выполнение VIEW при многократном ее JOIN-е в запросе Найти похожие ветки