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.

Because I never seen this before, I saw required columns for index join in where clause only.

Because row_id is direct access, and better
1. Go to the row by physically pointed by row_id
2. fetch the emp_no

While joining indexes is costly matter in any case
1. Get the row_id
2. Do a index join on the basis of row_id (Join is join, it will take at lest nlogn time)
3. Fetch the emp_no

This might work if a significant number of rows were being returned (>2%?) and thus rowid lookup was too costly, but also the table emp had wide rows and thus a table scan was far more expensive than the to index scans.