Update large number of rows in a table [ID 268395.1]

		修改时间 29-JUN-2007     类型 HOWTO     状态 PUBLISHED

In this Document
  Goal
  Solution

---

 

 

Applies to:

Oracle Server - Enterprise Edition - Version: 8.1.7 to 10.2
Information in this document applies to any platform.

Goal

How to efficiently update large number of rows in a large/big table.

Goals
=====
The idea is to have minimum amount of redo generation for this operation.
The amount of rollback generated should also be minimal.

Scenario
=======
We have a table of say 30 million records.
We want to update either say 1 million or 25 million records in this table, then in both situations the solution given below can be used for efficient results.

Solution

The direct update of so many records will result in generation of large amounts of undo and redo.

We can use the nologging option in the "create table as select" or "Append" hint in the insert statement to have minimal redo generated.

Below are the steps to accomplish this:

Method 1
=======
1) sql> create table large(col1 number primary key, col2 number,col3 varchar2(1000));
2) sql> begin
for i in 1..10000 loop
insert into large values (i,i,'bb');
end loop;
end;
3) sql> commit;

----- Suppose we need to update the col2 column value to col2 + 10 -----

4) sql> create table large_dup nologging as select col1, col2+10 as col2, col3 from large;
** This method of creating table has a disadvantage, we will lose any precision that we may have defined on our number columns. To avoid this refer to Method 2.

5) sql> drop table large;
6) sql> rename large_dup to large;
7) ### Recreate indexes, constraints, triggers on the table


Method 2
=======

## The first 3 steps are the same as above

4) Create the large_dup table using the table definition of large table, but with the nologging option set.
- In 9i the table definition can be obtained from dbms_metadata package.
- In 8i we can use the exp/imp with show=y.

5) sql>insert /*+ append */ into large_dup select col1, col2+10 as col2, col3 from large;
6) sql> drop table large;
7) sql> rename large_dup to large;
8) ### Recreate indexes, triggers on the table

In both the above methods we are recreating the table which needs to be updated. This is applicable only if the table is not currently being modified by any other sessions.

To enable future recovery, make sure to re-enable logging:
sql> alter the table <table_name> logging;

 and take a backup after logging has been enabled.