Hints used in SQL Query of Oracle

Hints used in Oracle

Some common hints used by developer in daily life to make the system performance better.

Hints used in SQL language for generating better execution plan to save resource cost.

Hints is solution provided by oracle to force optimizer to choose different path for execution plan as you want to make execution plan for effective.Hints used in SQL statement for alter the execution plan during execution of particular query.
Note: Do not specify schema name in hint

FULL Hint: It is used for full table scan purpose.

SELECT /*+ FULL(e) */ emp_name
FROM hr.emp e
WHERE last_name LIKE :b1;

Note:Perform full table scan on employee table even last_name have index on it.

Index Hint: Optimizer used indexes which mentioned in hints for generating execution plan.

Case 1: If you used the index name in statement as hint, then optimizer used index scan on this index. Optimizer doesn’t use full or another index scan on query.

SELECT /*+ INDEX (e emp_emp_idx)*/
emp_id, dept_id
FROM hr.emp e
WHERE emp_id > 50;

Case 2: If you used the more indexes in hint, then optimizer checked index scan and find better execution plan to run. Optimizer does not use full table scan or a scan on another index not mentioned.
SELECT /*+ INDEX (e dept_id_idx),INDEX (e emp_id_idx) */
emp_id, dept_id
FROM emp e
WHERE dept_id > 50 and emp_id = 10;

Case 3: If you used no indexes, then the optimizer scan each index present on table to find better execution plan but no full scan.

SELECT /*+ INDEX (emp)*/ emp_id, dept_id
FROM emp e
WHERE dept_id > 50 and emp_id = 10;

No Index Hints: Optimizer does not use indexes which is mention as hints.
Case 1: If you used index name, then that index is not use for SQL statement
SELECT /*+ NO_INDEX(e empid_idx) */ emp_id
FROM emp e
WHERE emp_id > 200;

Case 2:If you used multiple index then except mentioned index all other index is considered
Case 3: If you specify no_index then optimizer not considered any index

INDEX_FFS hint: Optimizer perform fast full index scan instead of full table scan.

SELECT /*+ INDEX_FFS(e empname_idx) */ first_name FROM emp e;

NO_INDEX_FFS hint: Optimizer did not do full index scan for that index which is mentioned in hint

SELECT /*+ NO_INDEX_FFS(e empname_idx) */ emp_name FROM emp e;

INDEX_SS hint: Optimizer choose index skip scan for table

SELECT /*+ INDEX_SS(e empname_idx) */ emp_name
FROM emp e
WHERE emp_name = 'RAM';

NO_INDEX_SS hint: causes the optimizer to exclude a skip scan of the specified indexes on the specified table.

SELECT /*+ NO_INDEX_SS(e emp_name_ix) */ ep_name
FROM emp e
WHERE emp_name = 'RAM';

Parallel hint: use to increased the speed of query by forcing optimizer to used parallel process

SELECT /*+ FULL(e) PARALLEL(e, 5) */ emp_name FROM emp e;

NO_PARALLEL hint: overrides a PARALLEL default value set for table. It is deprecated.
Example: Use of hint no_index_ss for increase the performance.

I have query which changes execution plan with different literals value. Sometime it’s run normal and in some cases it’s hanged.To overcome this problem, I used SQL query with hints because one execution plan use skip scan and degrade the performance of query.

Note: Query 1 pick wrong index which degrade the query performance but Query 2 with different literals value use correct index.
Same query used two different execution plan with different laterals.

To avoid the skip level scanning in query 1 used no_index_ss scan hint to generate good plan as shown in query 3.

Query 1: Used the wrong index in execution plan (Bad Query)
SELECT * FROM scott.Sales WHERE entity_no = ‘000151’ AND order_date = ’28-Dec-2016′ AND cust_number = ‘0000002000100084’;

Execution Plan
-----------------------------
Plan hash value: 2316688008
--------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                  |     1 |   609 |   338  (86)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES            |     1 |   609 |   338  (86)| 00:00:01 |
|*  2 |   INDEX SKIP SCAN                   | SALE_CUST_NO_IDX |    33 |       |   311  (93)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ORDER_DATE"=TO_DATE(' 2016-12-28 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
   2 - access("ENTITY_NO"='000151' AND "CUST_NUMBER"='0000002000100084')
       filter("CUST_NUMBER"='0000002000100084')

Query 2: Used the better index in execution plan (Good Query)
SELECT * FROM SCOTT.SALES WHERE entity_no = ‘000151’ AND order_date=’12-Dec-2016′ AND cust_number = ‘0000000151009034’;

Execution Plan
---------------------------
Plan hash value: 2381214534
------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |   609 |  1165   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES          |     1 |   609 |  1165   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | 1SALE_PRIM_IDX | 13707 |       |    60   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("CUST_NUMBER"='0000000151009034')
   2 - access("ENTITY_NO"='000151' AND "order_date"=TO_DATE(' 2016-12-12 00:00:00', 
              'syyyy-mm-dd hh24:mi:ss'))

Query 3: Used Hint to make it better (modified query)
SELECT /*+ NO_INDEX_SS(tran) */ * FROM scott.Sales WHERE entity_no = ‘000151’ AND
order_date = ’28-Dec-2016′ AND cust_number = ‘0000002000100084’;

Execution Plan
---------------------------
Plan hash value: 2381214534
------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                |     1 |   609 |  1164   (1)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| SALES          |     1 |   609 |  1164   (1)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | 1SALE_PRIM_IDX | 13689 |       |    60   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter("CUST_NUMBER"='0000002000100084';)
2 - access("ENTITY_NO"='000151' AND "ORDER_DATE"=TO_DATE(' 2016-12-28 00:00:00',
           'syyyy-mm-dd hh24:mi:ss'))
Advertisements

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.