Spostamento di partizioni se le viste indicizzate sono definite
Il partizionamento dei dati consente di gestire e accedere in modo rapido ed efficace a subset di dati, salvaguardando al contempo l'integrità dell'intero insieme di dati. La definizione di viste indicizzate su dati partizionati può aumentare ulteriormente la velocità e l'efficienza delle query. Queste viste definite vengono chiamate viste indicizzate allineate alle partizioni.
Una vista indicizzata è allineata alle partizioni con la tabella a cui fa riferimento se si verificano le seguenti condizioni:
Le funzioni della partizione degli indici delle viste indicizzate e della tabella:
Definiscono lo stesso numero di partizioni.
Definiscono gli stessi valori limite per le partizioni.
Gli argomenti delle funzioni di partizione devono essere la colonna stessa.
L'elenco delle proiezioni della definizione della vista include la colonna di partizionamento (invece di un'espressione che include la colonna di partizionamento) della tabella partizionata.
Dove la definizione della vista esegue un raggruppamento, la colonna di partizionamento è una delle colonne di raggruppamento incluse nella definizione della vista.
Dove la vista fa riferimento a più tabelle (utilizzando join, sottoquery, funzioni e così via), la vista indicizzata è allineata alle partizioni con solo una delle tabelle partizionate.
In SQL Server 2008 le viste indicizzate allineate alle partizioni possono essere spostate insieme alle tabelle partizionate sulle quali le viste sono definite. Le operazioni di manutenzione eseguite su partizioni o subset di dati sono ora più efficienti, perché sono relative ai soli dati necessari e non all'intera tabella. Ulteriori vantaggi del partizionamento dei dati con le viste indicizzate sono:
Manutenzione automatica. SQL Server 2008 gestisce automaticamente le viste indicizzate quando vengono eseguite le istruzioni INSERT, UPDATE o DELETE.
Miglioramento dell'aggregazione.Le prestazioni delle query di aggregazione possono essere significativamente migliorate se la vista indicizzata fornisce una sufficiente riduzione delle righe tramite l'aggregazione. Quando in SQL Server 2008 Enterprise Edition Query Optimizer associa automaticamente la query di aggregazione alla vista indicizzata appropriata oppure la query fa riferimento direttamente alla vista indicizzata utilizzando un hint NOEXPAND, le prestazioni delle query possono risultare migliori rispetto ai casi in cui la query accede all'intera tabella.
Vincoli nell'utilizzo dell'istruzione ALTER TABLE... SWITCH con le viste indicizzate
Quando viene utilizzata l'istruzione Transact-SQL ALTER TABLE...SWITCH per trasferire subset di dati tra tabelle di origine e di destinazione a cui viene fatto riferimento da una vista indicizzata, le tabelle e gli indici devono soddisfare le condizioni elencate in Trasferimento efficiente dei dati mediante lo spostamento di partizioni. Vedere la sezione Requisiti per tabelle e indici nel relativo argomento.
Per ulteriori informazioni sullo spostamento delle partizioni con viste con indice definito, vedere la sezione "Ulteriori requisiti per lo spostamento di partizioni" in Trasferimento efficiente dei dati mediante lo spostamento di partizioni.
Un scenario tipico in cui possono essere utilizzate viste indicizzate allineate alle partizioni è con un data warehouse di grandi dimensioni. In un data warehouse di grandi dimensioni, una grande tabella dei fatti viene generalmente partizionata per data e le query di aggregazione spesso sono eseguite sulla tabella dei fatti che esegue il join all'indietro con più tabella delle dimensioni. Lo spostamento di una partizione utilizzando l'istruzione ALTER TABLE … SWITCH può fornire i seguenti vantaggi:
Archiviazione rapida dei dati obsoleti della partizione meno recente della tabella dei fatti.
Spostamento rapido dei nuovi dati nella partizione più recente della tabella dei fatti di grandi dimensioni.
Nella seguente illustrazione viene mostrato il funzionamento della vista indicizzata allineata alle partizioni quando nuovi dati vengono spostati nella partizione più recente della tabella dei fatti. La nuova partizione spostata nella tabella dei fatti riporta l'aggregazione creata nella nuova partizione.
.gif "Spostamento delle viste con indice allineate alle partizioni")
Esempi
Nel seguente esempio viene descritto l'utilizzo dell'istruzione ALTER TABLE … SWITCH per rimuovere una partizione obsoleta da una tabella che contiene una parte di una vista indicizzata allineata alle partizioni.
In questo esempio, una tabella dei fatti relativa alle vendite [f_sales] contiene i dati di vendita per l'anno 2006. Tali dati vengono partizionati su base trimestrale. Di frequente vengono eseguite query che aggregano la quantità di vendite per data e punto vendita e viene creata una vista indicizzata per velocizzare la query di aggregazione definita nella tabella dei fatti. Quindi, il primo trimestre di dati del 2006 viene memorizzato nella tabella di archivio, mentre la vista indicizzata nella tabella dei fatti rimane corretta e utilizzabile.
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
Come mostrato nell'esempio precedente, dopo che la partizione è stata spostata tutti i dati della partizione 1 della tabella [f_sales] e la vista indicizzata [v_f_sales_sumamt] vengono spostati nella tabella corrispondente [sales_archive] e nella vista indicizzata [v_sales_archive_sumamt].
Per ottenere la quantità di vendite per data e per punto di vendita di luglio 2006, è possibile eseguire una query alla tabella [f_sales] o alla vista indicizzata [v_f_sales_sumamt], come mostrato nelle due query seguenti. I risultati sono gli stessi in entrambi casi, ma l'esecuzione sulle viste indicizzate migliora significativamente le prestazioni delle query, in quanto nella vista indicizzata vengono visualizzate le aggregazioni precalcolate e viene ridimensionato il numero di righe per un fattore di 10, come mostrato nell'esempio seguente.
-- 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
Lo spostamento delle partizioni mediante l'istruzione ALTER TABLE…SWITCH è un'operazione rapida, che riguarda solo i metadati. Quando le viste indicizzate sono allineate alle partizioni con la tabella di origine e di destinazione, lo spostamento delle partizioni consente di trasferire un subset dei dati dalla tabella di origine alla tabella di destinazione, nonché delle parti associate delle viste indicizzate allineate. L'integrità dell'insieme di dati viene gestita perché tutti gli oggetti associati alla tabella di origine (ad esempio tabelle, indici e viste indicizzate) sono inclusi nello spostamento della partizione. Utilizzando le viste indicizzate in una tabella partizionata, è possibile aumentare significativamente le prestazioni delle query di aggregazione che fanno riferimento alla tabella.