表分区中的分区交换

Vincent-yuan

发布于 2020-04-08 18:08:15

2.3K0

发布于 2020-04-08 18:08:15

文章被收录于专栏：Vincent-yuanVincent-yuan

插入，更新，删除操作在具有大量数据的表中会变的很慢。通过分区表的分区交换可以快速实现这个过程。

分区交换的条件

分区交换总是涉及两个表。数据从源表交换到目标表。所以目标表必须总是空的。

分区交换有很多要求的条件，下面是一些比较重要的：

源表和目标表(或者分区)必须有一样的列，索引，并且使用同样的分区列。
源表和目标表(或者分区)必须在同一个文件组中
目标表(或者分区)必须是空的

如果这些条件不满足，会报错。

分区交换示例

分区交换要使用 ALTER TABLE SWITCH 语法。下面是使用这个语法的4中方式：

从一个无分区的表交换到另一个无分区的表
从一个无分区的表交换到另一个分区表的一个分区
从一个分区表的一个分区交换到另一个无分区的表
从一个分区表的一个分区交换到另一个分区表的一个分区

下面的例子中，不会创建任何的索引，并且它们所有的分区都在PRIMARY文件组中。

这些示例并不意味着在实际使用时的例子。

1.无分区表到无分区表的交换

第一种方式，交换一个无分区表的所有数据到另一个空的无分区表

ALTER TABLE Source SWITCH TO Target

交换前：

交换后：

这种方式不是很常用，但是它确实是学习 ALTER TABLE SWITCH语法的比较好的方式，

因为它不要求必须要创建分区函数(partition functions) 和分区架构(partition schemes):

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
 
-- Create the Non-Partitioned Source Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
 
-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];

-- Verify row count before switch
SELECT COUNT(*) FROM SalesSource; -- 1461000 rows
SELECT COUNT(*) FROM SalesTarget; -- 0 rows

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT COUNT(*) FROM SalesSource; -- 0 rows
SELECT COUNT(*) FROM SalesTarget; -- 1461000 rows

-- If we try to switch again we will get an error:
ALTER TABLE SalesSource SWITCH TO SalesTarget; 
-- Msg 4905, ALTER TABLE SWITCH statement failed. The target table 'SalesTarget' must be empty.

-- But if we try to switch back to the now empty Source table, it works:
ALTER TABLE SalesTarget SWITCH TO SalesSource; 
-- (...STILL SUPER FAST!)

2.无分区表到有分区表的交换

第二种方式，使用 ALTER TABLE SWITCH 语法交换无分区表的所有数据到一个分区表指定的空的分区。

ALTER TABLE Source SWITCH TO Target PARTITION 1

交换前：

交换后：

如下sql

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);

-- Create the Non-Partitioned Source Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2013-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;
 
-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2013-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2013-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Verify row count before switch
SELECT COUNT(*) FROM SalesSource; -- 366000 rows
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget PARTITION 1; 
-- NOPE! We get an error:
-- Msg 4982, ALTER TABLE SWITCH statement failed. Check constraints of source table 'SalesSource' 
-- allow values that are not allowed by range defined by partition 1 on target table 'Sales'.

-- Add constraints to the source table to ensure it only contains data with values 
-- that are allowed in partition 1 on the target table
ALTER TABLE SalesSource
WITH CHECK ADD CONSTRAINT ckMinSalesDate 
CHECK (SalesDate IS NOT NULL AND SalesDate >= '2012-01-01');

ALTER TABLE SalesSource
WITH CHECK ADD CONSTRAINT ckMaxSalesDate 
CHECK (SalesDate IS NOT NULL AND SalesDate < '2013-01-01');

-- Try again. Is it really that fast...?
ALTER TABLE SalesSource SWITCH TO SalesTarget PARTITION 1; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT COUNT(*) FROM SalesSource; -- 0 rows
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4

3.分区表交换到一个无分区表

第三种方式，使用ALTER TABLE SWITCH语法，把一个分区表的指定分区的数据交换到一个空的无分区表。

ALTER TABLE Source SWITCH PARTITION 1 TO Target

交换前：

交换后：

如下sql:

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);
 
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Create the Non-Partitioned Target Table (Heap) on the [PRIMARY] filegroup
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON [PRIMARY];

-- Verify row count before switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('Sales')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4
SELECT COUNT(*) FROM SalesTarget; -- 0 rows

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4
SELECT COUNT(*) FROM SalesTarget; -- 366000 rows

4.分区表交换到分区表

第四种方式，使用 ALTER TABLE SWITCH　语法，把一个分区表指定分区的数据交换到另一个分区表的空的指定分区中。

ALTER TABLE Source SWITCH PARTITION 1 TO Target PARTITION 1

交换前：

交换后：

如下sql:

-- Drop objects if they already exist
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesSource')
  DROP TABLE SalesSource;
IF EXISTS (SELECT * FROM sys.tables WHERE name = N'SalesTarget')
  DROP TABLE SalesTarget;
IF EXISTS (SELECT * FROM sys.partition_schemes WHERE name = N'psSales')
  DROP PARTITION SCHEME psSales;
IF EXISTS (SELECT * FROM sys.partition_functions WHERE name = N'pfSales')
  DROP PARTITION FUNCTION pfSales;
 
-- Create the Partition Function 
CREATE PARTITION FUNCTION pfSales (DATE)
AS RANGE RIGHT FOR VALUES 
('2013-01-01', '2014-01-01', '2015-01-01');
 
-- Create the Partition Scheme
CREATE PARTITION SCHEME psSales
AS PARTITION pfSales 
ALL TO ([Primary]);
 
-- Create the Partitioned Source Table (Heap) on the Partition Scheme
CREATE TABLE SalesSource (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesSource(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2012-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2012-01-01','2013-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Create the Partitioned Target Table (Heap) on the Partition Scheme
CREATE TABLE SalesTarget (
  SalesDate DATE,
  Quantity INT
) ON psSales(SalesDate);
 
-- Insert test data
INSERT INTO SalesTarget(SalesDate, Quantity)
SELECT DATEADD(DAY,dates.n-1,'2013-01-01') AS SalesDate, qty.n AS Quantity
FROM GetNums(DATEDIFF(DD,'2013-01-01','2016-01-01')) dates
CROSS JOIN GetNums(1000) AS qty;

-- Verify row count before switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 0 rows in Partitions 2-4
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 0 rows in Partition 1, 365000 rows in Partitions 2-4

-- Turn on statistics
SET STATISTICS TIME ON;

-- Is it really that fast...?
ALTER TABLE SalesSource SWITCH PARTITION 1 TO SalesTarget PARTITION 1; 
-- YEP! SUPER FAST!

-- Turn off statistics
SET STATISTICS TIME OFF;

-- Verify row count after switch
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesSource')
ORDER BY PartitionNumber; -- 0 rows in Partition 1-4
SELECT 
    pstats.partition_number AS PartitionNumber
    ,pstats.row_count AS PartitionRowCount
FROM sys.dm_db_partition_stats AS pstats
WHERE pstats.object_id = OBJECT_ID('SalesTarget')
ORDER BY PartitionNumber; -- 366000 rows in Partition 1, 365000 rows in Partitions 2-4

错误信息(Error messages)

sql server 会提供详细的信息，当条件不满足时。你可以通过运行下面的查询，查看与 ALTER TABLE SWITCH有关的信息。

SELECT message_id, text 
FROM sys.messages 
WHERE language_id = 1033
AND text LIKE '%ALTER TABLE SWITCH%';

参考网址

本文参与腾讯云自媒体分享计划，分享自作者个人站点/博客。

原始发表：2020-04-06 ，如有侵权请联系 cloudcommunity@tencent.com 删除

sql

本文分享自作者个人站点/博客前往查看

如有侵权，请联系 cloudcommunity@tencent.com 删除。

本文参与腾讯云自媒体分享计划，欢迎热爱写作的你一起参与！

sql

登录后参与评论

0 条评论

热度