Sample Header Ad - 728x90

This refactoring from cursor on a dblink'ed Oracle table OK?

1 vote
1 answer
1136 views
The data is being compared from the local version of the table to one in the contracts linked server. I'm not certain what the goal of this code is in the first place, but I'm trying to get a handle on simplifying it a little. CURSOR c3 IS SELECT bs.billing_service_id, bs.oser_id FROM (SELECT oser_id FROM other_services MINUS SELECT oser_id FROM other_services@contracts) a, billing_service bs WHERE bs.oser_id = a.oser_id AND bs.sent = 0 AND bs.billing_service_id NOT IN ( SELECT bd.billing_service_id FROM billing_details bd); -- snip FOR c_rec3 IN c3 LOOP DELETE FROM billing_service WHERE billing_service_id = c_rec3.billing_service_id; END LOOP; Seems to me that this could be naively refactored (without fooling with any other issues it has like the implicit joins and other garbage) simply as: DELETE FROM billing_service WHERE billing_service_id IN ( SELECT bs.billing_service_id FROM (SELECT oser_id FROM other_services MINUS SELECT oser_id FROM other_services@contracts) a, billing_service bs WHERE bs.oser_id = a.oser_id AND bs.sent = 0 AND bs.billing_service_id NOT IN ( SELECT bd.billing_service_id FROM billing_details bd) ); I don't think it's a significant amount of data in these tables - about 26000 rows on each side, and I don't think the set of differences is that big of a subset of the two. But I was wondering if there are issues with DB Links which make cursors preferable to regular set-oriented methods? This operation is part of a stored procedure which is run from an Oracle job on a daily basis.
Asked by Cade Roux (6684 rep)
Jun 14, 2012, 09:44 PM
Last activity: Jun 18, 2012, 01:26 PM