SE の雑記

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

SQL Server でなんちゃってハッシュパーティショニング

leave a comment

SQL Server のパーティショニングですが、レンジパーティショニング (範囲分割) のみが実装されており、リストパーティショニングやハッシュパーティショニングは実装されていません。

ということでレンジパーティショニングでなんちゃってハッシュパーティショニングを実装するためのサンプルを。
パーティショキーでの検索がいまいちな使い勝手になるので、実際に利用する機会があるかは微妙ですが…。

パーティションの基本的な設定としては以下のようなクエリで作成しています。

IF OBJECT_ID('HashTable') IS NOT NULL
	DROP TABLE HashTable

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

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


CREATE PARTITION FUNCTION [HashTable_PF](int) AS RANGE RIGHT FOR VALUES (1,2,3)
GO

CREATE PARTITION SCHEME [HashTable_PS] AS PARTITION [HashTable_PF] ALL TO ([PRIMARY])
GO

 

このパーティションスキーマとパーティション関数を使用して以下のようなテーブルを作成します。

CREATE TABLE [dbo].[HashTable](
	[Col1] int IDENTITY NOT NULL,
	[Col2] nvarchar (36),
	[PartitionHash] AS ABS(CONVERT(int,HASHBYTES('MD2',CONVERT(nvarchar(10), Col2)))) % 4 PERSISTED NOT NULL
)

CREATE CLUSTERED INDEX CIX_HashTable ON HashTable (Col1) ON HashTable_PS(PartitionHash)

ALTER TABLE HashTable WITH CHECK ADD CONSTRAINT PK_HashTable PRIMARY KEY NONCLUSTERED (Col1) ON [PRIMARY]
GO

パーティション用の項目として「PartitionHash」を確定された計算列として設定し、パーティションテーブルの一意制約として、「Col1」の非クラスター化インデックスをパーティションスキーマを使用しない形 (インデックスがテーブルに固定されていない形式) で作成しています。

# パーティション インデックスの専用ガイドライン の一意の非クラスター化インデックスをパーティション分割列を含めない形での設定となります。

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

SET NOCOUNT ON

DECLARE @cnt int = 1
BEGIN TRAN
WHILE (@cnt <= 5000)
BEGIN
	INSERT INTO HashTable(Col2) VALUES(NEWID())
	SET @cnt += 1
END
COMMIT TRAN

 

データの挿入が終わったら以下のクエリでデータの格納状態を確認してみます。

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.HashTable'))


それなりにばらけて格納されていますね。

一意制約として使っている非クラスター化インデックスはパーティショニングしていませんので Partition_Number = 1 のみとなっています。

image

使い勝手の面はお察し下さい。

Written by masayuki.ozawa

7月 27th, 2015 at 8:45 pm

Posted in SQL Server

Tagged with

Leave a Reply

*