Re: A Question regarding null columns.

Thanks Richard this was my original point/question

To give a counter-example, in some cases for a NOT IN (Select col1 from table1 ) tagged on clause to a SELECT,
where col1 is null a filter is usually used, and where col1 is defined as not null, the optimizer can convert it nicely to an antijoin - so the optimizer is definitely aware of the constraint.

to point b: isn't that why NOVALIDATE RELY is out there plus also the fact that the optimizer is aware that the constraint is active -

So if I may - back to the original question ?

Can it automatically recognize if <select all rows where col1 is null> will return 0 rows if col1 is defined NOT NULL ?

The tri-state logic post took me back to first year college logic - ahhh nostalgia ....
thanks for the interest guys.
TIA T.

"Richard Foote" <richard.foote_at_bigpond.com> wrote in message news:l3iU8.26026$Hj3.80132_at_newsfeeds.bigpond.com...
> 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
> >
>
>
Received on Wed Jul 03 2002 - 08:06:51 CDT