Re: Is this a bug, or am I loosing it

  • From: "JayDBA" <jaykash@xxxxxxxxxxx>
  • To: <Jacques.Kilchoer@xxxxxxxxx>, "oracle-l-freelists" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 1 Feb 2007 14:27:44 -0600

Thanks for the link Jacques. I too am not very convinced with that, but one 
part of me says thats correct and the other says its not. What I fail to 
understand is, fine a <null> != <value> but there are 121K rows with NULL's and 
31K rows with NOT NULL's in table XX. Shouldnt the rows with values return 
something. IF that is the case then the NOT EXISTS shouldnt have returned any 
values eiither.

I forgot to mention that we are on Oracle 10.1.0.3.0 on Solaris (64-bit).

Regards,
J
  ----- Original Message ----- 
  From: Jacques Kilchoer 
  To: jaykash@xxxxxxxxxxx ; oracle-l-freelists 
  Sent: Thursday, February 01, 2007 2:08 PM
  Subject: RE: Is this a bug, or am I loosing it


  
http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:36834798025416

  Tom Kyte says: with the null values included, it is "not known" if the values 
are in the table or not. His explanation never really convinced me that this is 
the "correct" way to handle the query, but who am I to criticize Tom Kyte?



------------------------------------------------------------------------------
  De : oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] De 
la part de JayDBA
  Envoyé : jeudi, 1. février 2007 11:47
  À : oracle-l-freelists
  Objet : Is this a bug, or am I loosing it


  I am getting weird results from a NOT IN and NOT EXISTS query. Could this be 
because of NULL's or is it a bug?

  XX is the child table. YY is the parent table. There are some NULL's in XX.PH 
column whereas there are no NULL's in YY.PHONE_NUMBER.

  DEVDB 1{MYACCT}> desc xx
   Name                         Null?    Type
   ---------------------------- -------- ------------------
   PH                                    NUMBER(10)

  DEVDB 1{MYACCT}> desc yy
   Name                         Null?    Type
   ---------------------------- -------- ------------------
   PHONE_NUMBER                 NOT NULL NUMBER(10)

  DEVDB 1{MYACCT}> select count(*) from yy;
  JAY SAYS {ENTER} ..

    COUNT(*)
  __________
      688431

  1 row selected.

  Elapsed: 00:00:00.54
  DEVDB 1{MYACCT}> select count(*) from xx;
  JAY SAYS {ENTER} ..

    COUNT(*)
  __________
      152892

  1 row selected.

  Elapsed: 00:00:00.36
  DEVDB 1{MYACCT}> SELECT phone_number FROM yy WHERE phone_number NOT IN ( 
SELECT ph FROM xx);

  no rows selected

  Elapsed: 00:00:28.93
  DEVDB 1{MYACCT}> 
  DEVDB 1{MYACCT}> SELECT COUNT(phone_number) FROM yy WHERE NOT EXISTS (SELECT 
1 FROM xx WHERE ph = phone_number);
  JAY SAYS {ENTER} ...

  COUNT(PHONE_NUMBER)
  ___________________
               666936

  1 row selected.

  Elapsed: 00:00:01.46
  DEVDB 1{MYACCT}> SELECT count(phone_number) FROM yy WHERE phone_number NOT IN 
( SELECT ph FROM xx);
  JAY SAYS {ENTER} ..

  COUNT(PHONE_NUMBER)
  ___________________
                    0

  1 row selected.

  Elapsed: 00:00:31.83

  DEVDB 1{MYACCT}> SELECT count(phone_number) FROM yy WHERE phone_number NOT IN 
( SELECT ph FROM xx WHERE ph IS NOT NULL);
  JAY SAYS {ENTER} ...

  COUNT(PHONE_NUMBER)
  ___________________
               666936

   
  Regards,
  J

Other related posts:

  1. Home
  2. oracle-l
  3. Archive
  4. 02-2007