Re: quick FK question

From: Gints Plivna <gints.plivna_at_gmail.com>
Date: Wed, 23 Dec 2009 16:10:33 +0200
Message-ID: <6e49b6d00912230610w775a3e31h42a68473db36500a_at_mail.gmail.com>

Yep, it seems that my example is not correct. On the other hand for such fake rows one has to complete some values in NOT NULL columns (I don't speak only about FK columns). Usually they are some fake values, too. As soon as fake value becomes normal business value problems might arise.
I'd personally better deall with NULLs because this is WIDELY understand concept both by developers and DBMSes compared to some specific value representing NULL, which is -1 for project1, 0 for project2 and 187913267532 for project3.
But as more time goes by I'm more able to accept "never say never" thingy :)

Gints

2009/12/22 Yong Huang <yong321_at_yahoo.com>:
> Gints,
>
> With regard to your message at
> http://www.freelists.org/post/oracle-l/quick-FK-question,8
>
> If a "meaningless" value such as -1 is always present as opposed to left
> as null, it should only change certain queries such as
> select count(*) from x where [x_id condition which would include -1];
>
> Then the SQL should be rewritten as
> select count(*) from x where [x_id condition which would include -1] and
> x_id <> -1;
>
> (Of course the whole where clause could be rewritten in a more concise way.)
>
> Most queries should be unchanged. If the user intends to run
> select count(*) from x where x_business_value = 'DBA';
>
> why does he need to add "and x_id <> -1"? It alters the meaning of the
> original query. The SQL with this added part would be equivalent to the
> original SQL with "and x_id is not null", which the user doesn't bother
> to check. Right?
>
> I think the reason the data modeler wants to avoid outer joins is that
> they limit CBO's choice of join order. Filling in a meaningless value
> may be good in this sense. It's only bad that certain queries need to
> be re-evaluated (unless the business case is such that the value is
> guaranteed to be meaningless), that it artificially enlarges the value
> range for CBO cost calculation (unless a histogram is created to mitigate
> this effect), and that a B-tree index is unnecessarily a little larger.
>
> Yong Huang
>
>
>
>

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Dec 23 2009 - 08:10:33 CST