| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Join selectivity is 0 causing bad cardinality estimates.
<andrew.markiewicz_at_gmail.com> wrote in message
news:1165960064.046563.211800_at_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.htmlReceived on Tue Dec 12 2006 - 16:08:29 CST
 |
 |