现有一表,千万级
需要这样查询
select TO_CHAR(V_TIME,'YYYY-MM-DD')
count(*),count(distinct ip),count(distinct c_id) from a
group by TO_CHAR(V_TIME,'YYYY-MM-DD');
执行了11s,太慢了,有什么办法可以解决?
需要这样查询
select TO_CHAR(V_TIME,'YYYY-MM-DD')
count(*),count(distinct ip),count(distinct c_id) from a
group by TO_CHAR(V_TIME,'YYYY-MM-DD');
执行了11s,太慢了,有什么办法可以解决?
count(*),count(distinct ip),count(distinct c_id) from a
group by trunc(V_TIME);
替换掉
在a表上建立:v_time,ip,c_id的索引。
select TO_CHAR(V_TIME,'YYYY-MM-DD')
count(*),count(distinct ip),count(distinct c_id) from a
where V_TIME between ··and···
group by TO_CHAR(V_TIME,'YYYY-MM-DD');
不嫌麻烦的话,你就建个物化视图,指定 on commit方式。把统计压力扔给数据库去。