Re: Number 1 gripe about CBO: 0 <Cardinality< 1 (?????)

On 4 Apr 2007 07:40:29 -0700, "Kevin Kirkpatrick" <kvnkrkptrck_at_gmail.com> wrote:

>Clipped from a subtree of the execution plan (Oracle 10g R2)
>
>8 MERGE JOIN CARTESIAN Cardinality: 2,442
> 5 TABLE ACCESS BY INDEX ROWID SCHEMA1.T1 Cardinality: 1
> 4 INDEX RANGE SCAN SCHEMA1.T1_I1 Cardinality: 1
> 7 BUFFER SORT Cardinality: 2,323,987
> 6 TABLE ACCESS FULL SCHEMA1.T2 Cardinality: 2,323,987
>
>Since when does 2.3 Million times "1" equal 2,442??? Yes, I know -
>behind the scenes, the CBO has decided that the filter criteria of #4
>and #5 results in a cardinality of:
>
>Cardinality #5 = 20,000 x .05 x .01 x .01 x.01 = 0.0001 rows
>
>My beef is with the fact that the CBO does not immediately round this
>intermediate value up to "1" before applying it to future
>calculations. CARDINALITIES CAN NEVER EQUAL 0.0001 ROWS. They are
>either 1 or more. So how can it make sense to EVER evaluate a
>execution plan on the premise that one of intermediate steps will
>yield 0.0001 rows, a logical impossibility?
>
>
>The above snippet is a plan in a query that's been running in our
>production system for 12 hours now. step #4 / #5 actually yields 4
>rows, not .0001 rows; so the CBO is doing a series of index nested
>loops using > 10 million rows, not 2,400 rows.
>The point is not that the stats on T1 and conditional criteria on T1
>yielded a lower-than-actual estimate; the point is that no matter how
>low this estimate was, the CBO should have known that a cartesian join
>of T1 and T2 would yield at least the cardinality of T2.

The point is that you provide insufficient information by excluding the sql statement and part of the execution plan, so no one can judge whether your value judgements are correct. Also did you run the statement with event 10053 enabled?

-- 
Sybrand Bakker
Senior Oracle DBA