oracle效率问题,非常急
我用distinct去除重复项太慢,请问大家有什么好的办法。
select distinct jgbm,cfh from sjpt_cfls
where HANDLETIME between to_date('2012-04-01','yyyy-mm-dd') and to_date('2012-04-12','yyyy-mm-dd') and kss=2
其中主要是 cfh的字段值非常长,而且表里面数据有2000多万条。
如:
jgbm cfh
0004 041F074AE05M03H-12041000689
0010 0Q050749A05D13B-12040900355
0024 0O0I074BE2IE003-12041100100
0010 0Q050749A05D13B-12040900355
请问我应该如何写sql使他的执行效率变高
[解决办法]
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)
[解决办法]
这样呢
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
[解决办法]
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)
[解决办法]
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子句的末尾.
例如:
--(低效) 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’
[解决办法]
--假设主键或者唯一字段 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