How to Tune SQL Statement with CASE Express for Oracle?

Discussion in 'Oracle' started by Richard To, Nov 2, 2020.

  1. Richard To

    Richard To Member

    Sep 18, 2017
    Likes Received:
    Trophy Points:
    Here the following is a simple SQL statement with a CASE expression syntax.

    SELECT *
    FROM employee
    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.

Share This Page

  1. This site uses cookies to help personalise content, tailor your experience and to keep you logged in if you register.
    By continuing to use this site, you are consenting to our use of cookies.
    Dismiss Notice