Re: Strange optimizer problem

Gerold,
The difference between the slow and the fast query is that the driving table has changed.
This is because of the difference in cardinality estimates between the clauses,
m.vi_k_nr like '45481812%' and m.vi_k_nr like '4%'. If you dont have column level statistics, the optimizer estimates the selectivity of like 'A%' (NDV/72) is more than that of like 'AB%' (NDV/9342).
Likewise the selectivity of like 'AB%' is more than that of like 'ABC%' (NDV/19044).
I have seen that the selectivity remains the same after 3 characters or more.
i.e selectivity of like 'ABC%' is same as like 'ABCD%'

Also if you dont have column level statistics, the clauses like 'A%' and like 'B%' are considered the same.
So in your case the optimizer is changing the driving table due to the selectivity and hence cardinality changes for the table VIS_P501_MLT due to the clause m.vi_k_nr like '45481812%.

Try getting column level statistics for the column vi_k_nr in the table VIS_P501_MLT and see if the plan changes. regards
Srivenu Received on Thu Mar 04 2004 - 03:05:56 CST