Index usage with Like Operator and DOMAIN INDEX

How Index works with Like Operator?  What is DOMAIN INDEX and how it works?

When we use like operator with Indexed column and if you use wild card character towards end then ORACLE will use Index Search. But if you use it at the starting then ORCLE will do Full Table scan Why?

Because in that case ORACLE doesn’t know which data to search. So, ORACLE will guess that it can start with ‘A to Z’ or ‘a to z’ or number 0,1 to any.

Let’s see this with the below Example:

EMPLOYEES Table with an Index created on JOB_ID.

Normal Search: Which will obviously use Index created on JOB_ID.

SQL> SET AUTOTRACE TRACEONLY EXP
SQL> SELECT * From EMPLOYEES Where JOB_ID='ACT102';
Execution Plan
----------------------------------------------------------
Plan hash value: 2315968517
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |     5 |   245 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |     5 |   245 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | JOB_IND   |     5 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("JOB_ID"='ACT102')

With Like Operator and Wild card character at the end: This will still use Index scan as ORACLE knows from where to start.

SQL> SELECT * From EMPLOYEES Where JOB_ID Like 'ACT%';
Execution Plan
----------------------------------------------------------
Plan hash value: 2315968517
-----------------------------------------------------------------------------------------
| Id  | Operation                   | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |           |    10 |   490 |     3   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES |    10 |   490 |     3   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | JOB_IND   |    10 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("JOB_ID" LIKE 'ACT%')
       filter("JOB_ID" LIKE 'ACT%')

With Like Operator and Wild card character at the starting:  If you see the Execution Plan below have used Full Table Scan Because in this case, ORACLE doesn’t know which data to search so, it will follow Full Table scan.

SQL> SELECT * From EMPLOYEES Where JOB_ID Like '%ACT102';
Execution Plan
----------------------------------------------------------
Plan hash value: 1445457117
-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |           |     2 |    98 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS FULL| EMPLOYEES |     2 |    98 |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("JOB_ID" LIKE '%ACT102' AND "JOB_ID" IS NOT NULL)

Now, Solution to above problem is to use DOMAIN INDEX Search.
Let’s see how it works:

SQL> Conn Sys as sysdba
Enter password:
Connected.
SQL> grant execute on ctx_ddl to public;
Grant succeeded.
SQL> conn Myuser/Myuser
Connected.
SQL> Begin
  2  ctx_ddl.create_preference('SUBSTRING_PREF','BASIC_WORDLIST');
  3  ctx_ddl.set_attribute('SUBSTRING_PREF','SUBSTRING_INDEX','TRUE');
  4  End;

Drop the Existing Index and Create Domain Index on JOB_ID:

SQL> Drop Index INDX_JOB_ID;
Index dropped.
SQL> CREATE INDEX INDX_EMP_JOB_ID ON EMPLOYEES(JOB_ID) INDEXTYPE IS CTXSYS.CONTEXT PARAMETERS('Wordlist SUBSTRING_PREF memory 50m');
Index created.

Now, Search with Like Operator and Wild card character at the starting and see if now it is using Index Scan or Not.

SQL> SELECT * From EMP_LIST Where  contains (JOB_ID,'%ACT%') > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 3602428313
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    93 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_LIST    |     1 |    93 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | INDX_JOB_ID |       |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("JOB_ID",'%ACT%')>0)
Note
-----
   - dynamic sampling used for this statement (level=2)

SQL> SELECT * From EMP_LIST Where  contains (JOB_ID,'%ACT102') > 0;
Execution Plan
----------------------------------------------------------
Plan hash value: 3602428313
-------------------------------------------------------------------------------------------
| Id  | Operation                   | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |             |     1 |    93 |     4   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP_LIST    |     1 |    93 |     4   (0)| 00:00:01 |
|*  2 |   DOMAIN INDEX              | INDX_JOB_ID |       |       |     4   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("CTXSYS"."CONTAINS"("JOB_ID",'%ACT102')>0)
Note
-----
   - dynamic sampling used for this statement (level=2)

So, now ORACLE is doing Index Search. Get involved and share your thoughts on the comment box below.