22,209
社区成员
发帖
与我相关
我的任务
分享如何高效删除分区表中的数据？
有个分区表Table1，表的字段有：id(主键)，name, gender, spyndate（date类型）。 插入的记录根据 字段spydate分布到 12 个分区中，一月份的数据在分区1，二月份的数据在分区2，.......， 以此内推。
分区表Table1的记录特别多，有 2千万条记录，平均每个分区有两百万条数据。从2003开始一直到2015年的数据都有。
问题1： 现在要删除两个月以前的数据，该如何实现了？用 delete语句效率太低了，估计删一天都无法完成，而且会影响业务系统，如何实现？
问题2： 问题1是指一次执行删除两个月以前的数据，现在每天让系统自动删除两个月以前的数据，又如何实现了？
谢谢各位
分区表Table1的记录特别多，有 2千万条记录，平均每个分区有两百万条数据。从2003开始一直到2015年的数据都有。
问题1： 现在要删除两个月以前的数据，该如何实现了？用 delete语句效率太低了，估计删一天都无法完成，而且会影响业务系统，如何实现？
问题2： 问题1是指一次执行删除两个月以前的数据，现在每天让系统自动删除两个月以前的数据，又如何实现了？
谢谢各位
...全文
请发表友善的回复…
发表回复
giant7 2015-03-12
- 打赏
- 举报
谢谢你的回复！
你的分区方案是不是问题：
CREATE PARTITION SCHEME PS_PartitionedByMonth_1
AS PARTITION PF_PartitionedByMonth_1
TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')
你把所有的数据库都放在同一个文件组Primary，在切换分区时会不会把其他的分区数据也删除掉？
还是说： 分区与文件组在做切换分区时不会相互影响？
oraclecaicai 2015-03-12
- 打赏
- 举报
SQL Server中的分区是指表或索引的分区，是一个逻辑上的概念，从大到小的分级是这样的：表或索引 -> 分区(partition) -> 分配单元(allocation unit) -> 区(extent) -> 页(page)。
而文件组是一个物理上的概念，一个表的不同分区可以位于不同的文件组上，这样可以提高并发性能。分区方案就是用来指定，根据分区函数划分的若干个分区，分别位于哪些文件组上。我使用的测试环境，数据库test只有默认的PRIMARY文件组，所以就将分区表Table1的全部分区都放在PRIMARY上了。
实际上，普通表也是有分区的，只不过只有一个。你用普通表Table2和Table1中的某一个分区进行交换，实际上只是把逻辑上的所属关系改变了，原来Table2的分区1“挂接”到了Table1上，而Table1的分区5（假设）“挂接”到了Table2上。但是交换完，两个分区的partition id是不变的，物理上的存储情况也不会改变。这也是为什么交换分区能在很短的时间内完成。
另外，刚看了一下，昨天写的代码还是有点问题，月份错位了。SQL Server的分区概念比较复杂（相对于Oracle来说），分区方案有边界值靠左还是靠右的问题，特别容易搞错，呵呵。分区表不多的话，还是建议程序负责检查提醒，DBA手工来维护。你要是用程序来实现的话，一定要反复好好测试，这个要是错了事儿就大了~ 正确的查询语句如下：
SELECT
CASE pf.boundary_value_on_right
WHEN 0 THEN p.partition_number
ELSE p.partition_number - 1
END
FROM sys.objects obj
JOIN sys.indexes ind ON obj.object_id = ind.object_id
JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
WHERE obj.name = 'Table1'
AND prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
giant7 2015-03-11
- 打赏
- 举报
谢谢你的回复！
但是我们这边不能创建分区，也不能删除原始表。
oraclecaicai 2015-03-11
- 打赏
- 举报
忘了加表名条件了，补充一下：
SELECT
CASE pf.boundary_value_on_right
WHEN 0 THEN p.partition_number - 1
ELSE p.partition_number - 2
END
FROM sys.objects obj
JOIN sys.indexes ind ON obj.object_id = ind.object_id
JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
WHERE obj.name = 'Table1'
AND prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
oraclecaicai 2015-03-11
- 打赏
- 举报
假设分区函数和分区方案如下：
CREATE PARTITION FUNCTION PF_PartitionedByMonth_1 (date)
AS RANGE LEFT
FOR VALUES ('2014-12-01', '2015-01-01', '2015-02-01', '2015-03-01')
CREATE PARTITION SCHEME PS_PartitionedByMonth_1
AS PARTITION PF_PartitionedByMonth_1
TO ('PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY', 'PRIMARY')
假设表的定义如下
CREATE TABLE Table1
(
id int IDENTITY(1, 1)
, name varchar(30)
, gender bit
, spyndate date
CONSTRAINT PK_Table1 PRIMARY KEY CLUSTERED (id, spyndate)
) ON PS_PartitionedByMonth_1 (spyndate)
查询保留两个月数据以外的最近的分区号，语句如下：
SELECT
CASE pf.boundary_value_on_right
WHEN 0 THEN p.partition_number - 1
ELSE p.partition_number - 2
END
FROM sys.objects obj
JOIN sys.indexes ind ON obj.object_id = ind.object_id
JOIN sys.partition_schemes ps ON ind.data_space_id = ps.data_space_id
JOIN sys.partition_functions pf ON ps.function_id = pf.function_id
JOIN sys.partitions p ON ( obj.object_id = p.object_id AND ind.index_id = p.index_id )
LEFT JOIN sys.partition_range_values prv ON p.partition_number = prv.boundary_id
WHERE prv.value = DATEADD(MM, -2, CONVERT(DATE, DATEADD(DD, -1 * DATEPART(DD, GETDATE()) + 1, GETDATE())))
还在加载中灬 2015-03-11
- 打赏
- 举报
你们的SQL版本，我猜应该是SQL2008
据我了解，按月分区，如果是时间类型，也只是一段时间内而已，你说你们12个分区，也应该就是某一年的12个月而已，因此调动分区，还需要改分区范围
因此我需要知道你们是否是真的按时间类型分区，还是说是按由时间列计算出来的月份的计算列进行的分区?
还是有什么我没有了解的知识，也请科普一下
giant7 2015-03-11
- 打赏
- 举报
Table1里面的数据一直没有处理过，有从2003年到2015年 1月份到12月份的数据。如果下个月的第一天4月1日，开始执行任务---只保留最近2个月（2月份，3月份）的数据，那么会删除1月份，4月份，5月份，.......，12月的数据，对应删除分区1，分区4,分区5，........, 分区12 的数据。
但是，从此以后，每个月的第一天（如5月1日）只需要删除3个月以前的那个月的数据。
5月1日执行任务，只需要删除2月份的数据，因为当前只有2月份，3月份，4月份有数据
6月1日执行任务，只需要删除3月份的数据，
........
........
........
12月1日执行任务，只需要删除9月份的数据
1月1日执行任务，只需要删除10月份的数据
2月1日执行任务，只需要删除11月份的数据
3月1日执行任务，只需要删除12月份的数据
4月1日执行任务，只需要删除1月份的数据。
所以在写脚本时，要特别注意 1月1日，2月1日，3月1日执行任务时涉及的月份。
因此脚本如下：
#######################################################################
truncate table Table2
---这里首先要判断一下当前月份是不是 1，2，3 这三个数。如果是1，切换分区10；如果是2，切换分区11；如果是3，切换分区12；
---但是具体SQL 不知道该如何写，感觉这里不能纯粹写SQL就能行，应为还要判断月份--------》需要大家帮忙写下，谢谢
--- ......
---如果当前月份不是 1,2,3 的话，就执行下面的SQL
alter table Table1 switch partition (DATEPART(month, GETDATE())-3) to Table2 partition (DATEPART(month, GETDATE())-3)
truncate table Table2
#######################################################################
请帮忙看下，这个脚本该如何写呢？
谢谢！
还在加载中灬 2015-03-11
- 打赏
- 举报
SQL2008能直接按月分区吗?
你的是日期类型啊~
giant7 2015-03-11
- 打赏
- 举报
以月份为分区。
1月份的数据在分区1，2月份的数据在分区2.......12月份的数据在分区12。
总共有 12 个分区。
还在加载中灬 2015-03-11
- 打赏
- 举报
你一个表总共多少个分区呢
怎么分区的呢
giant7 2015-03-11
- 打赏
- 举报
现在的情况是这样。 有一张副表 Table2，表Table2跟表Table1结构一样，用到同样的分区构架/方案，用于作切换分区。
前提是：
1 表 Table1 数据不能删除，所以不能使用该语句： truncate table Table1
2 保留 2 个月以内的数据，每个月的第一天执行作业脚本。以4月1日为例，保留2月份和3月份的数据，其他分区数据全部删除。
现在碰到的问题是：
如何正确在SQL语句中把时间规划好？ 尤其是在 2月1日时，要保留当年1月份的数据，和去年12月的数据。
现在我把脚本写好了，如下效果：
truncate table Table2
-----首先确认系统时间是几月份
DATEPART(month, GETDATE())
-----然后用当前的月份数字往前面减去1，减2，得到要保留的数据
------- 这个应该写个PL/SQL，但是自己不知道该如何去实现，请大家帮帮忙。注意要跨越2年的数据(在2月1日开始执行作业，那么去年12月的数据必须保留)。
-------
-----假设要保留数据的月份是M，N，分别对应的分区是 partition M， partition N，那么除了M，N外，其他分区的数据必须通过切换分区到 Table2 上去，删除掉。假设剩下8个月的数据分区是A, B， C，D，过程如下：
alter table Table1 switch partition A to Table2 partition A
alter table Table1 switch partition B to Table2 partition B
.........
........
truncate table Table2
请大家帮帮忙，谢谢！
薛定谔的DBA 2015-03-11
- 打赏
- 举报
谢谢你的回复！
但是我们这边不能创建分区，也不能删除原始表。[/quote]
删除思路是这样，你可以改下。不用删除原来的表，是创建另一个表，表结构跟原表一样，这个表用来切换数据用。将最早的两个分区迁移到这个表，再truncate。切一个分区就truncate一次表。
还在加载中灬 2015-03-10
- 打赏
- 举报
嗯，忘了，你这是分区，应用分区的特点是优势
giant7 2015-03-10
- 打赏
- 举报
每个分区都是百万级别的记录数，用insert 效率会不会太低了。
用下面的语句效率才高吧
ALTER TABLE [Table1] SWITCH PARTITION 1 TO [Table2] PARTITION 1
ALTER TABLE [Table1] SWITCH PARTITION 2 TO [Table2] PARTITION 2
giant7 2015-03-10
- 打赏
- 举报
版主，提了一个很好的问题。
我也是应公司生成需求在做解决方案，有些细节方面的东西还要多推敲下，谢谢！
giant7 2015-03-10
- 打赏
- 举报
非常感谢！很受用！
szlixiaolong 2015-03-10
- 打赏
- 举报
使用TRUNCATE
薛定谔的DBA 2015-03-10
- 打赏
- 举报
先说我当前用过的例子：
表中只保存一年的数据，12个月，13个分区（包括主分区primary）
编写一个作业，每个月1号定时创建最近一个分区，并删除最早一个分区
（如到了2015-03-01 ，将删除2014-03-01的分区数据，并创建新的分区保存2015-03的数据）
删除的办法和思路是：
1. 增加新的分区，当前月份用（SPLIT RANGE）
2. 切换该表最早的分区（primary）到另一个结构相同的表（SWITCH PARTITION 1 TO ）
3. 切换后，把最早的分区合并（ MERGE RANGE）
4. 把切换出来的表数据清空（truncate table 很快）
5. 更新统计信息（或者重建索引）
（其实我这表还处于同步复制中，就不写了，思路这样，希望有帮助）
唐诗三百首 2015-03-09
- 打赏
- 举报
从LZ的描述看,Table1中只保留2个月的数据? 那么是否需考虑原先的分区方案(按月分区)是否合理呢.
例如, 当前是3月,只保留2个月内的数据,那么4->12这8个表分区都是空的? 以此类推,总是多数分区是空的,查询性能也不佳.
请问是否真有必要?
如果只想保留2个月内的数据,可以不必用分区表,并在时间字段上建索引即可.
oraclecaicai 2015-03-09
- 打赏
- 举报
如果Table2是个普通表，那么当时设计者的初衷，应该是用来给Table1做切换分区用的。每次用Table2替换Table1中最老的分区，然后对Table2执行truncate操作，这样既不用停业务又可以避免DELETE的低效。不过，只能以分区为单位清理数据，即按月清理。详情参考ALTER TABLE ... SWITCH PARTITION命令。
加载更多回复（5）
