[アーティクル]
09/28/2010

インデックス付きビューが定義されている場合のパーティション切り替え

データをパーティション分割すると、データコレクション全体の整合性を保ちながら、データサブセットの管理とアクセスを迅速かつ効率的に行えるようになります。パーティション分割されたデータにインデックス付きビューを定義すると、クエリの速度と効率がさらに向上します。これらの定義済みのビューは、パーティションで固定されたインデックス付きビューと呼ばれます。

インデックス付きビューは、次の条件が真の場合、参照先テーブルにパーティションで固定されます。

インデックス付きビューとテーブルのインデックスのパーティション関数 :
- 同数のパーティションが定義されている
- パーティションに同じ境界値が定義されている
- パーティション関数の引数が同じ列である
ビュー定義案一覧には、パーティション分割テーブルのパーティション分割列が表示されます (パーティション分割列を含む式ではありません)。
ビュー定義によってグループ化が実行される場合、ビュー定義に含まれているグループ列の 1 つがパーティション分割列になります。
(結合、サブクエリ、関数などを使用して) ビューが複数の表を参照している場合、インデックス付きビューは、パーティション分割テーブルのいずれか 1 つのみとパーティションで固定されます。

SQL Server 2008 では、パーティションで固定された一連のインデックス付きビューは、そのビューが定義されているパーティション分割テーブルと切り替えることができます。データのパーティションまたはサブセットに対するメンテナンス操作も、テーブル全体ではなく必要なデータのみを対象にできるので、効率が向上します。パーティション分割されたデータにインデックス付きビューを割り当てることで、さらに次のようなメリットが得られます。

自動メンテナンス。SQL Server 2008 では、INSERT、UPDATE、または DELETE ステートメントの実行時にインデックス付きビューが自動的にメンテナンスされます。
集計の向上。集計によって、インデックス付きビューの多数の行が削減されれば、集計クエリのパフォーマンスが大幅に向上します。SQL Server 2008 Enterprise では、クエリオプティマイザによって集計クエリが適切なインデックス付きビューと自動的に照合されるか、クエリが NOEXPAND ヒントを使用してインデックス付きビューを直接参照した場合、クエリがテーブル全体にアクセスする場合よりも、クエリのパフォーマンスが効率的になります。

インデックス付きビューで ALTER TABLE...SWITCH ステートメントを使用する場合の制約

Transact-SQL ALTER TABLE...SWITCH ステートメントを使用して、インデックス付きビューが参照しているテーブル間でデータサブセットを転送する場合、テーブルとインデックスは「パーティションの切り替えを使用した効率的なデータの転送」に示す条件を満たしている必要があります。そのトピックの「テーブルとインデックスの要件」を参照してください。

定義済みインデックスビューを含むパーティションの移動の詳細については、「パーティションの切り替えを使用した効率的なデータの転送」の「パーティションの移動に関する追加条件」を参照してください。

パーティションで固定されたインデックス付きビューは、大規模なデータウェアハウスなどで使用します。大規模なデータウェアハウスの場合、通常、大きなファクトテーブルが日付別にパーティション分割されており、このファクトテーブルに対して、複数のディメンションテーブルを結合する集計クエリが頻繁に実行されます。ALTER TABLE … SWITCH ステートメントを使用してパーティション内外に移動すると、次のような利点がもたらされます。

ファクトテーブルの最も古いパーティションから古いデータをすばやく抽出して、アーカイブできる。
大きなファクトテーブルの最新パーティションに、新しいデータをすばやく追加できる。

ファクトテーブルの最新パーティションに新しいデータを追加したとき、パーティションで固定されたインデックス付きビューがどのように機能するかを次の図に示します。ファクトテーブルに切り替えられた新しいパーティションには、そのパーティションで作成された集計が適用されます。

パーティションで固定されたインデックス付きビューの切り替え

例

ALTER TABLE … SWITCH ステートメントを使用して、テーブルの古いパーティション外に移動し、パーティションで固定されているインデックス付きビューの一部を取り出す方法を次の例に示します。

この例では、Sales ファクトテーブル [f_sales] に 2006 年度の 1 年分の売上データが含まれています。売上データは四半期別にパーティション分割されています。売上高を日付と店舗別に集計するクエリが頻繁に実行されるので、ファクトテーブルに定義した集計クエリの処理速度を向上するために、インデックス付きビューが作成されています。ここで、ファクトテーブルのインデックス付きビューを正しく使用可能な状態に保ったまま、2006 年第 1 四半期のデータをアーカイブテーブルにアーカイブします。

USE MASTER
GO

DROP DATABASE test_partition
GO

--Create database with the file groups that will be used by the partition schemes.
CREATE DATABASE test_partition
ON PRIMARY (NAME = 'paiv_Dat0', FILENAME='C:\temp\test_partition_DataFile0.mdf'),
FILEGROUP FG1 (NAME = 'paiv_Dat1', FILENAME = 'C:\temp\test_partition_DataFile1.ndf'),
FILEGROUP FG2 (NAME = 'paiv_Dat2', FILENAME = 'C:\temp\test_partition_DataFile2.ndf'),
FILEGROUP FG3 (NAME = 'paiv_Dat3', FILENAME = 'C:\temp\test_partition_DataFile3.ndf'),
FILEGROUP FG4 (NAME = 'paiv_Dat4', FILENAME = 'C:\temp\test_partition_DataFile4.ndf'),
FILEGROUP FG5 (NAME = 'paiv_Dat5', FILENAME = 'C:\temp\test_partition_DataFile5.ndf')
LOG ON (NAME = 'paiv_log', filename='C:\temp\test_partition_log.ldf')
GO
USE test_partition
GO

-- Create partition function and partition scheme.
CREATE PARTITION FUNCTION [PF1] (int)
AS RANGE LEFT FOR VALUES (20060331, 20060630, 20060930, 20061231);
GO
CREATE PARTITION SCHEME [PS1]
AS PARTITION [PF1] 
TO ([FG1], [FG2], [FG3], [FG4], [FG5]
, [PRIMARY]);
GO

-- Create fact table.
CREATE TABLE dbo.f_sales (date_key INT NOT NULL, cust_key INT, store_key INT, amt MONEY) ON PS1(date_key);
GO

-- Populate data into table f_sales. 
SET NOCOUNT ON
GO
DECLARE @d INT, @ds INT, @cs INT, @s INT
SET @d = 20060101
SET @ds = 7  -- date_key increment step

WHILE (@d <= 20061231) 
BEGIN
WHILE @d%100 > 0 AND @d%100 < 29
BEGIN
SET @cs = 10  -- # of records for customer/store for that date
SET @s = CAST ( RAND() * 1000 as INT )
WHILE (@cs > 0)
BEGIN
INSERT dbo.f_sales (date_key, cust_key, store_key, amt)
VALUES (@d, CAST ( RAND() * 1000 as INT ), @s, CAST ( RAND() * 1000 as MONEY ) )
SET @cs = @cs - 1
END
SET @d = @d + @ds
END
SET @d = @d + @ds
END
GO


-- The table with clustered index is partitioned using the partition scheme specified.
CREATE CLUSTERED INDEX UCIdx_f_sales on dbo.f_sales (date_key, cust_key, store_key) ON PS1(date_key)
GO

--Create indexed view, which aggregates on the date and store.
CREATE VIEW dbo.v_f_sales_sumamt WITH SCHEMABINDING AS
(
SELECT date_key, store_key, sum(ISNULL(amt,0)) AS amt, count_big(*) AS cnt
FROM dbo.f_sales AS sales
GROUP BY date_key, store_key
)
GO

-- Materialize the view. The indexed view is now partition-aligned with table f_sales.
CREATE UNIQUE CLUSTERED INDEX ucidx_v_sales_sum
ON dbo.v_f_sales_sumamt (date_key) ON PS1(date_key)
GO

-- Check data distribution in various partitions of the table & the indexed view.
SELECT OBJECT_NAME(p.object_id) as obj_name, p.index_id, p.partition_number, p.rows, a.type, a.filegroup_id 
FROM sys.system_internals_allocation_units a
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.f_sales'), OBJECT_ID(N'dbo.v_f_sales_sumamt '))
ORDER BY obj_name, p.index_id, p.partition_number

-- Create archive table to receive the partition that will be switched out of table f_sales. 
CREATE TABLE dbo.sales_archive (date_key INT NOT NULL, cust_key INT, store_key INT, amt MONEY) ON FG1
GO
CREATE CLUSTERED INDEX UCIdx_sales_archive on dbo.sales_archive (date_key, cust_key, store_key) ON FG1
GO
--Create indexed view with view definition matching v_f_sales_sumamt on table f_sales.
CREATE VIEW dbo.v_sales_archive_sumamt WITH SCHEMABINDING AS
(
SELECT date_key, store_key, sum(ISNULL(amt,0)) AS amt, count_big(*) AS cnt
FROM dbo.sales_archive AS sales
GROUP BY date_key, store_key
)
GO

-- Materialize the view. The indexed view is partition-aligned with table sales_archive.
CREATE UNIQUE CLUSTERED INDEX ucidx_v_sales_sum
ON dbo.v_sales_archive_sumamt(date_key) ON FG1
GO

-- Check data distribution in various partitions of the table and the indexed view. 
SELECT OBJECT_NAME(p.object_id) as obj_name, p.index_id, p.partition_number, p.rows, a.type, a.filegroup_id 
FROM sys.system_internals_allocation_units a
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.sales_archive'), OBJECT_ID(N'dbo.v_sales_archive_sumamt '))
ORDER BY obj_name, p.index_id, p.partition_number

-- Data associated with the old partition of the source table - [f_sales] and the indexed view [v_f_sales_sumamt] - 
-- is switched out to archive table [sales_archive] and the indexed view [v_sales_archive_sumamt].
ALTER TABLE dbo.f_sales SWITCH PARTITION 1 TO dbo.sales_archive

-- Data distribution in various partitions shows that 
-- partition 1 of [f_sales] and the indexed view [v_f_sales_sumamt] are now empty 
-- and these rows are now in [sales_archive] and [v_sales_archive_sumamt], respectively.
SELECT OBJECT_NAME(p.object_id) as obj_name, p.index_id, p.partition_number, p.rows, a.type, a.filegroup_id 
FROM sys.system_internals_allocation_units a
JOIN sys.partitions p
ON p.partition_id = a.container_id
WHERE p.object_id IN (OBJECT_ID(N'dbo.sales_archive'), OBJECT_ID(N'dbo.v_sales_archive_sumamt '), 
OBJECT_ID(N'dbo.f_sales'), OBJECT_ID(N'dbo.v_f_sales_sumamt '))
ORDER BY obj_name, p.index_id, p.partition_number

上記の例に示したように、パーティションが切り替わった後、テーブル [f_sales] のパーティション 1 のデータすべてとインデックス付きビュー [v_f_sales_sumamt] は、対応するテーブル [sales_archive] とインデックス付きビュー [v_sales_archive_sumamt] に移動します。

2006 年 7 月の売上高を日付および店舗別に取得するには、次の 2 つのクエリに示すように、テーブル [f_sales] またはインデックス付きビュー [v_f_sales_sumamt] に対してクエリを実行します。結果はどちらの場合でも同じですが、インデックス付きビューに対してクエリを実行すると、パフォーマンスが大幅に向上します。インデックス付きビューでは、次の例に示すように、事前計算済みの集計を具体化することで、行の数が 10 分の 1 に削減されるためです。

-- This query runs against the table [f_sales]
SELECT date_key, store_key AS [Store Number], sum(ISNULL(amt,0)) AS Sales_Amount
FROM dbo.f_sales
WHERE date_key >= '20060701' AND date_key < '20060801'
GROUP BY date_key, store_key
ORDER BY date_key, store_key
OPTION (EXPAND VIEWS)

-- This query runs against the indexed view [v_f_sales_sumamt]
-- the result of this query is the same as the one against the table
-- the indexed view materializes the pre-calculated aggregate, resulting in significant improvements in query performance   
SELECT date_key, store_key AS [Store Number], sum(ISNULL(amt,0)) AS Sales_Amount
FROM dbo.v_f_sales_sumamt WITH (NOEXPAND)
WHERE date_key >= '20060701' AND date_key < '20060801'
GROUP BY date_key, store_key

ALTER TABLE…SWITCH ステートメントを使用したパーティションの切り替えは、高速で、メタデータだけに影響を与える操作です。インデックス付きビューが転送元および転送先のテーブルとパーティションで固定されている場合にパーティションの切り替えを行うと、データのサブセットや、固定されたインデックス付きビューの関連する部分を転送元のテーブルから転送先のテーブルに移動することができます。転送元テーブルに関連付けられているすべてのオブジェクト (テーブル、インデックス、インデックス付きビューなど) はパーティションの切り替えに含まれているので、データ収集の整合性が保持されます。パーティション分割されたテーブルにインデックス付きビューを使用することで、テーブルを参照する集計クエリのパフォーマンスが大幅に改善されます。

次の方法で共有

インデックス付きビューが定義されている場合のパーティション切り替え

インデックス付きビューで ALTER TABLE...SWITCH ステートメントを使用する場合の制約

例

関連項目

概念

その他のリソース