Oracle Database Discussions

For a benchmark test, I am running folowing simple procedure to update all reocords in a table.

  PROCEDURE UPDATE_CUSTOMER IS
  CURSOR cAllRecords IS SELECT * FROM CUSTOMER;
  BEGIN
       -- update all records in a loop
       FOR i IN cAllRecords
       LOOP
           UPDATE CUSTOMER SET car_reg = 'ABC123' WHERE custno = i.custno;
           COMMIT;
       END LOOP;
       DBMS_OUTPUT.put_line('Records updated');
  END UPDATE_CUSTOMER; 

The table has around 1 million records.

When the table is partitioned (3-way), it is running over 15 minutes!!
But for normal table, it finishes in 3 minutes!

What is going wrong? I repeated test multiple times with same result.

On the other hand, when I just fire my SQL like this:

 UPDATE tablename SET car_reg = 'ABC123' 

The partitioned table is 3 times faster!

Edited by: movilogo on May 6, 2009 2:52 PM