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 |
-------------------------------------------------------------------------------