8i | 9i | 10g | 11g | 12c | Misc | PL/SQL | SQL | RAC | Linux

Home » Misc » Here

Comments for WITH Clause : Subquery Factoring

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 ?

Tim... said...


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. :)



DO NOT ask technical questions here! They will be deleted!

These comments should relate to the contents of a specific article. Constructive criticism is good. Advertising and offensive comments are bad and will be deleted!