Cardinality estimation
I am trying to understand how optimizer is estimating cardinality for Single table access path.
I generated 10053 trace using:
explain plan for
select empno,dname from emp e, dept d where e.deptno=d.deptno and e.deptno between 10 and 20
Extracts from tracefile:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."EMPNO" "EMPNO","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"="D"."DEPTNO" AND "E"."DEPTNO">=10 AND "E"."DEPTNO"<=20 AND "D"."DEPTNO">=10 AND 10<=20 AND "D"."DEPTNO"<=20
Table Stats::
Table: DEPT Alias: D
#Rows: 4 #Blks: 5 AvgRowLen: 20.00
Index Stats::
Index: PK_DEPT Col#: 1
LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
explain plan for
select empno,dname from emp e, dept d where e.deptno=d.deptno and e.deptno between 10 and 20
Extracts from tracefile:
Final query after transformations:******* UNPARSED QUERY IS *******
SELECT "E"."EMPNO" "EMPNO","D"."DNAME" "DNAME" FROM "SCOTT"."EMP" "E","SCOTT"."DEPT" "D" WHERE "E"."DEPTNO"="D"."DEPTNO" AND "E"."DEPTNO">=10 AND "E"."DEPTNO"<=20 AND "D"."DEPTNO">=10 AND 10<=20 AND "D"."DEPTNO"<=20
Table Stats::
Table: DEPT Alias: D
#Rows: 4 #Blks: 5 AvgRowLen: 20.00
Index Stats::
Index: PK_DEPT Col#: 1
LVLS: 0 #LB: 1 #DK: 4 LB/K: 1.00 DB/K: 1.00 CLUF: 1.00
0