Like operator and index usages

Typically we would have the following patterns when we use like operator.

1. SEARCH-STRING%
2. %SEARCH-STRING
3. %SEARCH-STRING%
4. SEARCH%STRING

1. a)The SEARCH-STRING% will perform INDEX RANGE SCAN data in least possible time.  

    b)Also uses INDEX FULL SCAN when you have only indexed column list in the queries.

    c) If multiple predicates used, this may loose the index usage and goes to full  table scan

2. When using %SEARCH-STRING it’s access the FULL table

3. When using %SEARCH-STRING% it’s access the FULL table.

4. The SEARCH%STRING will perform INDEX RANGE SCAN and generate an initial result set, containing the values that match first string i.e. SEARCH%. Next it will scan through the values to get second string i.e. %STRING

Let’s have a closer look

I have a table called EMPLOYEES and having a composite index on Last_name and First_name.

Case 1 (a):- Index range scan is in use, I am accessing all columns

SELECT * FROM employees WHERE LAST_NAME like 'K%';

Case 1 (b):- Index full scan is in use. Accessing only indexed columns in select.

SELECT FIRST_NAME,LAST_NAME FROM employees WHERE LAST_NAME like 'K%' OR LAST_NAME like 'L%';

Case 1(c ):- Added a predicate L% which leads to a full table scan.

SELECT * FROM employees WHERE LAST_NAME LIKE 'K%' OR LAST_NAME LIKE 'L%';

Case 2 & 3:- Used lower case filter predicate with %k% or k%

SELECT * FROM employees WHERE LAST_NAME LIKE '%k';

If you access only index columns, index full scan like case 2(a) above will be possible.

Case 4:- Used String%string

SELECT * FROM employees WHERE LAST_NAME LIKE 'Smith%Joe';

Optimizer first check the first part of the string i.e Smith and then matches with second part of the string i.e joe with index range scan.

Thanks

-Geek DBA