Re: cartesian cardinality estimates

From: VC <boston103_at_hotmail.com>
Date: Fri, 06 Feb 2004 02:57:35 GMT
Message-ID: <zoDUb.189252$nt4.798940@attbi_s51>

Hello Richard,

"Richard Kuhler" <noone_at_nowhere.com> wrote in message news:rXCUb.6144$a65.4303_at_twister.socal.rr.com...
> Why would the cardinality estimate for a MERGE JOIN CARTESIAN be lower
> than the product of the two cardinality estimates used for the cartesian?
>
> MERGE JOIN (CARTESIAN) (Card=1)
> TABLE ACCESS (FULL) OF 'X' (Card=1)
> TABLE ACCESS (FULL) OF 'Y' (Card=1291)
>
> I can see how this is possible if that "Card=1" is a lie and the value
> is actually much smaller than 1. However, it is my understanding that 1
> is the lower bound for any range cardinality calculation (the where
> condition on table X is COLUMN BETWEEN 'VAL1' and 'VAL2').
>
> What is happening here?

I've also noticed the same phenomenon. As you put it, it's a lie and usually means that one of the tables is empty (if MERGE JOIN (CARTESIAN) cardinality is also 1).

Consider this:

Case 1 -- t1 is empty (zero rows)

SQL> create table t1(x int)
2 /

Table created.

SQL> create table t2(x) as select object_id from all_objects 2 /

Table created.

SQL> analyze table t1 estimate statistics 2 /

Table analyzed.

SQL> analyze table t2 estimate statistics 2 /

Table analyzed.

SQL> set autot trace expl
SQL> select * from t1, t2
2 /

Execution Plan

0 SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=1 Bytes=17) 1 0 MERGE JOIN (CARTESIAN) (Cost=8 Card=1 Bytes=17)

   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=13)
   3    1     BUFFER (SORT) (Cost=6 Card=25370 Bytes=101480)
   4    3       TABLE ACCESS (FULL) OF 'T2' (Cost=6 Card=25370 Bytes=101480)

t1 cardinality and the cartesian join cardinality is one.

Case 2 -- t1 has one row

SQL>
SQL> insert into t1 values(1)
2 /

1 row created.

SQL> analyze table t1 estimate statistics 2 /

Table analyzed.

SQL> select * from t1, t2
2 /

Execution Plan

   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=8 Card=25370 Bytes=1
          52220)

   1    0   MERGE JOIN (CARTESIAN) (Cost=8 Card=25370 Bytes=152220)
   2    1     TABLE ACCESS (FULL) OF 'T1' (Cost=2 Card=1 Bytes=2)
   3    1     BUFFER (SORT) (Cost=6 Card=25370 Bytes=101480)
   4    3       TABLE ACCESS (FULL) OF 'T2' (Cost=6 Card=25370 Bytes=101480)

t1 cardinality is again one.
Now, the join cardinality is correct: 1 times 25370 equals 25370.

Rgds.

VC Received on Thu Feb 05 2004 - 20:57:35 CST