Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

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!

like image 295
Cimplicity Avatar asked Aug 14 '12 20:08

Cimplicity


People also ask

Does with clause improve performance in Oracle?

So the with clause is not only an option to use to optimise performance (by materialising with clause's output), but is also useful for readability and maintainability of complex SQL.

Does using with clause improve performance?

Oracle call's the WITH clause "sub-query factoring". Its main use is to improve the performance of queries which use the same sub-query more than once. We can also use it to make our code easier to understand but over-simplification can cause poor performance.

What is the advantage of WITH clause in Oracle?

The WITH clause may be processed as an inline view or resolved as a temporary table. The SQL WITH clause is very similar to the use of Global temporary tables. This technique is often used to improve query speed for complex subqueries and enables the Oracle optimizer to push the necessary predicates into the views.

What is the limit of in clause in Oracle?

In Oracle we can only put up to 1000 values into an IN clause.


1 Answers

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)

like image 120
Michael Durrant Avatar answered Oct 05 '22 04:10

Michael Durrant