Index Join

This is a feature you can use in a high volume SQL to avoid a table lookup. Most programmers know that if an index contains all of the columns used in a SQL, then Oracle does not need to access the table. But what if the columns you are using are spread over two indexes?

For example:

SELECT emp_no, sal
FROM   emp
WHERE  sal > 1000

Index emp_i1 : sal
Index emp_i2 : emp_no

No single index contains emp_no and sal. Oracle can read both indexes and perform a hash join of the two. This will often be slower than a full table scan, but if the rows of the table are particularly wide (lots of columns, or big VARCHAR2 / CHAR columns), the advantage of reading the skinny indexes could be significant.

To get Oracle to use an index join, use the INDEX_JOIN hint.

There is no hint to stop Oracle from performing an index join. In the unlikely event that you want to prevent an Index Join, use the INDEX hint to specify just one index, or the FULL hint for a Full Table Scan.