Oracle query execution in where clause

We are trying to execute a select query which uses a column name in the case statement and if the same column name is used in where clause it is going into an infinite loop.

eg

select empId,empName,
        (case when empDept in ('A','B','C') then empAge
              when empDept in ('E','F','G') then empExp 
              else 'Dept-Not found' end) 
        from employee  where empDept in ('A','B','C','D','E','F','G')

It does not matter even if we put or clause in the where cla开发者_运维百科use instead of in.

EDIT:Edited the Query

clearly there is something else going on. The WHERE clause is evaluated before the SELECT (column list) clause, there is no way it could produce an infinite loop.

Consider (10.2.0.1):

SQL> CREATE TABLE employee AS
  2  SELECT 1 empId, 'e1' empName, 1 empAge, 10 empExp, 'A' empDept FROM dual
  3  UNION ALL SELECT 2, 'e2', 2, 9, 'B' FROM dual
  4  UNION ALL SELECT 3, 'e3', 3, 8, 'C' FROM dual
  5  UNION ALL SELECT 4, 'e4', 4, 7, 'D' FROM dual
  6  UNION ALL SELECT 5, 'e5', 5, 6, 'E' FROM dual
  7  UNION ALL SELECT 6, 'e6', 6, 5, 'F' FROM dual;

Table created

SQL> select empId,empName,
  2          (case when empDept in ('A','B','C') then to_char(empAge)
  3                when empDept in ('E','F','G') then to_char(empExp)
  4                else 'Dept-Not found' end)
  5          from employee  where empDept in ('A','B','C','D','E','F','G');

     EMPID EMPNAME (CASEWHENEMPDEPTIN('A','B','C'
---------- ------- ----------------------------------------
         1 e1      1
         2 e2      2
         3 e3      3
         4 e4      Dept-Not found
         5 e5      6
         6 e6      5

As you can see in my example I had to add a to_char to your case expressions because all results from a case must have the same type. Without the to_char, in my case I obtained an ORA-00932. Maybe your tool hangs when a query returns with an error?

As a pretty wild guess based on the little information you've given, I would say that without the WHERE clause the query is doing a full table scan, but when you add the WHERE clause it switches to an index range scan. If records for the six departments you are interested in are spread throughout the table, it is very possible that an index range scan will be much less efficient than a table scan.

One possible cause of this is that statistics on the table and index are stale.

In any case, your first step when diagnosing a SQL performance problem should be to look at the execution plan.