求性能优化，关于cursor中loop循环时间过长解决思路

求性能优化，关于cursor中loop循环时间过长

SQL code

              FOR ADD_LOT_MAG_REC IN (SELECT OS_LOT_NO, OS_MAGAZINE, OS_WBMC, OS_OSMC                             FROM FWCATNS_3O_MACH_LOT                             WHERE OS_LOT_DT between reviewtime and smaxtime  AND OS_ACTIVE_STATUS = 'A')          LOOP                    SELECT COUNT(*) INTO iAddcount      FROM FWCATNS_3O_MACH_BASE      WHERE OS_LOT_NO = ADD_LOT_MAG_REC.OS_LOT_NO AND OS_MAGAZINE = ADD_LOT_MAG_REC.OS_MAGAZINE;              IF iAddcount = 0 THEN      INSERT INTO FWCATNS_3O_MACH_BASE                  (OS_LOT_NO,                    OS_MAGAZINE,                   OS_WBMC,                    OS_OSMC,                   )           VALUES (ADD_LOT_MAG_REC.OS_LOT_NO,                    ADD_LOT_MAG_REC.OS_MAGAZINE,                   ADD_LOT_MAG_REC.OS_WBMC,                    ADD_LOT_MAG_REC.OS_OSMC,                   );                           END IF;                                                 END LOOP;

上面是我的代码
先把数据取到cursor，然后对每条进行count，如果count为0那么就进行插入操作，不为0就证明原先有值，就不插入数据
现在我每次运行count那句话大概需要0.03s，我一次cursor里有2000条数据的话，那么就需要跑一分钟的时间
拜托看看怎么能优化一下，或者不用count这种验证手段来验证数据是否已经存在的话，那么还可以用什么方法

[解决办法]
可以考虑用merge into。


每次都count下，确实很耗费资源，影响性能。
[解决办法]
用merge into语句试试
[解决办法]
把insert语句换成这样试试：

SQL code

EXECUTE IMMEDIATE ' INSERT INTO FWCATNS_3O_MACH_BASE(OS_LOT_NO,OS_MAGAZINE,OS_WBMC,OS_OSMC,) VALUES (:1,:2,:3,:4)'USING ADD_LOT_MAG_REC.OS_LOT_NO,ADD_LOT_MAG_REC.OS_MAGAZINE,ADD_LOT_MAG_REC.OS_WBMC,ADD_LOT_MAG_REC.OS_OSMC;
[解决办法]
可以只添加新数据，不会覆盖以前的数据。


对于大批量的数据，merge into要比你游标count要快的多！
[解决办法]
先创建个表：
SQL code
MERGE into FWCATNS_3O_MACH_BASE fusing (SELECT OS_LOT_NO, OS_MAGAZINE, OS_WBMC, OS_OSMC       FROM   FWCATNS_3O_MACH_LOT       WHERE  OS_LOT_DT between reviewtime and smaxtime       AND    OS_ACTIVE_STATUS = 'A') t on    (f.OS_LOT_NO = t.OS_LOT_NO       and f.OS_MAGAZINE = t.OS_MAGAZINE)when  not match thenINSERT INTO FWCATNS_3O_MACH_BASE                  (OS_LOT_NO,                    OS_MAGAZINE,                   OS_WBMC,                    OS_OSMC,                   )           VALUES (ADD_LOT_MAG_REC.OS_LOT_NO,                    ADD_LOT_MAG_REC.OS_MAGAZINE,                   ADD_LOT_MAG_REC.OS_WBMC,                    ADD_LOT_MAG_REC.OS_OSMC,                   );
[解决办法]
刚才写错了

SQL code
select *from 表1wherenot exists(    SELECT 1       FROM FWCATNS_3O_MACH_BASE     WHERE OS_LOT_NO = 表1.OS_LOT_NO AND OS_MAGAZINE = 表1.OS_MAGAZINE)
[解决办法]
     SELECT COUNT(*) INTO iAddcount
     FROM FWCATNS_3O_MACH_BASE
     WHERE OS_LOT_NO = ADD_LOT_MAG_REC.OS_LOT_NO AND OS_MAGAZINE = ADD_LOT_MAG_REC.OS_MAGAZINE;

如果是这个慢，加个rownum, 速度就会快好多

    SELECT COUNT(*) INTO iAddcount
     FROM FWCATNS_3O_MACH_BASE
     WHERE OS_LOT_NO = ADD_LOT_MAG_REC.OS_LOT_NO AND OS_MAGAZINE = ADD_LOT_MAG_REC.OS_MAGAZINE
       and rownum = 1;


最好能用一个insert搞掂
[解决办法]
不清楚具体表数据量情况，先试一试这样的办法：
INSERT /*+append*/ INTO FWCATNS_3O_MACH_BASE NOLOGGING 
 
    (OS_LOT_NO, OS_MAGAZINE,OS_WBMC, OS_OSMC)
SELECT /*+USE_HASH(a,b)*/OS_LOT_NO, OS_MAGAZINE, OS_WBMC,OS_OSMC
  FROM FWCATNS_3O_MACH_LOT a
  WHERE OS_LOT_DT between reviewtime and smaxtime 
  AND OS_ACTIVE_STATUS = 'A'
  AND NOT EXISTS (
      SELECT 1 FROM FWCATNS_3O_MACH_BASE b
         WHERE OS_LOT_NO = a.OS_LOT_NO  
         AND OS_MAGAZINE = a.OS_MAGAZINE
  )

若非常慢，一般FWCATNS_3O_MACH_BASE表数据量较大，建议按照一定规则进行转存。上述MERGE INTO也可以完成。

上述/*+append*/为高水位线插入，每次执行完后，须进行一次COMMIT才可以进行下一次插入，NOLOGGING将以最小的日志写入，其插入速度基本和SELECT查询速度一致，若FWCATNS_3O_MACH_BASE数据量较大，这里推荐使用/*+USE_HASH(表名)*/来强制HASH连接，如果FWCATNS_3O_MACH_BASE数据量较小，比如只有几十条或者一百多条也可，而且FWCATNS_3O_MACH_LOT表上的字段OS_LOT_NO 、OS_MAGAZINE创建有相应的索引，那么此时由小表嵌套大表走嵌套循环，这样小表循环，大表也会自动根据小表的内容索引（前提是这两个字段上有索引才行）：

INSERT /*+append*/ INTO FWCATNS_3O_MACH_BASE NOLOGGING
    (OS_LOT_NO, OS_MAGAZINE,OS_WBMC, OS_OSMC)
SELECT /*+ordered use_nl(b,a)*/OS_LOT_NO, OS_MAGAZINE, OS_WBMC,OS_OSMC
  FROM FWCATNS_3O_MACH_LOT a
  WHERE OS_LOT_DT between reviewtime and smaxtime 
  AND OS_ACTIVE_STATUS = 'A'
  AND NOT EXISTS (
      SELECT 1 FROM FWCATNS_3O_MACH_BASE b
         WHERE OS_LOT_NO = a.OS_LOT_NO  
         AND OS_MAGAZINE = a.OS_MAGAZINE
  )


其余情况根据实际而定。。。。对于MERGE INTO楼上已经有回答，这里不多说了。 

 

查看更多 下一篇