select c.tskillgrp_code, nvl(sum(case when b.composite_timeout='0' and b.handle_timeout = '0' and b.reply_timeout = '0' and b.archive_timeout = '0' then 1 end),0) notTimeoutCount,nvl(sum(case when b.composite_timeout = '1' then 1 else 0 end),0) compositeCount,nvl(sum(case when b.handle_timeout = '1' then 1 else 0 end),0) handleCount,nvl(sum(case when b.sreply_timeout = '1' then 1 else 0 end),0) replyCount,nvl(sum(case when b.archive_timeout = '1' then 1 else 0 end),0) archiveCount,nvl(count(t.case_no),0) allCount from table_one t,table_two b,table_three c where t.case_no = b.case_no and t.case_no = c.case_no and c.deal_flag = '3' group by c.tskillgrp_code每个表中都有三百万以上的数据,所以关联查询起来相当慢,所以请帮忙优化一下,谢谢!
解决方案 »
- 怎么取前十条数据!
- 小弟大四学生 奉老师之命帮他代上一节Oracle 10g的课 关于事务处理和并发控制
- sqlldr 数据被replace掉的问题
- 如何判断某字符串中是否含有TAB和回车?
- 怎样能够手动控制ORACLE的启动!
- oracle数据库存储过程里是否可以包含 alter system语句!!急!!! 望高手给予解答.
- 100分求解!!参与有分!!!
- 关于logmnr的问题
- 在INTERNET网络上安装ORACLE?
- 请教!用何种方式处理这样的大量查询。。。。
- ORACLE数据库中sqlldr.exe 工具可以提取出来独立使用么?把与之关联的DLL等文件提取出来,该怎么做?
- ORACLE 性能优化中文化材料
select c.tskillgrp_code,
nvl(sum(case
when b.composite_timeout = '0' and b.handle_timeout = '0' and
b.reply_timeout = '0' and b.archive_timeout = '0' then
1
end),
0) notTimeoutCount,
nvl(sum(case
when b.composite_timeout = '1' then
1
else
0
end),
0) compositeCount,
nvl(sum(case
when b.handle_timeout = '1' then
1
else
0
end),
0) handleCount,
nvl(sum(case
when b.sreply_timeout = '1' then
1
else
0
end),
0) replyCount,
nvl(sum(case
when b.archive_timeout = '1' then
1
else
0
end),
0) archiveCount,
nvl(count(t.case_no), 0) allCount
from table_one t, table_two b, table_three c
where t.case_no = b.case_no
and t.case_no = c.case_no
and c.deal_flag = '3'
group by c.tskillgrp_code能精简的精简,该建索引的建索引
select d.tskillgrp_code,
sum(notTimeout) notTimeoutCount,
sum(composite) compositeCount,
sum(handleCount) handleCount,
sum(reply) replyCount,
sum(archive) archiveCount,
count(t.case_no) allCount
from (select c.tskillgrp_code,
case
when b.composite_timeout = '0' and b.handle_timeout = '0' and
b.reply_timeout = '' 0
'' and b.archive_timeout = '' 0 '' then
1
end notTimeout,
case
when b.composite_timeout = '1' then
1
else
0
end composite,
case
when b.handle_timeout = '' 1 '' then
1
else
0
end handle,
case
when b.sreply_timeout = '' 1 '' then
1
else
0
end reply,
case
when b.archive_timeout = '1' then
1
else
0
end archive,
t.case_no
from table_one t, table_two b, table_three c
where t.case_no = b.case_no
and t.case_no = c.case_no
and c.deal_flag = '3') d
group by d.tskillgrp_code这个你试试不过效果也不一定明显你也可以从sga,pga方向看看内存上的问题
case_no上建立索引是肯定的了
其他第一感觉不需要
还是慢的话,拉执行计划及awr报告出来
换成decode效果也不太明显,主要的原因是表内的数据量太大了!谢谢