[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 478
  • Last Modified:

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
0
Kent Olsen
Asked:
Kent Olsen
  • 5
  • 3
1 Solution
 
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:
do you have null values in your data?


NOT IN and NOT EXISTS behave differently with nulls
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
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now