如何高效的更新大量数据 -- 有待考究

 

来自ITPUB：无休无止

http://eagle_fan.itpub.net/post/461/10912

 

通常我们使用update来进行更新操作。不过，update操作是一项很昂贵的操作，具体为什么如此昂贵，将在后面的文章中介绍。

我在这里给出了一个例子，来谈谈如何高效的更新大量数据。例子中的表的数据量为10w行。

Update操作需要2分钟的时间，而新方法只要不到一秒钟!!

蓝色标记部分为注释。

 

SQL> set timing on

@@打开计时选项

SQL> desc dba_objects;
 Name                                      Null?    Type
 ----------------------------------------- -------- ---------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 SUBOBJECT_NAME                                     VARCHAR2(30)
 OBJECT_ID                                          NUMBER
 DATA_OBJECT_ID                                     NUMBER
 OBJECT_TYPE                                        VARCHAR2(18)
 CREATED                                            DATE
 LAST_DDL_TIME                                      DATE
 TIMESTAMP                                          VARCHAR2(19)
 STATUS                                             VARCHAR2(7)
 TEMPORARY                                          VARCHAR2(1)
 GENERATED                                          VARCHAR2(1)
 SECONDARY                                          VARCHAR2(1)

Elapsed: 00:00:00.40
SQL> connect / as sysdba;
Connected.
SQL> create table test as
  2  select owner,object_name,object_id
  3  from dba_objects;

Table created.

Elapsed: 00:00:01.63

@@创建测试表test

SQL> select count(*) from test;

  COUNT(*)
----------
      3261

Elapsed: 00:00:00.40
SQL> insert into test
  2  select * from test;

3261 rows created.

Elapsed: 00:00:00.61
SQL> insert into test
  2  select * from test;

6522 rows created.

Elapsed: 00:00:00.71
SQL> insert into test
  2  select * from test;

13044 rows created.

Elapsed: 00:00:00.21
SQL> select count(*) from test;

  COUNT(*)
----------
     26088

Elapsed: 00:00:00.40
SQL> insert into test
  2  select * from test;

26088 rows created.

Elapsed: 00:00:01.81
SQL> insert into test
  2  select * from test;

52176 rows created.

Elapsed: 00:00:01.63
SQL> select count(*) from test;

  COUNT(*)
----------
    104352

Elapsed: 00:00:00.20

@@向测试表中塞入10w行数据

SQL> desc test;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------

 OWNER                                              VARCHAR2(30)
 OBJECT_NAME                                        VARCHAR2(128)
 OBJECT_ID                                          NUMBER

SQL> update test
  2  set object_name=lower(object_name);

104352 rows updated.

Elapsed: 00:01:90.20

@@向test表中跟新10w行数据，耗时00:01:90.20

SQL> create table test2
  2  as select owner,lower(object_name) object_name,object_id
  3  from test;

Table created.

Elapsed: 00:00:00.41
SQL> select count(*) from test2;

  COUNT(*)
----------
    104352

Elapsed: 00:00:00.30

@@创建临时表test2，将更新的语句写在select子句中，耗时一秒钟不到

SQL> drop table test;

Table dropped.

@@将原来的test表drop掉

Elapsed: 00:00:00.40
SQL> rename test2 to test;

Table renamed.

Elapsed: 00:00:00.70

@@将临时表更名为test表

后记：这只是一个例子，用于说明一种高效的更新数据的方法。

         对于实际应用来讲，如果原来的表含有索引，授权，约束等的话，需要在新表上面重新创建。

         对于索引的创建，也有很多高效的方法。所以对于大批量的数据更新，采用上面所示的方法更为高效。