sql - 合并通过“UNION ALL”分区在一起的 2 个表

Question

我想删除当前数据库中的一个通用模式。我有 3 个对象，一个就足够了：current_table, history_table, combined_view.

current_table并且history_table具有完全相同的列并包含按时间戳拆分的数据，即history_table包含截至 2010 年 1 月 1 日的数据，并current_table包括自 2010 年 1 月 1 日以来的数据等。

组合视图是（穷人的分区）

select * from history_table
UNION ALL
select * from current_table

我希望有一个与视图同名的表，然后使用history_table和视图。我的算法是：

1. 放弃对截止时间的限制。
2. 将数据从 history_table 移动到 current_table
3. 将history_table 重命名为history_table_DEPR，将视图重命名为combined_view_DEPR，将current_table 重命名为combined_view

我目前通过以下SQL实现上述（2）：

INSERT INTO current_table
SELECT * FROM history_table

我想（2）是大部分时间花费的地方。我担心上面的插入会尝试为插入的每一行写一个日志，并且会比它可能的慢。在这种情况下，移动数据的最佳方式是什么？我不在乎记录这些动作。

标签： sqlsql-servertsqlsql-server-2014

Answer 1

我根本不会移动数据，特别是如果你要重复这个练习。使用一些分区技巧来打乱元数据。

1) 根据您的分离日期创建一个包含两个分区的中间临时表。2）创建最终的目标表，以您的视图命名，没有分区。3) 将现有表中的数据切换到分区表中。4) 将两个分区合并为一个分区。5）将剩余的分区切换到新的目标表中。6) 放下所有工作物体。7) 根据需要重复。

-- Step 0.
-- Standard issue pre-cleaning.
IF OBJECT_ID('dbo.OldData','U') IS NOT NULL
  DROP TABLE dbo.OldData;

IF OBJECT_ID('dbo.NewData','U') IS NOT NULL
  DROP TABLE dbo.NewData;

IF OBJECT_ID('dbo.CleanUp','U') IS NOT NULL
  DROP TABLE dbo.CleanUp;

IF OBJECT_ID('dbo.AllData','U') IS NOT NULL
  DROP TABLE dbo.AllData;

IF EXISTS (SELECT * FROM sys.partition_schemes
    WHERE name = 'psCleanUp')
  DROP PARTITION SCHEME psCleanUp;

IF EXISTS (SELECT * FROM sys.partition_functions  
    WHERE name = 'pfCleanUp') 
  DROP PARTITION FUNCTION pfCleanUp;

-- Mock up your existing situation. Two data tables.

CREATE TABLE dbo.OldData
(
  [Dates] DATE NOT NULL
  ,[OtherStuff] VARCHAR(1) NULL
);

CREATE TABLE dbo.NewData
(
  [Dates] DATE NOT NULL
  ,[OtherStuff] VARCHAR(1) NULL
);

INSERT INTO dbo.OldData
  (
    Dates
   ,OtherStuff
  )
VALUES
  (
    '20090101' -- Dates - date
   ,''        -- OtherStuff - varchar(1)
  );

INSERT INTO dbo.NewData
  (
    Dates
   ,OtherStuff
  )
VALUES
  (
    '20110101' -- Dates - date
   ,''        -- OtherStuff - varchar(1)
  )


-- Step .5
-- Here's where the solution starts.
-- Add check contraints to your existing tables.
-- The partition switch will require this to be sure
-- the incoming data works with the partition scheme.

ALTER TABLE dbo.OldData
ADD CONSTRAINT ckOld CHECK (Dates < '2010-01-01');

ALTER TABLE dbo.NewData
ADD CONSTRAINT ckNew CHECK (Dates >= '2010-01-01');

-- Step 1.
-- Create your partitioning artifacts and 
-- intermediate table. 

CREATE PARTITION FUNCTION pfCleanUp (DATE)
AS RANGE RIGHT FOR VALUES ('2010-01-01');

CREATE PARTITION SCHEME psCleanUp
AS PARTITION pfCleanUp
ALL TO ([PRIMARY]);

CREATE TABLE dbo.CleanUp
(
  [Dates] DATE NOT NULL
  ,[OtherStuff] VARCHAR(1) NULL
) ON psCleanUp(Dates);

-- Step 2.
-- Create your new target table.

CREATE TABLE dbo.AllData
(
  [Dates] DATE NOT NULL
  ,[OtherStuff] VARCHAR(1) NULL
);

-- Step 3.
-- Start flopping metadata around.

ALTER TABLE dbo.OldData 
SWITCH TO dbo.CleanUp PARTITION 1;

ALTER TABLE dbo.NewData
SWITCH TO dbo.CleanUp PARTITION 2;

-- Step 4.
-- Your old tables should be empty now.
-- Put all of the data into one partition.

ALTER PARTITION FUNCTION pfCleanUp()
MERGE RANGE ('2010-01-01');

-- Step 5.
-- Switch that partition out to your
-- spanky new table.

ALTER TABLE dbo.CleanUp 
SWITCH PARTITION 1 TO dbo.AllData;

-- Verify the data's where it belongs.

SELECT *
FROM dbo.AllData;

-- Verify the data's not where it shouldn't be.

SELECT * FROM dbo.OldData;
SELECT * FROM dbo.NewData;
SELECT * FROM dbo.CleanUp ;

-- Step 6.
-- Clean up after yourself.

DROP TABLE dbo.OldData;
DROP TABLE dbo.NewData;
DROP TABLE dbo.CleanUp;
DROP PARTITION SCHEME psCleanUp;
DROP PARTITION FUNCTION pfCleanUp;
-- This one's just here for me.
DROP TABLE dbo.AllData;