語句優化！
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用暴掉。我也知道這段代碼寫的相當不合理，但我就是改不好！研究了半個月了！
希望有人能幫我優化一下！謝謝各位了！
[解决办法]
可不可以先对这部分进行计算再与哪几个表进行关联，我看这里只用到了a,b这两个表
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,
[解决办法]
f和g这两张表，你试试把他们作为子查询表单独出来，求出结果再与其他表关联
[解决办法]
非的用一条语句吗?
建议使用临时表或者中间表,先计算出子查询或者过程记录集,然后再通过关联计算数据
[解决办法]
這種寫法不好，應該用INNER JOIN ON或LEFT OUTER JOIN ON 這樣比較清晰。然後應該現在一個表中檢索，減少數據量后再和下一個表進行連接，所以要用前面提到的標準語法。
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...
[解决办法]
里面的那个GROUP BY子查询重复了, 耗时不少
