SQLServer 重建重组表某个分区索引

测试：

--	DROP TABLE [DemoDB].[dbo].[Address]

SELECT * INTO [DemoDB].[dbo].[Address] FROM [AdventureWorks2014].[Person].[Address]
GO

USE [DemoDB]
GO

--创建分区函数
CREATE PARTITION FUNCTION [PF_ID](INT) 
AS RANGE LEFT FOR VALUES (10000,15000)
GO

--创建分区方案
CREATE PARTITION SCHEME [PS_ID] 
AS PARTITION [PF_ID] TO ([PRIMARY],[FG],[FG1] )
GO

--表分区
ALTER TABLE [dbo].[Address]
ADD CONSTRAINT [PK_Address] PRIMARY KEY CLUSTERED ([AddressID] ASC)
ON [PS_ID]([AddressID])
GO

--删除1/4的数据,使其产生碎片
DELETE FROM [dbo].[Address] WHERE [AddressID]%4=0


--查看表的分区
SELECT * FROM sys.partitions WHERE [object_id]=OBJECT_ID('Address')

SELECT index_id,partition_number,avg_fragment_size_in_pages,page_count,alloc_unit_type_desc
FROM sys.Dm_db_index_physical_stats(Db_id(),Object_id('Address'),NULL,NULL)

--现在重建表第三个分区的索引，再看看，碎片减少了。
ALTER INDEX [PK_Address] ON [dbo].[Address] 
REBUILD PARTITION = 3
GO

--重建表所有分区的索引，可以使用不同的数据压缩。分区3 none是没有压缩的。
ALTER INDEX [PK_Address] ON [dbo].[Address] 
REBUILD PARTITION = ALL
WITH 
(
	DATA_COMPRESSION = NONE ON PARTITIONS (3),DATA_COMPRESSION = ROW ON PARTITIONS (2),DATA_COMPRESSION = PAGE ON PARTITIONS (1)
)
GO

--以行方式压缩分区三（行压缩较久）
ALTER INDEX [PK_Address] ON [dbo].[Address] 
REBUILD PARTITION = 3 WITH (DATA_COMPRESSION = ROW ON PARTITIONS (3))
GO

--同样，重组也适合
ALTER INDEX [PK_Address] ON [dbo].[Address] 
REORGANIZE PARTITION = 3 
GO
ALTER INDEX [PK_Address] ON [dbo].[Address] 
REORGANIZE PARTITION = ALL
GO

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!