updating oracle table

[shrey3a]

Hi,

I've a very simple job, which updates the rows in Table.

The only problem is the table has 206 columns and 1 million rows.

I'm only updating 6-7 columns in table but the job performance is like it takes 10 hours to update. Can there be any suggestions to improve the performance of jobs from DS / oracle end.

We have asked the DBA's to build the index on the particular fields key columns of the table.

Regards,

[kcbland]

Create a table with the same column definitions as your load data and enable for parallel query. Bulk load your data into the work table using DIRECT path option and TRUNCATE method. Create an index on your update key (should be unique, hopefully you're not doing wildcard updates). Analyze your table. Write an update SQL script that either does an update naming the keys in the join, or use Merge. Alter session enable parallel dml at the top of the script.

Now, everytime you need to do a high-performance update, just bulk load into your work table with a truncate load method. After the load finishes, kick off the update script.

The answer is that DS has no secret backdoor method to load data into databases. It uses whatever methods the database maker provides. In your case, your update is probably slow for many reasons. The surest method for speeding performance is to remove all obstacles. Getting the data into the database is high-speed using DIRECT path bulk loading. Merging/updating the data is fastest method for updating data because a parallel query DML update takes place completely inside the database at the speed the database can handle it, instead of waiting on a DS job to feed rows over a TCP/IP connection.

[loveojha2]

if you are already aware of the columns that are going to get updated, then load only the key columns + the cols to be updated without the rest of columns which are not at all updated.

[chulett]

... and get those DBAs to add that index over the key fields so it doesn't have to do a full table scan on every row to find the one to update.