C:\Users\Administrator>sqlplus /nolog
SQL*Plus: Release 11.2.0.1.0 Production on 星期一 7月 5 20:47:21 2010
Copyright (c) 1982, 2010, Oracle. All rights reserved.
SQL> conn test/test
已连接。
SQL> create table t as select * from dba_objects;
表已创建。
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name='T';
BLOCKS
----------
1129
SQL> create global temporary table t_b1 as select * from t where 1=0;
表已创建。
SQL> -- Created on 2010-4-29 by ADMINISTRATOR
SQL> declare
2 -- Local variables here
3 num_cnt pls_integer;
4 i pls_integer;
5 j pls_integer;
6
7 type tRow is table of rowid;
8 rows tRow := tRow();
9 begin
10 -- Test statements here
11 select count(*)
12 into num_cnt
13 from t;
14
15 for i in 0..10000 loop
16 select rowid bulk collect
17 into rows
18 from t sample(0.01);
19
20 forall j in 1..rows.Count
21 insert into t_b1
22 select * from t where rowid=rows(j);
23
24 forall j in 1..rows.Count
25 delete from t where rowid=rows(j);
26
27 insert /*+ append */ into t select * from t_b1;
28
29 commit;
30 end loop;
31
32 end;
33 /
PL/SQL 过程已成功完成。
SQL> /
PL/SQL 过程已成功完成。
SQL> /
PL/SQL 过程已成功完成。
SQL> /
PL/SQL 过程已成功完成。
SQL> /
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name='T';
BLOCKS
----------
2495
SQL> -- Created on 2010-4-29 by ADMINISTRATOR
SQL> declare
2 -- Local variables here
3 num_cnt pls_integer;
4 i pls_integer;
5 j pls_integer;
6
7 type tRow is table of rowid;
8 rows tRow := tRow();
9 begin
10 -- Test statements here
11 select count(*)
12 into num_cnt
13 from t;
14
15 for i in 0..10000 loop
16 select rowid bulk collect
17 into rows
18 from t sample(0.05);
19
20 forall j in 1..rows.Count
21 insert into t_b1
22 select * from t where rowid=rows(j);
23
24 forall j in 1..rows.Count
25 delete from t where rowid=rows(j);
26
27 insert /*+ append */ into t select * from t_b1;
28
29 commit;
30 end loop;
31
32 end;
33 /
PL/SQL 过程已成功完成。
SQL> /
PL/SQL 过程已成功完成。
SQL> /
PL/SQL 过程已成功完成。
SQL> exec dbms_stats.gather_table_stats(user,'T');
PL/SQL 过程已成功完成。
SQL> select blocks from user_tables where table_name='T';
BLOCKS
----------
6767
by sundog315
