How to Tune SQL Statement with CASE Expression by Hints Injection for Oracle?

Discussion in 'Oracle' started by Richard To, Oct 10, 2022.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    75
    Likes Received:
    30
    Trophy Points:
    18
    Gender:
    Male
    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.
    [​IMG]
    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.
    [​IMG]

    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.

    [​IMG]
    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/
     
    shabbir likes this.

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