Oracle MINUS Operator – Select Items Never Ordered

Your options are rather limited because of your requirements to "constantly check ... sync" and "can't make any change in A". Things such as materialized view logs, dbms_alert, streams, and a standby database are all off the table.

If the tables in A are constantly having all of their rows updated then (as Jack Douglas said) a materialized view would be the easiest to setup. In the more likely event that most of the records don't change in A from moment to moment, you will probably want to setup a package (or packages) on B that select from A to merge and delete as necessary on B. This will only be as up to date as the frequency in which it is run, but given your requirements it may be the best you can do.

Specifically, your package should do the following:

• Delete from B rows that do not exist in A.
• Merge A into B updating when matched and inserting when not matched.

If you want to avoid hitting the table in A multiple times you could insert the entirety of the table into a global temporary table on B and then do your Delete/Merge from there.

Concerning Minus: Minus can tell you all the rows from a query of A that are not in B. By union-ing this with B minus query of A you can get all rows that are different, but this would probably take longer to process even before adding the insert/update part. If A doesn't get updates or deletes then you could insert the results of the first minus, but an insert into B...where not exists A... would still be faster and simpler.

Your requirement and logic behind the requirement makes sense, in theory. However, how quickly this can be achieved depends upon the data volume in tables A and B as well as availability of any useful indexes. Worst case scenario is if both the tables A and B have large volume of data and there are no useful indexes available on the tables. In that case (and provided table statistics are close to actual data), oracle will not be able to "find first unmatched record" any quicker than getting a count (provided you change your query to the LEFT JOIN approach mentioned above). Best case scenario is either both the tables A and B or table A has small volume of data and/or there are indexes on both tables on the combination of X, Y and Z columns. In this case, following query may perform better than doing a COUNT

SELECT COUNT(*) FROM DUAL
WHERE NOT EXISTS
(
    SELECT NULL
    FROM A LEFT JOIN B
    ON  A.X=B.X AND A.Y=B.Y AND A.Z=B.Z
    WHERE B.X IS NULL
) ;