金沙棋牌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手机下载,转载请注明出处：分区切换，对现有表分区

关键词：

上一篇：MySQL函数大全及用法示例，字符串截取相关函数小

下一篇：没有了

相关阅读：