Alternando partições quando exibições indexadas estão definidas

Restrições ao usar a instrução ALTER TABLE...SWITCH com exibições indexadas

Ao usar a instrução Transact-SQL ALTER TABLE...SWITCH para transferir subconjuntos de dados entre tabelas de origem e de destino que sejam referenciadas por uma exibição indexada, as tabelas e os índices devem cumprir as condições listadas em Transferindo dados de forma eficaz usando a alternância de partição. Consulte a seção de requisitos de tabela e de índice deste tópico.

Para obter mais informações sobre mover partições com exibições definidas por índices, consulte a seção "Requisitos adicionais para mover partições" em Transferindo dados de forma eficaz usando a alternância de partição.

Um cenário típico no qual podem ser usadas exibições indexadas alinhadas por partição é um grande data warehouse. Em um grande data warehouse, uma grande tabela de fatos geralmente é particionada por dados, e as consultas de agregação com freqüência são executadas na tabela de fatos em junção com várias tabelas de dimensões. Alternar uma partição com o uso da instrução ALTER TABLE ... SWITCH pode trazer os seguintes benefícios:

• Remova rapidamente dados antigos da partição mais antiga da tabela de fatos.

• Inclua rapidamente novos dados na partição mais recente da grande tabela de fatos.

O exemplo a seguir demonstra como uma exibição indexada por partição funciona quando novos dados são trazidos para a partição mais recente da tabela de fatos. A nova partição que é alternada na tabela de fatos leva consigo a agregação que é criada na nova partição.

Exemplos

O exemplo a seguir mostra como usar a instrução ALTER TABLE … SWITCH para alternar uma partição antiga de uma tabela que traz consigo uma exibição indexada alinhada por partição.

Neste exemplo, uma tabela de fatos de vendas [f_sales] contém um ano de dados de vendas para 2006. Os dados de vendas são particionados por trimestre. Freqüentemente são executadas consultas que agregam a quantidade de vendas por data e por loja, e uma exibição indexada é criada para acelerar a consulta de agregação que foi definida na tabela de fatos. Os dados do primeiro trimestre de 2006 devem ser arquivados em uma tabela de arquivo, ao mesmo tempo em que a exibição indexada na tabela de fatos permanece correta e usável.

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

Como demonstrado no exemplo anterior, depois de a partição ter sido alternada, todos os dados na partição 1 da tabela [f_sales] e da exibição indexada [v_f_sales_sumamt] são movidos para a tabela [sales_archive] e a exibição indexada [v_sales_archive_sumamt] correspondentes.

Para obter a quantidade de vendas por data e por loja para julho de 2006, você pode consultar a tabela [f_sales] ou a exibição indexada [v_f_sales_sumamt], como se vê nas duas consultas a seguir. Os resultados são iguais, mas a execução da exibição indexada melhora significativamente o desempenho da consulta porque a exibição indexada materializa os agregados pré-calculados e reduz o número de linhas por um fator de 10, como demonstrado no exemplo a seguir.

-- 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

Alternar partições com o uso da instrução ALTER TABLE.SWITCH é uma operação rápida de somente metadados. Quando as exibições indexadas são alinhadas por partição com as tabelas de origem e de destino, alternar as partições habilita-o a trazer um subconjunto de dados da tabela de origem para a tabela de destino e também associa partes das exibições indexadas alinhadas. A integridade da coleção de dados é mantida porque todos os objetos que estão associados com a tabela de origem (como tabelas, índices e exibições indexadas) são incluídos na alternância da partição. Ao usar exibições indexadas em uma tabela particionada, você pode aumentar significativamente o desempenho das consultas de agregação que referenciam a tabela.

Consulte também

Conceitos