July 12, 2006 at 2:04 pm
To make things simple I have this in a view
View_Fruit
Select Customer From Apples
Union All
Select Customer From Oranges
If I query the view with......
Select * FROM View_Fruit
Select * FROM View_Fruit WHERE Customer = 'Apples-R-Us' (This example where clause should return records from the first statement and zero from the second)
The time to return both t-sql's are the same. 50 / 50 split in the execution plan.
Ofcourse if I change things around and go with ...
Select * FROM Apples WHERE Customer = 'Apples-R-Us'
Select * FROM Oranges WHERE Customer = 'Apples-R-Us'
Then the times are really fast.
What am I missing?
July 12, 2006 at 2:27 pm
your assumption that it will only scan the first table is incorrect.
a union statement runs the query against both tables regardless of what it finds.
union eliminates duplicates,
Union All shows duplicates.
July 12, 2006 at 2:28 pm
Checking the execution plans of each should show why there's a difference.
Are you thinking of a partitioned view ? In a partitioned view, the tables involved and UNION ALL'ed have mutually exclusive data but also have a check constraint on the partitioning column. The check constraint allows the optimizer to completely exclude some of the unioned tables from consideration.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply