试下,这样执行时间select distinct a.* from (select t.recordid,
t.suid,
t.distance,
t.empty_distance,
t.price
from counter_single t
where t.recordid >0
and t.utc >= 1109606400
and t.utc <= 1113953400) a
t.suid,
t.distance,
t.empty_distance,
t.price
from counter_single t
where t.recordid >0
and t.utc >= 1109606400
and t.utc <= 1113953400) a
t.suid,
t.distance,
t.empty_distance,
t.price
from counter_single t
where t.recordid >0
and t.utc >= 1109606400
and t.utc <= 1113953400 group by t.recordid,
t.suid,
t.distance,
t.empty_distance,
t.price) a我不知道是否可以改善,你可以试试
试试这个
不知道你想完成什么功能,
select t.recordid,
t.suid,
t.distance,
t.empty_distance,
t.price
from counter_single t
where t.recordid >0
and t.rowid=(select max(rowid) from counter_single b
where t.recordid=b.recordid
and t.suid = b.suid
and t.distance=b.distance
and t.empty_distance=b.empty_distance
and t.price=b.price
)
and t.utc >= 1109606400
and t.utc <= 1113953400
where t.recordid >0
and t.utc >= 1109606400
and t.utc <= 1113953400group by t.recordid,
t.suid,
t.distance,
t.empty_distance,
t.price)
对于这样的海量数据,要想解决效率问题,可能就要另想办法了,
建一个实体化视图(物化视图)的话,我想能满足你的要求,
在基础表插入,删除,更新时自动更新视图,
不过这就要对插入,删除,更新操作带来负面的影响.
鱼和熊掌....只有看你怎么取舍了.
t.suid,
t.distance,
t.empty_distance,
t.price
from counter_single t
where t.recordid >0
and t.rowid=(select max(rowid) from counter_single b
where t.recordid=b.recordid
and t.suid = b.suid
and t.distance=b.distance
and t.empty_distance=b.empty_distance
and t.price=b.price
)
and t.utc >= 1109606400
and t.utc <= 1113953400
不知道你想去掉那列或那几列的重复数据?
如果是想去掉五列的重复数据,那就忍了吧!
谁说distinct不会进行索引扫描了?我支持 railgunman(堕落男人) 的建议!另外楼主,请把你的执行计划以及帖出来.以及oracle版本号.
为了方便大家帮你看.
另外,distinct是可以利用索引的。它是先利用索引,然后再在结果视图中排除重复。