SQL & PL/SQL

Hello Experts,

I have just read an article about JOIN CARDINALITY Oracle related stuff: Table Functions And Join Cardinality Estimates , without gathering statistics (with using dynamic sampling) the optimizer calculates wrong JOIN CARDINALITY why? I mean, I am trying to understand JOIN CARDINALITY, does it depend on statistics? Although I did not gether statistics, as you can see from the below, optimizer estimate the table cardinalities correct but JOIN CARDINALITY completely wrong. What do you think about this behaviour? Does the dynamic sampling mislead the optimizer?

drop table t1 purge;


drop table t2 purge;



create table t1
as
select rownum id, mod(rownum, 10)+1 as fk, rpad('X',10) filter from dual connect by level <= 1000;



create table t2 as
select rownum + 20 id, rpad('X', 10) filter from dual connect by level <= 10;



explain plan for
select * from t1 join t2 on t1.fk = t2.id;



select * from table (dbms_xplan.display);

Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |  1000 | 53000 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN         |      |  1000 | 53000 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |    10 |   200 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |  1000 | 33000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."FK"="T2"."ID")

Note
-----
   - dynamic sampling used for this statement (level=2)

exec dbms_stats.gather_table_stats(user, 'T1');


exec dbms_stats.gather_table_stats(user, 'T2');



explain plan for
select * from t1 join t2 on t1.fk = t2.id;



select * from table (dbms_xplan.display);

Plan hash value: 2959412835

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    32 |     8  (13)| 00:00:01 |
|*  1 |  HASH JOIN         |      |     1 |    32 |     8  (13)| 00:00:01 |
|   2 |   TABLE ACCESS FULL| T2   |    10 |   140 |     3   (0)| 00:00:01 |
|   3 |   TABLE ACCESS FULL| T1   |  1000 | 18000 |     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("T1"."FK"="T2"."ID")

Thanks in advance.