Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.misc -> case statement and nested loops
Hi,
I'm trying to create a table by using three case statements in my
where clause. But the problem is that this query keeps running. I
did the explain plan which shows that I have nested loops instead of a
hash join. Is there a better way to write out this statement, maybe by
using the decode statement or splitting this up and using a union?
create table market.matches tablespace
as
select /*+ use_hash(a,b) */ count(distinct(a.emp_id))
FROM table a, table b
WHERE (case when (a.emp_id is null and b.emp_id is null) then 'Y'
when (a.emp_id = b.emp_id) then 'Y' else 'N' end ) = 'Y' and (case when (a.dept_no is null and b.dept_no is null) then 'Y' when (a.dept_no = b.dept_no) then 'Y' else 'N' end ) = 'Y' and (case when (a.phone_flg is null and b.phone_flg is null) then 'Y' when (a.phone_flg = b.phone_flg ) then 'Y' else 'N' end ) = 'Y';
thanks! Received on Wed Mar 06 2002 - 13:52:29 CST