Use Index Hint in Oracle SQL queries
Use the index hint in SQL query will improve the performance. In some case optimizer is not able to pick the right index for the SQL queries, So for tuning some queries for better performance we have to use the HINT in the query.
Syntax:
--with table name
select /*+ index(table_name table_index_name) */ * from table_name;
-- with table alias
Select /*+ index(table_alias table_index_name) */ * from table_name table_alias;
Note: Index list found for table from following example query.
Example of using INDEX HINT
Following example will show how we will utilize the index name if optimizer is not choosing our index.
1. Check the index present in our employees table at HR schema.
set line 250 pages 1000
column table_name format a10
column index_name format a20
column column_name format a16
Select a.table_name, a.index_name, a.column_name
FROM dba_ind_columns a where a.table_owner='HR' and a.table_name = 'EMPLOYEES'
Order by a.table_name, a.Index_name,a.column_position,a.column_name;
TABLE_NAME INDEX_NAME COLUMN_NAME ---------- -------------------- ---------------- EMPLOYEES EMP_LAST_NAME_IDX LAST_NAME EMPLOYEES EMP_MANAGER_IX MANAGER_ID EMPLOYEES EMP_NAME_IX LAST_NAME EMPLOYEES EMP_NAME_IX FIRST_NAME
2. Execute the query by default optimizer will choose the index.
In some case we did not want to use the optimizer choose index then we will placed Index hint in the query which will pick index of our choose to improve query performance.
The following query will chose the index EX_MANAGER_IX instead of i using column last name and first name and i also want to use index based on names.
SQL> select * from hr.employees where last_name = 'RAM' and first_name = 'RAM' and manager_id =1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 621391157
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_MANAGER_IX | 1 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("LAST_NAME"='RAM' AND "FIRST_NAME"='RAM')
2 - access("MANAGER_ID"=1)
3. For changing index preference, we will use hint in this query to use INDEX build on Last_NAME and FIRST_NAME column.
SQL> select /*+ index(employees emp_name_ix) */ * from hr.employees where last_name = 'RAM' and first_name = 'RAM' and manager_id =1;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 1600171752
---------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 69 | 2 (0)| 00:00:01 |
|* 1 | TABLE ACCESS BY INDEX ROWID BATCHED| EMPLOYEES | 1 | 69 | 2 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | EMP_NAME_IX | 1 | | 1 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("MANAGER_ID"=1)
2 - access("LAST_NAME"='RAM' AND "FIRST_NAME"='RAM')
Note: the above example show the use of INDEX hint in the SQL query.
Smart way of Technology 