Re: Re: explain plan conundrum

Well, you have 10053, lev 8 guesses to make..... On 11/05/2003 12:04:26 PM, ryan_oracle_at_cox.net wrote:
> 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).
>

Mladen Gogala
Oracle DBA

Note:
This message is for the named person's use only. It may contain confidential, proprietary or legally privileged information. No confidentiality or privilege is waived or lost by any mistransmission. If you receive this message in error, please immediately delete it and all copies of it from your system, destroy any hard copies of it and notify the sender. You must not, directly or indirectly, use, disclose, distribute, print, or copy any part of this message if you are not the intended recipient. Wang Trading LLC and any of its subsidiaries each reserve the right to monitor all e-mail communications through its networks. Any views expressed in this message are those of the individual sender, except where the message states otherwise and the sender is authorized to state them to be the views of any such entity.

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Mladen Gogala
  INET: mladen_at_wangtrading.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).