SQL Database V12 Preview では新しい機能としてテーブルのパーティショニングがサポートされました。
この機能を少し試してみたいと思います。
テーブルのパーティショニング自体は、SQL Server 2005 の Enterprise Edition からサポートされており、V12 になって SQL Database でも利用できるようになりました。
テーブルのパーティショニングをする目的としては、
- 複数のファイルグループを使用することでパーティション単位にディスク I/O を分散させる
- 特定のパーティションのデータをアーカイブテーブルにスイッチ
- アーカイブテーブルへのスイッチ→ アーカイブテーブルTruncate を実施することで、特定パーティションのデータを瞬時に削除
(DROP PARTITION がないため、一度他のテーブルに移し、Truncate Table する必要があります。) - パーティション単位でインデックスのメンテナンス
というようなことが考えられるかと思います。
上記の 3 つの理由で考えた場合、SQL Database のパーティショニングでは [2] ~ [4] に対応することができます。
V12 の新機能として、DMV の拡充があります。
DMV と合わせて、システムビューも拡充されており、様々な情報が取得できるようになりました。
# Preview なので GA した際には動作が変わる可能性があります。
以下のクエリを実行してみます。
SELECT * FROM sys.filegroups SELECT * FROM sys.database_files
このクエリではファイルグループとデータベースのファイルの情報を取得することができます。
# 本投稿で実行しているクエリは V12 Preview でのみ動作可能なものが大半です。現行のバージョンでは動作しません。
SQL Database では、 PRIMARY のファイルグループを使用しており、データファイルとログファイルの 2 ファイルでデータベースが構成されていることが確認できます。
SQL Database では PRIMARY ファイルグループのみがサポートされているため、ファイルグループを追加して、パーティションを分散させるということができません。
それでは、実際にパーティションテーブルを作成してみたいと思います。
V12 ではヒープテーブルが作成できるようになりましたので、今回はヒープで作成してイン佐生。
CREATE PARTITION FUNCTION PF_PTTEST(datetime2(0)) AS RANGE RIGHT FOR VALUES ('2011-01-01', '2012-01-01', '2013-01-01', '2014-01-01', '2015-01-01', '2016-01-01') GO CREATE PARTITION SCHEME PS_PTTEST AS PARTITION PF_PTTEST ALL TO([PRIMARY]) GO CREATE TABLE PTTEST (Col1 uniqueidentifier, Col2 datetime2(0)) ON PS_PTTEST(Col2)
今回は日付を使用したパーティションテーブルを作成しています。
SQL Database では、PRIMARY ファイルグループのみが使用できますので、すべてのパーティションのデータは PRIMARY ファイルグループに格納をするように定義しています。
# 物理的なデータの配置ディスクを分割するのではなく、単一のファイルグループを使用した論理的な分割を行います。
このテーブルに対して以下のクエリでデータを挿入します。
SET NOCOUNT ON GO DECLARE @cnt int = 0, @datetime datetime2(0) = '2010-01-01' BEGIN TRAN WHILE (@cnt <= 100000) BEGIN INSERT INTO PTTEST VALUES(NEWID(), @datetime) SET @cnt += 1 SET @datetime = DATEADD(d, 1, @datetime) IF @datetime >= '2017-01-01' SET @datetime = '2010-01-01' END COMMIT TRAN
どのようなデータが入っているかを確認してみます。
SELECT MIN(Col2),MAX(Col2) FROM PTTEST SELECT $PARTITION.PF_PTTEST(Col2) AS PartitionNo, COUNT(*), MIN(Col2), MAX(Col2) FROM PTTEST GROUP BY $PARTITION.PF_PTTEST(Col2)
2010-01-01 から 2016-12-31 までのデータがパーティションに分散されて格納されているのが確認できます。
パーティションの構成も確認をしてみたいと思います。
SELECT * FROM sys.partition_range_values prv INNER JOIN sys.partition_functions pf ON prv.function_id = pf.function_id AND pf.name = 'PF_PTTEST'
設定した構成でパーティションが作成されているのが確認できますね。
それでは、次にパーティションのスイッチをしてみたいと思います。
CREATE TABLE PTTEST_ARCHIVE (Col1 uniqueidentifier, Col2 datetime2(0)) ALTER TABLE PTTEST SWITCH PARTITION 2 TO PTTEST_ARCHIVE
2 番目のパーティションを移動しましたので、2011 年のデータが移動されているはずですね。
先ほどの確認用のクエリを実行して確認をしてみます。
2011 年のデータが移動されていますね。
アーカイブ用のテーブルを確認すると、2011 年のデータが存在していることが確認できます。
SELECT MIN(Col2),MAX(Col2) FROM PTTEST_ARCHIVE
今回は移動先は非パーティションテーブルですが、アーカイブされたデータを戻すこともできます。
ここまでの状態で以下のスイッチを実行してみます。
ALTER TABLE PTTEST_ARCHIVE SWITCH TO PTTEST PARTITION 2
この状態では以下のエラーとなります。
メッセージ 4982、レベル 16、状態 1、行 51 ALTER TABLE SWITCH statement failed. Check constraints of source table ‘v12TEST.dbo.PTTEST_ARCHIVE’ allow values that are not allowed by range defined by partition 2 on target table ‘v12TEST.dbo.PTTEST’. ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル ‘xxxxxxx’ のチェック制約では、切り替え先テーブル ‘xxxxxxx’ のパーティション 2 で定義された範囲では許可されない値が許可されます。 |
アーカイブテーブルにはスイッチ先のパーティションに格納しようとしているデータ以外 (2011 年以外のデータ) が入っていることが保障されていません。
パーティションのスイッチで戻す場合には、スイッチ元のテーブルがスイッチ先のパーティションの範囲のデータであることを保証する必要があります。
そこで、以下のチェック制約を付与します。
ALTER TABLE PTTEST_ARCHIVE ADD CONSTRAINT [CHK_PTTEST_ARCHIVE_01] CHECK (Col2 BETWEEN '2011-01-01' AND '2011-12-31' AND Col2 IS NOT NULL) GO
これにより、アーカイブからデータを戻すことが可能となります。
注意するポイントとしては NOT NULL を保証するというところです。
NOT NULL を保証していない場合、以下のエラーとなってしまいます。
メッセージ 4972、レベル 16、状態 1、行 52 ALTER TABLE SWITCH statement failed. Check constraints or partition function of source table ‘v12TEST.dbo.PTTEST_ARCHIVE’ allows values that are not allowed by check constraints or partition function on target table ‘v12TEST.dbo.PTTEST’. ALTER TABLE SWITCH ステートメントが失敗しました。切り替え元テーブル ‘xxxxxx’ のチェック制約またはパーティション関数で許可される値が、切り替え先テーブル ‘xxxxxxxx’ のチェック制約またはパーティション関数では許可されません。 |
操作方法としては、通常のパーティションテーブルを触るのと同じ方法で使用することができそうですね。