Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

A quick first scan of the code makes me think that this could all be done with a single UPDATE statement....
–
PhilApr 11 '12 at 9:10

@Phil : tried it the first time, but because of the DBlink, i'm getting timed out. Forgot to mention that the DB link is running oracle 9i, and my instance is on oracle 11g R2 and dont tell anybody but its all in production :) So its heavily used while im trying to do this :(
–
Alex PetaApr 11 '12 at 9:51

3

Get them to sort out the firewall timeouts then. I'm not a big fan of writing nasty workaround SQL (and increasing remote DB roundtrips as a result) because of infrastructure issues. Sorry to be blunt!
–
PhilApr 11 '12 at 9:53

1

I agree with @phil. Your code has a loop where it is not necessary. I believe the correct thing to do just one update, you would be the correct test in a separate environment, this query to see if with a single query the execution becomes more performatic. If you experience a timeout error or disconnection, you should contact the DBA to check the problem with DBLINK. Not a good idea for you to slay the bench to relieve the network. The ideal is that both work together for better performance.
–
Rainier MorillaApr 11 '12 at 11:56

1

Can you explain plan the v_get_avids and v_get_results queries & edit your answer with the output. Cheers.
–
PhilApr 11 '12 at 14:37

1 Answer
1

If your problem is timing out and you have no way of solving that issue, maybe you might want to process your records in chunks, but also with a single update. Here is a little demo, just to give you the idea, you can use another chunking method other than mod if you don't have an incremental key. And I have to add, for the good practise follow Philip's advice if you can.