Oracle Database Discussions

Hi,

As per Oracle documentation, the factored subquery is treated as an inline view or temporary table and optimised accordingly. I have the following questions about the performance implications of this.

1. If the factored subquery is treated as an inline view, I can't think of any performance benefit thereof. It will only result in simplification of the SQL statement syntactically. Is this correct?
2. If the query is treated as a temporary table, then the result will be derived only once and will thus have performance benefit. Is this understanding correct?
3. Based on what does Oracle decide whether to treat it as an inline view or a temporary table?
4. Is there any other benefit than the one stated in point 2? I've heard that the subquery output is held in memory, thus saving I/O. Is that true?
5. Where to find more information about the performance perspective of subquery factoring?

Regards,
Rahul