是优化高手的进来优化下这个SQL

Question

一个批量更新的SQL，发现执行起来很慢。
不知道咋优化了，有知道的帮忙优化下，实在感谢。
SQL如下：
这个是一个需要定时更新某参与数的SQL：update web_business_bulletin b set b.quotenum=" +
         "(case " +
         " when b.bulletinType in (1, 2) " +
         " THEN (select count(distinct seller_id) " +
         " from bid_quote_tables n where n.project_id = b.projectid  and n.status >= 5)" +
         " when b.bulletinType in (30, 31) " +
         " THEN (select count(distinct selleruserid) from expr_bid_quotetable m where m.projectid = b.projectid) " +
         " end) " +
         " where b.itemState = 1 and b.bulletinType in (1, 2,30, 31)

Answer 1

这种情况如果不是特别要求一条SQL 完成，完全可以分两次Update,避免多次
b.bulletinType in (）的操作

Answer 2

看上去这个sql不需要什么参数啊，又是定时更新。这样的话
你建个存储过程，开个oracle job，还省得你写代码了。

Answer 3

存储过程里面分两条来执行，in 改成 or ,有必要的话，where条件里面的列加上索引。

Answer 4

分2条执行：update web_business_bulletin b
   set b.quotenum = (select count(distinct seller_id)
                       from bid_quote_tables n
                      where n.project_id = b.projectid
                        and n.status >= 5)
 where b.itemState = 1
   and b.bulletinType in (1, 2);
   
update web_business_bulletin b
   set b.quotenum = (select count(distinct selleruserid)
                       from expr_bid_quotetable m
                      where m.projectid = b.projectid)
 where b.itemState = 1
   and b.bulletinType in (30, 31);
貌似还是一样慢

Answer 5

存储过程你就可以拆开分两条sql了啊，而且可以改sql啊,还省了你代码用thread去控制的麻烦，不更好吗？存储过程里面分两条来执行，in 改成 or ,有必要的话，where条件里面的列加上索引。

Answer 6

select count(distinct seller_id)
                       from bid_quote_tables n
                      where n.project_id = b.projectid
                        and n.status >= 5
这句慢不慢？再说下，你在存储过程里面，就可以先把这个搜出来啦，免得你update半天都把表锁住用来select了，多好。

Answer 7

谢谢楼上的，差不多明白你的意思了。这句查询稍微有点慢。0.3吧！把Distinct去掉，快2倍。现在关键问题来了。
我不会写存储过程！
呵呵...
得先试试。
唉...

Answer 8

distinct 不能去啊，去了你的业务不对了就这句SQL？proc和job我给你写得了，然后你再看。＝＝啊。

Answer 9

create or replace procedure upd_wbb_quotenum 
as
o_cur sys_refcursor;
v_projectid bid_quote_tables.project_id%TYPE;
v_count web_business_bulletin.quotenum%TYPE;
v_m_pid expr_bid_quotetable.projectid%TYPE;
begin
--找出第一个sql需要的count及projectid
open o_cur for 
select project_id,count(distinct seller_id)
from bid_quote_tables 
where status >= 5
group by project_id; loop
fetch o_cur into v_projectid,v_count;
exit when o_cur%NOTFOUND;
--修改
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 1 or  bulletinType = 2)
and projectid = v_projectid;
--没找到，则啥也不干。
exception when sql%NOTFOUND then
null;
end if;
end loop;
close o_cur; --找出第二个SQL需要的count及projectid
open o_cur for
select projectid,count(distinct selleruserid)
from expr_bid_quotetable
group by projectid; loop
fetch o_cur into v_m_pid,v_count;
exit when o_cur%NOTFOUND;
--修改
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 30 or  bulletinType = 31)
and projectid = v_m_pid;
--没找到，则啥也不干。
exception when sql%NOTFOUND then
null;
end if;
end loop;
close o_cur;
commit; exception when others then
raise;
end upd_wbb_quotenum;
/
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
 'upd_wbb_quotenum;',
SYSDATE,'SYSDATE + 30/60/60/24'); --这里的时间间隔是30秒。需要多长执行一次，把这个变成一个分数就好了。
commit;
end;
/

Answer 10

在plsqldev或toad或sqlplus中执行。create or replace procedure upd_wbb_quotenum 
as
o_cur sys_refcursor;
v_projectid bid_quote_tables.project_id%TYPE;
v_count web_business_bulletin.quotenum%TYPE;
v_m_pid expr_bid_quotetable.projectid%TYPE;
begin
--找出第一个sql需要的count及projectid
open o_cur for 
select project_id,count(distinct seller_id)
from bid_quote_tables 
where status >= 5
group by project_id; loop
fetch o_cur into v_projectid,v_count;
exit when o_cur%NOTFOUND;
--修改
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 1 or  bulletinType = 2)
and projectid = v_projectid;
--没找到，则啥也不干。
exception when sql%NOTFOUND then
null;
end if;
end loop;
close o_cur; --找出第二个SQL需要的count及projectid
open o_cur for
select projectid,count(distinct selleruserid)
from expr_bid_quotetable
group by projectid; loop
fetch o_cur into v_m_pid,v_count;
exit when o_cur%NOTFOUND;
--修改
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 30 or  bulletinType = 31)
and projectid = v_m_pid;
--没找到，则啥也不干。
exception when sql%NOTFOUND then
null;
end if;
end loop;
close o_cur;
commit; exception when others then
raise;
end upd_wbb_quotenum;
/
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
 'upd_wbb_quotenum;',
SYSDATE,'SYSDATE + 30/60/60/24'); --这里的时间间隔是30秒。需要多长执行一次，把这个变成一个分数就好了。
commit;
end;
/

Answer 11

存储过程编译后，查看报错Compilation errors for PROCEDURE EPSIII.PRC_EXPR_QUOTENUMError: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
        begin
          case declare end exit for goto if loop mod null pragma raise
          return select update while with <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> <<
          close current delete fetch lock insert open rollback
          savepoint set sql execute commit forall merge pipe
       符号 "case在 "EXCEPTION" 继续之前已插入。
Line: 25
Text: exception when sql%NOTFOUND thenError: PLS-00103: 出现符号 "IF"在需要下列之一时：
        case
       符号 "case在 "IF" 继续之前已插入。
Line: 27
Text: end if;Error: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
        begin
          case declare end exit for goto if loop mod null pragma raise
          return select update while with <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> <<
          close current delete fetch lock insert open rollback
          savepoint set sql execute commit forall merge pipe
       符号 "case在 "EXCEPTION" 继续之前已插入。
Line: 47
Text: exception when sql%NOTFOUND thenError: PLS-00103: 出现符号 "IF"在需要下列之一时：
        case
       符号 "case在 "IF" 继续之前已插入。
Line: 49
Text: end if;

Answer 12

Compilation errors for PROCEDURE EPSIII.PRC_EXPR_QUOTENUMError: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
        begin
          case declare end exit for goto if loop mod null pragma raise
          return select update while with <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> <<
          close current delete fetch lock insert open rollback
          savepoint set sql execute commit forall merge pipe
       符号 "case在 "EXCEPTION" 继续之前已插入。
Line: 25
Text: exception when sql%NOTFOUND thenError: PLS-00103: 出现符号 "IF"在需要下列之一时：
        case
       符号 "case在 "IF" 继续之前已插入。
Line: 27
Text: end if;Error: PLS-00103: 出现符号 "EXCEPTION"在需要下列之一时：
        begin
          case declare end exit for goto if loop mod null pragma raise
          return select update while with <an identifier>
          <a double-quoted delimited-identifier> <a bind variable> <<
          close current delete fetch lock insert open rollback
          savepoint set sql execute commit forall merge pipe
       符号 "case在 "EXCEPTION" 继续之前已插入。
Line: 47
Text: exception when sql%NOTFOUND thenError: PLS-00103: 出现符号 "IF"在需要下列之一时：
        case
       符号 "case在 "IF" 继续之前已插入。
Line: 49
Text: end if;

Answer 13

create or replace procedure upd_wbb_quotenum 
as
o_cur sys_refcursor;
v_projectid bid_quote_tables.project_id%TYPE;
v_count web_business_bulletin.quotenum%TYPE;
v_m_pid expr_bid_quotetable.projectid%TYPE;
begin
--找出第一个sql需要的count及projectid
open o_cur for 
select project_id,count(distinct seller_id)
from bid_quote_tables 
where status >= 5
group by project_id; loop
fetch o_cur into v_projectid,v_count;
exit when o_cur%NOTFOUND;
--修改
begin
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 1 or  bulletinType = 2)
and projectid = v_projectid;
--没找到，则啥也不干。
exception when sql%NOTFOUND then
null;
end;
end loop;
close o_cur; --找出第二个SQL需要的count及projectid
open o_cur for
select projectid,count(distinct selleruserid)
from expr_bid_quotetable
group by projectid; loop
fetch o_cur into v_m_pid,v_count;
exit when o_cur%NOTFOUND;
--修改
begin
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 30 or  bulletinType = 31)
and projectid = v_m_pid;
--没找到，则啥也不干。
exception when sql%NOTFOUND then
null;
end;
end loop;
close o_cur;
commit; exception when others then
raise;
end upd_wbb_quotenum;
/

Answer 14

create or replace procedure upd_wbb_quotenum 
as
o_cur sys_refcursor;
v_projectid bid_quote_tables.project_id%TYPE;
v_count web_business_bulletin.quotenum%TYPE;
v_m_pid expr_bid_quotetable.projectid%TYPE;
begin
--找出第一个sql需要的count及projectid
open o_cur for 
select project_id,count(distinct seller_id)
from bid_quote_tables 
where status >= 5
group by project_id; loop
fetch o_cur into v_projectid,v_count;
exit when o_cur%NOTFOUND;
--修改
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 1 or  bulletinType = 2)
and projectid = v_projectid;
--没找到，则啥也不干。
if sql%NOTFOUND then
null;
end if;
end loop;
close o_cur; --找出第二个SQL需要的count及projectid
open o_cur for
select projectid,count(distinct selleruserid)
from expr_bid_quotetable
group by projectid; loop
fetch o_cur into v_m_pid,v_count;
exit when o_cur%NOTFOUND;
--修改
update web_business_bulletin 
set quotenum = v_count 
where itemState = 1
and (bulletinType = 30 or  bulletinType = 31)
and projectid = v_m_pid;
--没找到，则啥也不干。
if sql%NOTFOUND then
null;
end if;
end loop;
close o_cur;
commit; exception when others then
raise;
end upd_wbb_quotenum;
/

Answer 15

还是有点错Compilation errors for PROCEDURE EPSIII.UPD_WBB_QUOTENUMError: PLS-00103: 出现符号 "SQL"在需要下列之一时：
        others
          <an identifier> <a double-quoted delimited-identifier>
Line: 26
Text: exception when sql%NOTFOUND then

Answer 16

2009-12-08 17:02:58   50   可用分捐赠   没解决问题拿分了不好意思系统说明：crazylaa   在2009-12-08   17:02:58给   liyufu86   捐赠了50可用分;liyufu86   收到了：45可用分