oracle效率有关问题，非常

select distinct jgbm,cfh from sjpt_cfls  where HANDLETIME a between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2 and EXISTS ( SELECT 1 FROM  HANDLETIME b  WHERE a.jgbm = b.jgbm)
------解决方案--------------------
这样呢

SQL code
select jgbm,cfh,count(*) from sjpt_cfls  where HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2 group by jgbm,cfh having count(*) = 1
------解决方案--------------------
SQL code
select jgbm,cfh from sjpt_cfls  where HANDLETIME a between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2 and EXISTS ( SELECT 1 FROM  HANDLETIME b  WHERE a.jgbm = b.jgbm)
------解决方案--------------------
SQL code
 select jgbm,cfh  FROM sjpt_cfls a where exists (select 1 from sjpt_cfls b where a.jgbm=b.jgbm) and         HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2
------解决方案--------------------
ORACLE采用自下而上的顺序解析WHERE子句,根据这个原理,表之间的连接必须写在其他WHERE条件之前, 那些可以过滤掉最大数量记录的条件必须写在WHERE子句的末尾.  

例如:  

　
SQL code
　 --(低效) 　　SELECT … FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO); 　--(高效) 　　SELECT … FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’
------解决方案--------------------
探讨

引用:
SQL code


select jgbm,cfh
FROM sjpt_cfls a
where exists (select 1 from sjpt_cfls b where a.jgbm=b.jgbm) and
HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2


……

------解决方案--------------------
用in看效率如何

SQL code
--假设主键或者唯一字段 s_id select jgbm,cfh  FROM sjpt_cfls  where s_id in (select max(s_id) from sjpt_cfls group by jgbm,cfh) and         HANDLETIME between date'2012-04-01' and date'2012-04-12' and kss=2