I currently have a job writing data from sql server to oracle10g.
I'm using Oracle Enterprise stage and selected the option "Upsert" and "Update then Insert".
With that combination the data takes forever to get loaded with speed of 3 rows/sec.
When I changed the option to "Insert then Update" it rans extremely quick with a speed of 12000 rows/sec.
The key column checked for Update is CCY_FROM and the table does not have any indexes.
How can use the option "Update then Insert" efficiently in this case?
Thanks!
Oracle enterprise stage Upsert not running efficiently
Moderators: chulett, rschirm, roy
You would need an index over the update keys for it to have any kind of efficiency. Without it, every update becomes a full table scan, and only after that fails to find a record to update is the insert done. And without any unique constraint on the table, when inserts are done first they are all successful and no updates will ever be done.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers