For using Oracle With clause?

Question

I write a lot of reporting requests for my current employer, using the Oracle WITH clause, to allow myself to create simple steps, each of which is a data-centric transformation that builds upon each other to perform a complex task.

Today I was informed that overuse of the WITH clause can have negative side effects for Oracle server resources.

Can anyone explain why overuse of the Oracle WITH clause can cause the server to crash? Or point me to some articles where I can explore relevant use cases? I started using the WITH clause heavily to add structure to my code and make it easier to understand. Hopefully with some informative answers I can continue to use it effectively.

If the sample request is useful, I will try to publish it later today.

Thanks!

Answer 1

Based on this: http://www.dba-oracle.com/t_with_clause.htm , it seems like this is a way to avoid using temporary tables. However, as others have noted, this can mean heavier and more expensive queries that add extra leakage to the database server.

He can't "crash." This is a little dramatic. Most likely it will be slower, use more memory, etc. How this will affect your company will depend on the amount of data, the number of processors, the amount of processing (either with with or not)