Re: A Question regarding null columns.

Hi All,

This is all correct (and great fun to teach in class I might add). However, the difference here is that a "object_id is null" clause has been used not a "object_id = null" clause. This means that logically Oracle has no such confusion (not the right term I know) and will simply find all rows that contains a null value. The only way it can do that is it to perform a full table scan as null values are not indexed (as already stated).

The point that is being made here is that Oracle "knows" there are no null values, the constraint has determined that so why doesn't Oracle simply say "get lost" straight away (not the access path but you get the point) and deliver no rows.

I'm totally guessing here but here goes. The optimizer is simply not clever enough to know ! It bases it decisions on statistics on the tables and available indexes and the such but not directly on the constraint rules themselves. Therefore it has no viable option than to read the entire table.

If you don't like that one, I'll have another guess !! The constraint could either have been set with a novalidate clause (in which case null values could exist in the table) or as deferrable (in which case null values could exist). And as null values could exist regardless of the constraint it has no option but to read the whole table.

Then again I could be totally wrong (and not for the first time) in which case some clever bugger will let me know.

Cheers

Richard

"Martin Doherty" <martin.doherty_at_oracle.com> wrote in message news:3D20C875.FEDDC4ED_at_oracle.com...
> (drifting off thread...)
> It's not right to say that NULL = NULL is a false statement, or that NULL
<> NULL is a true statement. The
> picture is more complicated than that, and involves a strange twist on
boolean logic caused by the presence of
> nulls. You end up with 'trinary' truth tables instead of the classic
binary TRUE/FALSE truth tables.
>
> Assume your code example is written in PL/SQL. It is better to think of
NULL as 'unknown value'. If a PL/SQL
> comparison is unable to evaluate as definitely TRUE or FALSE, then it will
evaluate as NULL, and the 'else'
> clause of the if statement will be executed.
>
> Here are some illustrative examples:
>
> if (null = null) then a := 1; else a:=2; endif;
>
> Does one unknown equal another unknown? Well, the answer is of course,
unknown (i.e. null) and so the 'else'
> clause is triggered and a = 2.
>
> if (2 + 2 = 4 and b = null) then a := 1; else a:=2; endif;
>
> 2+2=4 is certainly TRUE, but b = null yields NULL (an unknown answer). So
we have TRUE AND NULL, which because
> of the unknown will also be unknown (both operands must be TRUE for the
AND operator to return TRUE). Result is
> null, and a = 2.
>
> if (2 + 2 = 4 or b = null) then a := 1; else a:=2; endif;
>
> Now we have TRUE OR NULL? The OR operator only requires one of its
operands to be TRUE in order to return TRUE.
> Result is TRUE, and a = 1.
>
> true and true => true
> true and false => false
> true and null => null
> false and false => false
> false and null => false (AND only needs one false to return a false)
> null and null => null
>
> true or true => true
> true or false => true
> true or null => true
> false or false => false
> false or null => null (OR will only return false if both operands are
false)
> null or null => null
>
> Same rules apply to the predicate in a WHERE clause, if the overall
predicate evaluates to TRUE then the row
> will be selected, if it evaluates to FALSE or NULL then it will not.
>
> If you want to test for two variables' equality, (treating NULL as a data
value), you'd have to write it like
> this:
>
> if (a = b or (a is null and b is null)) then ... else ... endif;
>
> or maybe
>
> if (nvl(a,'X') = nvl(b, 'X')) then ... else ... endif;
>
> but this is less reliable because a might be null and b might be 'X' but
they would evaluate as equal.
>
> (Please note, above examples based on knowledge of PL/SQL 2.0 only)
>
> Regards
> Martin Doherty
>
> P.S. if rusty memory serves me, in Oracle 7.3 it was possible to create
indexes on NULL columns, it could even
> be a UNIQUE index. You could have any number of rows in the table with a
key value of null (or all nulls), and
> the index would contain no entries for those rows. If it's a multi-column
unique index, NULL begins to act like
> a data value, e.g. with two key columns you would not be allowed to have
two rows both with the same key
> "ABC"/null but you could still have unlimited rows with the key of
null/null.
>
> P.P.S. You'll also see null behaving something like a data value when you
ORDER BY (all nulls go to the end) or
> GROUP BY (all nulls in same group), which is a practical if not
theoretically pure way of handling the
> situation. Think of it as putting all the unknown values together, without
implying that they equal each
> other.
>
> Chris Forbis wrote:
>
> > One possible reason is NULL can not be indexed with a normal index.
> > It would need a Bitmap index. (Unless someting has changed with newer
> > Oracle versions)
> >
> > 0 <> NULL
> > heck.. NULL <> NULL keep that in mind
> >
> > to test you can make a procedure..
> > if (NULL=NULL) then ... this will be be done because NULL <> NULL.
> >
> > "Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message
news:<ZVZT8.588$i5.5345_at_news.indigo.ie>...
> > > Consider :
> > >
> > > (Oracle 8173)
> > > Create table eraseme as select object_id from all_objects;
> > >
> > > Connected to:
> > > Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
> > > JServer Release 8.1.7.3.0 - Production
> > >
> > > SQL> desc eraseme;
> > > Name Null? Type
> > > ----------------------------------------- -------- ----------
> > >
> > > OBJECT_ID NOT NULL NUMBER
> > >
> > >
> > > SQL> explain plan for select count (*) from eraseme where object_id is
null;
> > >
> > > Explained.
> > >
> > > SQL> @ ?/rdbms/admin/utlxplp.sql
> > >
> > > Plan Table
> >
> --------------------------------------------------------------------------

--


> > > ---

> > > ----------------------------

> > > | Operation                  |  Name    |  Rows | Bytes|  Cost  |  TQ

> > > |IN-OUT|

> > > PQ Distrib | Pstart| Pstop |

> >

> --------------------------------------------------------------------------

--


> > > ---

> > > ----------------------------

> > > | SELECT STATEMENT           |          |       |      |        |

|


> > > |

> > >         |       |       |

> > > |  SORT AGGREGATE            |          |       |      |        |

|


> > > |

> > >         |       |       |

> > > |   TABLE ACCESS FULL        |ERASEME   |       |      |        |

|


> > > |

> > >            |       |       |

> >

> --------------------------------------------------------------------------

--


> > > ---

> > > ----------------------------

> > >

> > > 6 rows selected.

> > >

> > >

> > > Without wishing to cause major fuss my question would be -

> > >

> > > since object_id is known to be not null from the data dictionary why

does


> > > the optimizer ask for an FTS rather than immediate 0  ?

> > >

> > >  or is it a 'fiddled' FTS ? Checking the waits (there aren't many  but

there


> > > are a few consistent reads )

> > >

> > > Just part of an  issue I am trying to solve with distributed tables

(the


> > > above is self-contained on one instance though)

> > >

> > > TIA

> > >

> > > T

>