今天帮同事tuning 了一条sql 语句,具体如下:
SELECT e.equip_id, h.pkg_id AS device
FROM rasenh e, raseqp f, rasdfo g, wip_dvcinf h,web_wipmag_macgroup i
WHERE e.factory = 'ASSY'
AND e.hist_seq = f.last_hist_seq
AND e.equip_id = f.equip_id
AND e.factory = f.factory
AND f.equip_group1 = g.equip_group
AND g.oper = '2550'
AND h.factory = 'ASSY'
AND h.mes_device = e.device
AND i.loc='SGN'
AND e.equip_id=i.machine_id
e,f,g,h表都很大,上G,大概百万、千万行的数据量。
i表很小,才1000行。
一看执行计划:
一看cost还可以,可是run了2分钟还是没有出来,仔细看了一下4个表,感觉rasenh不应该做nested loop。于是把sql改了一下,如下:
select equip_id,device from
(
select a.equip_id,a.device,i.loc,i.machine_id from
(
SELECT e.equip_id, h.pkg_id AS device
FROM rasenh e, raseqp f, rasdfo g, wip_dvcinf h
WHERE e.factory = 'ASSY'
AND e.hist_seq = f.last_hist_seq
AND e.equip_id = f.equip_id
AND e.factory = f.factory
AND f.equip_group1 = g.equip_group
AND g.oper = '2550'
AND h.factory = 'ASSY'
AND h.mes_device = e.device order by equip_id
)a,sccrpt.web_wipmag_macgroup i
) where equip_id=machine_id and loc='SGN'
执行计划如下:
看,一开始就用了hash join,这正是我想要的,一run果然3秒钟就出来了。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/9340716/viewspace-441882/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/9340716/viewspace-441882/