Trying to  predict Oracle’s behavior with respect to using indexes with LIKE operator is not simple.  However you may be able to get close enough with the following algorithm.  Let me  list  the number of ways you can use the single  LIKE operator along with the algorithm.

1. Beginning of search expression (%ABC) :  Less likely to use an Index (Most likely to perform full table scan)
2. End of search expression (ABC%): More likely to use an Index
3. In  between search expression (A%BC) : More likely to used an Index

However one key thing I want to mention is that if possible avoid using LIKE operator even if index is used and look for ways to use replace LIKE with  SUBSTR/INSTR/ASCII and so on.  This is to reduce the number of consistent gets even if index is being used.

See example below  for all the 3 case scenarios. In the 1st few commands, we are creating tables , indexes and gathering stats.

 

SQL> CREATE TABLE MY_DBAOBJECTS AS SELECT * FROM DBA_OBJECTS UNION ALL 
 2 SELECT * FROM DBA_OBJECTS UNION ALL SELECT * FROM DBA_OBJECTS;
-
TABLE CREATED.
SQL> 
SQL> CREATE INDEX IDX_OBJ_NAME ON MY_DBAOBJECTS (OBJECT_NAME);
-
INDEX CREATED.

SQL> EXECUTE DBMS_STATS.GATHER_INDEX_STATS(OWNNAME=>'DATA_MASTER', INDNAME=>'IDX_OBJ_NAME');
-
PL/SQL PROCEDURE SUCCESSFULLY COMPLETED.
-
SQL> SET AUTOTRACE TRACEONLY EXPLAIN

-
Case:1
#####################################################################################
Beginning of search expression (%ABC): Less likely to use Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE '%DBA';
-
EXECUTION PLAN
------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 11 | 869 | 276 (1)|
|* 1 | TABLE ACCESS FULL| MY_DBAOBJECTS | 11 | 869 | 276 (1)|
------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
1 - FILTER("OBJECT_NAME" IS NOT NULL AND "OBJECT_NAME" LIKE '%DBA')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION

-
Case-2
#####################################################################################
End of search expression (ABC%): More likely to use an Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE 'DBA%';
-
EXECUTION PLAN
----------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 6176 | 476K| 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_DBAOBJECTS | 6176 | 476K| 10 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME | | | 2 (0)|
----------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("OBJECT_NAME" LIKE 'DBA%')
 FILTER("OBJECT_NAME" LIKE 'DBA%')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION

--
Case-3
#####################################################################################
In between search expression (A%BC) : More likely to used an Index
#####################################################################################

SQL> SELECT OBJECT_ID FROM MY_DBAOBJECTS WHERE OBJECT_NAME LIKE 'DBA%T';
-
EXECUTION PLAN
----------------------------------------------------------------------------------
| ID | OPERATION | NAME | ROWS | BYTES | COST (%CPU)|
----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 228 | 18012 | 10 (0)|
| 1 | TABLE ACCESS BY INDEX ROWID| MY_DBAOBJECTS | 228 | 18012 | 10 (0)|
|* 2 | INDEX RANGE SCAN | IDX_OBJ_NAME | 228 | | 2 (0)|
----------------------------------------------------------------------------------
PREDICATE INFORMATION (IDENTIFIED BY OPERATION ID):
---------------------------------------------------
2 - ACCESS("OBJECT_NAME" LIKE 'DBA%T')
 FILTER("OBJECT_NAME" LIKE 'DBA%T')
NOTE
-----
 - 'PLAN_TABLE' IS OLD VERSION
SQL>

Tagged: Like, Like operator and Indexes, Oracle Like operator