Partitioned View w index hints

[DBAWinnipeg]

Question

I have a view that is made up of 3 partitioned tables. If I specify the index hint that I would like for each of the tables within the view will those hints be used when selecting from the view

ie)

CREATE VIEW vw_Test AS
Select * from tbl_1 WITH(INDEX(idx_1))
UNION ALL
Select * from tbl_2 WITH(INDEX(idx_2))
UNION ALL
Select * from tbl_3 WITH(INDEX(idx_3))


and if I do

Select * from vw_Test where blah blah blah blah

will it try and select the indexes to use on it's own... or will the index hints get brought forward.



Thanks in advance!!!

Colin in da 'Peg

 

[NoCoolHandle]

My guess is the index hints will force the index. It might even cause the tables to be hit where they wouldn't normally. Thus causing a performance issue and removing some of the value of the partitioned view.

But if you have the basic query you could find out by doing a showplan.

(right click query analyzer and select "show execution plan")

Then all you can tell me for sure

Rob