SE の雑記

SQL Server の情報をメインに Microsoft 製品の勉強内容を日々投稿

非パーティションテーブルからパーティションテーブルへの切り替え

leave a comment

SQL Server 2005 移行の Enterprise Edition ではパーティションテーブルの利用が可能となりました。
パーティ初認ぐされたテーブルを使用することで大規模テーブルの大量データをアーカイブ、インデックスの一部メンテナンス、データのレンジによって使用するディスクを分散させる (パーティションをファイルグループで分けた場合) というようなことが可能となります。

SQL Server では SQL Server の評価版から製品版への移行 に記載しているような形でエディションを変更することができます。
エディション変更が可能なマトリックスは サポートされているバージョンとエディションのアップグレード に記載されており、Standard から Enterprise への変更も可能です。

image

 

Enterprise Edition に移行したタイミングでパーティショニングされたテーブルを使用したいということがあるかもしれません。
そこで、非パーティションテーブルをパーティションテーブルへ切り替える方法を少しまとめてみたいと思います。

今回は以下のテーブルを使用しています。

USE [PartitionTEST]
GO
CREATE TABLE [dbo].[BaseTable](
	[Col1] [bigint] NOT NULL,
	[Col2] [datetime] NOT NULL,
	[Col3] [nvarchar](100) NULL,
	[Col4] [nvarchar](100) NULL,
	[Col5] [bigint] NULL,
	[Col6] [nchar](100) NULL,
	[Col7] [int] NULL,
 CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED 
([Col1] ASC)
) ON [PRIMARY]

 

日付でパーティショニングをするために [datetime] 型のフィールドを用意しています。

ハッシュパーティショニングの検証をできるように最後に [int] 型のフィールドも設定しています。

このテーブルに対して、以下のクエリでデータを挿入します。

SET NOCOUNT ON
Truncate Table BaseTable
GO
DECLARE @i bigint = 1
DECLARE @hash smallint
DECLARE @date datetime = '2001/1/1'
DECLARE @dateadd bigint = 1

WHILE (@i <= 10000000)
BEGIN
	SET @hash = abs(convert(bigint,convert(varbinary, NEWID()))) % 10 + 1
	INSERT INTO BaseTable VALUES(@i, DATEADD(day, @dateadd, @date), NEWID(), NEWID(), ABS(RAND() * 10000), NEWID(),@hash)
	SET @i += 1
	IF @i % 1000 = 0
	BEGIN
		SET @dateadd += 1
	END
END

 

最後のフィールドには 1 ~ 10 の数値をランダムで設定し、データを分散できるようにしています。

# パーティションをこの列で設定し、ディスクをファイルグループで複数に分散させた場合には、適度にデータが異なるディスクに配置されます。

このテーブル用のパーティション関数を作成します。

USE [PartitionTEST]
GO
-- パーティション関数の作成
CREATE PARTITION FUNCTION 
[BaseTable_PF]
(datetime)
AS RANGE RIGHT FOR VALUES (
'2002/1/1','2003/1/1','2004/1/1','2005/1/1','2006/1/1','2007/1/1','2008/1/1','2009/1/1',
'2010/1/1','2011/1/1','2012/1/1','2013/1/1','2014/1/1','2015/1/1','2016/1/1','2017/1/1','2018/1/1','2019/1/1',
'2020/1/1','2021/1/1','2022/1/1','2023/1/1','2024/1/1','2025/1/1','2026/1/1','2027/1/1','2028/1/1','2029/1/1',
'2030/1/1')

 

次にパーティションスキーマを作成します。

今回は [PRIMARY] ファイルグループのみを使用しています。

# 本来であればパーティション用のファイルグループを用意するのが好ましいですが、今回は単純な構成で検証をするため。

-- パーティションスキーマの作成
CREATE PARTITION SCHEME
[BaseTable_PS]
AS PARTITION [BaseTable_PF]
ALL TO ([PRIMARY])
GO

 

これで、パーティションテーブルを使用する下準備は整いました。

まずは現在のテーブルの状態を確認してみます。

SELECT
	OBJECT_NAME(sp.object_id)
	, si.name
	, sp.partition_number
	, sp.rows
	, sp.data_compression_desc
	, si.type_desc
FROM
	sys.partitions sp
	LEFT JOIN
		sys.indexes as si
		ON
		si.object_id = sp.object_id
		AND
		si.index_id = sp.index_id
WHERE
	sp.object_id IN (OBJECT_ID(N'dbo.BaseTable'))
GO

image

現在はパーティションが単一のテーブル (非パーティションテーブル) としてテーブルが作成されています。

この状態から先ほど作成したパーティションスキーマをテーブルに割り当てるために以下のクエリを実行します。

ALTER TABLE [dbo].[BaseTable] DROP CONSTRAINT [PK_BaseTable]
GO

ALTER TABLE [dbo].[BaseTable] ADD  CONSTRAINT [PK_BaseTable] PRIMARY KEY CLUSTERED 
(
	[Col1] ASC,
	[Col2] ASC
)ON [BaseTable_PS](Col2)
GO

 

一度、クラスター化されたプライマリキーの制約を削除し、再度パーティションスキーマを使用して再作成を実施しています。

これによりクラスター化インデックスがパーティションスキーマを使用して構成されますのでデータがパーティショニングされて再構築されます。

今回は主キー以外の項目でパーティショニングをしていますので、プライマリキー制約にはパーティショニングで使用する項目を追加する必要があります。

実行後にパーティションの状態を確認すると以下のようにパーティショニングされたテーブルに変わっていることが確認できます。

image

今回は PRIMARY ファイルグループのみを使用していますので、アーカイブ用のテーブルを作成し、

CREATE TABLE [dbo].[BaseTable_Archive](
	[Col1] [bigint] NOT NULL,
	[Col2] [datetime] NOT NULL,
	[Col3] [nvarchar](100) NULL,
	[Col4] [nvarchar](100) NULL,
	[Col5] [bigint] NULL,
	[Col6] [nchar](100) NULL,
	[Col7] [int] NULL,
 CONSTRAINT [PK_BaseTable_Archive] PRIMARY KEY CLUSTERED 
([Col1] ASC)
) ON [PRIMARY]
GO

パーティションをスイッチします。

ALTER TABLE BaseTable
SWITCH PARTITION 1 TO BaseTable_Archive

 

これで特定のパーティションのデータをパージすることができました。

image

スイッチしたパーティションを戻す場合、

ALTER TABLE BaseTable_Archive
SWITCH PARTITION 1 TO BaseTable PARTITION 1

 

で戻すことができるのですが、先ほどのテーブルで戻そうとするとエラーになります。

警告: テーブル ‘PartitionTEST.dbo.BaseTable_Archive’ の指定されたパーティション 1 が、ALTER TABLE SWITCH ステートメントで無視されました。テーブルがパーティション分割されていません。

メッセージ 4982、レベル 16、状態 1、行 1

ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル ‘PartitionTEST.dbo.BaseTable_Archive’ のチェック制約では、切り替え先テーブル ‘PartitionTEST.dbo.BaseTable’ のパーティション 1 で定義された範囲では許可されない値が許可されます。

ALTER TABLE BaseTable
SWITCH PARTITION 1 TO BaseTable_Archive

アーカイブのテーブルには、チェック制約がついておらず、スイッチ後のベーステーブルで許可されない値が格納されている可能性があります。

それを保証するためにアーカイブのテーブルにチェック制約を付与します。

ALTER TABLE [dbo].[BaseTable_Archive] ADD CONSTRAINT
[BaseTable_Archive_CHK_RANGE] CHECK (([Col2]<'2002/1/1'))

 

今回はスイッチ先がパーティションテーブルではないため、以下のような書き方でもスイッチ可能です。

ALTER TABLE BaseTable_Archive
SWITCH TO BaseTable PARTITION 1

今回のサンプルは必ずからのテーブルに対してスイッチする (年ごとにアーカイブテーブルを用意する) ことを想定していますので、複数のパーティションをアーカイブしようとすると以下のようなエラーになります。

メッセージ 4905、レベル 16、状態 1、行 1

ALTER TABLE SWITCH ステートメントが失敗しました。切り替え先テーブル ‘PartitionTEST.dbo.BaseTable_Archive’ を空にしてください。

大規模データを効率よく処理する場合、パーティションの利用は考慮しなくてはいけないと思いますので、この辺のデザインパターンは自分でも整理しておかないとなと思いました。

Written by masayuki.ozawa

3月 10th, 2014 at 9:08 am

Posted in SQL Server

Tagged with

Leave a Reply

*