查询条件还有t1.curr_time between to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss') 我没有写出来.我在数据库pl/sql上查询时发现,sql中"union"改为"union all",排序"order by curr_time desc"去掉以后速度就非常快了,在1秒之内,如果不改速度是三十多秒. 现在的需求是需要排序和去重的.
对于大数据去重和排序 一般用分析函数 如:select * from (select a, b, c, row_number() over(partition by a, b, c order by a, b, c) rw from tab) where rw = 1 减少磁盘排序,增加内存排序,性能提高
union 本身就是并集加去重的,肯定不能用了,你参考下:会不会快点select a.curr_time, a.sms_num from (select t.*, row_number() over(partition by t.curr_time, t.sms_num order by t.curr_time, t.sms_num) rw from (select t1.curr_time, t1.sms_num from itf_sms t1, bomc.tfa_alarm_his t2 where t1.alarm_id = t2.s_fp_id and t2.event_time between to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss') and t1.curr_time between to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss') union all select t1.curr_time, t1.sms_num from itf_sms_bak t1, bomc.tfa_alarm_his t2 where t1.alarm_id = t2.s_fp_id and t2.event_time between to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss') and t1.curr_time between to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss')) t) a where a.rw = 1 order by a.curr_time desc
to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and
to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss')
我没有写出来.我在数据库pl/sql上查询时发现,sql中"union"改为"union all",排序"order by curr_time desc"去掉以后速度就非常快了,在1秒之内,如果不改速度是三十多秒.
现在的需求是需要排序和去重的.
如:select *
from (select a,
b,
c,
row_number() over(partition by a, b, c order by a, b, c) rw
from tab)
where rw = 1
减少磁盘排序,增加内存排序,性能提高
请指教!谢谢啦
另外union 这个函数还需要用吗?
from (select t.*,
row_number() over(partition by t.curr_time, t.sms_num order by t.curr_time, t.sms_num) rw
from (select t1.curr_time, t1.sms_num
from itf_sms t1, bomc.tfa_alarm_his t2
where t1.alarm_id = t2.s_fp_id
and t2.event_time between
to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and
to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss')
and t1.curr_time between
to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and
to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss')
union all
select t1.curr_time, t1.sms_num
from itf_sms_bak t1, bomc.tfa_alarm_his t2
where t1.alarm_id = t2.s_fp_id
and t2.event_time between
to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and
to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss')
and t1.curr_time between
to_date('2014-02-14 10:21:52', 'YYYY-MM-DD hh24:mi:ss') and
to_date('2014-02-17 10:21:57', 'YYYY-MM-DD hh24:mi:ss')) t) a
where a.rw = 1
order by a.curr_time desc