使用日期范围内的所有月份数字从按月份分区的表中进行选择

发布于 2024-10-16 20:13:08 字数 655 浏览 5 评论 0原文

我有一个按月分区的表（partmonth int）。用于查询表的存储过程仅接收日期范围。我需要找到一种方法来获取日期范围内的所有月份数字，并将它们传递到分区表的 where in (1,2...) 子句中。我尝试创建月份数字的临时表并使用 where in （从 #tmp 中选择月份数字），但这似乎在每个记录上执行 #tmp 或扫描所有分区。我还尝试将分区表连接到 tmp 表，但这似乎也会扫描所有分区。我假设查询优化器需要部分月份的硬值。

查询以获取所有月份数字：

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

现在我需要能够在 select 语句中使用这些月份数字，以便它们触发要使用的正确分区。

像下面这样的东西不起作用：从[交易]中选择* 其中部分月份在 (@months)

原文

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 partitioned 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)

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

心在旅行 2024-10-23 20:13:08

我发现使用“where in (...)”的 select 语句可用于表分区过滤。我看到的表扫描是由于使用了其他标准，即使我在（10）中传递了其中的位置，我也会得到扫描。

我还发现您也可以使用分区函数进行分区选择，但在我的环境和架构中，它导致索引扫描与索引查找。

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)

设置统计 xml 开启的实际执行计划结果。
没有在哪里：

<RunTimePartitionSummary>
  <PartitionsAccessed PartitionCount="13">
    <PartitionRange Start="1" End="13" />
  </PartitionsAccessed>
</RunTimePartitionSummary>

有在哪里：

<RunTimePartitionSummary>
  <PartitionsAccessed PartitionCount="1">
    <PartitionRange Start="10" End="10" />
  </PartitionsAccessed>
</RunTimePartitionSummary>

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>

回复收藏 0 原文

~没有更多了~