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: @OracleSK
Patrick Jolliffe <jolliffe@xxxxxxxxx> 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>
