|WITH Clause - Use the WITH clause to reduce repetition and simplify complex SQL statements.|
Raja said...1. What is the difference between correlated subquery and WITH Clause ?
2. What is the benefit of using WITH Clause apart from subquery being repeated ?
3. Does using WITH Clause gives any performance improvement ?
The WITH clause may be processed as a inline view by Oracle, or resolved as temporary table. The advantage of the latter is that repeated references to the query will be more efficient as the data is easily retrieved from the temporary table, rather than being requeried.
As with most things in Oracle, you need to assess the performance on a case-by-case basis.
ertugcc said...Your writing is very useful for me. Thank you
Kamal said...Very detailed explanation with the right approach for new developers going to use WITH clause
Lalit Kumar B said...Hi Tim,
Thanks a lot for the clarification about MATERIALIZE optimizer hint. It will surely help a lot of folks.
Tim... said...No problem. :)