Re: Re: explain plan conundrum

However, since it is a join predicate, the histogram data can not be used. The CBO uses the density values of the join column(s) to derive the join selectivity. The density value of a column changes (from 1/num_distinct) when you collect a histogram. If you create a frequency histogram (aka value based histograms or equi-width histogram), which you most likely did for a field with only four distinct values using the default size of 75, the calculated density will be much lower than 1/num_distinct (i.e. less than 1/4 = .25) and therefore the join selectivity and ultimately the join cardinality will be unrealistically low, increasing the likelihood that the CBO will choose an NL join.

At 04:49 PM 11/4/2003, you wrote:
>the data is very skewed, but i included 'for all indexes' and for all
>indexed columns. doesnt that create histograms? or do i have the syntax
>wrong. what i really needed was histograms, Ill bet.

Wolfgang Breitling
Oracle7, 8, 8i, 9i OCP DBA
Centrex Consulting Corporation
http://www.centrexcc.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Wolfgang Breitling
  INET: breitliw_at_centrexcc.com

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).