现在的项目中涉及到的数据量非常大,月数据量达到了一百万条以上,做报表时需要对这些数据进行遍历,然后统计计算出来的结果,但是当数据太大的时候速度会很慢,请问各位大侠有没有什么好的办法解决这种查询效率的问题。小弟不胜感激。
下面贴出部分sql语句,各位看看可以在优化下不?希望大家能够给出意见,有理即给分,谢谢各位了!
部分sql语句如下:select a.bname, nvl(b.c1, 0), nvl(round(b.c2, 4), 0)
from (select b.name bname from t_branch b where b.level_code != 3) a
left join (select distinct (s.branch_name),
sum(s.config_count) c2,
sum(s.main_count) c1
from t_statistic s
where s.statistic_project_id = 1
and s.tache_id = '2686'
and s.FLOW_ID in (225, 226)
group by s.branch_name) b on a.bname = b.branch_name
order by a.bname
下面贴出部分sql语句,各位看看可以在优化下不?希望大家能够给出意见,有理即给分,谢谢各位了!
部分sql语句如下:select a.bname, nvl(b.c1, 0), nvl(round(b.c2, 4), 0)
from (select b.name bname from t_branch b where b.level_code != 3) a
left join (select distinct (s.branch_name),
sum(s.config_count) c2,
sum(s.main_count) c1
from t_statistic s
where s.statistic_project_id = 1
and s.tache_id = '2686'
and s.FLOW_ID in (225, 226)
group by s.branch_name) b on a.bname = b.branch_name
order by a.bname
据说in比or 慢,你测试一下看看有没有效果;
2、字段左边尽量不要用函数,尤其在where后面的“=”左边的字段,
如果t_branch 表里的C1和C2均不会为null的话,则去年nvl函数。
sum(s.config_count) c2,
sum(s.main_count) c1
from t_statistic s
where s.statistic_project_id = 1
and s.tache_id = '2686'
and s.FLOW_ID in (225, 226)
group by s.branch_name做成物化视图,试一试,应该会快点
select distinct (s.branch_name),
nvl(round(sum(s.config_count),4),0) c2,
nvl(sum(s.main_count),0) c1
from t_statistic s
where exists(select 1 from t_branch b where b.name=s.branch_name and b.level_code<>3)
and s.statistic_project_id = 1
and s.tache_id = '2686'
and (s.FLOW_ID=225 or s.FLOW_ID=226)
group by s.branch_name
order by s.branch_name
select distinct (s.branch_name),
nvl(round(sum(s.config_count),4),0) c2,
nvl(sum(s.main_count),0) c1
from t_statistic s
where exists(select 1 from t_branch b where b.name=s.branch_name and b.level_code<>3)
and s.statistic_project_id = 1
and s.tache_id = '2686'
and (s.FLOW_ID=225 or s.FLOW_ID=226)
group by s.branch_name
order by s.branch_name
如果是字段是字符型,乖乖的给加上引号吧
另外,上面说的,加提示符,是开启并行查询,这要看服务器性能了,不是随便开多少都可以的。但总体来说,还是会提高性能的。最后,把执行计划帖出来吧,不然不太好分析。 第一感觉,就是表设计可能存在问题,是否可以加入索引来提高查询性能。
2.sql语句用大写的;因为oracle总是先解析sql语句,把小写的字母转换成大写的再执行。
另外对于在此处用左连接是否妥当,请lz考虑考虑。
些许拙见,不对的还望指出。共同学习。
既然已经根据 s.branch_name 进行了分组,为什么还要添加 distinct 呢,这完全是画蛇添足,
去掉 distinct 再看下执行计划