Optimizing Wildcard Search (LIKE '%KEYWORD%')

SETUP

create table t as select * from dba_objects;
insert into t select * from t;
insert into t select * from t;
insert into t select * from t;
commit;
create index t_n1 on t(object_name);
exec dbms_stats.gather_table_stats(ownname=>'donghua',tabname=>'t',method_opt=>'for all indexed columns size 254');

Not Tuning Done

SQL> select * from t where object_name like '%EMPLOYEES%';
64 rows selected.
Elapsed: 00:00:05.02

Execution Plan
----------------------------------------------------------
Plan hash value: 1601196873
--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      | 55195 |  5444K|  4420   (1)| 00:00:54 |
|*  1 |  TABLE ACCESS FULL| T    | 55195 |  5444K|  4420   (1)| 00:00:54 |
--------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE '%EMPLOYEES%')

Tuning 1: (If the final rows less than 5% of total records, which is usually better for Index, instead of full table scan)

SQL> select /*+ index(t,t_n1) */ * from t where object_name like '%EMPLOYEES%';
64 rows selected.
Elapsed: 00:00:04.12

Execution Plan
----------------------------------------------------------
Plan hash value: 3068238422
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      | 55195 |  5444K| 44318   (1)| 00:08:52 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    | 55195 |  5444K| 44318   (1)| 00:08:52 |
|*  2 |   INDEX FULL SCAN           | T_N1 | 55427 |       |  5490   (1)| 00:01:06 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("OBJECT_NAME" LIKE '%EMPLOYEES%')

Tuning 2: (If we only select few columns, instead of all columns) (In below example, we select 3 specific columns instead of *)

SQL> create index t_n2 on t(object_name,owner,object_type);

Index created.

SQL> select owner,object_name,object_type from t where object_name like '%EMPLOYEES%';
64 rows selected.
Elapsed: 00:00:02.70

Execution Plan
----------------------------------------------------------
Plan hash value: 2376357677
-----------------------------------------------------------------------------
| Id  | Operation            | Name | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |      | 55195 |  2156K|  2094   (1)| 00:00:26 |
|*  1 |  INDEX FAST FULL SCAN| T_N2 | 55195 |  2156K|  2094   (1)| 00:00:26 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("OBJECT_NAME" LIKE '%EMPLOYEES%')

Tuning 3: Oracle Text

SQL> create index t_t1 on t(object_name) indextype is CTXSYS.CONTEXT;
Index created.
Elapsed: 00:01:13.54

SQL> select * from t where contains (object_name, 'EMPLOYEES', 1) >0;
64 rows selected.
Elapsed: 00:00:00.02

Execution Plan
----------------------------------------------------------
Plan hash value: 2899541820
------------------------------------------------------------------------------------
| Id  | Operation                   | Name | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |      |   552 | 55752 |   124   (0)| 00:00:02 |
|   1 |  TABLE ACCESS BY INDEX ROWID| T    |   552 | 55752 |   124   (0)| 00:00:02 |
|*  2 |   DOMAIN INDEX              | T_T1 |       |       |     4   (0)| 00:00:01 |
------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("OBJECT_NAME",'EMPLOYEES',1)>0)