UPDATE 160 million ROWS HOW TO?

looric

呵呵，我更新了6个分区，花了60分钟。实在忍受不了了，就取消了。现在我要更新回去。
[php]
DECLARE
  pa_num NUMBER;
  pa_rec DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE; -- declare record variable  
  CURSOR c0 IS
    select PARTITION_NAME
      from DBA_TAB_PARTITIONS A
     where table_name = 'SM_CALLRECORD'
       AND SUBSTR(A.PARTITION_NAME, 3, 8) BETWEEN '20041002' AND '20050101'
     order by PARTITION_NAME;
  MY_CALLTIME SM_CALLRECORD.CALLTIME%TYPE;
BEGIN
  open c0;
  pa_num := 1;
   LOOP
    fetch c0
      into pa_rec;
    EXIT WHEN c0%NOTFOUND;
    dbms_output.put_line(sysdate || 'Updating ' || pa_rec ||
                         ' PARTITION_NAME' || '-p' || pa_num || '.');
    execute immediate 'UPDATE  /*+ PARALLEL(N,2) */ SM_CALLRECORD partition(' ||
                      pa_rec || ')
     SET CALLTIME = to_char((to_date(calltime,''yyyymmddhh24miss'')+8/24),''yyyymmddhh24miss'')';
    commit;
    dbms_output.put_line(sysdate || 'Updated ' || pa_rec ||
                         ' PARTITION_NAME' || '-p' || pa_num || '.');
    pa_num := pa_num + 1;
  END LOOP;
  close c0;
END;
[/php]

looric

Undo Tablespace Size and Retention Time
In order for changes to be rolled back or undone, Oracle makes a copy of the original data before modifying it. The original copy of the modified data is called undo data. Undo data is stored in a logical database structure called an undo tablespace.

The undo tablespace is of a finite size. Space usage within the tablespace is such that records can be overwritten. The undo data needs to be saved at least until the transaction has been committed. Until then the undo data is said to be in the active state. The amount of space available in the undo tablespace should, therefore, be at least large enough to hold the active undo data generated by currently active transactions. Otherwise some of these transactions may fail. After the active undo data is stored in the undo tablespace, Oracle automatically ensures that it is never overwritten until the corresponding transaction has been committed.

Even after the transaction has been committed, the undo data still cannot be overwritten immediately. It is important to the success of flashback functionality, and for read consistency for long running transactions, that records not be overwritten too soon. For example, if your longest query takes 15 minutes, the undo tablespace should be big enough to hold 15 minutes worth of undo information.
所以不论分区不分区提交都不会节省Undo Tablespace

mkcvictor

所以我不是告诉你加一个计数器,多少W条来commit一次.
在我的设计中我就是这样做的.

looric

楼上的老大，你帮我写个看看，我来执行测试任务。。

mkcvictor

DECLARE
  pa_num NUMBER;
  CURSOR c0 IS
    select PARTITION_NAME
    from DBA_TAB_PARTITIONS A
    where table_name = 'SM_CALLRECORD'
    AND SUBSTR(A.PARTITION_NAME, 3, 8) BETWEEN '20041002' AND '20050402'
    order by PARTITION_NAME;
  TYPE ref_cur IS REF CURSOR;
  l_cursor ref_cur;
  v_c0 C0%ROWTYPE;
  v_select_string VARCHAR2(500);
  v_update_string VARCHAR2(500);
BEGIN
  FOR v_c0 IN c0 LOOP
    v_select_string :='SELECT rowid t_rowid FROM SM_CALLRECORD PARTITION('||v_c0.PARTITION_NAME||')';
    OPEN l_cursor FOR v_select_string;
    pa_num := 1;
    FOR c1 IN l_cursor LOOP
       pa_num := pa_num+1;
       v_update_string :='UPDATE SM_CALLRECORD PARTITION('||v_c0.PARTITION_NAME||') SET CALLTIME = to_char((to_date(calltime,''yyyymmddhh24miss'')-8/24),''yyyymmddhh24miss'')';
       execute immediate v_update_string;
       IF pa_num >=100000 THEN  --issue commit per 10W records
          commit;
          pa_num := 0; --reset counter
       END IF;
    END LOOP;
    commit; --must be have more one commit command
  END LOOP;
END;

looric

FOR c1 IN l_cursor LOOP 报错。ora-06650，pls-00221，‘l_cursor ’不是过程或未定义。

mkcvictor

对不起,REF CURSOR不能这样用,请试试下面这个
DECLARE
  pa_num NUMBER;
  CURSOR c0 IS
    select PARTITION_NAME
    from DBA_TAB_PARTITIONS A
    where table_name = 'SM_CALLRECORD'
    AND SUBSTR(A.PARTITION_NAME, 3, 8) BETWEEN '20041002' AND '20050402'
    order by PARTITION_NAME;
  CURSOR c1(v_part VARCHAR2) IS
    SELECT rowid t_rowid FROM SM_CALLRECORD PARTITION(v_part);
  v_c0 C0%ROWTYPE;
BEGIN
  FOR v_c0 IN c0 LOOP
    FOR v_c1 IN C1(v_c0.partition_name) LOOP
       UPDATE SM_CALLRECORD SET CALLTIME = to_char((to_date(calltime,'yyyymmddhh24miss')-8/24),'yyyymmddhh24miss')
       WHERE rowid = v_c1.t_rowid;
       IF pa_num >=100000 THEN  --issue commit per 10W records
          commit;
          pa_num := 0;
       END IF;
    END LOOP;
    commit; --must be have more one commit command
  END LOOP;
END;

looric

这句话 CURSOR c1(v_part VARCHAR2) IS
SELECT rowid t_rowid FROM SM_CALLRECORD PARTITION(v_part);
报错，分区不存在，可能不能这样写。

looric

我直接把语句更改如下测试性能：
[php]
DECLARE
  pa_num NUMBER;
  v_part DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;
  CURSOR c0 IS
    select PARTITION_NAME
      from DBA_TAB_PARTITIONS A
     where table_name = 'SM_CALLRECORD'
       AND SUBSTR(A.PARTITION_NAME, 3, 8) = '20041002'; --只是一个分区
  CURSOR c1 IS SELECT rowid t_rowid FROM SM_CALLRECORD PARTITION(P_20041002); --直接把这个分区写出来，不用参数传递，呵呵，不会写啊。
  v_c0 C0%ROWTYPE;
BEGIN
  FOR v_c0 IN c0 LOOP
    FOR v_c1 IN C1 LOOP
      pa_num := 1;
      UPDATE SM_CALLRECORD
         SET CALLTIME = to_char((to_date(calltime, 'yyyymmddhh24miss') -
                                8 / 24),
                                'yyyymmddhh24miss')
       WHERE rowid = v_c1.t_rowid;
      pa_num := pa_num + 1;
      IF pa_num >= 100000 THEN
        --issue commit per 10W records
        commit;
        pa_num := 1;
      END IF;
    END LOOP;
    commit; --must be have more one commit command
  END LOOP;
END;
[/php]
执行后在oem里面一看，喔，需要时间45分钟以上。会滚段每10w增加8M左右。

mkcvictor

如果你只用一个分区的话你要将第一层的loop去掉,因为你不去掉才会花了你45minutes.
DECLARE
  pa_num NUMBER;
  v_part DBA_TAB_PARTITIONS.PARTITION_NAME%TYPE;
  CURSOR c0 IS
    select PARTITION_NAME
      from DBA_TAB_PARTITIONS A
     where table_name = 'SM_CALLRECORD'
       AND SUBSTR(A.PARTITION_NAME, 3, 8) = '20041002'; --只是一个分区
  CURSOR c1 IS SELECT rowid t_rowid FROM SM_CALLRECORD PARTITION(P_20041002); --直接把这个分区写出来，不用参数传递，呵呵，不会写啊。
  v_c0 C0%ROWTYPE;
BEGIN
  --FOR v_c0 IN c0 LOOP
    FOR v_c1 IN C1 LOOP
      pa_num := 1;
      UPDATE SM_CALLRECORD
         SET CALLTIME = to_char((to_date(calltime, 'yyyymmddhh24miss') -
                                8 / 24),
                                'yyyymmddhh24miss')
       WHERE rowid = v_c1.t_rowid;
      pa_num := pa_num + 1;
      IF pa_num >= 100000 THEN
        --issue commit per 10W records
        commit;
        pa_num := 1;
      END IF;
    END LOOP;
    commit; --must be have more one commit command
  --END LOOP;
END;