Use all month numbers in date range for select from table partitioned by month
I have a table that is partitioned by month (partmonth int). The stored proc used to query the table only receives a date range. I need to find a way to get all of the month numbers in the date range and pass them into a where in (1,2...) clause for the partitioned table. I have tried creating a temp table of the month numbers and using a where in (select monthnumber from #tmp), but this seems to execute the #tmp on each record or scan all partitions. I have also tried to join the partitio开发者_如何学JAVAned table to the tmp table, but this seems to scan all partitions as well. I assume the query optimizer need hard values for the partmonth where.
query to get all month numbers:
declare @months varchar(100)
select d.* into #tmp
from
(select distinct MonthNumber from [date] where [date] between '1/1/2010' and '4/1/2010') d
select @months = coalesce(@months + ', ', '') + cast(monthnumber as varchar(2)) from #tmp
select @months
drop table #tmp
Now I need to be able to use these month numbers in a select statement so that they trigger the correct partitions to be used.
Something like the following that does not work: select * from [transactions] where partmonth in (@months)
I have found using a select statment for "where in (...)" works for table partition filtering. The table scan I was seeing was due to other criteria used, i get the scan even if I pass in where in (10).
I have also found that you can use the partition function for partition selection also, but in my environment and schema, it caused and index scan vs index seek.
DECLARE @months TABLE (MonthNumber int)
insert into @months
select distinct MonthNumber from [date] where [date] between '10/1/2010' and '10/7/2010'
select * from [transaction] t
WHERE $Partition.TransactionPartitionFunction(t.partmonth) in (select monthnumber from @months)
Actual Execution plan resutls with set statistics xml on. Without where in:
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="13">
<PartitionRange Start="1" End="13" />
</PartitionsAccessed>
</RunTimePartitionSummary>
With where in:
<RunTimePartitionSummary>
<PartitionsAccessed PartitionCount="1">
<PartitionRange Start="10" End="10" />
</PartitionsAccessed>
</RunTimePartitionSummary>
精彩评论