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