SQL 語句優化

Question

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用暴掉。我也知道這段代碼寫的相當不合理，但我就是改不好！研究了半個月了！希望有人能幫我優化一下！謝謝各位了！

Answer 1

group by 项目太多(建立相关索引),把g.lookup_type = 'ERP_ORG_TYPE' a.用(select fieldlist from sfcs.sfcs_io_statistics where TYPE = 'I' and org_id = 4)代替from 后面的sfcs.sfcs_io_statistics 

Answer 2

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 select fieldlist from sfcs.sfcs_io_statistics where TYPE = 'I' and org_id = 4) a,
         sfcs.wip_wo b,
         sfcs.inv_pn_process c,
         sfcs.sh_route_code e,
         sfcs.sh_route_code e1,
         sfcs.inv_pn f,
         (select * from sfcs.sys_parameters where lookup_type = 'ERP_ORG_TYPE' ) 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.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 f.attribute05 IN g.lookup_code
GROUP BY b.wo_no,
         b.part_no,
         c.process,
         e.route_code,
         e1.route_code,
         g.meaning,
         d.outputqty

Answer 3

语句不算复杂啦, 不过 inner join 都用 where 去写是不好的该用临时表的用以上慢的问题是多方面的, 数据库本身优化也是其中之一, 只能说说而已, 实际还是靠自已确定关联是主键外键关系很重要, 没建关系那就没得说了, 那种不用于条件的主键(自动id比方)就不要建成簇式索引, 簇式索引应该是那种最常用于关联的有时100%用于关联用的多级索引反而更应该是簇式索引, 这时写语句时就要注意inner join条件的顺序(不是所有数据库都会识别顺序的), 这样就要求整个程序的 sql 作者在写代码时对设计是熟悉的, 这样看区分编码员和设计师似乎是不可能的, 质量是每个参与的人200w 算是记录很少了, 总得说 20亿(8亿mssql可能是极限, oracle也多不少哪去)记录跟 200w 在关联建得完整的数据库中查询时间不应大于 2-3 s 的区别

Answer 4

同意WuChenCan(雨中男孩)用并行查询会好些.
把g.lookup_type = 'ERP_ORG_TYPE' a.用(select fieldlist from sfcs.sfcs_io_statistics where TYPE = 'I' and org_id = 4)代替from 后面的sfcs.sfcs_io_statistics