wrong cardinality estimation when using outer join
Hello,
I have following query on 11g:
SELECT *
FROM t1
LEFT JOIN t2 ON (t1.fk_id = t2.id)
WHERE t1.type = 'F'
AND UPPER(t1.val) NOT IN ('VAL1','VAL2');
When I look it the explain plan for this query without the join I have wrong estimation, due to skewed data. This condition returns 50% of the data, around 2 million, but the estimation is 5000 rows for a full table scan on t1 with the filter.
Making histograms for type and UPPER(val) corrects this error, the estimation gives 2 million .
But the wrong estimation of 5000 stays with the left join included.
How can this be corrected?
Regards, Jan