Our shop has two production jobs that generate several reports by loading many thousands of rows of potential items into a table, then deleting the items that are of no interest. After the load steps there are about 200,000 rows. After the DELETE step, about 200 remain.
The jobs have run just fine for several years. When they ran this last time, the DELETE step found no rows to delete.
Here's the abbreviated SQL:
INSERT INTO names (last, first, dob, ...) ... ;
INSERT INTO name_dup1 (last, first, dob)
SELECT last, first, dob FROM names GROUP BY last, first, dob HAVING count(*) > 1;
DELETE FROM names
WHERE (last, first, dob) not in (SELECT last, first, dob FROM name_dup1);
The DELETE statement finds no rows to delete. None. Though it has for years. The client makes no difference. (A Copy/Paste of that statement from the batch script that runs via SQL*PLUS into TOAD makes no difference.) "0 rows found".
A rewrite of the DELETE solves it:
DELETE FROM names t0
WHERE not exists
(SELECT 1 FROM name_dup1 t1
WHERE t0.last = t1.last
AND t0.first = t1.first
AND t0.dob = t1.dob);
But it doesn't answer the critical question, "Why does the query no longer work"? There have been no updates or changes to Oracle in more than a year.
So the question is, why in the world does that DELETE statement no longer find data? And does this warrant an escalation to Oracle?