CODEWITHSUNDEEP
sqloracle-databaseplsqlplsqldeveloper
Ankit Mongia
Ankit Mongia

Reputation: 210

Updating a big table in oracle without drooping the base table

I am trying to update a table in sql query. My table contains half a million records and it is taking around 10 minutes of time. It is my dev environment.

When i will go for UAT or prod, the table there will contain a lot more data than the dev environment. updating the whole table and then putting a commit will consume a lot of memory. Can someone suggest me a good way of updating a table in batches.

I have gone through net and the most common solution i found is creating a new table by "create new_table as select (update ) from old_table." But in my case i can not use this as in my prod environment i can not drop and create table.

Thanks Ankit.

Upvotes: 0

Views: 787

Answers (2)

APC
APC

Reputation: 146239

CTAS remains the fastest option. Bear in mind you don't need to drop the original table; you can just rename and retain it to keep your management happy.

Otherwise you have some options depending on your circumstances.

If:

  • the concern is just about elapsed time; and
  • you have the Enterprise Edition license; and
  • Production has CPUs to spare

Then you could consider Parallel DML to solve the problem.

alter session enable parallel dml;

update /*+ parallel (your_table) */ your_table
set ..

If you have only Standard Edition but are on 11gR2 or higher you can use DBMS_PARALLEL_EXECUTE package to run a PL/SQL job in parallel. The caveats about CPU still apply.

Beyond that, you should look at the efficiency of the WHERE clause. An UPDATE is a scan too and can be tuned like any other query.

Splitting the update into batches will make the total elapsed time longer. Of course it will, you're doing more work. The main advantage of batching over a single update statement is that by committing chunks of records we don't lose work if the task falls over halfway through. This is only an advantage if there is a mechanism for distinguishing the updated records ( a status or date column which is updated by this task and no other). If you don't have a flag like that you run the risk of corrupting the table absolutely.

The simplest way of batching is a simple PL/SQL loop. Assuming you have a million rows to update:

begin
    for idx in 1..100 loop
         update your_table
         set whatever = whatever * 1.1
                    , status = 'touched'
         where status != 'touched'
          and rownum <= 10000;
          commit;
    end loop;
end;

Obviously you'll need some error handling, perhaps logging, etc. But resist the lure of bulk collect, forall etc unless your update rules are sufficiently complicated that they demand procedural logic.

Upvotes: 1

Max Lambertini
Max Lambertini

Reputation: 3719

If "Create Table as Select" is not an option, then your best bet is to write a PL/SQL stored proc that takes advantage of BULK COLLECT and FORALL statements. This is a good starting point http://www.oracle.com/technetwork/issue-archive/2012/12-sep/o52plsql-1709862.html

Upvotes: 0

Related Questions