RelationalDBDesign RelationalDBDesign

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 Slideshow 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.

  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 Examples
Always replace a non-correlated subquery that has the IN condition with a standard table join.

The next lesson examines index range scans.