Re: quick FK question
Date: Tue, 22 Dec 2009 09:44:29 -0800 (PST)
Message-ID: <679747.94498.qm_at_web80607.mail.mud.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-lReceived on Tue Dec 22 2009 - 11:44:29 CST