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

Question

create or replace procedure PRO_USER_PAY_MODEL_UPLOAD  ISprd_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万条。
大家给个优化的建议我太纳闷了，我怀疑是数据库本身有问题。

Answer 1

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

Answer 2

30w数据，最好有索引：
在a.if_prepay，b.code；
而且大数据查询不适宜用in，尽量使用exists代替......

Answer 3

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开销，不过插入变量都得是集合，空间换时间吧。

Answer 4

呵呵，多谢各位了。我纠结中。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万条，并且查询速度还是蛮快的。不理解为啥执行起来那么慢呢

Answer 5

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

Answer 6

以下是优化的sql，不过你把没有用到的变量最好是删除了，以免占用内存使用
create or replace procedure pro_user_pay_model_uploadis      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;

Answer 7

刚才没有修改变量，不好意思create or replace procedure pro_user_pay_model_uploadis      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;

Answer 8

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

Answer 9

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