OBIEE 11g "WITH SAWITH0 AS" subquery factoring clause in the generated sql
I've observed that the OBIEE 11g generates in the query log physical query using the WITH (sub-query factoring) clause to make the generated sql elegantly readable. This is great! Thanks for the developers. However I have some questions about this.
__Background__
Oracle Database' default behaviour is that if you have only one sub-query in the WITH section, it executes it as an in-line view and does not materialize it before the main sql is executed. If you have more than one, by default the database engine materializes all of them in the order of the definition. In some cases this can completely blow up the SGA and make the query never ending. To divert this behaviour you can apply two hints that work both in inline views and in sub-queries as well: /*+ MATERIALIZE */ and /*+ INLINE*/, however Analytics 11g does not seem to have hint capabilities at the logical table level, only at physical table level.
If we go with the current defaults, developers not aware of this feature can bump into serious performance issues for the sake of some syntax candy at the generated sql level, I'm afraid.
__Questions__
* Is it possible to turn the Analytics server not to use WITH but use inline views instead?
* Is there any way to sneak in some hints that would put the /*+ INLINE */ hint to the appropriate place in the generated sub-queries if needed
* Does the Oracle Database have any initialization parameter that can influence this sub-query factoring behavior and divert from the default?