Index Joins in Oracle database

 In normal scenario if you have multiple indexes on the columns on the table oracle optimizer would prefer one of the available indexes based on clustering factor of the index and other factors like use of order by clause on the indexed column etc.I will just show an example where I used index join method to eliminate table access by index row id and was able to retrieve data from indexes only.

Table has five columns and there are two separate indexes XYZ_I1 and XYZ_I2 which on COL1 and COL2 respectively. COL1 and COL2 contain sequence values and table XYZ has around 10000 records.

SQL> desc xyz
 Name                                                                Null?    Type
 ------------------------------------------------------------------- -------- ---------------------------------------------
 COL1                                                                         NUMBER
 COL2                                                                         NUMBER
 COL3                                                                         NUMBER
 COL4                                                                         NUMBER
 COL5                                                                         NUMBER

Now if I execute query (select col1, col2 from xyz where col1=1 and col2=2 order by col1, col2), under this condition Oracle can pick either of below two paths and oracle cannot use both the indexes-

a.) Table access by Index RowID XYZ using Index Range Scan of XYZ_I1 or 

b.) Table access by Index RowID XYZ using Index Range Scan of XYZ_I2

SQL> explain plan for select col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;

Explained.

SQL> select * from table(dbms_xplan.display());

Plan hash value: 3398653176

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |     8 |     2   (0)| 00:00:01 |

|*  1 |  TABLE ACCESS BY INDEX ROWID| XYZ    |     1 |     8 |     2   (0)| 00:00:01 |

|*  2 |   INDEX RANGE SCAN          | XYZ_I1 |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

But if you closely look upon where clause criteria and columns being fetched, you would find that we are trying to fetch only columns col1 and col2 data of which exist in the indexes itself. So, is there a way to join these two indexes and retrieve the data from indexes itself? Yes, there is and this is what so called as index joins.

SQL> explain plan for select /*+INDEX_JOIN(xyz XYZ_I1 XYZ_I2)*/ col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;

Explained.

SQL> select * from table(dbms_xplan.display());


Plan hash value: 364115606

---------------------------------------------------------------------------------------
| Id  | Operation          | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |                  |     1 |     8 |     3  (34)| 00:00:01 |
|*  1 |  VIEW              | index$_join$_001 |     1 |     8 |     3  (34)| 00:00:01 |
|*  2 |   HASH JOIN        |                  |       |       |            |          |
|*  3 |    INDEX RANGE SCAN| XYZ_I1           |     1 |     8 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN| XYZ_I2           |     1 |     8 |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

As you can clearly see from explain plan that oracle was able to fetch required data by joining two indexes and there is no table access by index row id.

Other efficient approach could be creating a composite index on (col1,col2) and fetching the data from leaf blocks of one single index.

SQL>create index XYZ_I3 on XYZ(COL1, COL2);
SQL>index created.

SQL> explain plan for select /*+INDEX_JOIN(xyz XYZ_I1 XYZ_I2)*/ col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;

Explained.

SQL> explain plan for select col1, col2 from xyz where col1=1 and col2=2 order by col1, col2;

Explained.

SQL> select * from table(dbms_xplan.display());

Plan hash value: 3398653124

--------------------------------------------------------------------------------------

| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |

--------------------------------------------------------------------------------------

|   0 | SELECT STATEMENT            |        |     1 |     8 |     1   (0)| 00:00:01 |

|*  1 |   INDEX RANGE SCAN          | XYZ_I3 |     1 |       |     1   (0)| 00:00:01 |

--------------------------------------------------------------------------------------

Hope I am able to explain the concept of index joins and you find this article useful. Please let me know for any questions.