How to Tune SQL Statement with CASE Express for Oracle?

Here the following is a simple SQL statement with a CASE expression syntax.

SELECT *
FROM employee
WHERE
CASE

WHEN emp_salary< 1000

THEN 'low'​

WHEN emp_salary>100000

THEN 'high'​

ELSE 'Normal'​

END = 'low';

Here the following are the query plans of this SQL, it takes 4.53 seconds to finish. The query shows a Full Table Scan of EMPLOYEE table due the CASE expression cannot utilize the emp_salary index.

We can rewrite the CASE expression into the following syntax with multiple OR conditions.

SELECT *
FROM employee
WHERE emp_salary < 1000 AND 'low' = 'low'

OR NOT ( emp_salary < 1000 ) AND emp_salary > 100000 AND 'high' = 'low'
OR NOT ( emp_salary < 1000 OR emp_salary > 100000 ) AND 'Normal' = 'low'​

Here is the query plan of the rewritten SQL and the speed is 0.10 seconds. It is 45 time better than the original syntax. The new query plan shows an INDEX RANGE SCAN OF emp_salary index.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrite with even better performance, but it is not suitable to discuss in the short article, maybe I can discuss later in my blog.

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