Re: Join selectivity is 0 causing bad cardinality estimates.

From: <andrew.markiewicz_at_gmail.com>
Date: 12 Dec 2006 14:27:52 -0800
Message-ID: <1165962472.795212.260240@73g2000cwn.googlegroups.com>

Thanks for the replies.
Tried dropping the histogram but the selectivity is still 0. Even 1/2 cardinality estimate would be better than 1. Not the best but definitely better.

Relevant trace data follows:

---

Table stats Table: EQUIPMENT Alias: E   TOTAL :: CDN: 7792 NBLKS: 52 AVG_ROW_LEN: 42

Column: VEND_MODEL  Col#: 3      Table: EQUIPMENT   Alias:  E
    NDV: 172       NULLS: 0         DENS: 5.8140e-03 LO:  0  HI: 0

    NO HISTOGRAM: #BKT: 1 #VAL: 2
-- Index stats
  INDEX NAME: I_EQUIPMENT_2 COL#: 3
    TOTAL :: LVLS: 1 #LB: 28 #DK: 172 LB/K: 1 DB/K: 6 CLUF: 1123   INDEX NAME: I_EQUIPMENT_3 COL#: 4
    TOTAL :: LVLS: 1 #LB: 15 #DK: 2208 LB/K: 1 DB/K: 1 CLUF: 2539
  INDEX NAME: I_EQUIPMENT_PK COL#: 1
    TOTAL :: LVLS: 1 #LB: 26 #DK: 7792 LB/K: 1 DB/K: 1 CLUF: 51   INDEX NAME: I_EQUIPMENT_UK1 COL#: 2
    TOTAL :: LVLS: 1 #LB: 19 #DK: 7792 LB/K: 1 DB/K: 1 CLUF: 4404
_OPTIMIZER_PERCENT_PARALLEL = 0 OPTIMIZER PERCENT INDEX CACHING = 60
  Access path: index (join index)

      Index: I_EQUIPMENT_2
  TABLE: EQUIPMENT
      RSC_CPU: 0 RSC_IO: 0
  IX_SEL: 0.0000e+00 TB_SEL: 5.8140e-03     Join: resc: 3 resp: 3
Join cardinality: 0 = outer (192) * inner (7792) * sel (0.0000e+00) [flag=0]
  Best NL cost: 4 resp: 3

On Dec 12, 4:08 pm, "Jonathan Lewis" <jonat..._at_jlcomp.demon.co.uk> wrote:
> <andrew.markiew..._at_gmail.com> wrote in messagenews: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 Lewishttp://jonathanlewis.wordpress.com
>
> Author: Cost Based Oracle: Fundamentalshttp://www.jlcomp.demon.co.uk/cbo_book/ind_book.html
>
> The Co-operative Oracle Users' FAQhttp://www.jlcomp.demon.co.uk/faq/ind_faq.html
Received on Tue Dec 12 2006 - 16:27:52 CST