Posted on

How to Tune SQL Statement with CASE Expression for PostgreSQL ?

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’
       END = :a

The following are the query plans for this SQL statement, which completes in 1.01 seconds with the bind variable ‘:a’  equal to ’low’. The query involves a Full Table Scan of the EMPLOYEE table because the CASE expression cannot utilize the emp_salary index.

We can refactor the CASE expression into the following syntax using multiple OR conditions

select *
  from employee
 where (emp_salary < 1000 and
        ‘low’ = :a or
        ((not (emp_salary < 1000)) or
         emp_salary is null) and
        emp_salary > 100000 and
        ‘high’ = :a)

Rewriting multiple OR conditions with an AND operation (e.g., ‘low’ = :a) can efficiently disable unnecessary data searches on the EMPLOYEE table.

Here is the query plan of the rewritten SQL and the speed is 0.005 seconds. It is around 200 times better than the original syntax. The new query plan shows an Bitmap Index Scan on emp_salary index.

This kind of rewrite can be achieved by Tosska SQL Tuning Expert for PostgreSQL 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.

Tosska SQL Tuning Expert Pro for PostgreSQL – Tosska Technologies Limited

Posted on

How to Tune SQL Statement with CASE Expression by Hints Injection 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.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/