Take the 2-minute tour ×
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.

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.

share|improve this question

1 Answer 1

Joins across database links can lead to sub-optimal execution plans as Oracle doesn't have all the information available about both sites. Queries across db links can (and do) perform just fine when joined to local tables though.

If writing a new query with local and remote tables I'd start joining it all together (set-based) then only break it into separate queries if performance is unacceptable and other tuning hasn't worked.

Given your query is just a simple minus with no joins to the remote site, I would expect the refactored set-based approach to be quicker. Make sure you test it though!

share|improve this answer
    
It is already doing the minus in the cursor, so I figured it will make no difference, since it could still decide to do a hash join on that. But in any case, I think it was just coded poorly, because there are no comments to indicate that this used a cursor for a particular reason. –  Cade Roux Jun 18 '12 at 14:47
    
I agree that it's coded poorly. As it's a minus there isn't a hash join to do to the remote table though - minus is an operation in its own right. Any joins will be done on the result of this minus, by which time you're finished with the remote table. –  Chris Saxon Jun 19 '12 at 10:04
    
The optimizer can't turn the MINUS into a JOIN automatically? –  Cade Roux Jun 19 '12 at 13:55
    
MINUS and JOIN are separate concepts. MINUS is find everything in set A not in set B. JOIN is combine set A and B (on the join key). You can JOIN the output of a MINUS, but the MINUS itself isn't a JOIN. –  Chris Saxon Jun 20 '12 at 12:55
    
But isn't a MINUS just a LEFT OUTER JOIN WHERE RIGHTHAND IS NULL (modulo duplicates)? –  Cade Roux Jun 20 '12 at 12:59

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.