Thursday, January 28, 2010

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)