+1 with Sayan's suggestion.
I'd like to first point out that the query is combining the WHERE clause
filtering predicates within the ON clause join predicates, but I don't
know if that would impact star transformation or not.
It would be more effective to first attempt rewriting the query in
old-style Oracle syntax and see if that works, because it definitely
should. If star transformation still doesn't happen, then there is
almost certainly a missing prerequisite (i.e. parameter setting, bitmap
indexes on dimensional-key columns, etc). If however it does work with
the old-style Oracle syntax, then I would consider seeing if it then
might work when the ANSI SQL syntax is specified correctly (i.e. join
predicates in ON clauses, filtering predicates in WHERE clauses).
On 10/22/15 14:35, Sayan Malakshinov wrote:
Have you tried to rewrite the query without ANSI joins?