OS： Red Hat Enterprise Linux ES relea...

OS： Red Hat Enterprise Linux ES release 4 (Nahant Update 4)

DB： Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - Prod

SGA：

      Total System Global Area  880803840 bytes
      Fixed Size                  1222384 bytes
      Variable Size             167774480 bytes
      Database Buffers          704643072 bytes
      Redo Buffers                7163904 bytes
PGA：281M

 

-- 二表关联更新(200万，200万，表结构同dba_tables)
-- T01.Id 有索引
SQL> select count(*) from t01;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t02;

 COUNT(*)
----------
 2000000

SQL> @/tmp/3.sql
04:07:44
04:09:35

PL/SQL procedure successfully completed.

SQL> ! cat /tmp/3.sql
declare
 maxrows number default 1000;
 cnt number;
 type type_id is table of number index by pls_integer;
 ids type_id;
 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 -- INDEX BY PLS_INTEGER;
 cursor cur is
 select /*+ hash(t01,t02) */ t02.id
 from t01,t02
 where t01.id=t02.id;
begin
 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 cnt := cnt + 1;
 open cur;
 loop
 exit when cur%notfound;
 fetch cur bulk collect into ids limit maxrows;
 forall i in 1 .. ids.count
 update t01 set val = 'abc'
 where id = ids(i);
 cnt := cnt + 1;
 commit;
 --dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 end loop;
 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
end;
/

-- 六表关联更新(200万，200万，200万，200万，200万，200万，表结构同dba_tables)
-- T01.Id 有索引
SQL> select count(*) from t01;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t02;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t03;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t04;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t05;

 COUNT(*)
----------
 2000000

SQL> select count(*) from t06;

 COUNT(*)
----------
 2000000

SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 type type_id is table of number index by pls_integer;
 5 ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id
 16 and rownum<=500000;
 17 begin
 18 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 19 cnt := cnt + 1;
 20 open cur;
 21 loop
 22 exit when cur%notfound;
 23 fetch cur bulk collect into ids limit maxrows;
 24 forall i in 1 .. ids.count
 25 update t01 set val = 'abc'
 26 where id = ids(i);
 27 cnt := cnt + 1;
 28 commit;
 29 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30 end loop;
 31 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 32* end;
SQL> /
04:33:45
04:35:09

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 type type_id is table of number index by pls_integer;
 5 ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id;
 16 -- and rownum<=500000;
 17 begin
 18 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 19 cnt := cnt + 1;
 20 open cur;
 21 loop
 22 exit when cur%notfound;
 23 fetch cur bulk collect into ids limit maxrows;
 24 forall i in 1 .. ids.count
 25 update t01 set val = 'abc'
 26 where id = ids(i);
 27 cnt := cnt + 1;
 28 commit;
 29 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 30 end loop;
 31 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 32* end;
SQL> /
04:35:38
04:38:57

PL/SQL procedure successfully completed.

-- fetch 二个字段的情况
SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 -- type type_id is table of number index by pls_integer;
 5 -- ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id,t06.table_name
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id
 16 and rownum<=100000;
 17 type type_id is table of t02.id%type index by pls_integer;
 18 type type_name is table of t06.table_name%type index by pls_integer;
 19 ids type_id;
 20 names type_name;
 21 begin
 22 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 23 cnt := cnt + 1;
 24 open cur;
 25 loop
 26 exit when cur%notfound;
 27 fetch cur bulk collect into ids,names limit maxrows;
 28 forall i in 1 .. ids.count
 29 update t01 set val = names(i)
 30 where id = ids(i);
 31 cnt := cnt + 1;
 32 commit;
 33 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 34 end loop;
 35 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 36* end;
SQL> /
05:05:38
05:06:29

PL/SQL procedure successfully completed.

SQL> edit
Wrote file afiedt.buf

 1 declare
 2 maxrows number default 1000;
 3 cnt number;
 4 -- type type_id is table of number index by pls_integer;
 5 -- ids type_id;
 6 --TYPE employee_aat IS TABLE OF employee.employee_id%TYPE
 7 -- INDEX BY PLS_INTEGER;
 8 cursor cur is
 9 select /* hash(t01,t02) */ t02.id,t06.table_name
 10 from t01,t02,t03,t04,t05,t06
 11 where t01.id=t02.id
 12 and t02.id=t03.id
 13 and t03.id=t04.id
 14 and t04.id=t05.id
 15 and t05.id=t06.id;
 16 -- and rownum<=100000;
 17 type type_id is table of t02.id%type index by pls_integer;
 18 type type_name is table of t06.table_name%type index by pls_integer;
 19 ids type_id;
 20 names type_name;
 21 begin
 22 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 23 cnt := cnt + 1;
 24 open cur;
 25 loop
 26 exit when cur%notfound;
 27 fetch cur bulk collect into ids,names limit maxrows;
 28 forall i in 1 .. ids.count
 29 update t01 set val = names(i)
 30 where id = ids(i);
 31 cnt := cnt + 1;
 32 commit;
 33 -- dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 34 end loop;
 35 dbms_output.put_line(to_char(sysdate,'hh24:mi:ss'));
 36* end;
SQL> /
05:07:06
05:11:05

PL/SQL procedure successfully completed.

SQL> select id,table_name,val from t01 where rownum<10;

        ID TABLE_NAME                     VAL
---------- ------------------------------ ------------------------------
         1 CON$                           CON$
         2 UNDO$                          UNDO$
         3 CDEF$                          CDEF$
         4 CCOL$                          CCOL$
         5 PROXY_ROLE_DATA$               PROXY_ROLE_DATA$
         6 FILE$                          FILE$
         7 FET$                           FET$
         8 TS$                            TS$
         9 PROXY_DATA$                    PROXY_DATA$

9 rows selected.