I have a query involving 4 tables. 3 have a foreign key relation. The optimizer estimates differ for all tables from the actual values.
Estimate Actual Rows returned Table A 2.8 1 Table B 4600 135000 Table C 1 135000 Table D 17700 255
The tables are joined from top to bottom. My desired plan would be the otherway round because I know, that only few rows in Table D will match.
A similar query for the same 4 tables results in:
Estimate Actual Rows returned Table D 490 290 Table C 3 1091 Table B 0.2 272 Table A 2.8 1
Both statements have the same number of search criteria one on Table A and one Table D. It only depends on the change of the value which is searched for in Table D. Difference in runtime between plan 1 and plan 2 is 3.5 to 0.01 seconds.
After some more tests it seems, that as soon as the estimate for matching rows in Table D falls below 1000 the better plan is used. A user estimate on the criteria for Table D is ignored by the optimizer, I wonder why? For the other criteria it is accepted.
What are your suggestions how to help the optimizer in such a situation?
asked 22 Jul '11, 11:40
Normally the database server maintains the selectivity estimates for the data/columns in each table automatically but they can get out of sync under some scenarios. When this happens running CREATE STATISTICS for each table/column may help.
create statistics A ( colA ); create statistics B ( colB ); create statistics C ( colC ); create statistics D ( colD );
where colA, colB, colC, colD are the columns from the respective tables that you are wanting to improve the selectivity estimates.
answered 22 Jul '11, 13:04