sql - 根据算术将 200 万行的一列更新为另一列

Question

在对同一表中的不同列执行一些数学运算后，我试图更新大约 200 万行的一列空值。基本上，我将分钟从一列转换为秒，并根据某些条件用秒更新空列。我已经在没有光标的情况下尝试过，但是它花费的时间太长而且无法完成。下面的脚本似乎也永远不会完成。这是我到目前为止所拥有的:

DECLARE
CURSOR c1 IS
    SELECT /*+ ORDERED USE_NL(g,e) */
        e.event_code, e.time, e.period, e.time_elapsed, e.rowid
    FROM table1.schedule s, table2.event e
    WHERE e.event_code = s.event_code                
    AND s.schedule_id in (22,39,49,51,53,55,57,59,61,63,65,66,
    68,69,71,72,75,77,78,80,82,84,86,87,89,92,93,95,97,98,
    101,103,105,107,109,111,114,116,118,120,122,125,128,130,
    133,135,137,140,50,52,54,56,58,60,62,64,67,70,73,74,76,79,
    81,83,85,88,90,91,94,96,99,100,102,104,106,108,110,112,113,
    115,117,119,121,123,124,126,127,129,131,132,134,136,138,141)
    AND e.time_elapsed IS NULL
    AND e.time IS NOT NULL
    AND (e.period > 0 OR e.period < 0);


TYPE EventRecType IS RECORD (
    Event_Code table1.schedule.event_code%type,
    evTime table2.event.time%type,
    evPeriod table2.event.period%type,
    evTimeElapsed table2.event.time_elapsed%TYPE,
    evRowId ROWID);

TYPE EventRecTab IS TABLE OF EventRecType INDEX BY PLS_INTEGER;

EventRec EventRecTab;

TYPE typ_evRecord IS RECORD (
  eRowId ROWID,
  TimeElapsed table2.event.time_elapsed%TYPE);

TYPE tab_evTable IS TABLE OF typ_evRecord INDEX BY PLS_INTEGER;

arr_evRecToUpdate tab_evTable;

BEGIN
  OPEN c1;
  LOOP
  FETCH c1 BULK COLLECT INTO EventRec LIMIT 50000; 

  FOR k in 1..EventRec.count LOOP
     if  EventRec(k).evPeriod = 1 AND EventRec(k).evTime < 150 then
       arr_evRecToUpdate(k).TimeElapsed := EventRec(k).evTime*60;
       arr_evRecToUpdate(k).eRowId := EventRec(k).evRowId;  
     elsif EventRec(k).evPeriod = 2 AND EventRec(k).evTime < 150 then          
       arr_evRecToUpdate(k).TimeElapsed := (EventRec(k).evTime-45)*60;
       arr_evRecToUpdate(k).eRowId := EventRec(k).evRowId;   
     elsif EventRec(k).evPeriod = 3 AND EventRec(k).evTime < 150 then    
       arr_evRecToUpdate(k).TimeElapsed := (EventRec(k).evTime-90)*60;
       arr_evRecToUpdate(k).eRowId := EventRec(k).evRowId;      
     elsif EventRec(k).evPeriod = 4 AND EventRec(k).evTime < 150 then      
       arr_evRecToUpdate(k).TimeElapsed := (EventRec(k).evTime-105)*60;
       arr_evRecToUpdate(k).eRowId := EventRec(k).evRowId; 
     elsif EventRec(k).evPeriod = 1 AND EventRec(k).evTime > 150 THEN    
       EventRec(k).evTime := ROUND(EventRec(k).evTime/60);                         
       arr_evRecToUpdate(k).TimeElapsed := EventRec(k).evTime;
       arr_evRecToUpdate(k).eRowId := EventRec(k).evRowId;  
     elsif (EventRec(k).evPeriod = 2) AND (EventRec(k).evTime > 150) THEN     
       EventRec(k).evTime := ROUND((EventRec(k).evTime/60)) + 45;              
       arr_evRecToUpdate(k).TimeElapsed := EventRec(k).evTime;
       arr_evRecToUpdate(k).eRowId := EventRec(k).evRowId;
     end if;

     EXIT WHEN EventRec.COUNT() = 0;
    END LOOP;
    FORALL i_loopIndex IN 1 .. arr_evRecToUpdate.COUNT
      UPDATE table2.event
      SET time_elapsed = arr_evRecToUpdate(i_loopIndex).TimeElapsed
      WHERE rowid = arr_evRecToUpdate(i_loopIndex).eRowid;
    COMMIT;
  END LOOP;        
 CLOSE c1;
END;

Answer 1

SELECT查询单独运行时是否耗时？
请阅读以下我的观点。它不会解决您的问题，但可能会对您有所帮助!

需要考虑的要点:

1) 所有你BULK COLLECT 到PL/SQL Collection 中的数据都将放在PGA 中，并且它是静态的。如果使用集合的任何其他 PL/SQL block 在后台运行，则所有 block 都将使用 PGA 并且它也不会共享。这绝对是一个代价高昂的操作，当集合很大并且在这里你以 50K 的批处理操作超过 40 次。当然，您可以使用 (subscript) 调用它们，这类似于使用其 index 查询表。

批量绑定(bind) 将在 PL/SQL 引擎 本身中消耗时间，然后再发送到 SQL 引擎。您使用 ROWID 的方法很棒。我并不是说永远不应该使用 PL/SQL 集合。这取决于数据库的繁忙程度。你也会在 PGA 中持有 2M 结果集，直到你的 CURSOR 耗尽!最好与 DBA 交谈，并在可能的情况下增加 PGA。

我的做法是在 PGA 中处理 2M 行，这并不好。

2) 正如评论中的某些人所建议的那样，拥有一个 staging table 也很好。我通过将其拆分为多个并行运行的脚本来编写更新操作。这可能需要更多的编码。

3) COMMIT 大小。您的 COMMIT 大小在这里是 50K。提交大小越高，redo/undo 日志的大小就越大。这个表是复制的吗？这些表是否有任何触发器？

4) 可用的文件处理脚本太多了(perl)。将查询数据下载到文件中，进行文件处理并在可能的情况下重新加载表。(这可能有利于 ，当要更新的计数 >= 总数的 50% 时)