How to Tune SQL with LIKE '%ROGER%' SQL Statements for Oracle?

The LIKE is a logical operator that determines if a character string matches a specified pattern. A pattern may include regular characters and wildcard characters. The LIKE operator is used in the WHERE clause of the SELECT, UPDATE, and DELETE statements to filter rows based on pattern matching.

Here is an example SQL that retrieves data from EMPLOYEE table employee’s name with a string pattern like “ROGER%”. If the emp_name is indexed, the following SQL will utilize index of the emp_name and the speed of the SQL will be fine.

select *
from employee
where emp_name like 'ROGER%';

If user is looking for emp_name with pattern like '%ROGER%', Oracle SQL Optimizer cannot utilize emp_name index to speed up the process, full table scan is normally be used and the performance will be bad.

select *
from employee
where emp_name like '%ROGER%';

Here the following are the query plan of this SQL, it takes 5.88 seconds to finish. The query shows a “Full Table Scan” of employee table.

You can see that this SQL cannot utilize index scan even the emp_name is indexed. Let me add a hints /*+ INDEX(@SEL$1 EMPLOYEE) */ to the SQL and ask Oracle to use index to retrieve records.

SELECT /*+ INDEX(@SEL$1 EMPLOYEE) */ *
FROM employee
WHERE emp_name LIKE '%ROGER%'

Here is the query plan of the rewritten SQL and it is now running faster. The new query plan shows that an Index Full Scan is used although the estimated cost is higher than the Original SQL.

You can also use hints /*+ index(employee EMP_NAME_INX) */ explicitly, if the /*+ INDEX(@SEL$1 EMPLOYEE) */ hints doesn’t work for you.

SELECT /*+ index(employee EMP_NAME_INX) */ *
FROM employee
WHERE emp_name LIKE '%ROGER%'

This kind of rewrites can be achieved by Tosska SQL Tuning Expert for Oracle automatically, it shows that the rewrite is more than 2 times faster than the original SQL. There is a SQL hints injection with even better performance, it is a little bit complicated to discuss in this short article here. May be we can discuss later.

https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/