Oracle INDEX hint : Different usage syntax for performance tuning

One of the most frequent uses of hints is to force a particular access path to be selected, and typically this means forcing the use of a particular index.

The simplest hint for forcing an index is the rather appropriately named INDEX hint. Typically, the INDEX hint is used to force the use of a particular index in this manner


I have created one example_table from the dictionary view all_objects and I have created 2 indexes on this table. (example_tbl_idx and example_tbl_idx2)

SQL> DESC EXAMPLE_TBL
 Name                                      Null?    Type
 ----------------------------------------- -------- -------------------------
 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(19)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)
 NAMESPACE                                          NUMBER
 EDITION_NAME                                       VARCHAR2(30)

SQL> CREATE INDEX EXAMPLE_TBL_IDX ON EXAMPLE_TBL(OWNER);

Index created.

SQL> CREATE INDEX EXAMPLE_TBL_IDX2 ON EXAMPLE_TBL(OBJECT_NAME);

Index created.

Here are the different methods to use INDEX hint.

Method 1
SQL> l
  1  SELECT /*+ INDEX(EX, EXAMPLE_TBL_IDX) */ OBJECT_NAME
  2  FROM   EXAMPLE_TBL EX
  3* WHERE  OWNER='SCOTT'
SQL> /

Execution Plan
----------------------------------------------------------
Plan hash value: 651641215

-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |  1905 | 59055 |    65   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EXAMPLE_TBL     |  1905 | 59055 |    65   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EXAMPLE_TBL_IDX |  1905 |       |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT')

Method 2
You can also instruct the optimizer to choose between a subset of indexes by specifying multiple index names

SQL> SELECT /*+ INDEX(EX, EXAMPLE_TBL_IDX, EXAMPLE_TBL_IDX2) */ OBJECT_NAME
FROM   EXAMPLE_TBL EX
WHERE  OWNER='SCOTT'
AND    OBJECT_NAME='EMP';

Execution Plan
----------------------------------------------------------
Plan hash value: 3522333813

------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    31 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EXAMPLE_TBL      |     1 |    31 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EXAMPLE_TBL_IDX2 |     2 |       |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SCOTT')
   2 - access("OBJECT_NAME"='EMP')



Method 3
You can simply specify that you want an index to be used but leave it up to the optimizer to choose the appropriate index.

SQL> SELECT /*+ INDEX(EX) */ OBJECT_NAME
FROM   EXAMPLE_TBL EX
WHERE  OWNER='SCOTT'  2    3  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 651641215

-----------------------------------------------------------------------------
| Id  | Operation                   | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                 |  1905 | 59055 |    65   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EXAMPLE_TBL     |  1905 | 59055 |    65   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | EXAMPLE_TBL_IDX |  1905 |       |     5   (0)| 00:00:01 |
-----------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT')

Method 4

You can also specify that you would like multiple indexes to be merged using the AND_EQUAL hint (although most of the time, merging indexes suggests you are missing an appropriate concatenated index)

SQL> SELECT /*+ AND_EQUAL(EX, EXAMPLE_TBL_IDX, EXAMPLE_TBL_IDX2) */ OBJECT_NAME
FROM   EXAMPLE_TBL EX
WHERE  OWNER='SCOTT'
AND    OBJECT_NAME='EMP'  2    3    4  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1444633027

------------------------------------------------------------------------------------
| Id  | Operation         | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |                  |     1 |    31 |     8   (0)| 00:00:01 |
|*  1 |  AND-EQUAL        |                  |       |       |            |          |
|*  2 |   INDEX RANGE SCAN| EXAMPLE_TBL_IDX2 |     2 |       |     3   (0)| 00:00:01 |
|*  3 |   INDEX RANGE SCAN| EXAMPLE_TBL_IDX  |  1905 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OBJECT_NAME"='EMP' AND "OWNER"='SCOTT')
   2 - access("OBJECT_NAME"='EMP')
   3 - access("OWNER"='SCOTT')

Method 4
By default Orcale scans indexes in ascending order. You can, however, specify that you wish the index to be scanned in descending order.

SQL> SELECT /*+ INDEX_desc(EX,EXAMPLE_TBL_IDX) */ OBJECT_NAME
FROM   EXAMPLE_TBL EX
WHERE  OWNER='SCOTT'  2    3  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 1564922202

------------------------------------------------------------------------------
| Id  | Operation                    | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                 |  1905 | 59055 |    65   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID | EXAMPLE_TBL     |  1905 | 59055 |    65   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN DESCENDING| EXAMPLE_TBL_IDX |  1905 |       |     5   (0)| 00:00:01 |
------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("OWNER"='SCOTT')
       filter("OWNER"='SCOTT')

If you don’t want to use an index, you can use the FULL hint. You might want to do this if
·         You are using rule-based optimization and the optimizer is selecting a very unselective index.
·         You are using cost-based optimization and the optimizer is using an index that appears to be selective(e.g. has a  large number of distinct values) but you happen to know that the particular alue being searched is not selective(for instance, you have a query that is using an index to get all people under 100 years of age)

Usage of FULL Hint:
SQL> SELECT /*+ FULL(EX) */ OBJECT_NAME
FROM   EXAMPLE_TBL EX
WHERE  OWNER='SCOTT'  2    3  ;

Execution Plan
----------------------------------------------------------
Plan hash value: 734479979

-------------------------------------------------------------------------------
| Id  | Operation         | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |             |  1905 | 59055 |   325   (4)| 00:00:04 |
|*  1 |  TABLE ACCESS FULL| EXAMPLE_TBL |  1905 | 59055 |   325   (4)| 00:00:04 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("OWNER"='SCOTT')
DBA Tips Data Pump Reference

0 comments:

Post a Comment

 

dba topics. Copyright 2011-16 All Rights Reserved | Site Map | Contact | Disclaimer | Google