When loading data into a table, instead of doing update for exisitng data and insert for new data, you can now combine the two into one step - merge. This may be beneficial in a data warehouse environment.

-- Tested with Oracle9i Release 9.2.0.1.0 on WindowsXP

SQL> create table t1 as select object_id, object_name from dba_objects where rownum < 20001;

Table created.

SQL> create table t2 as select object_id, object_name from dba_objects;

Table created.

SQL> create unique index t1_idx on t1(object_id);

Index created.

SQL> create unique index t2_idx on t2(object_id);

Index created.

SQL> set timing on
SQL> update (select t1.object_name, t2.object_name oname from t1, t2 where t1.object_id=t2.object_id) set object_name=oname;

20000 rows updated.

Elapsed: 00:00:01.04

Execution Plan
----------------------------------------------------------
   0      UPDATE STATEMENT Optimizer=CHOOSE
   1    0   UPDATE OF 'T1'
   2    1     NESTED LOOPS
   3    2       TABLE ACCESS (FULL) OF 'T1'
   4    2       TABLE ACCESS (BY INDEX ROWID) OF 'T2'
   5    4         INDEX (UNIQUE SCAN) OF 'T2_IDX' (UNIQUE)



SQL> insert into t1 select * from t2 where object_id is not null and not exists (select * from t1 wh
ere t1.object_id=t2.object_id);

10763 rows created.

Elapsed: 00:00:01.00

Execution Plan
----------------------------------------------------------
   0      INSERT STATEMENT Optimizer=CHOOSE
   1    0   FILTER
   2    1     TABLE ACCESS (FULL) OF 'T2'
   3    1     INDEX (UNIQUE SCAN) OF 'T1_IDX' (UNIQUE)



SQL> rollback;

Rollback complete.

Elapsed: 00:00:00.05

SQL> merge into t1 using
  2  (select * from t2 where object_id is not null) t2
  3  on (t1.object_id=t2.object_id)
  4  when matched then
  5     update set t1.object_name=t2.object_name
  6  when not matched then
  7     insert (object_id, object_name)
  8     values (t2.object_id, t2.object_name);

30763 rows merged.

Elapsed: 00:00:01.04

Execution Plan
----------------------------------------------------------
   0      MERGE STATEMENT Optimizer=CHOOSE (Cost=4750240 Card=33934128
          57 Bytes=760124479968)

   1    0   MERGE OF 'T1'
   2    1     VIEW
   3    2       HASH JOIN (OUTER) (Cost=32 Card=629 Bytes=103785)
   4    3         TABLE ACCESS (FULL) OF 'T2' (Cost=16 Card=629 Bytes=49691)
   5    3         TABLE ACCESS (FULL) OF 'T1' (Cost=12 Card=8577 Bytes=737622)

SQL> 


-- conclusions:
-- There seem to be some performance improvement using MERGE, based on this
-- relatively small data set.