Solved

DELETE anomaly

Posted on 2013-01-10
9
467 Views
Last Modified: 2013-01-10
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
0
Comment
Question by:Kent Olsen
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
9 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38764782
>>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
 
LVL 74

Accepted Solution

by:
sdstuber earned 500 total points
ID: 38764800
do you have null values in your data?


NOT IN and NOT EXISTS behave differently with nulls
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38764822
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 74

Expert Comment

by:sdstuber
ID: 38764832
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
 
LVL 45

Author Comment

by:Kent Olsen
ID: 38765057
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38765115
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
 
LVL 45

Author Comment

by:Kent Olsen
ID: 38765688
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
 
LVL 74

Expert Comment

by:sdstuber
ID: 38765740
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
 
LVL 45

Author Comment

by:Kent Olsen
ID: 38765799
So much for needing an internalist.  Just one good Sean will suffice.....

Thanks...
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
Via a live example, show how to restore a database from backup after a simulated disk failure using RMAN.

696 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question