Commutation de partitions lors de la définition de vues indexées
Le partitionnement des données permet de gérer des sous-ensembles de données et d’y accéder facilement et efficacement, tout en conservant l'intégrité de la collection de données au complet. La définition de vues indexées sur des données partitionnées permet d’augmenter la vitesse et l’efficacité de vos requêtes. Ces vues définies sont appelées vues indexées alignées sur les partitions.
Une vue indexée est alignée sur les partitions avec la table qu'elle référence si les conditions suivantes sont remplies :
Les fonctions de partition des index de la vue indexée et de la table :
définissent le même nombre de partitions ;
définissent les mêmes valeurs limites pour les partitions.
Les arguments des fonctions de partition doivent être la même colonne.
La liste de projection de la définition de la vue inclut la colonne de partitionnement (par opposition à une expression qui inclut la colonne de partitionnement) de la table partitionnée.
Lorsque la définition de la vue effectue un regroupement, la colonne de partitionnement est l'une des colonnes de regroupement incluse dans la définition de la vue.
Lorsque la vue fait référence à plusieurs tables (à l'aide de jointures, sous-requêtes, fonctions, etc.), la vue indexée est alignée sur les partitions avec une seule des tables partitionnées.
Dans SQL Server 2008, les vues indexées alignées sur les partitions peuvent permuter avec les tables partitionnées par rapport auxquelles les vues sont définies. Maintenant, les opérations de maintenance réalisées sur des partitions ou des sous-ensembles de données sont plus efficaces car elles ne ciblent que les données concernées, au lieu de la totalité de la table. Le partitionnement de données avec vues indexées offre les avantages supplémentaires suivants :
Maintenance automatique. SQL Server 2008 maintient automatiquement les vues indexées lors de l’exécution des instructions INSERT, UPDATE ou DELETE.
Amélioration de l'agrégation.La performance d’une requête d'agrégation peut être considérablement améliorée si la vue indexée assure une réduction de lignes suffisante après agrégation. Dans SQL Server 2008 Enterprise, lorsque l'optimiseur de requête met automatiquement en correspondance la requête d'agrégation avec la vue indexée appropriée ou si la requête fait directement référence à la vue indexée en utilisant un indicateur NOEXPAND, les performances des requêtes peuvent être plus élevées que lorsque la requête accède à la table entière.
Contraintes lors de l'utilisation de l’instruction ALTER TABLE…SWITCH avec des vues indexées
Lors de l'utilisation de l’instruction Transact-SQL ALTER TABLE... SWITCH pour transférer des sous-ensemble de données entre des tables sources et des tables cibles référencées par une vue indexée, les tables et index doivent satisfaire aux conditions spécifiées dans Transfert efficace de données à l'aide du commutateur de partitionnement. Consultez la section Conditions requises pour les index et les tables de cette rubrique.
Pour plus d'informations sur le déplacement de partitions avec des vues d'index définies, consultez la section « Conditions requises supplémentaires pour le déplacement de partitions » de Transfert efficace de données à l'aide du commutateur de partitionnement.
Dans un scénario typique, les vues indexées alignées sur les partitions sont utilisées avec un grand entrepôt de données. Dans un entrepôt de données volumineux, une grande table de faits est habituellement partitionnée par date, et les requêtes d'agrégation sont souvent exécutées sur la table de faits jointes à plusieurs tables de dimension. Le basculement d’une partition à l’aide de l’instruction ALTER TABLE…SWITCH peut offrir les avantages suivants :
Les données anciennes sont rapidement extraites de la partition la plus ancienne de la table de faits et archivées.
Les données nouvelles sont rapidement placées dans la partition la plus récente de la grande table de faits.
L'illustration suivante indique comment fonctionne une vue indexée alignée sur les partitions lorsque de nouvelles données sont placées dans la partition la plus récente de la table de faits. La nouvelle partition qui est basculée dans la table de faits emporte avec elle l'agrégation créée sur la nouvelle partition.
.gif "Basculement de vues indexées alignées sur les partitions")
Exemples
L'exemple suivant indique comment utiliser l’instruction ALTER TABLE…SWITCH pour basculer une partition ancienne d'une table en emportant une partie de vue indexée alignée sur les partitions avec elle.
Dans cet exemple, une table de faits de ventes [f_sales] contient une année de données de ventes pour 2006. Les données de ventes sont partitionnées sur une base trimestrielle. Des requêtes qui agrègent les montants des ventes par date ou par magasin sont fréquemment exécutées et une vue indexée est créée pour accélérer la requête d’agrégation qui a été définie sur la table de faits. Maintenant, les données du premier trimestre 2006 doivent être archivées dans une table d'archive, pendant que la vue indexée sur la table de faits reste correcte et utilisable.
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
Comme indiqué dans l'exemple précédent, une fois que la partition a été basculée, toutes les données dans la partition 1 de la table [f_sales] et de la vue indexée [v_f_sales_sumamt] sont transférées dans la table [sales_archive] et la vue indexée [v_sales_archive_sumamt] correspondantes.
Pour obtenir le montant des ventes par date et par magasin pour juillet 2006, vous pouvez lancer une requête sur la table [f_sales] ou bien sur la vue indexée [v_f_sales_sumamt], comme l’indiquent les deux requêtes suivantes. Les résultats sont les mêmes dans les deux cas, mais l'exécution sur la vue indexée améliore sensiblement les performances de la requête car la vue indexée matérialise les agrégats précalculés et divise par 10 le nombre de lignes, comme l’indique l'exemple suivant.
-- 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
La commutation de partitions à l’aide de l'instruction ALTER TABLE….SWITCH est une opération rapide, qui porte uniquement sur les métadonnées. Lorsque les vues indexées sont alignées sur les partitions avec la table source et la table cible, la commutation de partitions vous permet de transférer un sous-ensemble des données de la table source à la table cible, et également des parties de vues indexées alignées qui lui sont associées. L'intégrité de la collection de données est maintenue parce que tous les objets associés à la table source (tels que les tables, les index et les vues indexées) sont inclus dans la commutation de partition. En utilisant des vues indexées sur une table partitionnée, vous pouvez améliorer considérablement la performance des requêtes d'agrégation qui référencent la table.