How to Tune SQL Statement with DECODE Expression for Oracle?

Discussion in 'Oracle' started by Richard To, Feb 1, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    48
    Likes Received:
    18
    Trophy Points:
    8
    Gender:
    Male
    Here the following is an example SQL statement with a DECODE expression syntax.

    select * from employee
    where decode(emp_dept, 'AAA', 'ADM','AAB','ACC',emp_dept)='ADM'

    Here the following are the query plans of this SQL, it takes 6.41 seconds to finish. The query shows a Full Table Scan of EMPLOYEE table due to the DECODE expression cannot utilize the EMP_DEPT column’s index.
    [​IMG]
    We can rewrite the DECODE expression into the following semantical equivalent SQL statement with multiple OR conditions.

    SELECT *
    FROM employee
    WHERE emp_dept = 'AAA' AND 'ADM' = 'ADM'
    OR NOT ( emp_dept = 'AAA' )
    AND emp_dept = 'AAB'
    AND 'ACC' = 'ADM'​
    OR NOT ( emp_dept = 'AAA' OR emp_dept = 'AAB' )
    AND emp_dept = 'ADM'​

    Here is the query plan of the rewritten SQL and the speed is 0.41 seconds. It is 15 times better than the original syntax. The new query plan shows a BITMAP OR of two INDEX RANGE SCAN of EMP_DEPT index.
    [​IMG]
    This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there are other rewrites 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/

    [​IMG]
     

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