How to Tune SQL Statement with “Or Bind Variable is Null” Expression for Oracle?

Discussion in 'Oracle' started by Richard To, Mar 16, 2021.

  1. Richard To

    Richard To Member

    Joined:
    Sep 18, 2017
    Messages:
    64
    Likes Received:
    21
    Trophy Points:
    8
    Gender:
    Male
    Here the following is an example SQL statement with an “Or Bind Variable is Null” expression syntax.

    SELECT * FROM EMPLOYEE
    WHERE (EMP_ID<:INPUT_LIMIT OR :INPUT_LIMIT IS NULL)

    It is quite a common usage of using bind variables as a user’s input range to select a specific number of records from a table, a NULL or a specific input value may be used to indicate no limit is set and everything will be retrieved.

    Here the following are the query plans of this SQL, it takes 4.82 seconds to finish. The query shows a Full Table Scan of the EMPLOYEE table due to the OR expression cannot utilize the EMP_ID column’s index.
    [​IMG]
    We can rewrite the OR expression into the following semantical equivalent SQL statement with a UNION ALL operation. Basically, the UNION ALL split the OR operation into two parts, the first part returns the data with an index range scan that emp_id<:INPUT_LIMIT if :INPUT_LIMIT is not null, the second part returns all records if :INPUT_LIMIT is null.


    SELECT *
    FROM employee
    WHERE emp_id < :INPUT_LIMIT
    union all
    SELECT *
    FROM employee
    WHERE Lnnvl(emp_id < :INPUT_LIMIT)
    AND :INPUT_LIMIT IS NULL

    Here is the query plan of the rewritten SQL and the speed is 0.14 seconds. It is 34 times better than the original syntax. The new query plan shows a UNION ALL of INDEX RANGE SCAN of EMP_ID index and a predetermined filter:)INPUT_LIMIT IS NULL) of a full EMPLOYEE table scan.
    [​IMG]
    This kind of rewrite can be achieved by Tosska SQL Tuning Expert for Oracle automatically, there may be other rewrites with even better performance with a higher Rewrite Intelligent Level setting, 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