Упрощенно, есть таблица


есть индексы на таблицу:
...on mtable(f1, f10, f20);
...on mtable(f2, f10, f20);
...on mtable(f3, f10, f20);
Делаешь к ней запросы вида:


Каждый из них по отдельности отрабатывает быстро по index range scan соответствующего индекса, не обращаясь к таблице.

Теперь объединяем эти запросы:


План показывает, что по каждому запросу теперь кроме обращения к индексу идет и обращение к таблице по rowid
(при этом время выполнения запроса увеличивается минимум, на порядок)
Собственно вопрос, как дальше жить :)
Можно конечно попытаться разбить этот общий запрос на отдельные мелкие, забивай например временную таблицу или коллекцию, но дальше с этими данными надо тоже работать запросом, т.е. не хотелось бы кастовать в table и тем более писать в темповую таблицу.

Makar4ik, xtender, спасибо, что откликнулись. Оракл 11.2
К сожалению долго разбираться нет времени.
Позже попробую смоделировать (дома тоже не получилось воспроизвести, но делал не совсем точно), но пока стабильной работы запроса нет:
- или подхватывается не тот индекс, в котором есть все поля запроса, а тот который поменьше размером, то есть просто index(f1) например
- или подхватывается нужный индекс
На трассировку нет прав, беда.
Поэтому разбил на пять отдельных меленьких запросов, каждый из которых достает свои записи в коллекцию (условно rowid).
Потом объединяем эту коллекцию в одну.
И дальше делаем оставшуюся часть запроса.
Так вот часть разбитая на маленькие запросы работает теперь быстро, меньше секунды.
А оставшаяся же часть вообще врубает фулскан, хотя выборка идет по rowid из коллекции добытой на первом этапе.
Вот это вообще не понял )
То есть условно запрос вида:


Врубает фулскан по таблицам вьюшки v_my_view.
Вьюшка v_my_view имеет вид:


Подзапрос with t возвращает порядка 30строк.
В table1 и table2 50млн строк.

Если делать этот же запрос не к вьюшке из двух таблиц, а к каждой таблице отдельно:


или


то идет нормальное обращение по rowid
Если идем через вьюшку объединяющую эти две таблицы, обе эти таблицы фулсканятся.

В итоге пришлось взять весь запрос с union all засунуть в требуемый запрос и к каждой таблице прицепиться по rowid.
В итоге запрос отрабатывает в районе секунды, к чему и стремился.
Плохо, что нет времени разобрать в чем засада-то, что уж и по rowid начинает фулсканить, это надо уметь )))