DBA Tips for Speeding Up Large Table Updates!!

 Author: Kevin Miladin

In this era of technology databases have became mandatory part of any data driven application. An organization's business is wholly dependent on the transaction going on databases at the back end. With thousands of transactions taking place daily, the number of records in the database increase tremendously. Database management becomes complex due to rapid increase in the number of records one day or another. It is the responsibility of database administrator to use efficient methods for handling database data.

Performing Commit on N Records:

Sometimes it happens that we perform some operation and see the command executing and executing with no end. Consider a scenario where an UPDATE is attempted on a non-partitioned table containing say six million records.

We commit N records with the aim that it will be done faster and will probably not create any issues with the Rollback Segments. But things do not always go smooth and so we see the update command running and running and we sit waiting for hours for the update to be completed.

Performing Fast Large Update on Non-Partitioned Table:

Below is a better approach to attempt UPDATE on a large table.

Create Table AS:

First of all create a new table from old table by using CTAS (Create Table AS). Use ‘WHERE 1=0' so that only the definition gets created.

Nologging:

Now we will alter the new table and make it nologging.

Fast Insert:

Now we will perform fast insert from the source table to destination table by using append hint and parallel query options.

Create Index and add constraints:

Now we will create indexes and add constraints to the destination table.

Assign Grants:

Now execute grants on destination table.

Drop source table and rename destination table:

Now we will drop our source table and rename the destination table as that of our old table.

Statistics Collection and Re-compilation:

Now we will collect statistics and re-compile the dependent objects. If you do not re-compile the dependent objects then they will be re-compiled when they are called next time.

Performing Fast Large Update on Partitioned Table:

A better approach is to use a partitioned table whereby you just have to perform CTAS for each partition, then swap the partition of the source table with the destination table and then rebuild indexes in parallel. This approach is far more efficient than the previous approach. Read Again!!

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Oracle10g Wait Event Tuning
   Great Tips for Oracle Wrap Utility!!
   Inside Oracle 11g Virtual Columns!
   Testing the Oracle Recovery Plan