CODEWITHSUNDEEP
sqloracle-databaseoracle11gcommon-table-expression
Cimplicity
Cimplicity

Reputation: 2732

Over use of Oracle With clause?

I'm writing many reporting queries for my current employer utilizing Oracle's WITH clause to allow myself to create simple steps, each of which is a data-oriented transformation, that build upon each other to perform a complex task.

It was brought to my attention today that overuse of the WITH clause could have negative side effects on the Oracle server's resources.

Can anyone explain why over use of the Oracle WITH clause may cause a server to crash? Or point me to some articles where I can research appropriate use cases? I started using the WITH clause heavily to add structure to my code and make it easier to understand. I hope with some informative responses here I can continue to use it efficiently.

If an example query would be helpful I'll try to post one later today.

Thanks!

Upvotes: 7

Views: 4838

Answers (1)

Michael Durrant
Michael Durrant

Reputation: 96484

Based on this: http://www.dba-oracle.com/t_with_clause.htm it looks like this is a way to avoid using temporary tables. However, as others will note, this may actually mean heavier, more expensive queries that will put an additional drain on the database server.

It may not 'crash'. That's a bit dramatic. More likely it will just be slower, use more memory, etc. How that affects your company will depend on the amount of data, amount of processors, amount of processing (either using with or not)

Upvotes: 2

Related Questions