--在允许的情况下可以 1、create table newA as select id,name,age+1 age from A; 2、drop table A; 3、rename newA to A;
为什么这样很低效? update table_name set num=num+1
设置自动提交行数,会快很多 set autocommit 1000; update table_name set age=age+1;
dml 操作的缺点: 1、因为一次性修改的数据太多,而导致回滚段、临时表空间或者数据表空间爆满而失败,结果回滚又需要很长时间。没有指定分段提交的imp(导入) 操作也会出现这种情况。 2、因为该表的DML很频繁 ,如果执行的维护操作阻塞了这些DML,可能导致数据库的锁与阻塞很多,影响前台应用的正常使用。 3、因为该表利用MV或Trigger 做复制,瞬间几百万的更新可能会导致日志表爆满,甚至复制与同步出错。 ||||||||||||||| 建议 以上的过程用到了前一个案例的方法,用到了如下的几项技术: 1>循环更新,及时提交,减少对系统的压力与冲击 2>采用跟新标志,可以断点继续。如以上的案例中,跟新成功,标志位则跟新为1,这样, 通过查询临时表,就可以知道有多少数据被跟新了。同时,如果跟新是因为意外的情况, 中途终止了,因为没有根系的标志都是0,很容易断点跟新,而不用从头开始新的更新。|||||||||| for example 案例2: 假定有一个1000W级别的数据表,如my_test,需要根据这个表的user_id汇总记录,跟新到my_users 表中,关联条件是 my_test.user_id=my_users.id。汇总完成以后的记录可能是100W,my_users 没有MV或触发器在上面,但是又比较频繁的DML。 也就是说,跟新的记录数跟案例1一样,只有100W条记录,但是数据的获取逻辑复杂了很多,需要从别的表汇总以后获得。update my_users u set user_cnt= (select user_cnt from (select user_id,count(*) user_cnt from my_test group by user_id) tmp where tmp.user_id=u.id) where exists (select null from my_test t where u.id=t.user_id); 以上的语句肯定是能执行的,但是效率不能保证,因为在关联跟新中还需要汇总,这样不仅耗费了大量的时间,而且,这个跟新与汇总操作 将耗费大量的Undo与Temp空间,可能导致Undo以及Temp都不足。 所以在这种方案中,建议先建立一个汇总表,把需要跟新的数据汇总出来,如: SQL>create table tmp_test as select user_id,count(*) user_cnt ,0 flags from mytest group by user_id; 但是,汇总表也有一个问题,就是汇总表创建的时间点之后的跟新不能反映到跟新中去。不过,以上的一个Update语句一样不能解决这样的 问题。这个问题属于业务问题,这里假定my_test 可以是静态的表,或者新增加的数据可以跟新时间戳以后再同步。以上创建表的方式可以采用Nologging 与Append 的方式写入,这样的方式可以提高临时表的创建速度。在表的创建过程中,需要关注Undo 与Temp的使用情况。 监控undo 空间情况: SQL> select tablespace_name , 2 round(sum(bytes)/(1024*1024),2) free_space 3 from dba_free_space 4 where tablespace_name='UNDOTBS1' 5 group by tablespace_name;TABLESPACE_NAME FREE_SPACE ------------------------------ ---------- UNDOTBS1 167.44 监控临时段的使用情况:SQL> select sum(nvl(blocks,0))*8/1024 Msize from v$sort_usage; MSIZE ---------- 临时表创建之后,我们就可以根据临时表来跟新 my_users 了,考虑到 my_users 上海由dml业务存在,这里也必须写一个循环来操作: declare row_num number :=0; begin for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop --跟新目标表 update my_users t set t.user_cnt = c_user.user_cnt where id=c_user.user_id; --跟新临时表的标记位 update tmp_test f set f.flags = 1 where user_id=c_user.user_id; --跟新临时表的标记位 update tmp_test f set f.flags = 1 where user_id = c_user.user_id; --1000条提交一次 if mod(row_num,1000)=0 then commit; end if; end loop; commit ; end;
跟我想的一样,表复制的速度比update的速度要快不少。
+1 设置自动提交行数,会快很多 set autocommit 1000; update table_name set age=age+1; +1
大批量更新会写很多UNDO,这个对资源消耗较多
批量更新速度会很快! 以下是我的:oracle高效更新测试代码--1.创建测试表,并插入200万条记录 create table tb as select rownum id, '测试'||rownum name from dual connect by rownum<=2000000;
--2.删除表数据 --(1)直接使用delete删除 SQL> set timing on; SQL> update tb set name = '测试1';
2000000 rows updated
Executed in 30.641 seconds--(2)使用存储过程删除 --创建存储过程 create or replace procedure forall_update_test /** * PURPOSE : oracle中高效删除的测试 * REVISION> Version Date Author Description -------- -------- ---------- ------------------- 1.0 20100511 huangbiquan 1. create the procedure */ is type rid_arr is table of rowid index by binary_integer; v_rowid rid_arr; begin select rowid bulk collect into v_rowid from tb where rownum <= 10000; forall i in 1..v_rowid.count update tb set name = '测试2' where rowid = v_rowid(i); dbms_output.put_line('update rows:'||v_rowid.count);end;--使用存储过程删除 SQL> exec dbms_output.enable(999999999999999999999999999999);
PL/SQL procedure successfully completedSQL> set serveroutput on; SQL> set timing on;declare v_total pls_integer; begin select count(*) into v_total from tb; while(v_total > 0) loop execute immediate 'begin forall_update_test; end;'; commit; v_total := v_total - 10000; end loop; end;SQL> declare 2 v_total pls_integer; 3 begin 4 select count(*) 5 into v_total 6 from tb; 7 8 while(v_total > 0) loop 9 execute immediate 'begin forall_update_test; end;'; 10 commit; 11 v_total := v_total - 10000; 12 13 end loop; 14 end; 15 /
我就会这样update table_name set num=num+1 ;
1、create table newA as select id,name,age+1 age from A;
2、drop table A;
3、rename newA to A;
update table_name set num=num+1
set autocommit 1000;
update table_name set age=age+1;
1、因为一次性修改的数据太多,而导致回滚段、临时表空间或者数据表空间爆满而失败,结果回滚又需要很长时间。没有指定分段提交的imp(导入)
操作也会出现这种情况。
2、因为该表的DML很频繁 ,如果执行的维护操作阻塞了这些DML,可能导致数据库的锁与阻塞很多,影响前台应用的正常使用。
3、因为该表利用MV或Trigger 做复制,瞬间几百万的更新可能会导致日志表爆满,甚至复制与同步出错。
|||||||||||||||
建议
以上的过程用到了前一个案例的方法,用到了如下的几项技术:
1>循环更新,及时提交,减少对系统的压力与冲击
2>采用跟新标志,可以断点继续。如以上的案例中,跟新成功,标志位则跟新为1,这样,
通过查询临时表,就可以知道有多少数据被跟新了。同时,如果跟新是因为意外的情况,
中途终止了,因为没有根系的标志都是0,很容易断点跟新,而不用从头开始新的更新。||||||||||
for example
案例2:
假定有一个1000W级别的数据表,如my_test,需要根据这个表的user_id汇总记录,跟新到my_users 表中,关联条件是
my_test.user_id=my_users.id。汇总完成以后的记录可能是100W,my_users 没有MV或触发器在上面,但是又比较频繁的DML。
也就是说,跟新的记录数跟案例1一样,只有100W条记录,但是数据的获取逻辑复杂了很多,需要从别的表汇总以后获得。update my_users u set user_cnt=
(select user_cnt from
(select user_id,count(*) user_cnt from my_test group by user_id) tmp
where tmp.user_id=u.id)
where exists
(select null from my_test t where u.id=t.user_id);
以上的语句肯定是能执行的,但是效率不能保证,因为在关联跟新中还需要汇总,这样不仅耗费了大量的时间,而且,这个跟新与汇总操作
将耗费大量的Undo与Temp空间,可能导致Undo以及Temp都不足。
所以在这种方案中,建议先建立一个汇总表,把需要跟新的数据汇总出来,如:
SQL>create table tmp_test as select user_id,count(*) user_cnt ,0 flags from mytest group by user_id;
但是,汇总表也有一个问题,就是汇总表创建的时间点之后的跟新不能反映到跟新中去。不过,以上的一个Update语句一样不能解决这样的
问题。这个问题属于业务问题,这里假定my_test 可以是静态的表,或者新增加的数据可以跟新时间戳以后再同步。以上创建表的方式可以采用Nologging 与Append 的方式写入,这样的方式可以提高临时表的创建速度。在表的创建过程中,需要关注Undo
与Temp的使用情况。
监控undo 空间情况:
SQL> select tablespace_name ,
2 round(sum(bytes)/(1024*1024),2) free_space
3 from dba_free_space
4 where tablespace_name='UNDOTBS1'
5 group by tablespace_name;TABLESPACE_NAME FREE_SPACE
------------------------------ ----------
UNDOTBS1 167.44
监控临时段的使用情况:SQL> select sum(nvl(blocks,0))*8/1024 Msize from v$sort_usage; MSIZE
----------
临时表创建之后,我们就可以根据临时表来跟新 my_users 了,考虑到 my_users 上海由dml业务存在,这里也必须写一个循环来操作:
declare
row_num number :=0;
begin
for c_user in (select user_id,user_cnt from tmp_test t where flags = 0) loop
--跟新目标表
update my_users t set t.user_cnt = c_user.user_cnt where id=c_user.user_id;
--跟新临时表的标记位
update tmp_test f set f.flags = 1 where user_id=c_user.user_id;
--跟新临时表的标记位
update tmp_test f set f.flags = 1 where user_id = c_user.user_id;
--1000条提交一次
if mod(row_num,1000)=0 then
commit;
end if;
end loop;
commit ;
end;
设置自动提交行数,会快很多
set autocommit 1000;
update table_name set age=age+1;
+1
以下是我的:oracle高效更新测试代码--1.创建测试表,并插入200万条记录
create table tb
as
select rownum id, '测试'||rownum name
from dual
connect by rownum<=2000000;
--2.删除表数据
--(1)直接使用delete删除
SQL> set timing on;
SQL> update tb set name = '测试1';
2000000 rows updated
Executed in 30.641 seconds--(2)使用存储过程删除
--创建存储过程
create or replace procedure forall_update_test
/**
* PURPOSE : oracle中高效删除的测试
* REVISION>
Version Date Author Description
-------- -------- ---------- -------------------
1.0 20100511 huangbiquan 1. create the procedure
*/
is
type rid_arr is table of rowid index by binary_integer;
v_rowid rid_arr;
begin
select rowid
bulk collect
into v_rowid
from tb
where rownum <= 10000; forall i in 1..v_rowid.count
update tb set name = '测试2' where rowid = v_rowid(i);
dbms_output.put_line('update rows:'||v_rowid.count);end;--使用存储过程删除
SQL> exec dbms_output.enable(999999999999999999999999999999);
PL/SQL procedure successfully completedSQL> set serveroutput on;
SQL> set timing on;declare
v_total pls_integer;
begin
select count(*)
into v_total
from tb; while(v_total > 0) loop
execute immediate 'begin forall_update_test; end;';
commit;
v_total := v_total - 10000; end loop;
end;SQL> declare
2 v_total pls_integer;
3 begin
4 select count(*)
5 into v_total
6 from tb;
7
8 while(v_total > 0) loop
9 execute immediate 'begin forall_update_test; end;';
10 commit;
11 v_total := v_total - 10000;
12
13 end loop;
14 end;
15 /
update rows:10000
update rows:10000
update rows:10000
......
update rows:10000
PL/SQL procedure successfully completed
Executed in 14.86 seconds
注意
update rows:10000
update rows:10000
update rows:10000
update rows:10000update rows:10000
update rows:10000
update rows:10000
PL/SQL procedure successfully completed
Executed in 311 seconds
那是我之前做的oracle中高效删除实验的注释!