I have the following view
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
GO
ALTER VIEW web.vGridHotelBooking
WITH SCHEMABINDING
AS
SELECT
HBK_ID,
COF_ID,
COF_CST_ID,
HTL_Name,
COF_Data
FROM
web.HotelBooking
INNER JOIN
web.CustomerOfferBundle ON COF_ID = HBK_COF_ID
INNER JOIN
web.Hotel ON COF_HTL_ID = HTL_ID;
GO
CREATE UNIQUE CLUSTERED INDEX [CLI_vGridHotelBooking__HBK_ID]
ON [web].[vGridHotelBooking] ([HBK_ID]) ON [PRIMARY]
GO
When I execute the statement SELECT * FROM web.vGridHotelBooking
I expect to see a single clustered index scan, but instead I get this
This is the same plan I get when executing the SELECT statements directly.
What is that I am doing wrong here? I've used materialized views many times and I did not have that problem before.
EDIT 1
Running the query with a WHERE clause doesn't help either.
SELECT COF_ID
FROM web.vGridHotelBooking
WHERE COF_ID = '06A41DB5-8F14-4E6C-9084-3009E0626DAA';
EDIT 2
SELECT HBK_ID
FROM web.vGridHotelBooking
WHERE HBK_ID = 1801151518187788
EDIT 3
SELECT HBK_ID
FROM web.vGridHotelBooking WITH (INDEX(CLI_vGridHotelBooking__HBK_ID))
WHERE HBK_ID = 1801151518187788;
EDIT 4 Running the query with NOEXPAND yielded the correct plan this time.
SELECT *
FROM web.vGridHotelBooking WITH (NOEXPAND)
WHERE HBK_ID = 1801151518187788;
So the question then is - Why is that? Do I have to worry about this one.
Because CustomerOfferBundle
table has aprx 500 000 rows in it and the Hotel
table aprx 100 000
As discussed in the comments you can force the use of the indexed view using the WITH (NOEXPAND)
hint.
When you do so it shows that the forced plan is estimated at about 10% of the cost of the original plan so you might expect this to be chosen on cost grounds .
However the way that compilation works is that the view definition is first expanded out and then may or may not be matched back to the indexed view later in the optimisation process. For cheap plans optimisation may end without it ever getting to that step.
See Paul White's answer here for more about that. This also mentions
indexed view matching is not available in optimization phase 0 (transaction processing).
the transaction processing step is concerned with queries referencing at least 3 tables and nested loops joins so it is entirely possible for you that optimisation just ended there.
If you increase the size of the tables (especially HotelBooking
) and the original plan becomes more expensive more time will be spent on optimisation and the indexed view will probably end up matched.
You can always use the hint to be sure.