SQL优化的东东，大家帮个忙

[范佩西_11]

create or replace procedure PRO_USER_PAY_MODEL_UPLOAD IS

prd_inst_id varchar2(100);
latn_id varchar2(100);
service_nbr varchar2(100);
inst_stas_id varchar2(100);
pay_cust_id varchar2(100);
prd_inst_name varchar2(100);
code varchar2(100);
code_name varchar2(100);
user_Account varchar2(100);
accept_empee_id varchar2(100);

imsi_number number(10);
mid_number number(10);
mid_service_nbr varchar2(100);
mid_item_value varchar2(100);
imsi_code varchar2(100);
mid_empee_site varchar2(100);
temp_num number(10);
site_id_num number(10);

BEGIN


FOR payModel IN(select a.prd_inst_id,a.latn_id,a.service_nbr,a.prd_inst_stas_id,a.pay_cust_id,a.prd_inst_name, b.code,b.code_name,
nvl(a.user_account,a.service_nbr) AS User_Account,a.accept_empee_id
from tb_prd_prd_inst_999 a,tb_pty_code b where a.if_prepay=b.code and b.code_type = 'IF_PREPAY'
and a.prd_inst_stas_id in(1001,1201,1202,1203) and a.prd_id in ('5000','5221','5100')) LOOP

prd_inst_id := payModel.Prd_Inst_Id;
latn_id := payModel.Latn_Id;
service_nbr := payModel.Service_Nbr;
inst_stas_id := payModel.Prd_Inst_Stas_Id;
pay_cust_id := payModel.Pay_Cust_Id;
prd_inst_name := payModel.Prd_Inst_Name;
code := payModel.Code;
code_name := payModel.Code_Name;
user_Account := payModel.User_Account;
accept_empee_id := payModel.Accept_Empee_Id;

--IMSI 查询
SELECT COUNT(*) INTO imsi_number from SNUIMCARD where iccidcode in (SELECT a.val from tb_prd_prd_inst_fea_999 a where a.prd_fea_type_id = 1054 and a.prd_inst_id = prd_inst_id and rownum = 1);
select COUNT(*) INTO mid_number FROM TB_USER_PAY_MODEL_MIDDLE a WHERE a.productnbr = service_nbr or a.ProdNbr = service_nbr;
select COUNT(*) INTO site_id_num from tb_sys_site b where b.site_id IN(select site_id from tb_pty_empee where empee_id = accept_empee_id and rownum = 1) ;

IF (imsi_number > 0 and site_id_num > 0) THEN

SELECT imsicode INTO imsi_code from SNUIMCARD where iccidcode in (SELECT a.val from tb_prd_prd_inst_fea_999 a where a.prd_fea_type_id = 1054 and a.prd_inst_id = prd_inst_id and rownum = 1);
select b.NAME INTO mid_empee_site from tb_sys_site b where b.site_id IN(select site_id from tb_pty_empee where empee_id = accept_empee_id and rownum = 1) ;

IF(mid_number > 0) THEN --在中间表中找到数据
--1、判断付费模式状态是否一致
select productnbr,ITEMVALUE INTO mid_service_nbr,mid_item_value FROM TB_USER_PAY_MODEL_MIDDLE a WHERE a.productnbr = service_nbr or a.ProdNbr = service_nbr and rownum = 1;
IF (code <> mid_item_value) THEN

UPDATE TB_USER_PAY_MODEL_MIDDLE SET ITEMVALUE = code,
ordertypecd = '216',PAY_TYPE=2 WHERE productnbr = service_nbr;

END IF;

ELSE

INSERT INTO tb_user_pay_model_middle
(pay_inst_id, pay_type, pay_send_state, bpmprodtype, ordertypecd, bizeventnbr, prodcode, oldprodcode, productnbr, oldproductnbr, custname, useracctnbr, useracctdomain, imsi, oldimsi, prodstatuscd, oldstatuscd, latnid, cityname, itemaction, itemid, itemname, itemvalue, valuename, oldvalue, oldvaluename, empeeid, empeesite)
VALUES
(SE_TB_USER_PAY_MODEL_UPLOAD.NEXTVAL, '1', '100', '10', '100',
prd_inst_id, service_nbr, '', service_nbr, '',
prd_inst_name, user_Account, 'v_useracctdomain', imsi_code, '', inst_stas_id,
'', latn_id, '', '10', '320001', '用户付费模式', code,
'', '', '', accept_empee_id, mid_empee_site);

END IF;
COMMIT;
END IF;
END LOOP;

end;
/
纠结中帮人调存储过程，这个存储过程
payModel 这个游标可以遍历30万数据，但执行整个存储过程2小时没动静，后来我DBMS_OUTPUT一条一条输出，大概半小时才跑了1万条。
大家给个优化的建议我太纳闷了，我怀疑是数据库本身有问题。

[范佩西_11]

我2年多没搞oracle了。好多东西都忘光了，悲催。。。大家帮忙啊。

[NLP爱好者]

代码完整贴出来啊 ！不完整怎么调试！

[tangren]

[Quote=引用 5 楼 linzhangs 的回复:]

呵呵，多谢各位了。我纠结中。SQL code
select a.prd_inst_id,a.latn_id,a.service_nbr,a.prd_inst_stas_id,a.pay_cust_id,a.prd_inst_name, b.code,b.code_name,
nvl(a.user_account,a.service_nbr) AS User_Account,a.accept……
[/Quote]

1、查询一般只显示前面一页数据，当然较快。使用游标遍历30万数据，慢是肯定的。
2、尽量不要使用游标，而采用多表连接方式。
3、针对每个SQL语句，优化查询计划。

[NLP爱好者]

以下是优化的sql，不过你把没有用到的变量最好是删除了，以免占用内存使用

create or replace procedure pro_user_pay_model_upload

is

      v_prd_inst_id     tb_prd_prd_inst_999.prd_inst_id%type;

      v_latn_id         tb_prd_prd_inst_999.latn_id%type;
      v_service_nbr     tb_prd_prd_inst_999.service_nbr%type;
      v_inst_stas_id    tb_prd_prd_inst_999.prd_inst_stas_id%type;
      v_pay_cust_id     tb_prd_prd_inst_999.pay_cust_id%type;
      v_prd_inst_name   tb_prd_prd_inst_999.prd_inst_name%type;
      v_code            tb_pty_code.code%type;
      v_code_name       tb_pty_code.code_name%type;
      v_user_account    varchar2(100);
      v_accept_empee_id tb_prd_prd_inst_999.accept_empee_id%type;
      

      imsi_number     number(10);
      mid_number      number(10);
      mid_service_nbr varchar2(100);
      mid_item_value  varchar2(100);
      imsi_code       varchar2(100);
      mid_empee_site  varchar2(100);
      temp_num        number(10);
      site_id_num     number(10);
      
      --定义游标
      cursor paymodel is 
            select a.prd_inst_id,
                   a.latn_id,
                   a.service_nbr,
                   a.prd_inst_stas_id,
                   a.pay_cust_id,
                   a.prd_inst_name,
                   b.code,
                   b.code_name,
                   nvl(a.user_account,a.service_nbr) as user_account,
                   a.accept_empee_id
              from tb_prd_prd_inst_999 a,
                   tb_pty_code b
             where a.if_prepay = b.code
               and a.prd_inst_stas_id in(1001,1201,1202,1203)
               and a.prd_id in ('5000','5221','5100')
               and b.code_type = 'if_prepay';

                

begin

   --打开游标
   open paymodel ;
   
   loop
       
       --提取游标第一行数据
       fetch paymodel into v_prd_inst_id, v_latn_id,    v_service_nbr,
                           v_inst_stas_id,v_pay_cust_id,v_prd_inst_name,
                           v_code,v_code_name,v_user_account,
                           v_accept_empee_id;   
                              
       exit when paymodel%notfound;

      --imsi 查询
      select count(*) into imsi_number
        from snuimcard a
       where exists 
             (
               select 1
                 from tb_prd_prd_inst_fea_999 b
                where a.iccidcode = b.val
                  and a.prd_fea_type_id = 1054
                  and a.prd_inst_id = v_prd_inst_id
             );

             
      select count(*) into mid_number
        from tb_user_pay_model_middle a
       where (a.productnbr = v_service_nbr  or a.prodnbr = v_service_nbr);

       

      select count(*) into site_id_num
        from tb_sys_site b
       where exists
             (
               select 1
                 from tb_pty_empee c
                where b.site_id = c.site_id
                  and c.empee_id = v_accept_empee_id
             ) ;



    if (imsi_number > 0 and site_id_num > 0) then

        select imsicode into imsi_code
          from snuimcard v
         where exists
               (
                select 1
                 from tb_prd_prd_inst_fea_999 a
                where v.iccidcode = a.al
                  and a.prd_fea_type_id = 1054
                  and a.prd_inst_id = v_prd_inst_id
                ) ;

                
             
      select b.name into mid_empee_site
        from tb_sys_site b
       where exists
             (
               select 1
                 from tb_pty_empee n
                where b.site_id = n.site_id
                  and n.empee_id = v_accept_empee_id
             ) ;

              
       if(mid_number > 0) then --在中间表中找到数据

        --1、判断付费模式状态是否一致

          select productnbr,
                 itemvalue
            into mid_service_nbr ,
                 mid_item_value 
           from tb_user_pay_model_middle a
           where a.productnbr = v_service_nbr
                 or
                 a.prodnbr = v_service_nbr
            and rownum = 1;


            if (v_code <> mid_item_value) then

                update tb_user_pay_model_middle k
                   set k.itemvalue = v_code,
                       k.ordertypecd = '216',
                       k.pay_type=2
                 where k.productnbr = v_service_nbr;


           end if;


       else

          insert into tb_user_pay_model_middle
                    (
                      pay_inst_id,
                      pay_type,
                      pay_send_state,
                      bpmprodtype,
                      ordertypecd,
                      bizeventnbr,
                      prodcode,
                      oldprodcode,
                      productnbr,
                      oldproductnbr,
                      custname,
                      useracctnbr,
                      useracctdomain,
                      imsi,
                      oldimsi,
                      prodstatuscd,
                      oldstatuscd,
                      latnid,
                      cityname,
                      itemaction,
                      itemid,
                      itemname,
                      itemvalue,
                      valuename,
                      oldvalue,
                      oldvaluename,
                      empeeid,
                      empeesite
                     )
              values
                    (
                      se_tb_user_pay_model_upload.nextval,
                      '1',
                      '100',
                      '10',
                      '100',
                      prd_inst_id,
                      service_nbr,
                      '',
                      service_nbr,
                      '',
                      prd_inst_name,
                      user_account,
                      'v_useracctdomain',
                      imsi_code,
                      '',
                      inst_stas_id,
                      '',
                      latn_id,
                      '',
                      '10',
                      '320001',
                      '用户付费模式',
                      code,
                      '',
                      '',
                      '',
                      accept_empee_id,
                       mid_empee_site
                    );

         commit;           

       end if;

         
     end if;


  end loop;

  
  --关闭游标
  close paymodel;

end;

[NLP爱好者]

刚才没有修改变量，不好意思

create or replace procedure pro_user_pay_model_upload

is

      v_prd_inst_id     tb_prd_prd_inst_999.prd_inst_id%type;

      v_latn_id         tb_prd_prd_inst_999.latn_id%type;
      v_service_nbr     tb_prd_prd_inst_999.service_nbr%type;
      v_inst_stas_id    tb_prd_prd_inst_999.prd_inst_stas_id%type;
      v_pay_cust_id     tb_prd_prd_inst_999.pay_cust_id%type;
      v_prd_inst_name   tb_prd_prd_inst_999.prd_inst_name%type;
      v_code            tb_pty_code.code%type;
      v_code_name       tb_pty_code.code_name%type;
      v_user_account    varchar2(100);
      v_accept_empee_id tb_prd_prd_inst_999.accept_empee_id%type;
      

      imsi_number     number(10);
      mid_number      number(10);
      mid_service_nbr varchar2(100);
      mid_item_value  varchar2(100);
      imsi_code       varchar2(100);
      mid_empee_site  varchar2(100);
      temp_num        number(10);
      site_id_num     number(10);
      
      --定义游标
      cursor paymodel is 
            select a.prd_inst_id,
                   a.latn_id,
                   a.service_nbr,
                   a.prd_inst_stas_id,
                   a.pay_cust_id,
                   a.prd_inst_name,
                   b.code,
                   b.code_name,
                   nvl(a.user_account,a.service_nbr) as user_account,
                   a.accept_empee_id
              from tb_prd_prd_inst_999 a,
                   tb_pty_code b
             where a.if_prepay = b.code
               and a.prd_inst_stas_id in(1001,1201,1202,1203)
               and a.prd_id in ('5000','5221','5100')
               and b.code_type = 'if_prepay';

                

begin

   --打开游标
   open paymodel ;
   
   loop
       
       --提取游标第一行数据
       fetch paymodel into v_prd_inst_id, v_latn_id,    v_service_nbr,
                           v_inst_stas_id,v_pay_cust_id,v_prd_inst_name,
                           v_code,v_code_name,v_user_account,
                           v_accept_empee_id;   
                              
       exit when paymodel%notfound;

      --imsi 查询
      select count(*) into imsi_number
        from snuimcard a
       where exists 
             (
               select 1
                 from tb_prd_prd_inst_fea_999 b
                where a.iccidcode = b.val
                  and a.prd_fea_type_id = 1054
                  and a.prd_inst_id = v_prd_inst_id
             );

             
      select count(*) into mid_number
        from tb_user_pay_model_middle a
       where (a.productnbr = v_service_nbr  or a.prodnbr = v_service_nbr);

       

      select count(*) into site_id_num
        from tb_sys_site b
       where exists
             (
               select 1
                 from tb_pty_empee c
                where b.site_id = c.site_id
                  and c.empee_id = v_accept_empee_id
             ) ;



    if (imsi_number > 0 and site_id_num > 0) then

        select imsicode into imsi_code
          from snuimcard v
         where exists
               (
                select 1
                 from tb_prd_prd_inst_fea_999 a
                where v.iccidcode = a.al
                  and a.prd_fea_type_id = 1054
                  and a.prd_inst_id = v_prd_inst_id
                ) ;

                
             
      select b.name into mid_empee_site
        from tb_sys_site b
       where exists
             (
               select 1
                 from tb_pty_empee n
                where b.site_id = n.site_id
                  and n.empee_id = v_accept_empee_id
             ) ;

              
       if(mid_number > 0) then --在中间表中找到数据

        --1、判断付费模式状态是否一致

          select productnbr,
                 itemvalue
            into mid_service_nbr ,
                 mid_item_value 
           from tb_user_pay_model_middle a
           where a.productnbr = v_service_nbr
                 or
                 a.prodnbr = v_service_nbr
            and rownum = 1;


            if (v_code <> mid_item_value) then

                update tb_user_pay_model_middle k
                   set k.itemvalue = v_code,
                       k.ordertypecd = '216',
                       k.pay_type=2
                 where k.productnbr = v_service_nbr;


           end if;


       else

          insert into tb_user_pay_model_middle
                    (
                      pay_inst_id,
                      pay_type,
                      pay_send_state,
                      bpmprodtype,
                      ordertypecd,
                      bizeventnbr,
                      prodcode,
                      oldprodcode,
                      productnbr,
                      oldproductnbr,
                      custname,
                      useracctnbr,
                      useracctdomain,
                      imsi,
                      oldimsi,
                      prodstatuscd,
                      oldstatuscd,
                      latnid,
                      cityname,
                      itemaction,
                      itemid,
                      itemname,
                      itemvalue,
                      valuename,
                      oldvalue,
                      oldvaluename,
                      empeeid,
                      empeesite
                     )
              values
                    (
                      se_tb_user_pay_model_upload.nextval,
                      '1',
                      '100',
                      '10',
                      '100',
                      v_prd_inst_id,
                      v_service_nbr,
                      '',
                      v_service_nbr,
                      '',
                      v_prd_inst_name,
                      v_user_account,
                      'v_useracctdomain',
                      v_imsi_code,
                      '',
                      v_inst_stas_id,
                      '',
                      v_latn_id,
                      '',
                      '10',
                      '320001',
                      '用户付费模式',
                      v_code,
                      '',
                      '',
                      '',
                      v_accept_empee_id,
                      v_mid_empee_site
                    );

         commit;           

       end if;

         
     end if;


  end loop;

  
  --关闭游标
  close paymodel;

end;

[304的的哥]

[Quote=引用楼主 linzhangs 的回复:]
payModel 这个游标可以遍历30万数据，但执行整个存储过程2小时没动静，后来我DBMS_OUTPUT一条一条输出，大概半小时才跑了1万条。
[/Quote]
30w数据，最好有索引：
在a.if_prepay，b.code；
而且大数据查询不适宜用in，尽量使用exists代替......

[hudingchen]

SELECT a.val
                           FROM tb_prd_prd_inst_fea_999 a
                          WHERE a.prd_fea_type_id = 1054
                            AND a.prd_inst_id = prd_inst_id
                            AND rownum = 1
SELECT site_id
                           FROM tb_pty_empee
                          WHERE empee_id = accept_empee_id
                            AND rownum = 1

上面查询条件 分别重复了两次，可以用变量存储。

大数据量插入更新，使用forall 语句可以减少loop开销，不过插入变量都得是集合，空间换时间吧。

[deng8818]

这程序写得，没说明，无注释，过段时间可能只有天看得懂了。

[hudingchen]

用exists替换in没有什么用，9i之后优化器会优化，基本没有什么区别，瓶颈还是30万次循环

[范佩西_11]

呵呵，多谢各位了。我纠结中。

select a.prd_inst_id,a.latn_id,a.service_nbr,a.prd_inst_stas_id,a.pay_cust_id,a.prd_inst_name, b.code,b.code_name,
  nvl(a.user_account,a.service_nbr) AS User_Account,a.accept_empee_id
  from tb_prd_prd_inst_999 a,tb_pty_code b where a.if_prepay=b.code and b.code_type = 'IF_PREPAY'  
  and a.prd_inst_stas_id in(1001,1201,1202,1203) and a.prd_id in ('5000','5221','5100')

这个游标的查询有30万条，并且查询速度还是蛮快的。不理解为啥执行起来那么慢呢

[范佩西_11]

to:lkz2004
用open cursor 与for 循环没区别啊。另外那个exists我也改过，也没太多用处。
to：tangren
2、尽量不要使用游标，而采用多表连接方式。
3、针对每个SQL语句，优化查询计划
这个怎么采用多表连接方式呢。是个什么意思？

[Rotel-刘志东]

1、30w的数据量用游标不会快，在程序中尽量少用游标。
2、采用多个表的连接方式。
3、具体要看一下你的查询计划了，具体应该优化哪一方面的。

[NLP爱好者]

学习中。。。。

[范佩西_11]

我是在业务方面理清了逻辑，把遍历的游标减小到1万条，在优化了语句，现在不到5分钟。谢谢大家。