vsbabu.org

UPDATE statement

In its simple form, it involves:

UPDATE employees SET salary = salary + 100.0;
COMMIT;

Though it is very easy, there is a situation where this may not work.

If the table has lots of records, and if you are updating many of them, there is a chance that your rollback segment might get exhausted.

If you encounter such situation, the solution is to process records in batches and then commit after every batch.

PL/SQL block

This indeed looks more cumbersome than the previous one. This can involve a cursor followed by update on each record:

commit_interval := 10000;
count := 0;
FOR rec_employee IS (SELECT * FROM employees FOR UPDATE OF salary)
LOOP
    UPDATE employees SET salary = salary + 5 WHERE employee_id = rec_employee.employee_id;
    count := count + 1;
    IF count >= commit_interval THEN
        COMMIT;
        count := 0;
    END IF;
END LOOP
COMMIT;

A periodic commit will ensure that the rollback segment is not filled up. Depending upon the size of your rollback segment, you might have to adjust the commit interval.

This too has a disadvantage. If your table is very very large - say 10 million records, this will take a lot of time if you want to update all the records. Such situations are very common in data uploads to data warehouses.

The next method, though it looks ridiculous can be very fast.

Export, process outside and then import back

It involves unloading the data into an plain text format, may be CSV. Then you use UNIX utilities like sed, awk or perl to update the file. After that, truncate the table and upload the data.

You can write a pretty quick SQL script that works as the inverse of SQL*Loader. Unload the data in some field separated format. Pipe (|) separated format is very good, if your data does not have this character.. CSV is good, but the field separator is not as straight forward as pipe, because you will have to put quotes around text that contains comma. Multi-character field separators can complicate things.

Once you’ve a flat file, depending upon the type of update you need, use common UNIX text processing tools to update the file. Believe me, this can be pretty much faster than Oracle updates.

After you get the updates done, truncate the table and then use SQL*Loader to upload the modified file using direct path.

I’ve very often used this method in data warehouses. Usual SQL and PL/SQL methods used to run for hours. Using UNIX tools used to get things done in less than an hour.

Though this is the most complex to implement this strategy, it is not without other troubles. If you need to do data validation with respect to other tables or constraints, this becomes impractical.