RE: wrong cardinality

From: Christian Antognini <Christian.Antognini_at_trivadis.com>
Date: Tue, 14 Feb 2006 02:09:40 +0100
Message-ID: <F2C9CCA71510B442AF71446CAE8AEBAF205C71@MSXVS04.trivadis.com>

Stalin

>Can anyone explain why the cardinality is way off from the expected
>2Mil mark. Below query should return a count of 2million rows. However
>both index and full scan give 133k.

Data in TYPE and DOMAINID is probably correlated. Since the CBO has no clue about it, it does wrong estimates...

>2 - filter(("OBJECTS"."DOMAINID"='3330000000000000000000000000'
> OR "OBJECTS"."DOMAINID"='tK001bHUV2UNYwZJoNYz57S1Z68A') AND
> "OBJECTS"."TYPE"='cpe')

Basically, in such a filter, a formula like the following one is applied:

(selectivity1+selectivity2-selectivity1*selectivity2)*selectivity3*numrows = (0.25+0.25-0.25*0.25)*0.06666*4000137 = 117K

Quite close to 133K... some kind of correction is missing... but it should give you an idea on how the estimation works...

HTH
Chris

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Feb 13 2006 - 19:09:40 CST