DELETE anomaly

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);

Open in new window


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);

Open in new window


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?


Thanks,
Kent
LVL 46
Kent OlsenData Warehouse Architect / DBAAsked:
Who is Participating?
 
sdstuberCommented:
do you have null values in your data?


NOT IN and NOT EXISTS behave differently with nulls
0
 
slightwv (䄆 Netminder) Commented:
>>A rewrite of the DELETE solves it:

If would be interesting to see the explain plan for the two.

>>So the question is, why in the world does that DELETE statement no longer find data?

If this was 11g, I would have an idea (results cache issue) but with a 10g tag, off the top of my head, I can't think of anything.

I'll ponder this for a while to see if I can think of something.

>>And does this warrant an escalation to Oracle?

I would open an SR (it's technically free.... and never hurts).  Something does seem wrong.
0
 
sdstuberCommented:
for example...

SQL> INSERT INTO names(LAST, FIRST, dob)
  2       VALUES (NULL, NULL, NULL);

1 row created.

SQL> INSERT INTO names(LAST, FIRST, dob)
  2       VALUES (NULL, NULL, NULL);

1 row created.

SQL> INSERT INTO name_dup1(LAST, FIRST, dob)
  2      SELECT   LAST, FIRST, dob
  3          FROM names
  4      GROUP BY LAST, FIRST, dob
  5        HAVING COUNT(*) > 1;

1 row created.

SQL> DELETE FROM names
  2        WHERE (LAST, FIRST, dob) NOT IN (SELECT LAST, FIRST, dob FROM name_dup1);

0 rows deleted.

SQL> DELETE FROM names t0
  2        WHERE NOT EXISTS
  3                  (SELECT 1
  4                     FROM name_dup1 t1
  5                    WHERE t0.LAST = t1.LAST AND t0.FIRST = t1.FIRST AND t0.dob = t1.dob);

2 rows deleted.

SQL>
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
sdstuberCommented:
It doesn't have to be a null triplicate either.  A single null is sufficient

INSERT INTO names(LAST, FIRST, dob)
     VALUES ('John', 'Doe', null);
INSERT INTO names(LAST, FIRST, dob)
     VALUES ('John', 'Doe', null);
0
 
Kent OlsenData Warehouse Architect / DBAAuthor Commented:
Good catch, Sean.

It turns out to be a data issue in that the table name_dup1 has a single null row in it.

Apparently the application (a fat client) has an issue since this should never happen.


And Oracle's null handling bites me again.  sigh...



Thanks!
Kent
0
 
sdstuberCommented:
glad I could help,  but as for Oracle's NULL handling...

same results in DB2


delete from names;
delete from name_dup1;

INSERT INTO names(LAST, FIRST, dob)
     VALUES ('John', 'Doe', null );

INSERT INTO names(LAST, FIRST, dob)
     VALUES ('John', 'Doe', null);

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);


select * from names;
------------------------------------------------------------------------------
delete from names
DB20000I  The SQL command completed successfully.

delete from name_dup1
DB20000I  The SQL command completed successfully.

INSERT INTO names(LAST, FIRST, dob) VALUES ('John', 'Doe', null )
DB20000I  The SQL command completed successfully.

INSERT INTO names(LAST, FIRST, dob) VALUES ('John', 'Doe', null)
DB20000I  The SQL command completed successfully.

INSERT INTO name_dup1(LAST, FIRST, dob) SELECT   LAST, FIRST, dob FROM names GROUP BY LAST, FIRST, dob HAVING COUNT(*) > 1
DB20000I  The SQL command completed successfully.

DELETE FROM names WHERE (LAST, FIRST, dob) NOT IN (SELECT LAST, FIRST, dob FROM name_dup1)
SQL0100W  No row was found for FETCH, UPDATE or DELETE; or the result of a 
query is an empty table.  SQLSTATE=02000

select * from names

LAST                 FIRST                DOB       
-------------------- -------------------- ----------
John                 Doe                  -         
John                 Doe                  -         

  2 record(s) selected.

Open in new window


delete from names;
delete from name_dup1;

INSERT INTO names(LAST, FIRST, dob)
     VALUES ('John', 'Doe', null );

INSERT INTO names(LAST, FIRST, dob)
     VALUES ('John', 'Doe', null);

INSERT INTO name_dup1(LAST, FIRST, dob)
    SELECT   LAST, FIRST, dob
        FROM names
    GROUP BY LAST, FIRST, dob
      HAVING COUNT(*) > 1;

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);

select * from names;


select * from names;

select * from name_dup1;
------------------------------------------------------------------------------
delete from names
DB20000I  The SQL command completed successfully.

delete from name_dup1
DB20000I  The SQL command completed successfully.

INSERT INTO names(LAST, FIRST, dob) VALUES ('John', 'Doe', null )
DB20000I  The SQL command completed successfully.

INSERT INTO names(LAST, FIRST, dob) VALUES ('John', 'Doe', null)
DB20000I  The SQL command completed successfully.

INSERT INTO name_dup1(LAST, FIRST, dob) SELECT   LAST, FIRST, dob FROM names GROUP BY LAST, FIRST, dob HAVING COUNT(*) > 1
DB20000I  The SQL command completed successfully.

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)
DB20000I  The SQL command completed successfully.

select * from names

LAST                 FIRST                DOB       
-------------------- -------------------- ----------

  0 record(s) selected.


select * from names

LAST                 FIRST                DOB       
-------------------- -------------------- ----------

  0 record(s) selected.


select * from name_dup1

LAST                 FIRST                DOB       
-------------------- -------------------- ----------
John                 Doe                  -         

  1 record(s) selected.

Open in new window

0
 
Kent OlsenData Warehouse Architect / DBAAuthor Commented:
Wow.

Someone that knows the SQL internals a lot better than I needs to explain how a single null in the "exceptions table" voids the entire table..
0
 
sdstuberCommented:
A not in (B)      

is equivalent to

A != ALL (B)


Since any value of A will always return false for A != NULL

A!= ALL (B)   is false if at at least one B is NULL.

So one NULL B is sufficient to exclude all A
0
 
Kent OlsenData Warehouse Architect / DBAAuthor Commented:
So much for needing an internalist.  Just one good Sean will suffice.....

Thanks...
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.