----分別建立兩個源表和目標表
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

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程式碼能力仍須提升

oracle海量表資料刪除及清理高效方法

相關文章