Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: A Question regarding null columns.

Re: A Question regarding null columns.

From: Richard Foote <richard.foote_at_bigpond.com>
Date: Thu, 4 Jul 2002 00:09:24 +1000
Message-ID: <StDU8.26880$Hj3.82938@newsfeeds.bigpond.com>


Hi There,

I give in !

Note that the rely flag doesn't necessary mean a constraint can't be in a non validated state, it simply means you're telling Oracle to trust you (honest gov) that the constraints are being maintained. I know query rewrites of MV through dimensions use the rely flag (provided the query_rewrite_integrity = trusted) but I'm not sure if it applies here.

Regardless, I ran a couple of simple tests (with definitely validated constraints) and get the same results. I also ran a similar test using a check constraint equal to a single value and searched for another value and guess what. No rows returned, no logical reads, no nothing. So why doesn't it apply to the not null constraint ? Good question !!

Sorry I was of zip help.

Regards

Richard
"Telemachus" <telemachus_at_ulysseswillreturn.net> wrote in message news:9HCU8.966$i5.6696_at_news.indigo.ie...
> 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 - 09:09:24 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US