Use Index Hint in Oracle SQL queries

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.

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.