Re: Join Cardinality

Hello Patrick,
please have a look at the following paper ( http://www.adellera.it/static_html/investigations/select_without_replacement/SelectWithoutReplacement.pdf ) on page 5.

I am not at my desk right now and so have not calculated it with your example but give it a try :)

Best Regards
Stefan Koehler

Independent Oracle performance consultant and researcher Website: http://www.soocs.de
Twitter: _at_OracleSK

> Patrick Jolliffe <jolliffe_at_gmail.com> hat am 31. Mai 2019 um 10:37 geschrieben:
>
> List,
>
> I thought I had a reasonable grasp of join cardinality and selectivity, however been looking at an issue for the last couple of days, and feel i understand things less than when I started.
>
> Take simplified testcase below.  My understanding is that the estimated join cardinality should be:
> JCARD=CARD(T1)*CARD(T2)*JSEL
>
> where JSEL=1/MAX(NDV(T1.JOIN1), NDV(T2.JOIN1))=1/MAX(1000,1000)=1/1000
>
> Hence JCARD should be
>
> 33*50/1000=1.65
> Where does the optimizer get 33 from?  (Obviously CARD(T1)=33 but what's that got to do with it).
> I've got Jonathan's CBO  Fundamentals in front of me (open at chapter 10) so references to relevant section in that more than welcomed.
> Thanks in advance
> Patrick
>
>
> SQL> create table t1 (join1 number not null, rand20 number) ;
>  
>
> Table created.
>
> SQL> create table t2 (join1 number not null, rand30 number) ;
>
> Table created.
>
> SQL> insert /*+append*/ into t1(join1, rand20)
>   2  select rownum * 10, round(dbms_random.value(1,20)) from dual  connect by rownum <= 1000;
>
> 1000 rows created.
>
> SQL> insert /*+append*/ into t2(join1, rand30)
>   2      select rownum, round(dbms_random.value(1,30)) from dual  connect by rownum <= 1000;
>
> 1000 rows created.
>
> SQL> exec dbms_stats.gather_table_stats(null, 't1', method_opt=>'for all columns size 1');
>
> PL/SQL procedure successfully completed.
>
> SQL> exec dbms_stats.gather_table_stats(null, 't2', method_opt=>'for all columns size 1');
>
> PL/SQL procedure successfully completed.
>
> SQL> explain plan for select * from t1, t2 where t1.join1 = t2.join1 and t1.rand20 = 1 and t2.rand30 = 1;
>
> Explained.
>
> SQL> select * from table(dbms_xplan.display());
>
> PLAN_TABLE_OUTPUT
> ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
> Plan hash value: 2959412835
>
> -----------------------------------------------------------------------------------
> | Id  | Operation                  | Name | Rows  | Bytes | Cost (%CPU)| Time     |
> -----------------------------------------------------------------------------------
> |   0 | SELECT STATEMENT           |      |    33 |   462 |     6   (0)| 00:00:01 |
> |*  1 |  HASH JOIN                 |      |    33 |   462 |     6   (0)| 00:00:01 |
> |*  2 |   TABLE ACCESS STORAGE FULL| T2   |    33 |   231 |     3   (0)| 00:00:01 |
> |*  3 |   TABLE ACCESS STORAGE FULL| T1   |    50 |   350 |     3   (0)| 00:00:01 |
> -----------------------------------------------------------------------------------
>
> Predicate Information (identified by operation id):
> ---------------------------------------------------
>
>    1 - access("T1"."JOIN1"="T2"."JOIN1")
>    2 - storage("T2"."RAND30"=1)
>        filter("T2"."RAND30"=1)
>    3 - storage("T1"."RAND20"=1)
>        filter("T1"."RAND20"=1)
>
> 19 rows selected.
>
> SQL>

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