Example of using INDEX Hint in Oracle

Correct way of using INDEX Hint in Oracle SQL Queries

Syntax of using Index hint:

select /*+ index(TABLE_NAME INDEX_NAME) */

Examples:

Table Name is mandatory in Index Hint:
You can always use the table name in the hint of query otherwise index in not use.


Incorrect Way: Don't specify the table name or only specify index name will not work.
select /*+ index() */ * from departments;
-------------------------------------------------------------------------------
|Id|Operation       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|0|SELECT STATEMENT |             |    27 |   567 |     3   (0)| 00:00:01 |
|1|TABLE ACCESS FULL| DEPARTMENTS |    27 |   567 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Correct way: Table name is must.
Index name is not mandatory, it will choose index based on stats.
select /*+ index(departments dept_id_pk) */ * from departments;
OR
select /*+ index(departments) */ * from departments;

-------------------------------------------------------------------------------
|Id| Operation                        |Name       |Rows|Bytes|Cost(%CPU)|Time|
-------------------------------------------------------------------------------|0| SELECT STATEMENT                  |           | 27 | 567 |2(0)| 00:00:01 |
|1|TABLE ACCESS BY INDEX ROWID BATCHED|DEPARTMENTS| 27 | 567 |2(0)| 00:00:01 |
|2| INDEX FULL SCAN                   |DEPT_ID_PK | 27 |     |1(0)| 00:00:01 |
-------------------------------------------------------------------------------

Use of Table Alias with index hint:
Note: If you use the table alias in Query then you must specify the table alias in hint instead of table name otherwise index in not used.


InCorrect Way: If alias present then table name not work.
select /*+ index(departments) */ * from departments d;
-------------------------------------------------------------------------------
|Id|Operation       | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|0|SELECT STATEMENT |             |    27 |   567 |     3   (0)| 00:00:01 |
|1|TABLE ACCESS FULL| DEPARTMENTS |    27 |   567 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Correct Way: Must use the alias of table if it specified.
select /*+ index(d) */ * from departments d;
-------------------------------------------------------------------------------
|Id| Operation                        |Name       |Rows|Bytes|Cost(%CPU)|Time|
-------------------------------------------------------------------------------|0| SELECT STATEMENT                  |           | 27 | 567 |2(0)| 00:00:01 |
|1|TABLE ACCESS BY INDEX ROWID BATCHED|DEPARTMENTS| 27 | 567 |2(0)| 00:00:01 |
|2| INDEX FULL SCAN                   |DEPT_ID_PK | 27 |     |1(0)| 00:00:01 |
-------------------------------------------------------------------------------

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.