High number of estimated rows
When I try to join these tables with a temp table and query, I am getting index scan and the number of estimated rows is equal to total number of rows.
My temp table is t(id,date,areatype) and contains 14 million rows.
Below is the query:
Select A.,B., C.col1, d.Col2 from a
inner join t on a.id=t.id and a.date=t.date and a.areatype=t.areatype
inner join b on t.id=b.id and t.date=b.date and t.areaType=b.AreaType
inner join c on t.id=c.id and t.date=c.date and t.areaType=c.AreaType
inner join d on t.id=d.id and t.date=d.date and t.areatype=d.areatype
When I see the plan, then all the tables have clustered index scan and estimated number of rows as 52 million
The distinct area type are 4 only. But id and date are many and approx count for one areatype is 13 million.
Could you please help on what is the wrong in the query or index?