Partition Switching When Indexed Views Are Defined
Partycjonowanie danych pozwala na zarządzanie i szybko i efektywnie dostępu podzbiór danych przy jednoczesnym zachowaniu spójności zbierania danych.Definiowanie widoków indeksowanych na podzielonym na partycje danych dalej może zwiększyć szybkość i wydajność kwerend.Te widoki zdefiniowane są nazywane Widoki indeksowane wyrównany do partycji.
Widok indeksowany jest partycja wyrównane z tabelą się odwołuje, jeśli są spełnione następujące warunki:
Funkcje partycji indeksów widok indeksowany i tabela:
Określić tę samą liczbę partycji.
Należy zdefiniować te same wartości graniczne dla partycji.
Argumenty funkcji partycji musi być w tej samej kolumnie.
Lista rzutowania definicji widoku zawiera kolumna partycjonowania (w przeciwieństwie do wyrażenie zawierającego kolumna partycjonowania) z tabela partycjonowana.
W przypadku, gdy definicja widoku wykonuje grupowanie, kolumna partycjonowanie na partycje jest jedną z kolumn grupowanie, znajdujących się w definicji widoku.
W przypadku, gdy kilka tabel (za pomocą sprzężeń, podkwerend, funkcje i tak dalej) odwołuje się do widoku, widok indeksowany jest partycja wyrównaną tylko jednej z tabel podzielonym na partycje.
W SQL Server 2008, mogą być przełączane wyrównany do partycji Widoki indeksowane wraz z tabelami podzielonym na partycje w widokach są określone przed. Operacji konserwacji na partycje lub podzbiór danych są teraz wykonać bardziej efektywne, ponieważ te operacje miejsce docelowe tylko dane, które jest wymagane, a nie całą tabela.Dodatkowe korzyści do partycjonowanie na partycje danych z Widoki indeksowane są następujące:
Automatic maintenance.SQL Server 2008 automatically maintains indexed views when INSERT, UPDATE, or DELETE statements are run.
Poprawa agregacja.kwerenda agregująca można znacznie poprawić wydajność, jeśli widok indeksowany zawiera za mało redukcję wiersza do agregacja.W SQL Server 2008 Flaga, gdy optymalizator kwerendy automatycznie dopasowuje agregacja kwerendę do odpowiedniego indeksowane widoku lub kwerendy bezpośrednio dotyczy widok indeksowany, za pomocą wskazówkę dotyczącą NOEXPAND, wydajność kwerendy mogą być bardziej efektywne niż gdy kwerenda uzyskuje dostęp do całej tabela.
Ograniczenia podczas korzystania z ALTER tabela... przełącznik wyciągu z widoków indeksowanych
Podczas korzystania z Transact-SQL Instrukcja ALTER tabela... przełącznik transferu podzbiory danych między źródłową i docelową tabele, do których odwołuje się widok indeksowany, tabele oraz indeksy muszą spełniać warunki wymienione w Wydajne przesyłania danych przy użyciu przełączania partycji. Zobacz wymagania dotyczące indeksu i tabela części tego tematu.
Aby uzyskać więcej informacji na temat przenoszenia partycji z widoków zdefiniowanych indeksu zobacz sekcję "Dodatkowe wymagania dla przenoszenia partycji" z Wydajne przesyłania danych przy użyciu przełączania partycji.
Typowy scenariusz, w którym można użyć widoków indeksowanych wyrównany do partycji jest z magazyn danych duże.W magazynie danych duże, duże tabela fakt zazwyczaj na partycje według data, i są często wykonywane kwerendy agregującej, tabela fakt łączenie się z kilku tabel wymiarów.Przełączanie na partycję lub przy użyciu instrukcja ALTER tabela … SWITCH może przynieść następujące korzyści:
Szybko archiwizować przeterminowanych danych z partycji najstarsze tabela fakt.
Szybko przenieść nowe dane do najnowszych partycji dużych tabela fakt.
Na poniższej ilustracji przedstawiono działanie widok indeksowany wyrównany do partycji na nowych danych jest umieszczany w partycji najnowsze tabela fakt.Nowa partycja, która jest włączane do tabela fakt powoduje wzdłuż agregacja, która jest tworzona na nowej partycji.
.gif "Partition-Aligned Index Views Switching")
Przykłady
W poniższym przykładzie opisano za pomocą instrukcja ALTER tabela … PRZEŁĄCZYĆ się do przełączania się starej partycji tabela, która zapewnia część wyrównany do partycji widok indeksowany wraz z nim.
W tym przykładzie sprzedaży tabela faktów [f_sales] zawiera jeden rok danych o sprzedaży dla danych sprzedaży 2006.The roku jest podzielony na partycje na kwartał. Kwerendy są często uruchamiane tego agregatu kwoty sprzedaży według data i Magazyn, a widok indeksowany jest tworzony do przyspieszenia kwerenda agregująca, które zdefiniowano w tabela fakt.Teraz pierwszy kwartał dane 2006 jest mają być archiwizowane do tabela archiwalnej, natomiast widok indeksowany w tabela fakt zachowuje poprawne i użyteczne.
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
Jak pokazano w poprzednim przykładzie po przełączył partycji, wszystkie dane w partycji 1 tabela [f_sales] i [widok indeksowanyv_f_sales_sumamt] są przenoszone odpowiadające im [tabelasales_archive] i [widok indeksowanyv_sales_archive_sumamt].
Aby kwoty sprzedaży według dat przez Magazyn lipca 2006 r.), można wysyłać kwerendy dla każdej tabela [f_sales] lub [widok indeksowanyv_f_sales_sumamt], jak pokazano w następującym dwie kwerendy. Wyniki są takie same w obu przypadkach, ale uruchomiony przed widok indeksowany, znacznie poprawia wydajność kwerendy, ponieważ widok indeksowany materializes zagregowanych wstępnie obliczane i zmniejsza liczbę wierszy o 10, jak to pokazano w poniższym przykładzie.
-- 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
Partycja przełączania przy użyciu instrukcja ALTER tabela…SWITCH jest szybkie, tylko do metadane operacji.Gdy widoki indeksowane są partycji wyrównaną urządzenie źródłowe i tabela miejsce docelowe, przełączanie partycji pozwala przełączyć podzbiór danych z urządzenie źródłowe tabela do tabela miejsce docelowe, a także skojarzona części wyrównany widoków indeksowanych.Integralność zbierania danych zostanie zachowane, ponieważ wszystkie obiekty, które są skojarzone z tabela źródłowej (takich jak tabele, indeksów i widoki indeksowane) są uwzględniane w przełączniku partycji.Przy użyciu widoków indeksowanych na podzielonym na partycje tabela, można znacznie zwiększyć wydajność kwerendy agregującej, które odwołują się do tabela.