Join Cardinality
Date: Fri, 31 May 2019 16:37:49 +0800
Message-ID: <CABx0cSVWHkm=fABP1K=QphXFy8UZ396_AmO3PwM5--E2gT0THQ_at_mail.gmail.com>
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 |00:00:01 |
-----------------------------------------------------------------------------------
| 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)|
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
Received on Fri May 31 2019 - 10:37:49 CEST