What if job aborts after 50% or more is done ?

[urshit_1983]

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.

[DSguru2B]

This is related to restartability. Search this forum for different techinques.

[ray.wurlod]

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.

[urshit_1983]

Thank you so much Ray. Cheers to ya.