Path: text.usenetserver.com!out04b.usenetserver.com!news.usenetserver.com!in02.usenetserver.com!news.usenetserver.com!postnews.google.com!news4.google.com!border1.nntp.dca.giganews.com!nntp.giganews.com!local01.nntp.dca.giganews.com!nntp.bt.com!news.bt.com.POSTED!not-for-mail NNTP-Posting-Date: Tue, 12 Dec 2006 16:08:27 -0600 From: "Jonathan Lewis" Newsgroups: comp.databases.oracle.server References: <1165960064.046563.211800@n67g2000cwd.googlegroups.com> Subject: Re: Join selectivity is 0 causing bad cardinality estimates. Date: Tue, 12 Dec 2006 22:08:29 -0000 X-Priority: 3 X-MSMail-Priority: Normal X-Newsreader: Microsoft Outlook Express 6.00.2900.2869 X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.2962 X-RFC2646: Format=Flowed; Original Message-ID: Lines: 45 NNTP-Posting-Host: 86.130.242.224 X-AuthenticatedUsername: NoAuthUser X-Trace: sv3-SHFDnHNoButWh0ky4pox1tzcbcnHKDb7YiViP4n2HHNV9tOWeFVFOOf3QyQQO+Us3+mlKvhbggDO4YG!wDV5R7cjLOQYeporW8eAucrtz5o+gCLAsDO4saJAQf04d6o24kf96cl0Fv4YvqP8azCf6T9yU7xN X-Complaints-To: abuse@btinternet.com X-DMCA-Complaints-To: abuse@btinternet.com X-Abuse-and-DMCA-Info: Please be sure to forward a copy of ALL headers X-Abuse-and-DMCA-Info: Otherwise we will be unable to process your complaint properly X-Postfilter: 1.3.32 Xref: usenetserver.com comp.databases.oracle.server:419063 X-Received-Date: Tue, 12 Dec 2006 17:08:29 EST (text.usenetserver.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