Re: quick FK question

From: Yong Huang <yong321_at_yahoo.com>
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-l
Received on Tue Dec 22 2009 - 11:44:29 CST

Original text of this message