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
Last activity: Jun 18, 2012, 01:26 PM