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. 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 filterINPUT_LIMIT IS NULL) of a full EMPLOYEE table scan. 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/
