Re: Join Cardinality
Date: Fri, 31 May 2019 12:45:50 +0200 (CEST)
Message-ID: <795415105.482941.1559299550708_at_ox.hosteurope.de>
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-lReceived on Fri May 31 2019 - 12:45:50 CEST