Re: Tuning By cardinality Estimates
Date: Mon, 9 Mar 2009 10:08:48 +0000 (GMT)
I should have mentioned this that i am on 10g and have no idea how to make the optimizer aware of the correlation between the columns.
I created a index MYINDX1 as Riyaz suggested on SRC,TF,CDT columns on the SH table and though the estimated cardinality is still of by a factor of 10 the join between SH and SC is now a hash join.
Riyaj how did you guess that i need a index there ?
However the estimated rows from the hash join is still very less 218 against the actual 21489 off by factor of 100 and after that it falls back to NL join beacause of the wrong cardinality estimates.
Any ideas on how do i make the optimizer aware of the real cardinalities without resorting to a hint