SQL Tuning: String Functions in the WHERE clause

2:15 AM database, oracle No Comments

This is the Oracle SQL Performance Tuning tips and trick from Mas FF. Say we have a SQL like this,

SELECT *
FROM tabel t
WHERE LOWER(t.kol)
LIKE ‘%hehe%’ AND SUBSTR(t.kol2,2,4) = ‘AB’

Look familiar? This is a standard query kok. But do you know the implications and substr LOWER function in velocity problem? Apparently the LOWER and substr is to lower-case an entire row in the column and t.kol substring to an entire row in the column before comparing it with t.kol2 hehe and AB. If the number of line too much, of course, speed degradation will occur very significant (read: insignificant).

Solution: Use the sort function and substr LOWER wisely. If you are sure of a column in lowercase, then the right input (in this case hehe) should be changed to lowercase as well. Similarly to substr function.

Tips and tricks so short SQL Tuning. : D