分区切换,对现有表分区
在SQL Server中,对超级大表做数据归档,使用select和delete命令是十分耗费CPU时间和Disk空间的,SQL Server必须记录相应数量的事务日志,而使用switch操作归档分区表的老数据,十分高效,switch操作不会移动数据,只是做元数据的置换,因此,执行分区切换操作的时间是非常短暂的,几乎是瞬间完成,但是,在做分区切换时,源表和靶表必须满足一定的条件:
在SQL Server中,普通表可以转化为分区表,而分区表不能转化为普通表,普通表转化成分区表的过程是不可逆的,将普通表转化为分区表的方法是:
在分区表上创建的索引分为:分区对齐索引(Aligned Index)和非对齐索引。对齐索引是指索引结构的分区和基础表的分区相同,这意味着,在表的一个分区上创建的索引,索引结构也在同一个分区中。索引结构在每个分区中都是独立存储和维护的,如果索引结构和表结构在同一个分区(也就是在同一个文件组上),那么索引是与基础表对齐的索引。创建对齐索引,并不意味着必须使用相同名称的分区scheme,只要分区schem使用的分区边界和每个分区存储的文件组相同,这两个分区schem是等价的,使用等价的分区scheme创建的索引和基础表是对齐的。
- 表的结构相同:列的数据类型,可空性(nullability)相同;
- 索引结构必须相同:索引键的结构,聚集性,唯一性,列的可空性必须相同;
- 主键约束:如果源表存在主键约束,那么靶表必须创建等价的主键约束;
- 唯一约束:唯一约束可以使用唯一索引来实现;
- 索引键的结构:索引键的顺序,包含列,唯一性,聚集性都必须相同;
- 存储的数据空间(data space)相同:源表和靶表必须创建在相同的FileGroup或Partition Scheme上;
在分区架构(Partition Scheme)上创建聚集索引,就是说,将聚集索引分区。
索引对齐能够提升查询性能,能够实现分区的隔离和分区的切换(switch)操作,分区的隔离能够避免分区列上的竞争,从而有效避免死锁;通过分区的切换和数据表的截断(TRUNCATE TABLE)这一操作组合,使得在删除海量数据表上的一个分区时,能够瞬间完成,而分区对齐的好处不止如此,建议创建对齐索引。
分区切换是将源表中的一个分区,切换到靶表(target_table)中,靶表可以是分区表,也可以不是分区表,switch操作的语法是:
数据库中已有分区函数(partition function) 和分区架构(Partition scheme):
在分区表上创建索引,如果没有使用ON子句指定文件组,那么,默认创建的索引是分区索引,分区scheme和基础表相同,索引的每一个分区和基础表对应的分区存储在同一个文件组,这就是所谓的对齐索引。另外一种情况,基础表是没有分区的,而索引是分区的,在这种情况下创建分区索引时,需要在CREATE INDEX的ON子句中指定分区scheme。
ALTER TABLE schema_name . table_name
SWITCH [ PARTITION source_partition_number_expression ]
TO target_table [ PARTITION target_partition_number_expression ]
在分区表上创建对齐索引,分区列必须包含在聚集索引键,唯一索引键(唯一索引,主键约束,唯一约束)中;对于对齐的非聚集索引(不是唯一索引),分区列可以是包含列,也可以是索引键。
一,创建示例数据
-- create parition function
CREATE PARTITION FUNCTION pf_int_Left (int)
AS RANGE LEFT
FOR VALUES (10,20);
--determine partition number
select $Partition.pf_int_left(21)
CREATE PARTITION SCHEME PS_int_Left
AS PARTITION pf_int_Left
TO ([primary], [primary], [primary]);
一,对齐的非聚集索引
View Code
创建分区函数,分区scheme,分区表和分区索引:
-- create parition function
create partition function pf_int_left (int)
as range left
for values (10,20);
--create partition scheme
create partition scheme ps_int_left
as
partition pf_int_left
all to ([primary]);
--create partitioned table
create table dbo.dt_partition
(
ID int null,
Code int null
)
on ps_int_left (id)
--Create staging table
create table dbo.dt_SwitchStaging
(
ID int null,
Code int null
)
on [primary]
如果在普通表上存在聚集索引,并且聚集索引列是分区列,那么重建聚集索引,就能使表转化成分区表。聚集索引的创建有两种方式:使用clustered 约束(primary key 或 unique约束)创建,使用 create clustered index 创建。
View Code
一,在分区架构(Partition Scheme)上,创建聚集索引
-- create parition function
create partition function pf_int_left (int)
as
range left
for values (10,20);
--create partition scheme
create partition scheme ps_int_left
as
partition pf_int_left
to ([primary], [primary], [primary]);
-- create partition heap
create table dbo.dt_partitiontable
(
ID int not null,
code int not null,
name varchar(10) null,
)
on PS_int_Left(ID);
创建靶表 dt_SwitchStaging,用于存储分区表的数据
如果聚集索引是使用 create clustered index 创建的,并且聚集索引列就是分区列,使普通表转换成分区表的方法是:删除所有的 nonclustered index,在partition scheme上重建clustered index
View Code
二,源表和目标表的结构必须相同
1,表dbo.dt_partition的聚集索引是使用 create clustered index 创建的,
在分区表上创建非聚集索引,在默认情况下,创建的是对齐索引:
1,数据列的可空性必须相同(nullability)
create table dbo.dt_partition
(
ID int,
Code int
)
create clustered index cix_dt_partition_ID
on dbo.dt_partition(ID)
--create nonclustered index
create nonclustered index idx_partition_Code
on dbo.dt_partition_table(Code)
由于靶表的ID列是非空的(not null),源表的ID列是可空的(null),可空性不同,在切换分区时,SQL Server会抛出错误消息:
2,从系统表Partition中,查看该表的分区只有一个
在分区表上创建辅助索引(不是唯一索引和聚集索引)时,默认创建的是对齐索引。如果索引键中包含分区列,并且使用相同的分区scheme,那么创建的索引就是和基础表对齐的索引。如果没有显式指定分区scheme,或者索引键中没有包含分区列,SQL Server自动向辅助索引中添加分区列,作为包含列。
alter table dbo.dt_SwitchStaging
alter column ID int not null;
--swith partition 2 to staging table
alter table dbo.dt_partition
switch partition 2
to dbo.dt_SwitchStaging
select *
from sys.partitions p
where p.object_id=object_id(N'dbo.dt_partition',N'U')
二,对齐的聚集索引
ALTER TABLE SWITCH statement failed because column 'ID' does not have the same nullability attribute in tables 'dbo.dt_partition' and 'dbo.dt_SwitchStaging'.
在创建聚集索引时,SQL Server保证分区列是聚集索引键。如果没有显式指定分区列为聚集索引键,那么SQL Server会自动添加分区列作为索引列,注意:聚集索引没有包含列。
2,数据列的数据类型必须相同
3,使用partition scheme,重建表的聚集索引
在创建聚集索引的语句中,如果没有显式指定分区列和分区scheme,那么SQL Server 使用基础表上的分区列和分区scheme创建聚集索引,SQL Server自动把分区列添加到索引键中。
在执行分区切换时,源表和靶表的数据类型必须相同,即使数据类型相兼容,SQL Server会抛出错误消息:
create clustered index cix_dt_partition_ID
on dbo.dt_partition(ID)
with(drop_existing=on)
on PS_int_Left(ID)
create clustered index cidx_partition_table_code
on dbo.dt_partition_table(code)
alter table dbo.dt_SwitchStaging
alter column ID bigint null
4,重建聚集索引之后,表的分区有三个
创建聚集索引的目的是为了从物理上组织数据表的存储结构,由于,对表分区影响表的物理结构,使得表的数据按照物理存储顺序存储,因此,SQL Server内部强制分区列必须是聚集索引的一列。
ALTER TABLE SWITCH statement failed because column 'ID' has data type int in source table 'dbo.dt_partition' which is different from its type bigint in target table 'dbo.dt_SwitchStaging'.
select *
from sys.partitions p
where p.object_id=object_id(N'dbo.dt_partition',N'U')
三,对齐的唯一索引
三,隐式的Check约束,实现分区的可空属性
在创建唯一索引(唯一索引和唯一约束)时,唯一索引键中必须显式包含分区列,SQL Server 强制每个分区上的索引列都是唯一的。这意味着,在创建对齐的唯一索引时,不管创建的是聚集索引还是非聚集索引,必须在唯一索引键中显式指定分区列。
分区列(Partition Column)允许为NULL,SQL Server在分区时,将NULL值作为最小值,存储在最左边的第一个分区中,其Partition Number是1。
二,如果表的聚集索引是使用Primary key clustered 来创建,并且primary key 就是分区列
--drop clustered index
drop index cidx_partition_table_code
on dbo.dt_partition_table
--create unique clustered index
create unique clustered index cidx_partition_table_code
on dbo.dt_partition_table(code)
--create unique nonclustered index
create unique nonclustered index idx_partition_table_code
on dbo.dt_partition_table(code)
Any data with a NULL in the partition column will reside in the leftmost partition. NULL is considered smaller than the minimum value of the data type’s values.
在SQL Server中,不能修改约束,将普通表转换成分区表,有两种方式来实现,第一种方式是:在删除clustered constraint 时,将数据移动到分区scheme上;第二种方式,删除clustered constraint,在分区scheme上重建clustered constraint。
由于唯一约束在底层使用唯一索引来保证唯一性,因此,在分区表上创建的唯一约束,必须显式包含分区列。
分区函数(Partition Function)定义分区列(Partition Column)在每一个分区的取值区间(Value Range),在SQL Server内部,取值区间是使用Check约束来实现的,每一个Partition都有一个check 约束,用于限定Partition column的取值范围:
1,在删除clustered 约束时,将数据移动到分区scheme上
如果没有显式指定分区列,SQL Server会抛出错误消息:
- Partition Number=1,Partition column允许存在null;
- 其他Partition,Partition column不允许存在null;
使用 alter table drop constraint 命令,在删除聚集索引时,将数据移动到指定的Partition Scheme上,此时该表变成分区的堆表:
Column 'ID' is partitioning column of the index 'xxx'. Partition columns for a unique index must be a subset of the index key.
对于Unknown值,Check约束认为逻辑结果是True,例如,check(ID>1 and ID<10), 如果ID=Null,那么表达式ID>1 and ID<10 返回Unknown(或null),但是,Check约束返回的结果是True,即不违反check约束。
ALTER TABLE schema_name . table_name
DROP [ CONSTRAINT ] constraint_name
[ WITH ( MOVE TO { partition_scheme_name(partition_column_name ) | filegroup | [default] } )]
四,对齐的主键约束
四,表的索引结构必须相同,唯一性和聚集性也必须相同
move to 选项的作用是将Table移动到新的Location中,如果新的location 是partition scheme,那么在删除clustered 约束时,SQL Server将表数据移动到分区架构中,这种操作和使用 create table on partition scheme创建分区表的作用相同。
在SQL Server 内部,主键约束(Primary Key)自动创建唯一索引(unique index),只不过索引列必须是非空的(not null),因此,在创建主键约束时,不管创建的是聚集索引还是非聚集索引,主键必须显式包含分区列。
在执行分区切换时,表的索引结构,唯一性和聚集性必须相同,在SQL Server中,使用unique index 实现unique 约束的唯一性。
create table dbo.dt_partition_pk
(
ID int not null constraint pk__dt_partition_ID primary key clustered ,
Code int not null
)
alter table dbo.dt_partition_pk
drop constraint pk__dt_partition_ID
with( move to PS_int_Left(ID))
-- create pk nonclustered index
alter table dbo.dt_partition_table
add constraint PK__partition_table_code_nonclustered
primary key nonclustered (code)
-- create pk clustered index
alter table dbo.dt_partition_table
add constraint PK__partition_table_code_clustered
primary key clustered (code)
1,索引的聚集性
2,删除clustered 约束,在partition scheme上重建clustered 约束
如果没有显式指定分区列,SQL Server会抛出错误消息:
在分区表上创建一个聚集索引(clustered index),在切换分区时,SQL Server抛出错误信息,要求靶表必须创建聚集索引
create table dbo.dt_partition_pk
(
ID int not null constraint pk__dt_partition_ID primary key clustered ,
Code int not null
)
alter table dbo.dt_partition_pk
drop constraint pk__dt_partition_ID
alter table dbo.dt_partition_pk
add constraint pk__dt_partition_ID primary key clustered(ID)
on PS_int_Left(ID);
Column 'ID' is partitioning column of the index 'PK__xxxx'. Partition columns for a unique index must be a subset of the index key.
--create clustered index
create clustered index cix_dt_partition_ID
on dbo.dt_partition(ID)
三,将堆表转换成分区表
五,非对齐索引
ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'cix_dt_partition_ID' while the table 'dbo.dt_SwitchStaging' does not have clustered index.
使堆表转换成分区,只需要在堆表上创建一个分区的clustered index
也可以在分区表上创建非对齐索引,所谓非对齐索引,是指索引的分区scheme和基础表的分区scheme不同,其物理存储结构也不同。在默认情况下,SQL Server在分区表上创建的是对齐索引,因此,要创建非对齐索引,必须在CREATE INDEX命令中显式指定索引结构存储的数据空间,数据空间是文件组,或者跟基础表不同的分区scheme。这意味着,非对齐索引存储在特定的文件组上,或者跟其基础表有不同的分区scheme。
2,唯一约束
create table dbo.dt_partition_heap
(
ID int not null,
Code int not null
)
create clustered index cix_partition_heap_ID
on dbo.dt_partition_heap(ID)
on PS_int_Left(ID)
create nonclustered index idx_partition_table_code
on dbo.dt_partition_table(code)
on file_group_name;
在分区表上创建唯一聚集约束(unique clustered),在切换分区时,SQL Server抛出错误消息,要求靶表必须创建唯一索引
四,普通表转化成分区表的过程是不可逆的,普通表能够转化成分区表,而分区表不能转化成普通表。
六,查看索引的元数据
alter table dbo.dt_partition
add constraint UQ__dt_partition_ID_Code
unique clustered(ID,Code)
普通表存储的Location是FileGroup,分区表存储的Location是Partition Scheme,在SQL Server中,存储表数据的Location叫做Data Space。通过在Partition Scheme上创建Clustered Index ,能够将已经存在的普通表转化成partition table,但是,将Clustered index删除,表仍然是分区表,转化过程(将普通表转换成分区表)是不可逆的,一个Partition Table 是不能转化成普通表的,即使通过合并分区,使Partiton Table 只存在一个Partition,这个表的仍然是Partition Table,这个Table的Data Space 是Partition Scheme,而不会转化成File Group。
使用系统视图:sys.indexes 查看索引的元数据:
ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'UQ__dt_partition_ID_Code' while the table 'dbo.dt_SwitchStaging' does not have clustered index.
从 sys.data_spaces 中查看Data Space ,共有两种类型,分别是FG 和 PS。FG是File Group,意味着数据表的数据存储在File Group分配的存储空间,一个Table 只能存在于一个FileGroup中。PS 是Partition Scheme,意味着将数据分布式存储在不同的File Groups中,存储数据的File Group是根据Partition column值的范围来分配的。对于分区表,SQL Server从指定的File Group分配存储空间,虽然一个Table只能指定一个Partition Scheme,但是其数据却分布在多个File Groups中,这些File Groups由Partition Scheme指定,可以相同,也可以不同。
select i.name as index_name
,i.index_id
,i.type_desc as index_type
,ds.name as data_space
,ds.type_desc as data_space_type
,i.is_unique
,i.is_unique_constraint
,i.is_primary_key
,ic.index_column_id
,col_name(ic.object_id,ic.column_id) as column_name
,ic.key_ordinal
,ic.partition_ordinal
,ic.is_included_column
from sys.indexes i
inner join sys.index_columns ic
on i.object_id=ic.object_id
and i.index_id=ic.index_id
inner join sys.data_spaces ds
on i.data_space_id=ds.data_space_id
where i.object_id=object_id('table_name','U')
order by i.index_id
,ic.index_column_id
Workaround1:在靶表上创建唯一聚集索引(unique clustered),而不是创建unique clustered 约束,switch 成功;
查看Table的Data Space,通过索引的data_space_id 字段来查看各个索引(聚集索引是表本身)数据的存储空间:
--create unique clustered index
create unique clustered index ucix_dt_SwitchStaging_ID_Code
on dbo.dt_SwitchStaging(ID,Code)
select o.name as TableName,o.type_desc,
i.name as IndexName,
i.index_id,i.type_desc,i.data_space_id,
ds.name as DataSpaceName,ds.type_desc
from sys.indexes i
inner join sys.objects o
on o.object_id=i.object_id
inner join sys.data_spaces ds
on i.data_space_id=ds.data_space_id
where i.object_id=object_id(N'[dbo].[dt_test_partition]')
and i.index_id=0
参考文档:
Workaround2:在靶表上创建unique clustered 约束,switch 成功;
在分区之前,查看Data_space是Name是 Primary File Group
Partitioned Tables and Indexes.aspx)
3,主键约束
在创建Primary key 约束时,主键列是不可空的
在分区之后,查看Table的 Data Space 是ps_int Partition Scheme
--drop table
drop table dbo.dt_partition
go
drop table dbo.dt_SwitchStaging
GO
--create partitioned table
create table dbo.dt_partition
(
ID int not null,
Code int null,
)
on PS_int_Left (ID)
go
--Create staging table
create table dbo.dt_SwitchStaging
(
ID int not null,
Code int null
)
on [primary]
go
目前无法将Table的Data Space 转化成FG
View Code
为分区表创建主键约束,使用唯一聚集索引(unique clustered)实现,跟唯一聚集约束的唯一区别是唯一约束列允许为NULL
alter table dbo.dt_partition
add constraint PK__dt_partition_ID
primary key clustered(ID)
将分区表的第二个分区切换到靶表,SQL Server抛出错误信息,要求靶表必须创建唯一聚集索引,注意,不是创建聚集主键;
--swith partition 2 to staging table
alter table dbo.dt_partition
switch partition 2
to dbo.dt_SwitchStaging
ALTER TABLE SWITCH statement failed. The table 'dbo.dt_partition' has clustered index 'PK__dt_partition_ID' while the table 'dbo.dt_SwitchStaging' does not have clustered index.
在靶表上创建唯一聚集索引,在执行分区切换时,SQL Server抛出错误消息:没有等价的索引,这是因为聚集主键创建的索引是唯一的,聚集的,非空的,而唯一聚集索引是唯一的,聚集的,可空的,两者不是完全等价的。
--create unique clustered index
create unique clustered index cix_dt_SwitchStaging_ID
on dbo.dt_SwitchStaging(ID)
ALTER TABLE SWITCH statement failed. There is no identical index in source table 'dbo.dt_partition' for the index 'cix_dt_SwitchStaging_ID' in target table 'dbo.dt_SwitchStaging' .
在靶表上创建聚集主键,switch成功
--add primary key clustered constraint
alter table dbo.dt_SwitchStaging
add constraint PK__dt_SwitchStaging_ID
primary key clustered(ID)
五,总结
在执行分区操作时,要求源表和靶表必须满足:
- 表的结构相同:列的数据类型,可空性(nullability)相同;
- 索引结构必须相同:索引键的结构,聚集性,唯一性,列的可空性必须相同;
- 主键约束:如果源表存在主键约束,那么靶表必须创建等价的主键约束;
- 唯一约束:唯一约束可以使用唯一索引来实现;
- 索引键的结构:索引键的顺序,包含列,唯一性,聚集性都必须相同;
- 存储的数据空间(data space)相同:源表和靶表必须创建在相同的FileGroup或Partition Scheme上;
参考文档:
ALTER TABLE (Transact-SQL).aspx)
本文由金沙棋牌发布于金沙棋牌app手机下载,转载请注明出处:分区切换,对现有表分区
关键词:
下一篇:没有了