oracle海量表数据删除及清理高效方法

 

http://hi.baidu.com/songyunkui/item/04aba589f7ffa705100ef3c1

----分别创建两个源表和目标表
SQL> set time on
13:10:49 SQL> set timing on
 
13:11:13 SQL> create table t_source(a int,b int);
 
Table created
 
Executed in 0.016 seconds
 
13:11:15 SQL> create table t_target(a int,b int);
 
Table created
 
Executed in 0.016 seconds
 
13:11:32 SQL>

13:15:49 SQL> alter system set pga_aggregate_target=2g;
 
alter system set pga_aggregate_target=2g
 
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-00840: PGA_AGGREGATE_TARGET cannot be modified to the specified value

---分别向t_source和t_target插入数据
13:55:26 SQL> insert into t_source select level,level+2 from dual connect by level<=9e5;
 
900000 rows inserted
 
Executed in 0.983 seconds
 
13:55:31 SQL> commit;
 
Commit complete
 
Executed in 0 seconds
 
13:55:37 SQL> insert into t_target select level,level+2 from dual connect by level<=9e5;
 
900000 rows inserted
 
Executed in 0.905 seconds
 
13:55:52 SQL> commit;

 

declare

  maxrows      number default 5000;

  row_id_table dbms_sql.Urowid_Table;

  p_id_table   dbms_sql.Number_Table;

  cursor acnt_first_cur is

    select /*+ use_hash(t1,t2) parallel(t1,4) parallel(t2,4) */

     t2.login_region, t1.rowid

      from account_first_login t1, account_active_day t2

     where t1.account = t2.account

       and t1.login_date = t2.login_date

     order by t1.rowid;

begin

  open acnt_first_cur;

  loop

    exit when acnt_first_cur%notfound;

    fetch acnt_first_cur bulk collect

      into p_id_table, row_id_table limit maxrows;

    forall i in 1 .. row_id_table.count

      update account_first_login

         set login_region = p_id_table(i)

       where rowid = row_id_table(i);

    commit;

  end loop;

end;

---编写的批量更新海量表的plsql语句
declare
maxrows pls_integer default 5000;
row_id_table dbms_sql.Urowid_Table;
p_b_table dbms_sql.Number_Table;
cursor cur_1 is
select t_source.b,t_target.rowid
from t_source,t_target
where t_source.a=t_target.a
order by t_target.rowid;
begin
 open cur_1;
 loop
      exit when cur_1%notfound;
      fetch cur_1 bulk collect into p_b_table,row_id_table
      limit maxrows;
      forall i in 1..row_id_table.count
       update t_target
       set b=p_b_table(i)
       where rowid=row_id_table(i);
       commit;
  end loop;
end;
 
ORA-01410: invalid ROWID ---报错因为cursor提取的是t_source的rowid,而更新却是t_target的rowid
ORA-06512: at line 17

---dbv验证无物理坏块
C:\Users\123>dbv file=C:\TBS_HANG1.DBF logfile=c:\tbs_hang.log

DBVERIFY: Release 11.2.0.1.0 - Production on Mon Jan 28 15:22:20 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY: Release 11.2.0.1.0 - Production on Mon Jan 28 15:22:20 2013

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

DBVERIFY - Verification starting : FILE = C:\TBS_HANG1.DBF

DBVERIFY - Verification complete

Total Pages Examined         : 225912
Total Pages Processed (Data) : 219083
Total Pages Failing   (Data) : 0
Total Pages Processed (Index): 511
Total Pages Failing   (Index): 0
Total Pages Processed (Other): 1037
Total Pages Processed (Seg)  : 0
Total Pages Failing   (Seg)  : 0
Total Pages Empty            : 5281
Total Pages Marked Corrupt   : 0
Total Pages Influx           : 0
Total Pages Encrypted        : 0
Highest block SCN            : 2104663 (0.2104663)

 

15:25:40 SQL> declare
           2  maxrows pls_integer default 5000;
           3  row_id_table dbms_sql.Urowid_Table;
           4  p_b_table dbms_sql.Number_Table;
           5  cursor cur_1 is
           6  select t_source.b,t_target.rowid
           7  from t_source,t_target
           8  where t_source.a=t_target.a
           9  order by t_target.rowid;
          10  begin
          11   open cur_1;
          12   loop
          13        exit when cur_1%notfound;
          14        fetch cur_1 bulk collect into p_b_table,row_id_table
          15        limit maxrows;
          16        forall i in 1..row_id_table.count
          17         update t_target
          18         set b=p_b_table(i)
          19         where rowid=row_id_table(i);
          20         commit;
          21    end loop;
          22  end;
          23  /
 
PL/SQL procedure successfully completed
 
Executed in 9.313 seconds

---速度非常快,仅用时9秒多

---如果想删除海量表的数据,是否可用上述的方法

---如果仅删除单表的数据,不关联,好似很快删除完毕
15:30:29 SQL> delete from t_target where a<=400000;
 
400000 rows deleted
 
Executed in 3.291 seconds
 
15:31:12 SQL> commit;
 
Commit complete
 
Executed in 0.031 seconds

---但一般情况可能删除海量表要关联其它的表
--如下sql运行很久皆不完毕
16:30:22 SQL> delete from t_target tt where tt.a =(select ts.a from t_source ts where ts.a=tt.a and rownum<=200000);

---我们故技重用,采用plsql forall,bulk collect,看下效果又是如何

declare
maxrows pls_integer default 5000;
row_id_table dbms_sql.Urowid_Table;
p_b_table dbms_sql.Number_Table;
cursor cur_1 is
select t_source.a,t_target.rowid
from t_source,t_target
where t_source.a=t_target.a and
      rownum<=200000
order by t_target.rowid;
begin
 open cur_1;
 loop
      exit when cur_1%notfound;
      fetch cur_1 bulk collect into p_b_table,row_id_table
      limit maxrows;
      forall i in 1..row_id_table.count
       --update t_target
       --set b=p_b_table(i)
       --where rowid=row_id_table(i);
       delete from t_target tt where tt.rowid=row_id_table(i) and tt.a=p_b_table(i);
       commit;
  end loop;
end;

PL/SQL procedure successfully completed
 
Executed in 3.354 seconds ---用时仅3秒多

---验证删除表的数据,确实200000记录已删除
16:37:06 SQL> select count(*) from t_target;
 
  COUNT(*)
----------
    700000
 
Executed in 0.015 seconds

小结:
      1,plsql集合操作确实效能非常高
      2,对于plsql number_table,rowid_table集合类型的理解不到位
      3,编写plsql代码能力仍须提升

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/9240380/viewspace-753427/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/9240380/viewspace-753427/