Hi Tom,

i have a requirement where i need to update 2 million records in 120 tables (ORACLE).i have created indexes on each table since the same column is referred in where clause and in update statement.Each table has 1 lakh records on an average.Also i have disabled the foriegn key constraints before update.

For this i have a written procedure where it will fetch the records and run the update statments.From the blogs i came to know Bulk Collect & FORALL is good option in oracle .But there is only small difference in time that i can see to run the update statements.Are they any other approach to increase the performance and reduce the time to update the records into the tables.


Could you please help me to find a solution.

Regards,
K.Kishore

SQL> create table t1
  2  as select d.* from dba_objects d,
  3   ( select 1 from dual connect by level <= 20 );

Table created.

SQL>
SQL> select count(*) from t1;

  COUNT(*)
----------
   1939380

SQL>
SQL> set timing on
SQL> update t1 set object_id = object_id + 1;

1939380 rows updated.

Elapsed: 00:00:47.09

SQL> create index t1_ix on t1 ( object_id );

Index created.

Elapsed: 00:00:05.86

SQL> update t1 set object_id = object_id + 1;

1939380 rows updated.

Elapsed: 00:02:17.85