Sometimes we may find that Oracle chooses to use FTS(Full table scan) instead of using index when LIKE operator is used in where clause. Below example illustrates this problem and fixes.

SQL> create table t as select * from dba_objects;
SQL> create index t_idx on t(object_id);
SQL> exec dbms_stats.gather_table_stats('USER','T',cascade=>TRUE);
SQL> SELECT * FROM t WHERE object_id like '111%';
111 rows selected.

Execution Plan
----------------------------------------------------------
Plan hash value: 1357081020

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |  2723 |   244K|   166   (2)| 00:00:02 |
|*  1 |  TABLE ACCESS FULL| T    |  2723 |   244K|   166   (2)| 00:00:02 |
--------------------------------------------------------------------------

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

1 - filter(TO_CHAR("OBJECT_ID") LIKE '111%')

As you can see from the above query, when using like operator, index t_idx is not used in the explain plan because there is an implicit conversion when like operator is used , this conversion is implemented on the ID column as to_char(ID). As the function wrapped ID column then any index on ID column won't be used.

We can improve the performance of this query by two means, either create function based index on to_char(object_id) or change the object_id to text colum,e.g. varchar2.




Method 1(creating function-based index):

SQL> create index t_idx2 on test(to_char(object_id));
Index created.
SQL> select * from t where to_char(object_id) like '111%';
111 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 1998783028

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |  2723 |   244K|   104   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T   |  2723 |   244K|   104   (0)| 00:00:02 |
|*  2 |   INDEX RANGE SCAN          | T_IDX2 |   490 |       |     3   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access(TO_CHAR("OBJECT_ID") LIKE '111%')
filter(TO_CHAR("OBJECT_ID") LIKE '111%')

Method 2(changing the object_id to type varchar2)
Use the following SQL statements to create a table with object_id column of type varchar2.

SQL> create table t2 as select * from dba_objects where 1=0;
SQL> alter table t2  modify object_id varchar(30);
--SQL> alter table t2 add constraint test2_pk primary key (object_id);
SQL> create index t2_idx on t2(object_id);
SQL> exec dbms_stats.gather_table_stats('USER','T2',cascade=>TRUE);
SQL> insert /*+ append*/ into t2 select * from dba_objects;

SQL> select * from t2 where object_id like '111%';

Execution Plan
----------------------------------------------------------
Plan hash value: 4244861920

--------------------------------------------------------------------------------------
| Id  | Operation                   | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |        |   214 | 19902 |   217   (0)| 00:00:03 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T2     |   214 | 19902 |   217   (0)| 00:00:03 |
|*  2 |   INDEX RANGE SCAN          | T2_IDX |   214 |       |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------------------

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

2 - access("OBJECT_ID" LIKE '111%')
filter("OBJECT_ID" LIKE '111%')

By either creating FBI on the column or changing the datatype to varchar, indexes are used during queries and thus the performance is improved alot.


Note that when the LIKE operator is implemented with wildcard card in the begining of the string, e.g. ... where object_id like '%111%', there is noway to use the index since it begins with wildcard % which is indeterministic.