EXPLAIN PLAN   «Prev  Next»

Lesson 7 Reading non-correlated subqueries
Objective Identify non-correlated Subqueries in Oracle

Identify non-correlated Subqueries in Oracle

A non-correlated subquery is a condition where the value of an inner query is not tied, or correlated to, the value that is specified in the outer query. When you issue a table join or a non-correlated subquery, Oracle will use the nested loop method to join the tables together. The following series of images illustrates a common DBA query to select all users that have privileges but not those granted via a role (e.g., a direct GRANT to a table). This type of query is commonly used in role-based security environments, where the DBA is looking for explicit GRANTs to tables.

The body of the select statement simply select the username from dba_users
1) 
SELECT
  USERNAME
FROM
  dba_users
WHERE
  USERNAME NOT IN
	(SELECT 
	  GRANTEE
	FROM
	  dba_role_privs
	)
The body of the select statement simply selects the username from dba_users. In the WHERE clause, we select username where there is not a matching row in the dba_role_privs table. Non-correlated subqueries are almost always used with NOT IN predicates in the WHERE clause. It is never a good idea to perform a non-correlated subquery with the IN clause because a regular table join is more efficient.
2)
Let us reverse the NOT IN condition from the prior query and change it to an IN Clause 
SELECT
  USERNAME
FROM
  dba_users
WHERE
  USERNAME IN
  (SELECT
    GRANTEE
  FROM
    dba_role_privs
  );
To illustrate, let us reverse the NOT IN condition from the prior query and change it to an IN Clause. Now we see the following identical queries below.
3)
The second query is easier to understand, and it will run much faster 
SELECT 
  USERNAME
FROM
  dba_users a,
	dba_role_privs b
WHERE
  a.USERNAME = b.GRANTEE;

As we can see, the second query is easier to understand, and it will run much faster than the non-correlated subquery.

  1. The body of the select statement simply select the username from dba_users
  2. Let us reverse the NOT IN condition from the prior query and change it to an IN Clause
  3. The second query is easier to understand, and it will run much faster

Non-correlated Subquery

Question: Are non-correlated subqueries faster than a correlated subquery?
Answer: In non-correlated subqueries, the subquery should be executed before the outer query and the non-correlated subquery is executed only once. The data from the
  1. outer query and the
  2. subquery
are independent and one execution of the subquery will work for all the rows from the outer query.
Any SQL statement is declarative and any given query can be written in many ways. They will all have the same results, but with different execution speeds. In this example of a non-correlated subquery, we locate all authors who have not yet published a book. Note that the subquery is non-correlated because the subquery makes no reference to anything in the outer query. 

select
 author_last_name
from
 author
where
 author_key not in 
 (select author_key from book_author);

This type of "NOT IN" subquery is called a non-correlated subquery because the subquery does not make any references to the outside query. We can sometimes re-write a non-correlated subquery into a correlated subquery.

Non-correlated subquery:

select
 data1
from
 tablename
where
 key IN
 -- noncorrelated subquery
   (select
      other_data1
    from
      inner_table
   );

Here is the correlated subquery equivalent. Note the reference in the inner query to the column value in the outer query: 
select
   data2
from
   tablename
where
   key IN
   -- correlated subquery
   (select
      other_data2
    from
      inner_table
    where
      tablename.key = inner_table.key
   );

Always replace a non-correlated subquery that has the IN condition with a standard table join.
The next lesson examines index range scans.