SQL Server
テーブル パーティションを使用してデータベースのメンテナンスを簡素化する
Noah Gomez
概要:
- パーティション テーブルを作成する
- パーティションを追加およびマージする
- パーティション インデックスを作成および管理する
この記事で使用しているコードのダウンロード: GomezPartitioning2007_03.exe (156KB)
以前は、データベース管理者は、数百万行のデータ量があるテーブルを管理する場合には複数のテーブルを作成せざるを得ませんでした。テーブルをパーティションに分割すると、管理者はそれらのテーブルの多くのクエリの実行中に
テーブルを結合して戻す必要があります。パーティションを結合するには、データが存在する場所を特定するパーティション ビューまたはラッパー ストアド プロシージャを作成し、データセットの取得に必要なパーティションのみを検索する別のストアド プロシージャを実行する必要があります。
この方法で機能は果たしますが、手間がかかります。複数のテーブルやインデックスの管理、およびテーブルの関連付けに使用する方法は、しばしば管理およびメンテナンス上の問題を引き起こします。また、複数のテーブルを作成してデータを分割すると、ストアド プロシージャ、メンテナンス ジョブ、データ変換サービス (DTS) ジョブ、アプリケーション コードなどのプロセスにパーティションの性質を認識させる必要があるため、柔軟性が低下します。そこで、コードを変更しなくてもこれらの擬似パーティションを追加または削除できるようにするために、通常、これらの要素は非動的な方法で作成され、結果として非効率的になります。
SQL Server™ 2005 Enterprise Edition および Developer Edition では、1 つのテーブルに格納されている膨大なデータを複数の小さいパーティションに分割して効率的に管理およびメンテナンスすることができます。単一のエントリポイントでデータ セグメントを作成できるため、従来の多くの管理上の問題が軽減されます。単一のエントリ ポイント (テーブル名またはインデックス名) を使用することで、複数のデータ セグメントがアプリケーション コードから隠蔽されるため、管理者や開発者はコード ベースを調整しなくても、必要に応じてパーティションを変更できます。
つまり、アプリケーションのコードを変更せずに、複数のパーティションの作成、移動、古いパーティションの削除、データのパーティション方法の変更を行うことができるということです。アプリケーション コードでは、同じベース テーブルまたはインデックス名を呼び出すだけです。また、個々のインデックスに格納されるデータ量が減り、これらのインデックスのメンテナンスにかかる時間も短縮されます。データを空のパーティションに読み込めば、読み込み速度も向上します。
技術的には、SQL Server 2005 では、すべてのテーブルがパーティションに分割され、すべてのテーブルに 1 つ以上のパーティションが存在します。SQL Server 2005 では、データベース管理者は個々のテーブルにパーティションを追加できます。テーブルおよびインデックスのパーティションは固定定義された低レベルのパーティションであり、列単位のパーティションは作成できませんが、単一エントリ ポイント (テーブル名またはインデックス名) を使用できるため、エントリ ポイントの背後にあるパーティションの数をアプリケーション コードが意識する必要はありません。パーティションは、ベース テーブル上およびベース テーブルに関連付けられたインデックス上に作成できます。
パーティション テーブルを作成する
既定の単一パーティションを超えて増加する機能を持つテーブルを作成するには、パーティション関数やパーティション スキームを使用します。これらのオブジェクトにより、ストレージ設計時にデータをセグメントに分割し、これらのセグメントの配置を制御することができます。たとえば、データの経過時間などの共通の識別情報に基づいて複数のドライブ配列にデータを分散させることができます。1 つの列に基づいてテーブルをパーティションに分割することができますが、各パーティションには、他のパーティションに配置できないデータを格納する必要があることに注意してください。
パーティション関数: テーブルをパーティションに分割する場合、まず、データをどのようなセグメントに分割するかを決めます。パーティション関数を使用して、データの各行を異なるパーティションにマップします。これらのデータの各行は、text、ntext、image、xml、timestamp、varchar(max)、nvarchar(max)、varbinary(max)、別名データ型、共通言語ランタイム (CLR) ユーザー定義データ型以外の任意のデータ型の列によってマップ可能です。ただし、パーティション関数はデータ行を 1 つのテーブル パーティションのみに配置できるようにする必要があります。つまり、1 つのデータ行が一度に複数のパーティションに所属できないようにします。
テーブルをパーティションに分割するには、対象テーブルにパーティション列を作成する必要があります。パーティション列は、作成済みのテーブルのテーブル スキーマに配置できます。または、テーブルを変更して、後から列を追加することもできます。列には NULL 値を使用できますが、NULL 値が格納されているすべての行は、既定でテーブルの一番左のパーティションに配置されます。これを避けるには、パーティション関数を作成するときに、NULL 値がテーブルの一番右のパーティションに配置されるように指定します。左のパーティションを使用するか、右のパーティションを使用するかは、パーティション スキーマを変更してパーティションを追加する場合や、既存のパーティションを削除する場合のための設計上の重要な選択になります。
パーティション関数を作成する際、LEFT または RIGHT のいずれかのパーティション関数を選択できます。LEFT パーティションと RIGHT パーティションの違いは、パーティション スキーム内での境界値の配置です。LEFT パーティション (既定値) には、パーティションの境界値が含まれますが、RIGHT パーティションでは、境界値は次のパーティションに配置されます。
この概念をもう少しよく理解するために、次の単純な LEFT および RIGHT パーティションを見てみましょう。
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
CREATE PARTITION FUNCTION Right_Partition (int) AS RANGE RIGHT
FOR VALUES (1,10,100)
最初の関数 (Left_Partition) では、値 1、10、および 100 がそれぞれ 1 番目、2 番目、および 3 番目のパーティションに配置されます。2 番目の関数 (Right_Partition) では、値はそれぞれ 2 番目、3 番目、および 4 番目のパーティションに配置されます。
パーティション テーブルを作成する際は、パーティションをなるべく均等に取得することが重要です。それにより、パーティションに必要な領域を把握しやすくなります。LEFT および RIGHT を使用すると、データの配置が決まり、これによりパーティションのサイズと、そのパーティションに作成したインデックスのサイズも決まります。
データの値を配置するパーティション番号を指定するには、次のように $PARTITION 関数を使用します。
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Right_Partition (10)
最初の SELECT ステートメントの結果は 2、2 番目の SELECT ステートメントの結果は 3 です。
パーティション スキーム: 関数を作成してデータの分割方法を決めたら、個々のパーティションをディスク サブシステム上のどこに作成するかを決める必要があります。パーティション スキームを使用してディスク レイアウトを作成します。パーティション スキームでは、ファイル グループを利用して各パーティションをディスク サブシステムに配置することによって、個々のパーティションのディスク ストレージを管理します。パーティション スキームを構成して、すべてのパーティションを 1 つのファイル グループに配置するか、すべてのパーティションを異なるファイル グループに配置するか、または複数のパーティションでファイル グループを共有することができます。後者の方法を選択すると、データベース管理者はディスク I/O をきわめて柔軟に分散できます。
図 1 は、1 つまたは複数のファイル グループをパーティション スキームに割り当てる方法を示しています。パーティション スキームで使用するファイル グループは、パーティション スキームを作成する前にデータベースに存在している必要があります。
Figure 1 ファイル グループをパーティション スキームに割り当てる
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the different filegroups
CREATE PARTITION SCHEME Different_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup3, Filegroup4)
--Filegroups must currently exist in database
--Place multiple partitions into the different filegroups
CREATE PARTITION SCHEME Multiple_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
TO (Filegroup1, Filegroup2, Filegroup1, Filegroup2)
--Filegroups must currently exist in database
図 1 のようなサンプル パーティション関数を作成し、パーティション スキームを使用してテーブルを作成する場合、個々のデータ行を新しくパーティション分割したテーブルのどこに配置するかを指定できます。これらのデータ行をパーティション テーブルに挿入した後で、データ行の分布を表示できます。この操作を実行するコードは図 2 のようになります。
Figure 2 データ行を配置して分布を表示する
--Prepare database
IF OBJECT_ID('Partitioned_Table') IS NOT NULL
DROP TABLE Partitioned_Table
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
ALL TO ([PRIMARY])
CREATE TABLE Partitioned_Table
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Determine where values will be placed (this is not required)
--You should try to do this before executing the code
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (2)
SELECT $PARTITION.Left_Partition (3)
SELECT $PARTITION.Left_Partition (4)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (11)
SELECT $PARTITION.Left_Partition (12)
SELECT $PARTITION.Left_Partition (13)
SELECT $PARTITION.Left_Partition (14)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
SELECT $PARTITION.Left_Partition (102)
SELECT $PARTITION.Left_Partition (103)
SELECT $PARTITION.Left_Partition (104)
--Insert data into partitioned table
INSERT INTO Partitioned_Table VALUES (1,'Description')
INSERT INTO Partitioned_Table VALUES (2,'Description')
INSERT INTO Partitioned_Table VALUES (3,'Description')
INSERT INTO Partitioned_Table VALUES (4,'Description')
INSERT INTO Partitioned_Table VALUES (10,'Description')
INSERT INTO Partitioned_Table VALUES (11,'Description')
INSERT INTO Partitioned_Table VALUES (12,'Description')
INSERT INTO Partitioned_Table VALUES (13,'Description')
INSERT INTO Partitioned_Table VALUES (14,'Description')
INSERT INTO Partitioned_Table VALUES (100,'Description')
INSERT INTO Partitioned_Table VALUES (101,'Description')
INSERT INTO Partitioned_Table VALUES (102,'Description')
INSERT INTO Partitioned_Table VALUES (103,'Description')
INSERT INTO Partitioned_Table VALUES (104,'Description')
--View the distribution of data in the partitions
SELECT ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('Partitioned_Table')
パーティション テーブルを変更する
事前に入念な計画を立てても、パーティション テーブルを作成および設定した後で調整が必要になる場合があります。たとえば、パーティション スキームは予定どおりに機能しても、新しいデータが蓄積してきたために新しいパーティションを追加する必要が出てきたり、一度に大量のパーティション データを削除する必要が生じることがあります。パーティション テーブルおよび基になるパーティションの構造は、テーブルを作成してデータが格納された後でも変更できるようになっています。
パーティションの追加: 多くのパーティション計画では、後から新しいパーティションを追加できる機能を採り入れます。追加のタイミングを特定の日付や増分する ID 列の値によって設定することができますが、事前に計画していなかった場合は、後日になってから新しいパーティションをパーティション テーブルに追加することが可能です。図 2 で作成したテーブルの場合を考えてみましょう。このテーブルに新しいパーティションを追加して 500 より大きい値を格納するには、次のようにします。
--Determine where values live before new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 4
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(500)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (501) --should return a value of 5
パーティションを追加する機能があれば、柔軟性が大幅に向上します。図 3 は、関数の左側にパーティションを追加する方法を示しています。この場合、最初にパーティション スキームを作成したときに作成したファイル グループは使い尽くしているため、新しいパーティションを配置するパーティション スキームを指定する必要があります。すべてのパーティションに PRIMARY ファイル グループを使用する場合でも、パーティション スキームを指定して、新しいパーティションの PRIMARY ファイル グループを再使用する必要があります。
Figure 3 関数の左側にパーティションを追加する
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 2
--Add new filegroups to the partitioning scheme
ALTER PARTITION SCHEME Primary_Left_Scheme
NEXT USED [PRIMARY]
--Create new partition
ALTER PARTITION FUNCTION Left_Partition ()
SPLIT RANGE(5)
--Determine where values live after new partition
SELECT $PARTITION.Left_Partition (5) --should return a value of 2
SELECT $PARTITION.Left_Partition (1) --should return a value of 1
SELECT $PARTITION.Left_Partition (10) --should return a value of 3
2 つのパーティションのマージ: SQL Server では、データを保持したまま、テーブルからパーティションを削除できます。この機能を利用して、古いアクティブ データをアーカイブ データにマージしたり、使用するパーティションの数を減らすことができるため、パーティション テーブルの管理が容易になります。また、この機能を使用してパーティションをマージし、ファイル グループ間でデータを移動して、特定のドライブ配列のディスク領域を解放することも可能です。図 4 のコードは、同一ファイル グループ上のパーティション間でデータを移動する方法を示しています。
Figure 4 パーティション間でデータを移動する
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
--Merge two partitions
ALTER PARTITION FUNCTION Left_Partition()
MERGE RANGE (10)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Check where data would be placed
SELECT $PARTITION.Left_Partition (1)
SELECT $PARTITION.Left_Partition (10)
SELECT $PARTITION.Left_Partition (100)
SELECT $PARTITION.Left_Partition (101)
パーティション テーブルへの単一パーティション テーブルの移動: 読み込みルーチンでは、多くの場合、データ テーブルに移動する前に大量のデータをデータベースに読み込んで、変更や集計を行う必要があります。SQL Server 2005 のパーティション機能では、単一のパーティション テーブルを複数のパーティションを持つテーブルに移動できます。これにより、データを単一の読み込みテーブルに読み込んでデータを変更し、テーブル全体を既存のテーブルに移動する際に、個々のデータ行を移動するためのオーバーヘッドが生じません。パーティションのこの層では、基になるパーティション構造の変更は行わず、パーティション テーブルの変更を行います。図 5 は、この機能の実装方法を示しています。
Figure 5 テーブル全体を既存のテーブルに移動する
--Prepare database
IF OBJECT_ID(‘multiple_partition’) IS NOT NULL
DROP TABLE multiple_partition
GO
IF OBJECT_ID(‘single_partition’) IS NOT NULL
DROP TABLE single_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = ‘Primary_Left_Scheme’)
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = ‘Left_Partition’)
DROP PARTITION FUNCTION Left_Partition
GO
--Create single partition table
CREATE TABLE single_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
--Table must have a CHECK Constraint
ALTER TABLE single_partition
WITH CHECK
ADD CONSTRAINT CK_single_partition
CHECK (col1 > 100)
INSERT INTO single_partition VALUES (101,’Description’)
INSERT INTO single_partition VALUES (102,’Description’)
INSERT INTO single_partition VALUES (103,’Description’)
INSERT INTO single_partition VALUES (104,’Description’)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
INSERT INTO multiple_partition VALUES (1,’Description’)
INSERT INTO multiple_partition VALUES (2,’Description’)
INSERT INTO multiple_partition VALUES (3,’Description’)
INSERT INTO multiple_partition VALUES (4,’Description’)
INSERT INTO multiple_partition VALUES (10,’Description’)
INSERT INTO multiple_partition VALUES (11,’Description’)
INSERT INTO multiple_partition VALUES (12,’Description’)
INSERT INTO multiple_partition VALUES (13,’Description’)
INSERT INTO multiple_partition VALUES (14,’Description’)
INSERT INTO multiple_partition VALUES (100,’Description’)
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_
ID(‘single_partition’))
--Move the single table into the partitioned table
ALTER TABLE single_partition SWITCH TO multiple_partition PARTITION 4
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID(‘multiple_partition’), OBJECT_ID(‘single_partition’))
テーブル間のパーティションの移動: 古いデータを別のアーカイブ テーブルに移動するのは一般的な管理作業です。アーカイブ プロセスでは、通常、一連のステートメントを使用して、リソースの使用状況をトランザクション ログに追加できるようにします。しかし、大量のデータをアーカイブするには、パーティションの所有権を別のテーブルに切り替えた方がトランザクション ログのオーバーヘッドが生じず、作業も単純です。この機能を使って、データベース管理者は、古いデータをアクティブなテーブルからアーカイブ テーブルに移動できます。実際にデータを移動するわけではないので、個々のデータ行を移動する場合よりはるかに短時間で処理されます。図 6 は、この機能の実装方法を示しています。
Figure 6 古いデータをアーカイブ テーブルに移動する
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Archive_Scheme')
DROP PARTITION SCHEME Archive_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Archive_Partition')
DROP PARTITION FUNCTION Archive_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Create archive function
CREATE PARTITION FUNCTION Archive_Partition (int) AS RANGE LEFT
FOR VALUES (100,200,300)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Archive_Scheme
AS PARTITION Archive_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Archive_Scheme (col1)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
INSERT INTO archive_data VALUES (200,'Description')
INSERT INTO archive_data VALUES (300,'Description')
INSERT INTO archive_data VALUES (400,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data PARTITION 1
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
単一のパーティションを使用して新しいテーブルを作成する: 単一のパーティションを既存のパーティション テーブルからパーティションが存在しない空のテーブルに移動することができます。この機能により、データベース管理者は、単一パーティション上でインデックスをメンテナンスしたり、削除プロセスをログに記録することなく、大量のデータを簡単に削除することができます。図 7 は、空のテーブルにパーティションを移動し、その新しいテーブルを使用してデータを削除する方法を示しています。
Figure 7 データを移動および削除する
--Prepare database
IF OBJECT_ID('active_data') IS NOT NULL
DROP TABLE active_data
GO
IF OBJECT_ID('archive_data') IS NOT NULL
DROP TABLE archive_data
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Active_Scheme')
DROP PARTITION SCHEME Active_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Active_Partition')
DROP PARTITION FUNCTION Active_Partition
GO
--Create active function
CREATE PARTITION FUNCTION Active_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Active_Scheme
AS PARTITION Active_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE active_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
) ON Active_Scheme (col1)
CREATE TABLE archive_data
(
col1 INT PRIMARY KEY CLUSTERED
,col2 VARCHAR(15)
)
INSERT INTO active_data VALUES (1,'Description')
INSERT INTO active_data VALUES (2,'Description')
INSERT INTO active_data VALUES (3,'Description')
INSERT INTO active_data VALUES (4,'Description')
INSERT INTO active_data VALUES (10,'Description')
INSERT INTO active_data VALUES (11,'Description')
INSERT INTO active_data VALUES (12,'Description')
INSERT INTO active_data VALUES (13,'Description')
INSERT INTO active_data VALUES (14,'Description')
INSERT INTO active_data VALUES (100,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Switch ownership of partition to another table
ALTER TABLE active_data SWITCH PARTITION 3 TO archive_data
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
--Drop all archive data without logging
DROP TABLE archive_data
GO
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] IN (OBJECT_ID('active_data'),OBJECT_ID('archive_
data'))
パーティション インデックス
テーブルのデータをパーティションに分割する機能が使用できれば、通常はパーティション インデックスの作成機能も使用できます。この機能を使用して、データベース管理者は、テーブル全体のデータではなく、分割されたデータに基づいてインデックス構造を設計することができます。パーティション インデックスを作成すると、パーティション インデックス上に個別の B ツリーが作成されます。インデックスを分割すると、作成するインデックスが小さくなり、データを変更、追加、削除する際のストレージ エンジンによるメンテナンスが容易になるという効果があります。小さいインデックスをデータベース管理者が個別に管理することができるため、膨大なデータセットのメンテナンスが容易になります。
パーティション インデックスの作成: パーティション インデックスを作成する際、固定インデックスまたは非固定インデックスを作成できます。固定インデックスの場合、パーティション データとの直接的な関係を使用してインデックスを作成します。非固定インデックスの場合は別のパーティション スキーマを選択します。
推奨されるのは固定インデックスです。パーティション テーブルを作成後、インデックスを作成するときに別のパーティション スキームを指定しなければ、自動的に固定インデックスになります。固定インデックスを使用すると、テーブルにパーティションを追加する際の柔軟性が向上し、パーティションの所有権を他のテーブルに切り替えることができます。これらの機能があれば、たいていの場合、パーティション テーブルを作成し、インデックスにテーブルのパーティション スキーマを使用するだけでパーティションの目標は果たされるでしょう。
インデックスのデータがテーブルのデータに固定されていないテーブルに対して、インデックスを作成することができます。データがパーティション テーブルに存在する場合、データを多様な方法で結合できます (クエリ オプティマイザを使用して、パーティション データを他のパーティション データと効率的に結合することが可能です)。非パーティション テーブルでも同様の機能を使用可能であり、パーティション インデックスを (単一のパーティション テーブルに対して) 作成すれば、インデックスのメンテナンスが容易になります。
図 8 のコードは、パーティションに分割された非クラスタ化インデックスをパーティション テーブルに作成します。非クラスタ化インデックスは、テーブルに固定され、テーブルのパーティション列を非クラスタ化インデックス キーとして使用します。
Figure 8 パーティション テーブルに対する、パーティションに分割された非クラスタ化インデックス
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 VARCHAR(15)
) ON Primary_Left_Scheme (col1)
--Create partitioned non-clustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col1)
INSERT INTO multiple_partition VALUES (1,'Description')
INSERT INTO multiple_partition VALUES (2,'Description')
INSERT INTO multiple_partition VALUES (3,'Description')
INSERT INTO multiple_partition VALUES (4,'Description')
INSERT INTO multiple_partition VALUES (10,'Description')
INSERT INTO multiple_partition VALUES (11,'Description')
INSERT INTO multiple_partition VALUES (12,'Description')
INSERT INTO multiple_partition VALUES (13,'Description')
INSERT INTO multiple_partition VALUES (14,'Description')
INSERT INTO multiple_partition VALUES (100,'Description')
INSERT INTO multiple_partition VALUES (101,'Description')
INSERT INTO multiple_partition VALUES (102,'Description')
INSERT INTO multiple_partition VALUES (103,'Description')
INSERT INTO multiple_partition VALUES (104,'Description')
--Verify partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
--Verify index partitions
SELECT partition_id, index_id FROM sys.partitions pt
WHERE pt.[object_id] = OBJECT_ID('multiple_partition')
図 9 のコードは、固定されていない非クラスタ化インデックスをパーティション テーブルに作成します。この非クラスタ化インデックスは、別の列をインデックス キーに使用し、そのキーを他のパーティション テーブルに対する照合結合に使用できます。
Figure 9 パーティション テーブルに対する、固定されていない非クラスタ化インデックス
--Prepare database
IF OBJECT_ID('multiple_partition') IS NOT NULL
DROP TABLE multiple_partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Primary_Left_Scheme')
DROP PARTITION SCHEME Primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_schemes
WHERE [name] = 'Index_primary_Left_Scheme')
DROP PARTITION SCHEME Index_primary_Left_Scheme
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Left_Partition')
DROP PARTITION FUNCTION Left_Partition
GO
IF EXISTS(SELECT [name] FROM sys.partition_functions
WHERE [name] = 'Index_Left_Partition')
DROP PARTITION FUNCTION Index_Left_Partition
GO
--Create partitioned index function
CREATE PARTITION FUNCTION Index_Left_Partition (int) AS RANGE LEFT
FOR VALUES (10,50,100)
--Create partitioned table
CREATE PARTITION FUNCTION Left_Partition (int) AS RANGE LEFT
FOR VALUES (1,10,100)
--Place all index partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Index_primary_Left_Scheme
AS PARTITION Index_Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
--Place all partitions into the PRIMARY filegroup
CREATE PARTITION SCHEME Primary_Left_Scheme
AS PARTITION Left_Partition
--Partition must currently exist in database
ALL TO ([PRIMARY])
CREATE TABLE multiple_partition
(
col1 INT
,col2 INT
) ON Primary_Left_Scheme (col1)
--Create non-aligned partitioned nonclustered index
CREATE NONCLUSTERED INDEX cl_multiple_partition ON multiple_
partition(col2)
ON Index_primary_Left_Scheme (col2)
INSERT INTO multiple_partition VALUES (1,10)
INSERT INTO multiple_partition VALUES (2,10)
INSERT INTO multiple_partition VALUES (3,10)
INSERT INTO multiple_partition VALUES (4,10)
INSERT INTO multiple_partition VALUES (10,50)
INSERT INTO multiple_partition VALUES (11,50)
INSERT INTO multiple_partition VALUES (12,50)
INSERT INTO multiple_partition VALUES (13,50)
INSERT INTO multiple_partition VALUES (14,50)
INSERT INTO multiple_partition VALUES (100,100)
INSERT INTO multiple_partition VALUES (101,100)
INSERT INTO multiple_partition VALUES (102,100)
INSERT INTO multiple_partition VALUES (103,100)
INSERT INTO multiple_partition VALUES (104,100)
--Verify row count on partitioned data
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id = 0
--Verify row count on partitioned index
--Row counts will not match those found in the data partitions
SELECT OBJECT_NAME(ps.[object_id])
,ps.partition_number
,ps.row_count
FROM sys.dm_db_partition_stats ps
INNER JOIN sys.partitions p
ON ps.partition_id = p.partition_id
AND p.[object_id] = OBJECT_ID('multiple_partition')
AND p.index_id <> 0
パーティション インデックスのメンテナンス: 以前には、数百万行あるいは数十億行のデータが存在する大規模なテーブルのインデックスをメンテナンスする作業は手間がかかりすぎて、データベース管理者はそれに忙殺されていました。インデックスの再構築時にデータがロックされることが原因で、メンテナンスを行わないまま放置されることも珍しくありませんでした。SQL Server 2005 では、データベース管理者は、ベース テーブルを長時間ロックすることなく、インデックスをオンラインでメンテナンスできます。とはいえ、この方法でも、ユーザーがデータにアクセスしている最中にインデックスをメンテナンスするため、リソースの使用率が原因でシステムのパフォーマンスが低下する可能性があります。そこで、よりよい方策として、インデックスを小さいセグメントに分割し、その細分化されたパーティションに対するインデックスをメンテナンスするという方法があります。たとえば、1 つのインデックス パーティションをメンテナンスするには、図 8 で示したコードの最後に次のコード スニペットを追加するだけでよいのです。
ALTER INDEX cl_multiple_partition
ON multiple_partition
REBUILD Partition = 2
単一のインデックス パーティションに対するインデックスのメンテナンスはオフラインで行う必要があり、インデックスのメンテナンス中にはテーブル ロックが発生する可能性があります。これを回避するには、単一のパーティションを別のパーティションに移動し、インデックスのメンテナンスを行ってからパーティションをメイン テーブルに戻すという方法があります。この方法では、パーティションをテーブルに戻してクラスタ化インデックスを更新する際にパフォーマンスの問題が生じますが、テーブル全体がロックされ、システム リソースが減ることに比べたら、大した問題ではありません。
まとめ
これまで見てきたように、SQL Server 2005 のテーブル パーティションを利用すれば、アプリケーション コードや SQL Server プロセスを変更することなく、大規模なテーブルのデータの格納およびメンテナンスの柔軟性を向上させることができます。これらの機能を備えた SQL Server は、エンタープライズ レベルの基幹データベース向けの強力なプラットフォームになります。
Noah GomezVerizon のシニア SQL Server 開発 DBA を務め、VLDB および大規模アプリケーションを専門にしています。Professional Association for SQL Server (PASS) の会員でもあり、Verizon DBA チームの一員として、2003 年に Winter Corp. Top Ten Grand Prize を受賞した数テラバイト規模の VLDB にも携わりました。
© 2008 Microsoft Corporation and CMP Media, LLC. All rights reserved; 許可なしに一部または全体を複製することは禁止されています.