Index Hints and Like Clause

Sometime the requirement is to search a column of a table using like operator 
with both side %, as we may need to search employees whose names have 'AR'. 
so that table can be searched by query with ENAME LIKE '%AR%' condition 
or INSTR(ENAME,'AR')>0 condition. But this requirement may lead Oracle to avoid 
the use of index.

Problem:

SQL> CREATE INDEX MYEMP_ENAME_INDX ON MYEMP(ENAME);
Index created.

SQL> SELECT * FROM MYEMP WHERE ENAME LIKE '%AR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2208738075
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |    14 |   546 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS FULL| MYEMP |    14 |   546 |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------


Here we can provide an Index Hint to query to suggest Oracle Database Engine to use index,
Oracle may use index after providing index hint in query.

Solution:

SQL> SELECT /*+ INDEX (MYEMP MYEMP_ENAME_INDX) */ * FROM MYEMP WHERE ENAME LIKE '%AR%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2208738075
------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    39 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| MYEMP            |     1 |    39 |     2   (0)| 00:00:01 |
|*  2 |   INDEX FULL SCAN           | MYEMP_ENAME_INDX |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------


Same can be used when we write this requirement using INSTR.

Solution:

SQL> SELECT /*+ INDEX (MYEMP MYEMP_ENAME_INDX) */ * FROM MYEMP WHERE INSTR(ENAME,'AR')>0;
Execution Plan
----------------------------------------------------------
Plan hash value: 2418123361
---------------------------------------------------------------------------
| Id  | Operation         | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |       |     1 |    39 |     3   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| MYEMP |     1 |    39 |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------- 

Get More Query Optimization Tips:
http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html

12 comments:

  1. Hmm, 11.2 seems to not need this:

    SCOTT@ORCL> SELECT * FROM EMP WHERE ENAME LIKE '%AR%';

    Execution Plan
    ----------------------------------------------------------
    Plan hash value: 3381260881

    ----------------------------------------------------------------------------------------
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    ----------------------------------------------------------------------------------------
    | 0 | SELECT STATEMENT | | 1 | 38 | 2 (0)| 00:00:01 |
    | 1 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 38 | 2 (0)| 00:00:01 |
    |* 2 | INDEX FULL SCAN | ENAME_IX | 1 | | 1 (0)| 00:00:01 |
    ----------------------------------------------------------------------------------------

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

    2 - filter("ENAME" LIKE '%AR%' AND "ENAME" IS NOT NULL)

    ReplyDelete
    Replies
    1. On this case Optimizer may or may not use the index, so use of hint is suggested...

      Delete
  2. sir..

    This is very nice article.I have few queries..

    Can u please provide some simple examples of the below ones in Oracle.

    1.Partitioning (Table, Query), External tables.
    2. Hints
    3. TKPROF
    4. How will we reduce the cost of the sql query statement..?

    Thanks,
    Rajkumar

    ReplyDelete
    Replies
    1. read following articles:
      for optimization: http://nimishgarg.blogspot.in/2012/10/query-optimization-tips-for-oracle.html
      Tkprof: http://nimishgarg.blogspot.in/2010/10/oracle-tkprof-simple-steps.html
      PL/SQL Profiler: http://nimishgarg.blogspot.in/2013/03/dbmsprofiler-overview-and-how-to-install.html
      External Table: http://nimishgarg.blogspot.in/2012/06/create-external-table-from-csv-file.html

      Delete
  3. Thanks....!!! This is really very helpful

    ReplyDelete
  4. Sir,

    Can you please explain about step by step Oracle EXTERNAL TABLES..?

    ReplyDelete
    Replies
    1. http://nimishgarg.blogspot.in/2012/06/create-external-table-from-csv-file.html

      Delete
    2. This is really very helpful. Thanks !!!

      Delete
  5. I have one question why is the optimizer is ignoring index when the cost is lower then full table access? And why we need to use hint to make optimizer force the index use?

    ReplyDelete
    Replies
    1. Oracle Database does not use index with like operator, because oracle can not estimate the cardinality with like operator. So oracle has given us a way to use index with like operator if we know that cost would be low

      Delete
  6. Hi Nimish,

    The last output (Index Hint with INSTR) is wrong.
    In 11g, 12c if we use Index Hint along with INSTR then it will use Index.

    ReplyDelete