I am creating and indexing a view.
The index does materialize physically (i think -- since sp_spaceused now returns values -- not nulls)
But any query searching the view does NOT use the materialized index.
Here's an example:
-- Create/Populate test table
create table test (i int constraint PK primary key)
insert test select 1 union select 2
-- Create View
set ansi_nulls, ansi_padding, ansi_warnings, concat_null_yields_null, quoted_identifier on
go
create view mv
with schemabinding
as
select i from dbo.test
go
--Index the column checking if the index materializes exec sp_spaceused mv create unique clustered index mvidx on mv (i) exec sp_spaceused mv |
-- The graphic execution plan shows that this query still uses PK index on the original table. This is the output from showplan_text: set showplan_text on go select * from mv where i = 1 |--Clustered Index Seek(OBJECT:([db].[dbo].[test].[PK]), SEEK:([db].[dbo].[test].i=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD) |
So I try to hint to the index on the Materialized View, DOH!! still uses the original table index:
set showplan_text on go select * from mv with (index(mvidx)) where i = 1 |--Clustered Index Seek(OBJECT:([db].[dbo].[test].[PK]), SEEK:([db].[dbo].[test].i=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD) |
So I say - why is it ignoring the hint And I try a non-existing index... The statement compiles and executes just fine
of course still using the original PK table index.... Similar usage of an index hint works fine with base tables, but it doesn't seem to work with materialized views. I tried other index hints (option (loop join) on the view, and they work ok...set showplan_text on go select * from mv with (index(BLAHBLAH)) where i = 1 |--Clustered Index Seek(OBJECT:([db].[dbo].[test].[PK]), SEEK:([db].[dbo].[test].i=CONVERT_IMPLICIT(int,[@1],0)) ORDERED FORWARD) |
So it seems that the compiler completely avoids the indexes on the MV using it as a regular view... Unless I'm missing something (and I really hope I am, since the application I'm building relies on MVs)
Thanks!
Materialized View Doesn't Materialize. (and queries on MVs ignore index hints)
HectorCruz
SELECT @@version
For the first SELECT example it's not unusual for the optimizer to choose some access plan other than the indexed view when accessing the data from the table itself is just as efficient. With so little data in the table it knows it has an optimum plan without considering any index.
So for a truer test you might want to add another few columns to the table and a thousand rows or so .. that way there'd be a real benefit for the optimizer to use the indexed view over the table.
But as to why the second two didn't use the index and didn't give an error, perhaps we can figure that out once you provide the version information.
Don
Krithi
2. If you want to guarantee that your indexed view will be used in any edition of SQL Server, then use the NOEXPAND hint, e.g.:
set showplan_text on
go
select * from mv with (noexpand) where i = 1
This tells the query processor to treat mv just like it was a regular table for purposes of optimizing this query.
For details on what's new for indexed views in SQL Server 2005, and some general overview of indexed views, see this white paper:
http://www.microsoft.com/technet/prodtechnol/sql/2005/ipsql05iv.mspx
Regards,
Eric Hanson
Microsoft
Program Manager, SQL Server Query Optimizer Team
BNick
Don, here's the version info:
SQL Server 2005 - 9.00.1187.07 (Intel X86)
Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
I simplified the example. The actual mv has 8 columns, 100,000 rows.
still kinda curious as to why the optimizer picks to expand the view when the hint is not used. According to profiler, the 'noexpand' plan is much more efficient....
Anyway -- more of an inconvenience rather than a problem...
tkim11
the real view joins 11 tables with group by/aggregations. I just simplified for the example.
Micromause
Since the query
select * from mv where i = 1
is, after expansion, a single table query with a simple selection predicate, "trivial plan" optimization is being invoked. Trivial plan optimization is a short-cut that doesn't go through full optimization. Indexed view matching is only considered during full optimization.
If you try a query and indexed view with a join or group by/aggregation, then full optimization will be done and you'll be more likely to get a match with the indexed view. Of course, the cost of using the indexed view will have to be lower or the optimizer still might not use it in the query plan.