Forums | Aggregator | Books | Industry News | Firefox Plugins | Social | Links

Comments

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 ?

Tim... said...

Hi.

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.

Cheers

Tim...

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.

Regards,
Lalit

Tim... said...

No problem. :)

Cheers

Tim...

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!

Add your comments here.
Name
Comment
(max 400 chars - plain text)