Update large number of rows in a table [ID 268395.1]
来源:互联网 发布:淘宝子账号如何设置 编辑:程序博客网 时间:2024/05/16 02:41
修改时间 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.
- Update large number of rows in a table [ID 268395.1]
- Fastest Way to Update Rows in a Large Table in SQL Server
- Deleting large number of rows
- Invalid update: invalid number of rows in section 0. The number of rows contained in an ....
- quick count the number of the rows in a big table
- delete many rows from a large table
- Selecting Rows Randomly from a Large Table
- IOS常见bug.001—'NSInternalInconsistencyException', reason: 'Invalid update: invalid number of rows in
- IOS删除tableview'NSInternalInconsistencyException', reason: 'Invalid update: invalid number of rows in
- 一个奇葩错误:Invalid update: invalid number of rows in section 0
- iOS开发bug消灭之:Invalid update: invalid number of rows in section 0.
- 一个奇葩错误:Invalid update: invalid number of rows in section 0
- 转:How to submit the rows of a repeating table in InfoPath to a SharePoint list
- delete duplicate rows in a table
- 'Invalid update: invalid number of sections. The number of sections contained in the table view aft
- 删除tableview的section遇到crash:The number of rows contained in an existing section after the update
- How to delete a large number of data in SharePoint for List when refreshing data?
- Get the rows count of all the table in Sybase
- Extjs、ASP.net前后台Grid分页 数据库多表交互
- ObjectCreator3
- 不引入第三变量的变量交换的几种方法
- Phone起步-2:iPhone常用控件、UIActionSheet、UIAlertView
- 敏捷软件交付项目管理及相关工具
- Update large number of rows in a table [ID 268395.1]
- Android在TQ2440开发板上的移植
- nginx安装
- Oracle 中的 TO_DATE 和 TO_CHAR 函数
- 使用apache benchmark(ab) 测试报错: apr_socket_recv: Connection timed out (110)
- 分布式系统资源
- Linux 内核内存检测工具 - Kmemcheck (转)
- Linux设备驱动开发环境的搭建
- 面向对象的三个基本特征(讲解)