Solved

DELETE anomaly

Posted on 2013-01-10
9
459 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:Kdo
  • 5
  • 3
9 Comments
 
LVL 76

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 73

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 73

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
 
LVL 73

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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 45

Author Comment

by:Kdo
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 73

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:Kdo
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 73

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:Kdo
ID: 38765799
So much for needing an internalist.  Just one good Sean will suffice.....

Thanks...
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Article by: Swadhin
From the Oracle SQL Reference (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries006.htm) we are told that a join is a query that combines rows from two or more tables, views, or materialized views. This article provides a glimps…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
This video shows how to recover a database from a user managed backup
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.

762 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

Need Help in Real-Time?

Connect with top rated Experts

24 Experts available now in Live!

Get 1:1 Help Now