Optimzer and table cardinality

From: Simon Newbery <simon_at_simnew.demon.co.uk>
Date: 11 Jun 1999 12:23:05 GMT
Message-ID: <01be512c$87f5db00$LocalHost@w-snewbery.uk.sequent.com>

Fellow Oracle users,

I am currently working on a problem with the optimizer which seems to behave in a way that I do not understand when predicates in a query join the a column in a table to itself.

Whilst I realise that this is not an optimal thing to do, some applications that generate SQL do produce this kind of query and it would appear that if a query of the sort :

select empno
from emp, dept

where emp.deptno = dept.deptno            
and   emp.deptno > 10                  

and dept.deptno = dept.deptno
;

is run that the computed cardinality of the dept table drops from 3 to 1. If the final predidate is dropped off, then the original computed cardinality of 3 is returned.
The significance of this being that in this scenario, the database sometimes tried to do a cartesian join between emp and dept which is fine with small record numbers.

These of course are just example tables. If we take a real life example of dept being 50,000 rows and emp being 200,000 rowd then a cartesian join between them would be a killer. If anyone else has noticed this, please let me know as I am anxious to get to the bottom of how the optimizer works with this. I really don't understand how the optimizer can get to a computed cardinality of 1 when the table might have 50,000 records in it !.

Thanks everyone. Received on Fri Jun 11 1999 - 07:23:05 CDT