Tuesday, April 17, 2018

Syntax for index hint

Use /*+ INDEX(table_name index_name) */

For example:
set autot trace exp stat
select  /*+ INDEX(MYTABLE MY_IDX1) */ min(entry_num)
from sales.sales_log
where sold_date >= '05.12.2017';

Which gave me the following plan:
-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                   | Name          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                            |               |     1 |    11 |     9   (0)| 00:00:01 |       |       |
|   1 |  SORT AGGREGATE                             |               |     1 |    11 |            |          |       |       |
|   2 |   PARTITION RANGE ITERATOR                  |               |     1 |    11 |     9   (0)| 00:00:01 |     1 |    13 |
|*  3 |    TABLE ACCESS BY LOCAL INDEX ROWID BATCHED| SALES         |     1 |    11 |     9   (0)| 00:00:01 |     1 |    13 |
|   4 |     INDEX FULL SCAN                         | MY_IDX1       |     3 |       |     3   (0)| 00:00:01 |     1 |    13 |
-----------------------------------------------------------------------------------------------------------------------------

Line 4 of the explain plan output shows that the index MY_IDX1 is being used.

No comments:

Post a Comment