Re: quick FK question
Date: Wed, 23 Dec 2009 08:51:41 -0800 (PST)
Message-ID: <300500.41716.qm_at_web113209.mail.gq1.yahoo.com>
>>But as more time goes by I'm more able to accept "never say never" thingy
That has become my experience too..... Perhaps that is a good technical interview question of some sort.... It's funny how we seem to sometimes want this hard and fast set of rules to broker our every decision. Why is that I wonder? Is it because if we know the hard and fast rules and know how to apply them that we feel more like we know what we are doing? But then, is saying there is a "It depends" rule setting a rule itself?
Personally, I'm glad DBA'ism isn't really that way. I know that to a degree it's predicated on rules, math, concepts and logic but I also like that there is a bit of an art to it too....
RF
Robert G. Freeman
Oracle ACE
Ask me about on-site Oracle Training! RMAN, DBA, Tuning, you name it!
Author:
Oracle Database 11g RMAN Backup and Recovery (Oracle Press) - ON ITS WAY SOON!
OCP: Oracle Database 11g Administrator Certified Professional Study Guide (Sybex)
Oracle Database 11g New Features (Oracle Press)
Oracle Database 10g New Features (Oracle Press)
Other various titles
Blog: http://robertgfreeman.blogspot.com
Check out my new blog series on installing Oracle Database 11gR2 on Windows using VMWare!
From: Gints Plivna <gints.plivna_at_gmail.com> To: Yong Huang <yong321_at_yahoo.com>
Cc: oracle-l_at_freelists.org
Sent: Wed, December 23, 2009 7:10:33 AM
Subject: Re: quick FK question
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 -- http://www.freelists.org/webpage/oracle-lReceived on Wed Dec 23 2009 - 10:51:41 CST