Path: text.usenetserver.com!out03b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!16g2000cwy.googlegroups.com!not-for-mail
From: "EscVector" <Junk@webthere.com>
Newsgroups: comp.databases.oracle.server
Subject: Re: Join selectivity is 0 causing bad cardinality estimates.
Date: 12 Dec 2006 14:16:01 -0800
Organization: http://groups.google.com
Lines: 50
Message-ID: <1165961761.805346.111320@16g2000cwy.googlegroups.com>
References: <1165960064.046563.211800@n67g2000cwd.googlegroups.com>
   <frmdnbkwbrTGteLYnZ2dnUVZ8q6nnZ2d@bt.com>
NNTP-Posting-Host: 72.165.244.50
Mime-Version: 1.0
Content-Type: text/plain; charset="us-ascii"
X-Trace: posting.google.com 1165961768 22011 127.0.0.1 (12 Dec 2006 22:16:08 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: Tue, 12 Dec 2006 22:16:08 +0000 (UTC)
User-Agent: G2/1.0
X-HTTP-UserAgent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1) Gecko/20061010 Firefox/2.0,gzip(gfe),gzip(gfe)
Complaints-To: groups-abuse@google.com
Injection-Info: 16g2000cwy.googlegroups.com; posting-host=72.165.244.50;
   posting-account=YZW96w0AAABjQaJtmc4mPml4qWWrMr8V
Xref: usenetserver.com comp.databases.oracle.server:419065
X-Received-Date: Tue, 12 Dec 2006 17:16:08 EST (text.usenetserver.com)

Jonathan,
Would it make sense to histogram the other table as I suggested, even
if there is little skew, possibly removing the halved cardinality
estimate?


Jonathan Lewis wrote:
> <andrew.markiewicz@gmail.com> wrote in message
> news:1165960064.046563.211800@n67g2000cwd.googlegroups.com...
> > Hello.
> > I have a tuning issue which has brought me a situation that is
> > confusing.  I have reduced the issue down to a simple join which should
> > return all rows in the larger table (7792 rows).  But the CBO join
> > cardinality estimate is 1 for this join.  I created a 10053 trace and
> > found the join cardinality calculation  uses a selectivity of zero
> > causing the CBO to estimate 1 row.  What would cause this?
> >
> > There are no implicit conversion issues I can find.  There are no
> > histogram lookups since there are not even any filtering predicates on
> > the sql....
>
>
> You have a histogram on one end of the join.
>
> Column: VEND_MODEL  Col#: 3      Table: EQUIPMENT   Alias:  E
>     NDV: 172       NULLS: 0         DENS: 3.3694e-03
>     HEIGHT BALANCED HISTOGRAM: #BKT: 200 #VAL: 65
>
>
>
> Wolfgang Breitling and Alberto Dell'Era were exchanging email
> with me a little while ago about an issue where the optimizer would
> halve the join cardinality estimate in circumstances similar to yours.
> Possibly you've been caught by the same "anomaly".
>
> Try dropping the histogram temporarily to see if the cardinality changes.
>
>
> --
> Regards
>
> Jonathan Lewis
> http://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentals
> http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html