Re: quick FK question
- From: Yong Huang <yong321@xxxxxxxxx>
- To: gints.plivna@xxxxxxxxx
- Date: Tue, 22 Dec 2009 09:44:29 -0800 (PST)
Gints,
With regard to your message at
//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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
