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.64 seconds to finish. The query shows a Full Table Scan of the EMPLOYEE table due to the CASE expression cannot utilize the emp_salary index. It is because the CASE statement disabled the index range search of the emp_salary index.
Commonly, we will try to enable index search by forcing the SQL with an Index hint as the following:
SELECT /*+ INDEX(@SEL$1 EMPLOYEE) */ *
FROM employee
WHERE CASE
WHEN emp_salary < 1000
THEN ‘low’
WHEN emp_salary > 100000
THEN ‘high’
ELSE ‘Normal’
END = ‘low’
Although the CASE statement disabled the index range search of the emp_salary index, an index full scan is now enabled to help filter the result more quickly compared with the original full table scan of the EMPLOYEE table.
This hint injection takes 0.38 seconds and it is 12 times faster than the original SQL will full table scan. For this kind of SQL statement that you cannot change your source code, you can use SQL Patch with the hints and SQL text deployed to the database without the need of changing your source code.
If you can modify your source code, the best performance will be to 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’
The new query plan shows an INDEX RANGE SCAN OF emp_salary index.
This kind of rewrite and hints injection can be achieved by Tosska SQL Tuning Expert Pro for Oracle automatically,
https://tosska.com/tosska-sql-tuning-expert-pro-tse-pro-for-oracle/