Alternando partições quando exibições indexadas estão definidas
O particionamento de dados habilita-o a gerenciar e acessar subconjuntos de dados de forma rápida e eficaz e, ao mesmo tempo, mantém a integridade de todos os dados da coleção. A definição de exibições indexadas em dados particionados pode aumentar ainda mais a velocidade e eficiência de suas consultas. Essas exibições definidas são chamadas exibições indexadas alinhadas por partição.
Uma exibição indexada é alinhada por partição com a tabela que referencia se as seguintes condições forem verdadeiras:
As funções de partição dos índices da exibição e da tabela indexadas:
Definem o mesmo número de partições.
Definem os mesmos valores de limite para partições.
Os argumentos das funções de partição devem estar na mesma coluna.
A lista de projeção da definição da exibição inclui a coluna de particionamento (oposta a uma expressão que inclua a coluna de particionamento) da tabela particionada.
Quando a definição da exibição executa um agrupamento, a coluna de particionamento é uma das colunas de agrupamento incluídas na definição da exibição.
Quando a exibição referencia várias tabelas (usando junções, subconsultas, funções e assim por diante), a exibição indexada é alinhada por partição apenas com uma das tabelas particionadas.
No SQL Server 2008, exibições indexadas alinhadas por partição podem ser alternadas com as tabelas particionadas em relação às quais as exibições estão definidas. Agora as operações de manutenção realizadas em partições ou subconjuntos de dados são realizadas com mais eficácia porque visam apenas aos dados exigidos, em vez de toda a tabela. Os benefícios adicionais de particionar os dados com exibições indexadas são:
Manutenção automática. SQL Server 2008 mantém exibições indexadas automaticamente quando as instruções INSERT, UPDATE ou DELETE são executadas.
Melhoria de agregação.O desempenho da consulta de agregação pode ser melhorado significativamente se a exibição indexada reduzir as linhas suficientemente por meio da agregação. No SQL Server 2008 Enterprise, quando o otimizador de consultas corresponde automaticamente a consulta de agregação com a exibição indexada adequada ou quando a consulta refere-se diretamente à exibição indexada usando uma dica NOEXPAND, o desempenho da consulta pode ser mais eficiente do que quando a consulta acessa toda a tabela.
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.
.gif "Alternância das Exibições de Índice Alinhadas por 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.