I have a random question just came up in my mind.
For eg.
-Suppose I want to do UPSERT 2M rows in oracle table.
-Now due to some reasons the job is aborted after processing 1M rows.
-Either I can reset the job and run again or look for another option as 50% work is done.
-I will go for another option.
Help me in the logic:
-What if I make a temporary reference table if 50% or more rows are processed and give commit to both tables( original and temp) when job is aborted.
-Now I can use this temp table as look up when I start the job again so alot of time is saved.
Please suggest.
What if job aborts after 50% or more is done ?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ
What if job aborts after 50% or more is done ?
"Nobody is expert in Everything,
But Everybody is expert in Something."
But Everybody is expert in Something."
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Your logic looks fine. But consider the question of array size also. You will probably need to unwind the final array because not all its rows were committed.
Possibly better logic would be to flag all records into the target with a unique run ID (or last updated timestamp) and do your lookup against the target constrained on that ID. This works fine for the inserts. Now, how do you expect to be able to reset the updated records back to their previous form? This involves taking a snapshot of the target table before your main processing even starts; the snapshot can be discarded once a successful run completes.
Possibly better logic would be to flag all records into the target with a unique run ID (or last updated timestamp) and do your lookup against the target constrained on that ID. This works fine for the inserts. Now, how do you expect to be able to reset the updated records back to their previous form? This involves taking a snapshot of the target table before your main processing even starts; the snapshot can be discarded once a successful run completes.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 73
- Joined: Wed Jun 28, 2006 3:27 pm
- Location: NJ