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