Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Brighton, MI
    Posts
    4

    Unanswered: null values ignored when using != filter

    I just noticed what I believe is a change in behavoir with
    null values but want to confirm. this is under oracle 8.1.7.4 on Solaris.

    records with the specified column as null are not
    returned by the following sql statement:
    ==================
    select *
    from table
    where column_name != 'A';

    I need to use the following

    SELECT *
    FROM table
    WHERE NVL(column_nm,'null') != 'A';
    ========================
    what gives?

    this a rather big deal. did not notice this behavoir under Oracle 7.x,8.0.x
    or 8.1.7.0.

    -Gerry Bragg
    gerry.bragg@altarum.org

  2. #2
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1

    Re: null values ignored when using != filter

    That is the way NULLs work, and have always worked, in Oracle - in all versions I have ever used from 6.0 to 9i. A NULL is not a value, it is the absence of a value. A NULL is neither equal to nor not equal to anything else, including NULL. The only tests that work with NULLs are "x IS NULL" and "x IS NOT NULL". The correct for of your query is:

    SELECT *
    FROM table
    WHERE (column_name IS NULL OR column_name != 'A');

    It's a pain, but it's not a bug.

  3. #3
    Join Date
    Jun 2002
    Location
    Brighton, MI
    Posts
    4

    Re: null values ignored when using != filter

    Well that's a BIG miss on my part. Found a related doc on metalink that
    addresses this issue also. any diff between NVL or IS NULL? From docs
    sounds like both will cause us to miss an index. Thanks for the reply!

    -Gerry Bragg


    Originally posted by andrewst
    That is the way NULLs work, and have always worked, in Oracle - in all versions I have ever used from 6.0 to 9i. A NULL is not a value, it is the absence of a value. A NULL is neither equal to nor not equal to anything else, including NULL. The only tests that work with NULLs are "x IS NULL" and "x IS NOT NULL". The correct for of your query is:

    SELECT *
    FROM table
    WHERE (column_name IS NULL OR column_name != 'A');

    It's a pain, but it's not a bug.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    "any diff between NVL or IS NULL?"

    sure, NVL is oracle's COALESCE, it assigns a value when the expression is null

    IS NULL, on the other hand, just returns TRUE or FALSE

    "both will cause us to miss an index"

    ? what do you mean by missing an index?

    rudy

  5. #5
    Join Date
    Jun 2002
    Location
    Brighton, MI
    Posts
    4
    In terms of "diff" I was speaking of the oracle cost based optimizer in what plan will be generated. I believe both approaches can cause a full scan on an indexed column. Thought in your experience you may have a preference in the implemention from a performance standpoint. The table I'm hitting has 32m rows and req's prohibit a not null constraint on the column being filtered. Approx 5% of the rows would meet the filter criteria which implies that an index range scan would conceivably improve performance.

    thanks!


    Originally posted by r937
    "any diff between NVL or IS NULL?"

    sure, NVL is oracle's COALESCE, it assigns a value when the expression is null

    IS NULL, on the other hand, just returns TRUE or FALSE

    "both will cause us to miss an index"

    ? what do you mean by missing an index?

    rudy

  6. #6
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    Provided Answers: 1
    Originally posted by bragggf
    In terms of "diff" I was speaking of the oracle cost based optimizer in what plan will be generated. I believe both approaches can cause a full scan on an indexed column. Thought in your experience you may have a preference in the implemention from a performance standpoint. The table I'm hitting has 32m rows and req's prohibit a not null constraint on the column being filtered. Approx 5% of the rows would meet the filter criteria which implies that an index range scan would conceivably improve performance.

    thanks!
    No difference - the index cannot be used in either case. In fact, if there is an index on column_name alone, then rows where column_name is null will not even have an entry in the index.

    However, you could create a function-based index on NVL(column_name,'null'), then the NVL version could use that index.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •