Is there any way to get the CBO (8.1.7) to recognize parent/child
relationships? This seems to be an extreme example of the Join Independence
Assumption Fallacy (or I guess maybe the Predicate Independence Assumption)
discussed by Wolfgang Breitling in "Fallacies of the Cost Based Optimizer".
If I am joining a parent and child table (parent PK [a,b]), Oracle assumes a
resulting cardinality of :
rows(parent)*rows(child)/{max(NDV[parent.a],NDV[child.a])*max(NDV[parent.b],
NDV[child.b])}.This is assuming independence of field a and b. However, since they make up the PK of the parent, the denomenator is actually rows(parent) so the actual cardinality is rows(child). This causes the CBO to dramatically underestimate the join cardinality. If this is part of a multi-table join the entire execution plan can be thrown way off. I would assume that since the FK constraint is part of the data dictionary, Oracle would find a way to use this information. I checked by running autotraces with and without the FK, but the plan remained unchanged. Any suggestions? Thanks. Henry -- Please see the official ORACLE-L FAQ: http://www.orafaq.net -- Author: Henry Poras INET: [EMAIL PROTECTED] Fat City Network Services -- 858-538-5051 http://www.fatcity.com San Diego, California -- Mailing list and web hosting services --------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
