CBO with Foreign Key

From: Henry Poras <hporas_at_etal.uri.edu>
Date: Thu, 16 Oct 2003 12:19:26 -0800
Message-ID: <F001.005D3673.20031016121926@fatcity.com>

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: hporas_at_etal.uri.edu

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: ListGuru_at_fatcity.com (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). Received on Thu Oct 16 2003 - 15:19:26 CDT