SELECT b.wo_no, b.part_no, c.process, e.route_code input,
e1.route_code output, SUM (a.qty) inputqty,d.outputqty,
TO_CHAR (NVL (TRUNC (d.outputqty / SUM (a.qty), 2) * 100, 0) || '%') output_rate,
SUM (a.qty) - d.outputqty wip,
g.meaning org
FROM sfcs.sfcs_io_statistics a,
sfcs.wip_wo b,
sfcs.inv_pn_process c,
sfcs.sh_route_code e,
sfcs.sh_route_code e1,
sfcs.inv_pn f,
sfcs.sys_parameters g,
(SELECT SUM (a1.qty) outputqty, b1.wo_no, b1.part_no, c1.process
FROM sfcs.sfcs_io_statistics a1,
sfcs.wip_wo b1,
sfcs.inv_pn_process c1
WHERE a1.route_code = c1.o_ws_id
AND a1.TYPE = 'O'
AND a1.wo_key = b1.wo_key
AND c1.part_no = b1.part_no
AND a1.part_no = c1.part_no
GROUP BY b1.wo_no, b1.part_no, c1.process) d
WHERE a.route_code = c.i_ws_id
AND c.i_ws_id = e.routecode_key
AND c.o_ws_id = e1.routecode_key
AND a.TYPE = 'I'
AND a.wo_key = b.wo_key
AND c.part_no = b.part_no
AND a.part_no = c.part_no
AND c.part_no = f.part_no
AND g.lookup_type = 'ERP_ORG_TYPE'
AND f.attribute05 IN g.lookup_code
AND a.org_id = 4
GROUP BY b.wo_no,
b.part_no,
c.process,
e.route_code,
e1.route_code,
g.meaning,
d.outputqty
sfcs_io_statistics這里面有200W的記錄
wip_wo記錄為15000
inv_pn_process記錄4000
sh_route_code記錄150
inv_pn記錄1000
sys_parameters記錄2000現在這段代碼執行時間太長了。大概15分鐘。而且執行到最後總是把表空間TEMP用暴掉。我也知道這段代碼寫的相當不合理,但我就是改不好!研究了半個月了!希望有人能幫我優化一下!謝謝各位了!
e1.route_code output, SUM (a.qty) inputqty,d.outputqty,
TO_CHAR (NVL (TRUNC (d.outputqty / SUM (a.qty), 2) * 100, 0) || '%') output_rate,
SUM (a.qty) - d.outputqty wip,
g.meaning org
FROM sfcs.sfcs_io_statistics a,
sfcs.wip_wo b,
sfcs.inv_pn_process c,
sfcs.sh_route_code e,
sfcs.sh_route_code e1,
sfcs.inv_pn f,
sfcs.sys_parameters g,
(SELECT SUM (a1.qty) outputqty, b1.wo_no, b1.part_no, c1.process
FROM sfcs.sfcs_io_statistics a1,
sfcs.wip_wo b1,
sfcs.inv_pn_process c1
WHERE a1.route_code = c1.o_ws_id
AND a1.TYPE = 'O'
AND a1.wo_key = b1.wo_key
AND c1.part_no = b1.part_no
AND a1.part_no = c1.part_no
GROUP BY b1.wo_no, b1.part_no, c1.process) d
WHERE a.route_code = c.i_ws_id
AND c.i_ws_id = e.routecode_key
AND c.o_ws_id = e1.routecode_key
AND a.TYPE = 'I'
AND a.wo_key = b.wo_key
AND c.part_no = b.part_no
AND a.part_no = c.part_no
AND c.part_no = f.part_no
AND g.lookup_type = 'ERP_ORG_TYPE'
AND f.attribute05 IN g.lookup_code
AND a.org_id = 4
GROUP BY b.wo_no,
b.part_no,
c.process,
e.route_code,
e1.route_code,
g.meaning,
d.outputqty
sfcs_io_statistics這里面有200W的記錄
wip_wo記錄為15000
inv_pn_process記錄4000
sh_route_code記錄150
inv_pn記錄1000
sys_parameters記錄2000現在這段代碼執行時間太長了。大概15分鐘。而且執行到最後總是把表空間TEMP用暴掉。我也知道這段代碼寫的相當不合理,但我就是改不好!研究了半個月了!希望有人能幫我優化一下!謝謝各位了!
解决方案 »
- 条件 r.report_id = v.report_vessel_id(+)什么意思
- sequence的用法.
- DBMS_JOB的问题
- 【求助】客户端如何安装Oracle logminer工具?(条件都具备)
- oracle 在UNIX备份
- 各位,怎样得知TABLE中哪一个字段是主键 (Primary Key)
- 寻求10在线文档,鸟语的也可以
- 在一個表中重復查詢中一個fields對應多個value的問題 已解決一部分,請大家幫再看看!!!謝謝
- 回复必给分 ,在Oracle中如何查看连接过或正在连接数据库的用户电脑ip , hostname 以及用何种软件登录的,是以哪个用户名登入的??
- 有关字符串连接??请帮忙
- 紧急求一个表延迟更新的触发器
- 本地不装客户端怎么连接到远程的oracle数据库服务器上?急等
TO_CHAR (NVL (TRUNC (d.outputqty / SUM (a.qty), 2) * 100, 0) || '%') output_rate,
SUM (a.qty) - d.outputqty wip,
select somecol from table1 where con1 inner join on table1.col = table2.col where con2 inner join table3 on table2.col = table3.col where con3...