金沙棋牌app手机下载

当前位置:金沙棋牌 > 金沙棋牌app手机下载 > 分区切换,对现有表分区

分区切换,对现有表分区

来源:http://www.logblo.com 作者:金沙棋牌 时间:2019-11-22 11:10

在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 ]

图片 1图片 2

在分区表上创建对齐索引,分区列必须包含在聚集索引键,唯一索引键(唯一索引,主键约束,唯一约束)中;对于对齐的非聚集索引(不是唯一索引),分区列可以是包含列,也可以是索引键。

一,创建示例数据

-- 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]);

一,对齐的非聚集索引

图片 3图片 4

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 创建。

图片 5图片 6

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'.

图片 7

在创建聚集索引时,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约束,实现分区的可空属性

图片 8

在创建唯一索引(唯一索引和唯一约束)时,唯一索引键中必须显式包含分区列,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,主键约束

图片 9

在创建Primary key 约束时,主键列是不可空的

在分区之后,查看Table的 Data Space 是ps_int Partition Scheme

图片 10图片 11

图片 12

--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手机下载,转载请注明出处:分区切换,对现有表分区

关键词: