CODEWITHSUNDEEP
t-sql
dudeNumber4
dudeNumber4

Reputation: 4707

Simple TSQL union view won't respect indexes of tables

I have 2 tables with identical structure & a view that unions them together. The view, however, isn't using the indexes on the underlying tables. Is there some way to make SQL Server respect the indexes without knowing which particular indexes will be needed by the caller? The plan for the view should be the same as the plan for a query that mimics the view's query...

Update: Even adding the query hint to use the index in the view's query does nothing!

Reproduce:

create TABLE Uno ( Id int NOT NULL primary key identity(1,1), SomeInt int NULL, SomeOtherCrap varchar(max) null )
CREATE NONCLUSTERED INDEX IX_SomeInt ON dbo.Uno ( SomeInt )

create TABLE Dos ( Id int NOT NULL primary key identity(1,1), SomeInt int NULL, SomeOtherCrap varchar(max) null )
CREATE NONCLUSTERED INDEX IX_SomeInt2 ON dbo.Dos( SomeInt )
go

insert Uno values (1, 'some other crap')
go

declare @i int = 0;
while @i < 500000
begin
    insert Dos values( @i, 'a bunch of useless crap' )
    set @i = @i + 1
end
go

update statistics uno IX_SomeInt
update statistics dos IX_SomeInt2
go

create View Both as
select * from Uno
union
select * from Dos

Query against view:

View Plan

Query against individual tables:

Union Plan

Upvotes: 2

Views: 1028

Answers (2)

Hogan
Hogan

Reputation: 70523

The solution is to use the following view:

create View Both as 
   select * from Uno 
     union all 
   select * from Dos

Why does this work. As @BaconBits points out when you do the following

select *
from (select * from Uno
     union
     select * from Dos)
where SomeInt > 1

The optimizer can't tell that it can use the index on each table. However union all works different and a good optimizer can leverage that. Since union all has no cohesion (that is nothing in one select will effect the other select) the optimizer knows that it can slit the analysis as if you were doing the where clause on each select.

This optimization effect works within a view. Views are exactly the same as if you had typed out the whole query with a sub-select. They are useful for clarity, documentation, and security, but there is not difference between having a view and typing in the full select.

... except on systems where you can indexes to views, but that is another story and out of scope for this question.

Upvotes: 3

Bacon Bits
Bacon Bits

Reputation: 32170

Your non-view query specifies something that an index covers, while no index covers the view. The view says "union these then filter". The query says "filter these two tables then union them".

Compare:

select * from Uno where SomeInt > 1
union
select * from Dos where SomeInt > 1

To:

select *
from (select * from Uno
    union
    select * from Dos)
where SomeInt > 1

How is the query engine supposed to know that those are equivalent? Once the query engine does the union, it can't really use either index since, at the very least, the implicit DISTINCT has changed which rows appear in the result set (some from Uno and some from Dos). Any information gained by scanning the index wouldn't deterministically determine which rows should appear in the result set, so no index can be used.

Upvotes: 0

Related Questions