im not concerned about the type of join. Im strictly concerned about the join order. does oracle use histograms and distinctness in determining join order? The odd thing is that it chose a different join order on these tables earlier and on 'similiar' joins(ie large number of records and only 4 distinct values on the join column) oracle chooses the proper join 'order'
>
> From: Wolfgang Breitling <breitliw_at_centrexcc.com>
> Date: 2003/11/05 Wed AM 11:49:26 EST
> To: Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>
> Subject: 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).
>
--
Please see the official ORACLE-L FAQ: http://www.orafaq.net
--
Author: <ryan_oracle_at_cox.net
INET: ryan_oracle_at_cox.net
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 Wed Nov 05 2003 - 11:04:26 CST
