select t.company company,
t.syncdomain syncdomain,
sum(case when t.subchannel_1 > 0 or t.subchannel_2 > 0 or t.subchannel_3 > 0 or t.subchannel_4 > 0 or t.subchannel_5 > 0 or t.subchannel_6 > 0 or t.subchannel_7 > 0 or t.subchannel_8 > 0 then 1 else 0 end) subchannel,
(nvl(sum(t.basesalary), 0) + nvl(sum(t.posisalary), 0) +
nvl(sum(t.perfsalary), 0) + nvl(sum(t.assisalary), 0) +
nvl(sum(t.overtimesalary), 0) + nvl(sum(t.othersalary), 0) +
nvl(sum(t.bonus), 0)) sumvalue,
(nvl(sum(t.basesalary), 0) + nvl(sum(t.posisalary), 0) +
nvl(sum(t.perfsalary), 0) + nvl(sum(t.assisalary), 0)) wage,
(nvl(sum(t.overtimesalary), 0) + nvl(sum(t.othersalary), 0) +
nvl(sum(t.bonus), 0)) prize
from emp_sala_safe_annu_syn t, --此表中数据有八百多万
(select c.id from org_company_syn c where c.validto > sysdate and c.activeflag = 1 start with c.id = '3' connect by prior c.id = c.parent) c, --这一条语句最好不要变
Bi_Empinfo bi --此表数据八十多万
where t.company = c.id
and t.employee = bi.employee
and t.year = '2011'
and t.month in (1)
group by t.company, t.syncdomain
----我用到了emp_sala_safe_annu_syn 表中的年和月 联合索引 建了个syncdomain
索引没用上 Bi_Empinfo也有索引 但似乎都没用上 执行计划显示耗费57947 执行出来结果需要2到三分钟不等 月份多的话可能更久
------解决方案--------------------
t.company, t.syncdomain 瓶颈在这里，这里消耗大。
注意，from 后面大表在前面，where后能过滤大量数据的条件在最后
------解决方案--------------------
1你要是能够先整理下t表的数据该多好啊
把为null的先初始化0
所有的nvl都去掉了
2 and t.year = '2011'
and t.month in (1)
时间到底是字符还是数字 别让数据库自己做转换啦
3好像看到bi表格了 呵呵 如果查询实在多 考虑做个中等粒度的聚合表吧
------解决方案--------------------
自己多学习啊 索引并不是建的越多就越好的
------解决方案--------------------
select
t.company company
,t.syncdomain syncdomain
,sum(case wehn t.subchannel_1
+t.subchannel_2
+t.subchannel_3
+t.subchannel_4
+t.subchannel_5
+t.subchannel_6
+t.subchannel_7
+t.subchannel_8
then 1 else 0
end) subchannel
,nvl(sum(t.basesalary+t.posisalary+t.perfsalary+t.assisalary+t.overtimesalary+t.othersalary+t.bonus,0) sumvalue
,(nvl(sum(t.basesalary+t.posisalary+t.perfsalaryt.assisalary), 0) wage
,nvl(sum(t.overtimesalaryt.othersalary+t.bonus), 0) prize
from emp_sala_safe_annu_syn t, --此表中数据有八百多万
(select c.id from org_company_syn c where c.validto > sysdate and c.activeflag = 1 start with c.id = '3' connect by prior c.id = c.parent) c, --这一条语句最好不要变
Bi_Empinfo bi --此表数据八十多万
where t.company = c.id
and t.employee = bi.employee
and t.year = '2011'
and t.month in (1)
group by t.company, t.syncdomain
------解决方案--------------------
,sum(case wehn t.subchannel_1
+t.subchannel_2
+t.subchannel_3
+t.subchannel_4
+t.subchannel_5
+t.subchannel_6
+t.subchannel_7
+t.subchannel_8
>=1
then 1 else 0
end) subchannel
忘记写>=1了
------解决方案--------------------
索引，我是这么理解的。
比如说我们查字典的时候，怎么才能快速查找到自己想要的汉字呢？大概有以下几种：
1，按照字典一页一页查，
2，按照字典前面的导航页，也就是字母顺序来查，