Tuning SQL Subqueries Oracle Tips by Burleson Consulting

In Chapter 12, we briefly discussed hinting techniques that could be used to tune subqueries, but subquery tuning involves far more than adding hints. In this chapter, we will take a closer look at subqueries and see when they are legitimate, when to replace them with other join methods, and how get the best performance from both correlated and non-correlated subqueries. This chapter will contain the following topics:

• Basics of Oracle subqueries

• Correlated versus non-correlated subqueries

• Automatic SQL transformation of subqueries

• Tuning subqueries with the IN and EXISTS clauses

• Tuning queries with the NOT IN and NOT EXISTS clauses

• Tuning queries with non-equality conditions

• Hints to improve subquery execution speed

Let’s begin with an overview of the types of subqueries.

Basics of Oracle Subqueries

Whenever possible, the use of a subquery within Oracle SQL should be avoided. In some cases, the subquery can be replaced with a standard join operation, thereby avoiding the overhead that is associated with subqueries. However, there are circumstances when the use of an Oracle subquery is unavoidable, and this section describes the rules for determining the best way to specify a subquery for maximum performance.

One of the shortcomings of Oracle SQL is that there are numerous ways to write most SQL statements, each of which will return identical results, but they may have radically different access paths and execution times. While the Oracle SQL optimizer will often detect “complex” subqueries and decompose them into equivalent join operations, taking the subquery and converting it into a nested loop join, we cannot always count on the optimal access path to service the query.

In cases where we must use subqueries, there are several options that we need to consider. We have the choice of using a correlated or a non-correlated subquery, and we also have the choice of using either the IN clause or the EXISTS clause as the comparison condition for the subquery.

The summary in Table 19-1 show a summary of techniques for each type of subquery.               

	Standard Subquery		Anti-join Subquery
	IN	EXISTS	NOT IN	NOT EXISTS
Correlated subquery	Redundant Boolean predicates. Can always be replaced with a standard join	Automatic Transformation to nested loop join	Rewrite as select distinct outer join	Rewrite as select distinct outer join
Non-correlated subquery	Automatic transformation to nested loop join	Never appropriate	Rewrite as nested loop join with minus operator	Never appropriate

Table 19-1: Summary of Techniques for Each Subquery Type

 

From Table 19-1, you see there are four possible forms for the subquery, either correlated or non-correlated and either standard or anti-join. Let’s begin by looking at the basic form of each type of subquery. We will start by comparing a correlated subquery with a non-correlated one. Here is a non-correlated subquery:

select
   stuff
from
   tablename
where
   key IN
   -- noncorrelated subquery
   (select
      other_stuff
    from
      inner_table
   )
;

Here is the correlated subquery. Note the reference in the inner query to the column value in the outer query:

select
   stuff
from
   tablename
where
   key IN
   -- correlated subquery
   (select
      other_stuff
    from
      inner_table
    where
      tablename.key = inner_table.key
   )
;

Next, we can look at the anti-joins. As you know, an anti-join subquery is a subquery that uses the NOT EXISTS or NOT IN clauses. Just like standard queries, anti-join queries may be correlated or non-correlated.

Here is an example of an anti-join, non-correlated subquery:

select stuff
from
   tablename
where
   key NOT IN

   -- noncorrelated subquery
   (select
      other_stuff
    from
      inner_table
   )
;

Here is the correlated subquery form of the anti-join.

select stuff
from
   tablename
where
   key NOT IN
   -- correlated subquery
   (select
      other_stuff
    from
      inner_table
    where
      tablename.key = inner_table.key
   )
;

Now that you've seen the basic forms of subqueries, let's examine each of these subquery types and look at the most efficient execution plans for Oracle SQL.

This is an excerpt from "Oracle High-Performance SQL Tuning" by Donald K. Burleson, published by Oracle Press.