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:
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.
Nice blog, Thanks For Sharing this informative Article.
ReplyDeleteOracle Fusion SCM Online Training
Oracle Fusion Financials Online Training
This comment has been removed by the author.
ReplyDelete