Path: text.usenetserver.com!out02b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!j44g2000cwa.googlegroups.com!not-for-mail From: "EscVector" Newsgroups: comp.databases.oracle.server Subject: Re: Join selectivity is 0 causing bad cardinality estimates. Date: 12 Dec 2006 20:05:47 -0800 Organization: http://groups.google.com Lines: 57 Message-ID: <1165982747.380966.62340@j44g2000cwa.googlegroups.com> References: <1165960064.046563.211800@n67g2000cwd.googlegroups.com> <1165961761.805346.111320@16g2000cwy.googlegroups.com> <1165962649.719151.162500@16g2000cwy.googlegroups.com> NNTP-Posting-Host: 68.85.26.134 Mime-Version: 1.0 Content-Type: text/plain; charset="us-ascii" X-Trace: posting.google.com 1165982753 9135 127.0.0.1 (13 Dec 2006 04:05:53 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Wed, 13 Dec 2006 04:05:53 +0000 (UTC) In-Reply-To: <1165962649.719151.162500@16g2000cwy.googlegroups.com> 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: j44g2000cwa.googlegroups.com; posting-host=68.85.26.134; posting-account=YZW96w0AAABjQaJtmc4mPml4qWWrMr8V Xref: usenetserver.com comp.databases.oracle.server:419093 X-Received-Date: Tue, 12 Dec 2006 23:05:53 EST (text.usenetserver.com) But, I didn't see the first table showing any histograms in the 10053 which is why I suggested to histogram all tables involved with all cols, to see what would happen. I was about to post this when Jonathan noted this and said to drop the hist stats. andrew.markiewicz@gmail.com wrote: > We also have histograms calculated using a similar method as you have, > FOR ALL COLUMNS SIZE AUTO. > > > On Dec 12, 4:16 pm, "EscVector" wrote: > > 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: > > > 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