oracle选择第二大的数据,Oracle中如何更新一张大表记录

3、方法2：PL/SQL匿名块

上面方法1的最大问题在于“一次性瞬间压力”大。无论是Undo量、还是Rollback量，都是有很大的问题。即使我们的系统能够支持这样的操作，如果update过程中存在其他的作业，必然受到影响。

PL/SQL匿名块的原则在于平稳负载，分批的进行处理。这个过程需要使用bulk collect批量操作，进行游标操作。

我们首先还原实验环境。

SQL> truncate table t_target;

Table truncated

SQL> insert /*+append*/into t_target select * from t;

9318016 rows inserted

SQL> commit;

Commit complete

代码片段。

SQL> set timing on;

SQL> declare

2    type rowid_list is table of urowid index by binary_integer;

3    rowid_infosrowid_list;

4    i number;

5    cursor c_rowids is select rowid from t_target;

6  begin

7    open c_rowids;

8

9    loop

10      fetch c_rowidsbulk collect into rowid_infos limit 2000;

11

12      foralli in 1..rowid_infos.count

13        update t_target set owner=to_char(length(owner)+1)

14            where rowid=rowid_infos(i);

15

16      commit;

17      exit when rowid_infos.count<2000;

18    end loop;

19    close c_rowids;

20  end;

21  /

PL/SQL procedure successfully completed

Executed in 977.081 seconds

我们在977s完成了整个操作。这个方法有几个特点：首先是批量的获取bulk collect指定数量更新批量。第二个是使用forall的方法批量更新，减少引擎之间的切换。第三是更新一批之后，commit操作。

这样的好处在于平稳化undo使用。如果数据量少，这种代码方法可能比直接update要慢。但是如果数据量大，特别是海量数据情况下，这种方法是可以支持非常大的数据表更新的。

代码中update操作，使用rowid，如果有其他业务方面的主键也可以使用替换。

在编程实践中，有时候我们可能不能使用PL/SQL代码片段，只能使用SQL语句。这种时候就需要结合业务方面有没有特点可以使用？这种时候往往也就考验开发人员对业务特点的理解了。

在使用forall的时候，要注意一批更新的数量。根据一些Oracle文献透露，内部SQL引擎在update的时候，也是分批进行的，每批大约1000条记录。经验告诉我们每批数量在1-5万比较合适。

这是第二种方法，下面我们介绍一种简单、可行的手段，比较方便。

4、方法3：insert append方法

从Undo和Redo的角度看，我们更喜欢insert，特别是生成较少redo的nologging和append插入。Update和Delete操作，都会生成Undo记录，在我们看来，都是可以想办法减少的方法。

这种方法的思路是：利用insert，将原来的数据表插入到一个新建立的数据表。在insert过程中，整理column的取值，起到update相同的效果。

下面是实验过程。我们先创建数据表，注意可以设置nologging属性。

SQL> create tablet_renamenologging as select * from t_target where 1=0;

Table created

Executed in 0.889 seconds

在这个过程中，我们创建的是一个空表。之后就可以插入数据，这种方法比较麻烦的地方，就是需要在insert脚本中列出所有的数据列。当然，借用一些工具技巧，这个过程也可以很简单。

SQL> insert /*+append*/into t_rename

2  selectto_char(length(owner)) owner,

3  OBJECT_NAME,

4  SUBOBJECT_NAME,

5  OBJECT_ID,

6  DATA_OBJECT_ID,

7  OBJECT_TYPE,

8  CREATED,

9  LAST_DDL_TIME,

10  TIMESTAMP,

11  STATUS,

12  TEMPORARY,

13  GENERATED,

14  SECONDARY,

15  NAMESPACE,

16  EDITION_NAME from t_target;

9318016 rows inserted

Executed in 300.333 seconds

使用append操作，可以减少redo log的生成。从结果看，一共执行了300s左右，效率应该是比较好的。

之后，提交事务。将原来的数据表删除，将新数据表rename成原有数据表名称。

SQL> commit;

Commit complete

Executed in 0.031 seconds

SQL> drop table t_target purge;

Table dropped

Executed in 1.467 seconds

SQL> rename t_rename to t_target;

Table renamed

Executed in 0.499 seconds

SQL> select count(*) from t_target;

COUNT(*)

----------

9318016

Executed in 14.336 seconds

最后，可以将nologging属性修改回来，将数据表约束添加上。

SQL> alter table t_target logging;

Table altered

Executed in 0.015 seconds

这种方法的好处在于效率，在数据量维持中高的情况下，这种方法速度是比较吸引人的。但是，这种方式也要消耗更多的存储空间。在存储空间允许的情况下，可以用这种方法。

如果数据量更大，达到海量的程度，比如几十G的数据表，这种方法就值得考量一下了。需要结合硬件环境和运行环境完成。另外，这种方法涉及到数据表的创建等运维工作特性，故不适合在应用程序中使用，适合在运维人员过程中使用。

还有，就是这种方法的实际对备份的影响。由于我们使用了nologging+append选项，生成的redo log数量是不足以进行还原的，所以如果要实现完全恢复的话，数据库实际上是失去了连续还原的依据。因此，如果真正使用了这个方法在生产环境下，之后需要进行数据库全备份操作。

如果数据库版本为11.2以上，我们可以使用Oracle的一个新特性dbms_parallel_execute包，进行数据表并行更新。详见下面介绍。