다음을 통해 공유

  • 아티클

인덱싱된 뷰가 정의된 경우 파티션 전환

데이터를 분할하면 데이터 하위 집합을 빠르고 효율적으로 관리 및 액세스하는 동시에 전체 데이터 컬렉션의 무결성을 유지할 수 있습니다. 분할된 데이터에 대해 인덱싱된 뷰를 정의하면 쿼리의 속도와 효율성을 더 높일 수 있습니다. 이렇게 정의된 뷰를 파티션 정렬 인덱싱된 뷰라고 합니다.

다음과 같은 경우 인덱싱된 뷰는 자신이 참조하는 테이블과 파티션 정렬됩니다.

  • 인덱싱된 뷰 및 테이블의 인덱스에 대한 파티션 함수:

    • 같은 수의 파티션을 정의하는 경우

    • 파티션에 대해 같은 경계 값을 정의하는 경우

    • 파티션 함수의 인수가 같은 열이어야 하는 경우

  • 분할 열이 포함된 식과 달리 뷰 정의의 프로젝션 목록에 분할된 테이블의 분할 열이 포함된 경우

  • 뷰 정의가 그룹화를 수행할 때 분할 열이 뷰 정의에 포함된 그룹화 열 중 하나인 경우

  • 뷰가 조인, 하위 쿼리, 함수 등을 사용하여 여러 열을 참조할 때 인덱싱된 뷰가 분할된 테이블 중 하나와만 파티션 정렬되는 경우

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 문을 사용하여 파티션 정렬 인덱싱된 뷰의 일부를 가져오는 테이블의 이전 파티션에서 전환하는 방법을 보여 줍니다.

이 예에서 판매 팩트 테이블 [f_sales]에는 2006년 한 해 동안의 판매 데이터가 포함됩니다. 판매 데이터는 분기별로 분할됩니다. 일반적으로 판매량을 날짜와 대리점별로 집계하는 쿼리가 실행되고 팩트 테이블에 정의된 집계 쿼리의 속도를 높이기 위해 인덱싱된 뷰가 생성됩니다. 여기서는 2006년 1/4분기 데이터가 보관 테이블에 보관되고 팩트 테이블의 인덱싱된 뷰가 정확하고 사용 가능한 상태로 유지됩니다.

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] 테이블 및 [v_f_sales_sumamt] 인덱싱된 뷰의 파티션 1에 있는 모든 데이터가 해당 [sales_archive] 테이블 및 [v_sales_archive_sumamt] 인덱싱된 뷰로 이동합니다.

2006년 7월의 판매량을 날짜와 대리점별로 가져오려면 다음 두 쿼리와 같이 [f_sales] 테이블 또는 인덱싱된 뷰 [v_f_sales_sumamt]에 대해 쿼리를 실행하면 됩니다. 두 경우 모두 같은 결과를 반환하지만 인덱싱된 뷰에 대해 쿼리를 실행하면 다음 예와 같이 인덱싱된 뷰가 미리 계산된 집계를 구체화하고 행 수를 10배 정도 줄이기 때문에 쿼리 성능이 크게 향상됩니다.

-- 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 문을 사용하여 파티션을 전환하는 작업은 메타데이터 전용의 빠른 작업입니다. 인덱싱된 뷰가 원본 및 대상 테이블과 파티션 정렬되면 파티션 전환을 통해 원본 테이블에서 대상 테이블로 데이터의 하위 집합뿐 아니라 정렬된 인덱싱된 뷰의 관련 부분도 가져올 수 있습니다. 원본 테이블(예: 테이블, 인덱스 및 인덱싱된 뷰)과 관련된 모든 개체가 파티션 전환에 포함되므로 데이터 컬렉션의 무결성이 유지됩니다. 분할된 테이블에서 인덱싱된 뷰를 사용하면 해당 테이블을 참조하는 집계 쿼리의 성능을 크게 향상시킬 수 있습니다.