Physically loaded in Sorted order
test@10GR2> create table big_table_clust
2 nologging
3 as
4 select rownum as cust_id,
5 object_name as cust_name,
6 abs(dbms_random.random) as txn_amount,
7 to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
8 from all_objects;
Table created.
Elapsed: 00:00:08.74
test@10GR2> declare
2 l_max_id number;
3 begin
4 for i in 1..2
5 loop
6 select max(cust_id)
7 into l_max_id
8 from big_table_clust;
9
10 insert /*+ append */ into big_table_clust (cust_id,cust_name,txn_amount,dob)
11 select l_max_id+rownum-1, cust_name,txn_amount,dob
12 from big_table_clust;
13
14 commit;
15 end loop;
16 end;
17 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:01.76
test@10GR2>
test@10GR2>
test@10GR2> select cust_id,count(*)
2 from big_table_clust
3 group by cust_id
4 having count(*) > 1;
CUST_ID COUNT(*)
---------- ----------
112455 2
56228 2
Elapsed: 00:00:00.71
test@10GR2>
test@10GR2> create table t1(
2 x number constraint t1_pk primary key,
3 y varchar2(30),
4 z number constraint t1_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> create table t2(
2 x number constraint t2_pk primary key,
3 y varchar2(30),
4 z number constraint t2_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t3(
2 x number constraint t3_pk primary key,
3 y varchar2(30),
4 z number constraint t3_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> create table t4(
2 x number constraint t4_pk primary key,
3 y varchar2(30),
4 z number constraint t4_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t5(
2 x number constraint t5_pk primary key,
3 y varchar2(30),
4 z number constraint t5_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.06
test@10GR2>
test@10GR2>
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.12
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T4');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.06
test@10GR2>
test@10GR2>
test@10GR2> alter session set timed_statistics=true;
Session altered.
Elapsed: 00:00:00.00
test@10GR2>
test@10GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:01.12
test@10GR2>
test@10GR2>
test@10GR2> insert into t1 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_clust;
insert into t1 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_PK) violated
Elapsed: 00:00:01.70
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> insert into t2 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_clust
4 log errors reject limit unlimited;
224910 rows created.
Elapsed: 00:00:27.98
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.11
test@10GR2>
test@10GR2> insert /*+ append */ into t3 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_clust;
insert /*+ append */ into t3 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T3_PK) violated
Elapsed: 00:00:02.45
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
test@10GR2>
test@10GR2> insert /*+ append */ into t4 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_clust
4 log errors reject limit unlimited;
insert /*+ append */ into t4 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T4_PK) violated
Elapsed: 00:00:02.74
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.00
test@10GR2>
test@10GR2>
test@10GR2> declare
2 cursor c1 is select * from big_table_clust;
3 type t1 is table of c1%rowtype index by pls_integer;
4 r1 t1;
5 l_err_cnt number;
6 begin
7 open c1;
8
9 loop
10 fetch c1 bulk collect into r1 limit 1000;
11 begin
12 exit when r1.count = 0;
13 forall i in 1..r1.count save exceptions
14 insert /* plsql_bulk */ into t5 values r1(i);
15 exception
16 when others then
17 l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
18 end;
19 r1.delete;
20 end loop;
21
22 close c1;
23 end;
24 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:02.40
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.03
Randomly Sorted Order
test@10GR2> create table big_table_nonclust
2 nologging
3 as
4 select rownum as cust_id,
5 object_name as cust_name,
6 abs(dbms_random.random) as txn_amount,
7 to_date('01/01/1900','mm/dd/yyyy')+rownum as dob
8 from all_objects
9 order by dbms_random.random;
Table created.
Elapsed: 00:00:09.99
test@10GR2> declare
2 l_max_id number;
3 begin
4 for i in 1..2
5 loop
6 select max(cust_id)
7 into l_max_id
8 from big_table_nonclust;
9
10 insert /*+ append */ into big_table_nonclust (cust_id,cust_name,txn_amount,dob)
11 select l_max_id+rownum-1, cust_name,txn_amount,dob
12 from big_table_nonclust
13 order by dbms_random.random;
14
15 commit;
16 end loop;
17 end;
18 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:04.14
test@10GR2> create table t1(
2 x number constraint t1_pk primary key,
3 y varchar2(30),
4 z number constraint t1_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> create table t2(
2 x number constraint t2_pk primary key,
3 y varchar2(30),
4 z number constraint t2_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t3(
2 x number constraint t3_pk primary key,
3 y varchar2(30),
4 z number constraint t3_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t4(
2 x number constraint t4_pk primary key,
3 y varchar2(30),
4 z number constraint t4_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.04
test@10GR2>
test@10GR2> create table t5(
2 x number constraint t5_pk primary key,
3 y varchar2(30),
4 z number constraint t5_chk check (z is not null),
5 dt date
6 );
Table created.
Elapsed: 00:00:00.06
test@10GR2>
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T2');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.10
test@10GR2> exec dbms_errlog.create_error_log(dml_table_name=>'T4');
PL/SQL procedure successfully completed.
Elapsed: 00:00:00.09
test@10GR2>
test@10GR2>
test@10GR2> alter session set timed_statistics=true;
Session altered.
Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> alter session set events '10046 trace name context forever, level 12';
Session altered.
Elapsed: 00:00:01.07
test@10GR2>
test@10GR2> insert into t1 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_nonclust;
insert into t1 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T1_PK) violated
Elapsed: 00:00:04.84
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> insert into t2 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_nonclust
4 log errors reject limit unlimited;
224914 rows created.
Elapsed: 00:00:22.88
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.07
test@10GR2>
test@10GR2> insert /*+ append */ into t3 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_nonclust;
insert /*+ append */ into t3 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T3_PK) violated
Elapsed: 00:00:08.06
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
test@10GR2>
test@10GR2> insert /*+ append */ into t4 (x,y,z,dt)
2 select cust_id, cust_name,txn_amount,dob
3 from big_table_nonclust
4 log errors reject limit unlimited;
insert /*+ append */ into t4 (x,y,z,dt)
*
ERROR at line 1:
ORA-00001: unique constraint (TEST.T4_PK) violated
Elapsed: 00:00:03.12
test@10GR2>
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.01
test@10GR2> declare
2 cursor c1 is select * from big_table_nonclust;
3 type t1 is table of c1%rowtype index by pls_integer;
4 r1 t1;
5 l_err_cnt number;
6 begin
7 open c1;
8
9 loop
10 fetch c1 bulk collect into r1 limit 1000;
11 begin
12 exit when r1.count = 0;
13 forall i in 1..r1.count save exceptions
14 insert /* plsql_bulk */ into t5 values r1(i);
15 exception
16 when others then
17 l_err_cnt := l_err_cnt + sql%bulk_exceptions.count;
18 end;
19 r1.delete;
20 end loop;
21
22 close c1;
23 end;
24 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:18.34
test@10GR2> commit;
Commit complete.
Elapsed: 00:00:00.04
test@10GR2>
In short – direct path, LOG ERRORS rocks, when your data's are good especially dml error logging does not work with direct path loads and unique constraints - if you violate a unique constraint with direct path - you rollback the entire statement at the end.
| Clusterd Table | CPU | Elapsed | Physical IO | Logical IO | Rows |
| Conventional path loading | 0.31 | 0.41 | 1 | 9032 | 0 |
| Conventional path loading With Error logging | 6.37 | 27.6 | 0 | 706887 | 224910 |
| Direct path loading | 0.65 | 2.11 | 0 | 7301 | 224912 |
| Direct path loading With Error logging | 0.71 | 2.39 | 0 | 7344 | 224912 |
| Plsql Bulk binding with SAVE Exceptions | 1.06 | 1.47 | 0 | 31304 | 224910 |
| NON - Clusterd Table | CPU | Elapsed | Physical IO | Logical IO | Rows |
| Conventional path loading | 0.31 | 0.41 | 1 | 9032 | 0 |
| Conventional path loading With Error logging | 6.37 | 27.6 | 0 | 706887 | 224910 |
| Direct path loading | 0.65 | 2.11 | 0 | 7301 | 224912 |
| Direct path loading With Error logging | 0.71 | 2.39 | 0 | 7344 | 224912 |
| Plsql Bulk binding with SAVE Exceptions | 1.06 | 1.47 | 0 | 31304 | 224910 |
