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,
updating oracle table
Moderators: chulett, rschirm, roy
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.
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.
Kenneth Bland
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle
Rank: Sempai
Belt: First degree black
Fight name: Captain Hook
Signature knockout: right upper cut followed by left hook
Signature submission: Crucifix combined with leg triangle