I like the LIKE operator!


Let suppose the we want to find specific workers using their names from the employees table.
First we want to list the available indexes for that table:

SELECT i.index_name,c.column_position,c.column_name
FROM USER_INDEXES I, USER_IND_COLUMNS C
WHERE i.index_name=c.index_name AND i.table_name='EMPLOYEES'
ORDER BY i.index_name,c.column_position;

LIKE8

As we can observe that the EMP_NAME_IX is compound index
which consists of two columns:
– LAST_NAME
– FIRST_NAME
in this order.
Now we want to find those people whose name starts with capital ‘K’:

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

like1
We can observe that Oracle used the index with INDEX RANGE SCAN operation which is generally is a good choice.
However, if we add another – similar – predicate into the WHERE clause:

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

like12

The optimizer has chosen  the FTS (Full Table Scan).
But when we put the indexed columns  into the column list only:

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

like13

The optimizer used the INDEX FULL SCAN operation!
What about if we want to find those people whose name contains a lower ‘k’?

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

like7

As you can see an FTS (Full Table Scan) was used by the optimizer, because
the the literal was inside the mask (‘%k%’) and all columns were projected.
However, if we are looking for the LAST_NAME first :

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

like9

we can see that the optimizer used the INDEX FULL SCAN
which is probably faster than the FTS.
Recommendation: If you know only substring of the name,
please use the INDEX FULL SCAN operation,
after – having the full names- you can retrieve all columns using
INDEX RANGE SCAN with ‘literal%’ template.
Observe that if we use the second (not prefixed) column of the index in the WHERE clause and put either the first or other components(only!) in the column list, the optimizer will use an INDEX FULL SCAN operation.

SELECT LAST_NAME FROM employees  WHERE  FIRST_NAME LIKE 'K%';

LIKE10

Generally we can say that using the LIKE operator with well formatted mask and putting the necessary column in the select list we have a good chance for using the suitable index(es).
Please do not use SUBSTR function instead of  LIKE operator even if it is applicable!
(Only if you have a function based index for that expression)

Advertisements

One Response to “I like the LIKE operator!”

  1. ravi Says:

    gud
    need more so can practise well! including pl/sql fully triggers also

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s


%d bloggers like this: