Comparing an indexed column (or hash cluster key) to a sub-query

Consider the following query

col1 is indexed, and we are expecting Oracle to evaluate the sub-query and use the result to perform an indexed lookup on table_name.

This is quite feasible, but only under some conditions:

Examples of queries that may use an index (indexed column underlined)

Examples of queries that will not use an index.


NOT IN sub-query Sorry, there is simply no way to index this. If the outer table is large and you have no other indexed columns you can access, then you have a High Volume query. You can probably still tune it, but you may never get lightning performance.
{>|<}[=] [ANY|ALL] sub-query

A normal col <= :val will use an index, but col <= (sub-query) will not. Why not? It's a mystery. One workaround is to place the sub-query in a PL/SQL stored function. When the query is rewritten to col <= func(), the index will be used.

Note that this work-around will not work for hash clusters which can only ever be scanned with equals or IN operators.

correlated sub-query If you really want indexed access on the outer table, you must rewrite the SQL to avoid the correlated sub-query. This may be possible by performing a join instead of a sub-query, or using an uncorrelated IN sub-query in place of an equals (=) sub-query.

©Copyright 2003