Will this query be optimized for a partitioned view?

[Peder Bacher]

Hello :-)

My question is: If I query a partitioned view, but don't know the values
in the "where x in(<expression> )" clause, i.e.: select * from viewA
where intVal in(select intVal from tbl1) . Compared to: select * from
viewA where intVal in(5,6).
Of course "intVal" is partitioning column.
Will this result in an optimized query that searches only the relevant
tables?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

[Erland Sommarskog]

Peder Bacher (pe****@ofir.dk ) writes:

My question is: If I query a partitioned view, but don't know the values
in the "where x in(<expression> )" clause, i.e.: select * from viewA
where intVal in(select intVal from tbl1) . Compared to: select * from
viewA where intVal in(5,6).
Of course "intVal" is partitioning column.
Will this result in an optimized query that searches only the relevant
tables?

That's up to the optimizer, and the only way to find out is to test. I would
not really expect it though, because the optimizer would in that case have
to first run part of the query, and the build the rest of the query plan,
at it does not do that. So unless there is a constraint on tbl1.IntVal,
the optimizer will have to assume worst case, and construct a plan that
searches all tables in the view.
--
Erland Sommarskog, SQL Server MVP, es****@sommarsk og.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp