日期:2014-05-17  浏览次数:20709 次

SQL语句优化问题
INSERT     INTO     msg(phone,message,date,state)    
                          select     usertel,msg,sysdate, 's '     from     fm,a     where     fm.status <> 10     and         fm.status <> 11    
and         fm.status <> 0     and     sysdate-interval     '10 '     hour     > =date     and     sysdate-interval     '30 '     hour     <     date    
and     fm.nbr     not     in(select     nbr     from     history     where     nbr=fm.nbr    
and     (status=1     OR     status=2)     and     (DS= 'A '     OR     DS= 'B '     OR     DS= 'C '     OR     DS= 'D '))    
and     ((fm.id <> '50 '     and     fm.id=a.ID     and     a.st= 'A ')    
or(fm.id= '50 '     and     fm.doid=a.ID         and     a.st= 'A ')     );  

加了这个条件变得非常慢,用游标循环都比这个快,这个有没办法优化,哪能优化都行?
and     ((fm.id <> '50 '     and     fm.id=a.ID     and     a.st= 'A ')    
or(fm.id= '50 '     and     fm.doid=a.ID         and     a.st= 'A ')     );

------解决方案--------------------
如果只是考虑优化最后两句的话,下面应该是可以比较好的,没有数据,没试过,LZ可以试试

INSERT INTO msg
(phone, MESSAGE, DATE, state)
SELECT usertel, msg, SYSDATE, 's '
FROM fm, a
WHERE fm.status <> 10
AND fm.status <> 11
AND fm.status <> 0
AND SYSDATE - INTERVAL '10 ' HOUR > = DATE
AND SYSDATE - INTERVAL '30 ' HOUR < DATE
AND fm.nbr NOT IN (
SELECT nbr
FROM history
WHERE nbr = fm.nbr
AND (status = 1 OR status = 2)
AND (ds = 'A ' OR ds = 'B ' OR ds = 'C ' OR ds = 'D '))
AND (DECODE (fm.ID, '50 ', fm.odid, fm.ID) = a.ID AND a.st = 'A ');