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') );
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') );
(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');
对sql语句,除了 增加索引,优化空间很小
谢谢你!你的优化速度提高5倍!