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.
- The body of the select statement simply select the username from dba_users
- Let us reverse the NOT IN condition from the prior query and change it to an IN Clause
- 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.