ORACLE: CASE in WHERE clause taking more time for result

Question

Employee Table has 100k data.

Select emp.* 
from Employee emp 
  inner join Department dept 
     on dept.Dept_no = (case when emp.Dept_NO= 11 then (select Id from CONSTANT where costant_name = 'ABC' )
                             else emp.Dept_NO end );

This query is taking too much time to run. How to make this query will run fast?

Answer 1

To avoid select within case, cross join with constant might help. Though, I guess it (the constant table) is small and returns only one row for this condition so that's fast enough anyway.

SELECT e.*
  FROM employee e
       CROSS JOIN constant c
       JOIN department d
          ON d.dept_no =
                CASE WHEN e.dept_no = 11 THEN c.id ELSE e.dept_no END
 WHERE c.constant_name = 'ABC';

Meanwhile, check whether columns involved into joining operation (dept_no) are indexed.

Explain plan might reveal some useful info.