Partitionswechsel bei definierten indizierten Sichten
Durch die Datenpartitionierung können Sie Teilmengen von Daten schnell und effizient verwalten und darauf zugreifen, während die Integrität der gesamten Datenauflistung erhalten bleibt. Durch das Definieren von indizierten Sichten für partitionierte Daten können die Geschwindigkeit und die Effizienz von Abfragen weiter erhöht werden. Diese definierten Sichten werden als indizierte Sichten mit ausgerichteten Partitionen bezeichnet.
Die Ausrichtung einer indizierten Sicht mit ausgerichteten Partitionen erfolgt an der Tabelle, auf die sie verweist, wenn die folgenden Bedingungen erfüllt sind:
Die Partitionsfunktionen der Indizes der indizierten Sicht und Tabelle erfüllen die folgenden Bedingungen:
Sie definieren dieselbe Anzahl an Partitionen.
Sie definieren dieselben Begrenzungswerte für Partitionen.
Die Argumente der Partitionsfunktionen müssen sich in derselben Spalte befinden.
Die Projektionsliste der Sichtdefinition umfasst die Partitionierungsspalte (im Gegensatz zu einem Ausdruck, der die Partitionierungsspalte einschließt) der partitionierten Tabelle.
Wenn die Sichtdefinition eine Gruppierung ausführt, ist die Partitionierungsspalte eine der in der Sichtdefinition eingeschlossenen Gruppierungsspalten.
Wenn die Sicht auf mehrere Tabellen verweist (mit Verknüpfungen, Unterabfragen, Funktionen usw.), erfolgt die Ausrichtung der indizierten Sicht mit ausgerichteten Partitionen nur an einer der partitionierten Tabellen.
In SQL Server 2008 können indizierte Sichten mit ausgerichteten Partitionen zusammen mit den partitionierten Tabellen gewechselt werden, anhand derer sie definiert werden. Außerdem können Wartungsvorgänge, die an Partitionen oder Datenteilmengen ausgeführt werden, effizienter ablaufen, da diese Vorgänge nur auf die tatsächlich erforderlichen Daten angewendet werden und nicht auf die gesamte Tabelle. Das Partitionieren von Daten mit indizierten Sichten bietet außerdem die folgenden Vorteile:
Automatische Wartung. SQL Server 2008 verwaltet indizierte Sichten automatisch, wenn INSERT-, UPDATE- oder DELETE-Anweisungen ausgeführt werden.
Aggregationsverbesserung. Die Leistung von Aggregatabfragen kann deutlich verbessert werden, wenn die indizierte Sicht eine ausreichende Zeilenverringerung durch Aggregation bietet. Wenn der Abfrageoptimierer in SQL Server 2008 Enterprise die Aggregatabfrage automatisch der entsprechenden indizierten Sicht zuordnet oder die Abfrage durch einen NOEXPAND-Hinweis direkt auf die indizierte Sicht verweist, kann die Abfrageleistung höher sein, als wenn die Abfrage auf die gesamte Tabelle zugreift.
Einschränkungen beim Verwenden der ALTER TABLE...SWITCH-Anweisung mit indizierten Sichten
Beim Verwenden der Transact-SQL ALTER TABLE...SWITCH-Anweisung zum Übertragen von Datenteilmengen zwischen Quell- und Zieltabellen, auf die durch eine indizierte Sicht verwiesen wird, müssen die Tabellen und Indizes die unter Effizientes Übertragen von Daten durch Partitionswechsel aufgeführten Bedingungen erfüllen. Weitere Informationen finden Sie im Abschnitt zu Tabellen- und Indexanforderungen in diesem Thema.
Weitere Informationen zum Verschieben von Partitionen mit definierten Indexsichten finden Sie im Abschnitt "Weitere Anforderungen für das Verschieben von Partitionen" unter Effizientes Übertragen von Daten durch Partitionswechsel.
Ein typisches Szenario für die Verwendung von indizierten Sichten mit ausgerichteten Partitionen ist ein großes Data Warehouse. In einem großen Data Warehouse wird eine große Faktentabelle normalerweise nach Datum partitioniert, und Aggregatabfragen werden häufig für die mit mehreren Dimensionstabellen verknüpfte Faktentabelle ausgeführt. Das Hinein- oder Hinauswechseln einer Partition mithilfe der ALTER TABLE … SWITCH-Anweisung kann die folgenden Vorteile bieten:
Schnelles Archivieren von veralteten Daten aus der ältesten Partition der Faktentabelle.
Schnelles Einfügen von neuen Daten in die neueste Partition der großen Faktentabelle.
In der folgenden Abbildung wird dargestellt, wie eine indizierte Sicht mit ausgerichteten Partitionen funktioniert, wenn neue Daten in der neuesten Partition der Faktentabelle eingefügt werden. Die neue Partition, die in die Faktentabelle gewechselt wird, behält die Aggregation bei, die auf der neuen Partition erstellt wird.
.gif "Indexsichtenwechsel mit ausgerichteter Partitionierung")
Beispiele
Im folgenden Beispiel wird das Verwenden der ALTER TABLE … SWITCH-Anweisung zum Herauswechseln einer alten Partition aus einer Tabelle veranschaulicht, die Teile einer indizierten Sicht mit ausgerichteten Partitionen einbringt.
In diesem Beispielt enthält eine Umsatzfaktentabelle [f_sales] Umsatzdaten für ein Jahr (2006). Die Umsatzdaten werden vierteljährlich partitioniert. Häufig werden Abfragen ausgeführt, die den Umsatz nach Datum und nach Geschäft aggregieren, und es wird eine indizierte Ansicht erstellt, um die für die Faktentabelle definierte Aggregatabfrage zu beschleunigen. Die Daten für das erste Quartal 2006 sollen in einer Archivtabelle archiviert werden, und gleichzeitig soll die indizierte Sicht der Faktentabelle korrekt bleiben und weiterhin verwendet werden können.
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
Wie im vorhergehenden Beispiel gezeigt, werden nach dem Partitionswechsel alle Daten in Partition 1 der Tabelle [f_sales] und der indizierten Sicht [v_f_sales_sumamt] in die entsprechende Tabelle [sales_archive] und in die indizierte Sicht [v_sales_archive_sumamt] verschoben.
Zum Abfragen des Umsatzes nach Datum und Geschäft für Juli 2006 können Sie die Tabelle [f_sales] oder die indizierte Sicht [v_f_sales_sumamt] abfragen, wie in den folgenden beiden Abfragen dargestellt. Die Ergebnisse sind in beiden Fällen gleich, das Abfragen der indizierten Sicht erhöht die Abfrageleistung jedoch erheblich, da in der indizierten Sicht die im Voraus berechneten Aggregate materialisiert und die Anzahl der Zeilen um den Faktor 10 reduziert werden. Dies wird im folgenden Beispiel dargestellt.
-- 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
Bei einem Partitionswechsel mit der ALTER TABLE…SWITCH-Anweisung handelt es sich um einen schnellen, nur auf die Metadaten bezogenen Vorgang. Wenn die Ausrichtung von indizierten Sichten mit ausgerichteten Partitionen an der Quell- und Zieltabelle erfolgt, können Sie durch den Partitionswechsel eine Datenteilmenge aus der Quelltabelle in die Zieltabelle verschieben, ebenso wie zugeordnete Teile von ausgerichteten indizierten Sichten. Die Integrität der Datenauflistung wird beibehalten, da alle der Quelltabelle zugeordneten Objekte (z. B. Tabellen, Indizes und indizierte Sichten) in den Partitionswechsel eingeschlossen werden. Durch die Verwendung von indizierten Sichten in einer partitionierten Tabelle können Sie die Leistung von Aggregatabfragen, die auf die Tabelle verweisen, erheblich erhöhen.