Using the EXISTS Function and the Nested Subquery in Oracle

By Richard Niemiec on April 20, 2013


A helpful tip to remember is to use the EXISTS function instead of the IN function in most circumstances. The EXISTS function in Oracle checks to find a single matching row to return the result in a subquery. Because the IN function retrieves and checks all rows, it is slower.

Oracle has also improved the optimizer so it often performs this optimization for you as well. Consider the following example, where the IN function leads to very poor performance. This query is faster only if the ITEMS table is extremely small:

 

0432_002

 

 

 

 

 

 

 

 

 

 

In this query, the entire ITEMS table is retrieved. This query is faster when the condition PRODUCT_ID = 167 substantially limits the outside query:

 

0433_001

 

TIP

Using the nested subquery with an EXISTS clause may make queries dramatically faster, depending on the data being retrieved from each part of the query. Oracle11g often makes this translation internally, saving you time and giving you performance gains!

Related Posts

Leave a Reply