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. 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. 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/
