Поделиться через

  • Статья

Переключение секций при наличии индексированных представлений

Секционирование данных позволяет эффективно и быстро управлять подмножествами данных и получать доступ к ним, сохраняя при этом целостность всего набора данных. Определение индексированных представлений секционированных данных может еще больше повысить скорость и эффективность запросов. Такие представления называются индексированными представлениями, выровненными по секциям.

Индексированное представление секционируется так же, как и соответствующая таблица, если выполняются следующие условия.

  • Функции секционирования индексов индексированного представления и таблицы:

    • В указанных таблицах имеется одинаковое количество секций.

    • В указанных таблицах секции имеют одинаковые граничные значения.

    • Аргументы функций секционирования должны быть в одном и том же в столбце.

  • Столбец секционирования секционированной таблицы содержится в проекционном списке определения представления (в отличие от случая, когда столбец секционирования содержится в выражении).

  • Если определение представления задает группирование, столбец секционирования является одним из столбцов группирования, заданных в определении представления.

  • Если представление ссылается на несколько таблиц (используя соединения, подзапросы, функции и т. д.), то индексированное представление выравнивается по секциям с только одной из секционированных таблиц.

В SQL Server 2008 индексированные представления, выровненные по секциям, могут быть переключены вместе с секционированными таблицами, на которых они определены. Операции обслуживания для секций или поднаборов данных выполняются теперь более эффективно, так как нацелены только на нужные данные, а не на всю таблицу. Дополнительные преимущества секционирования данных с индексированными представлениями описаны далее.

  • Автоматическое обслуживание. SQL Server 2008 автоматически обновляет индексированные представления, когда выполняются инструкции INSERT, UPDATE или DELETE.

  • Улучшенная статистика.Производительность статистических запросов может значительно возрасти, если индексированное представление обеспечит достаточное уменьшение количества строк благодаря статистической обработке. В выпуске SQL Server 2008 Enterprise, если оптимизатор запросов автоматически связывает статистический запрос с соответствующим индексированным представлением или если запрос прямо ссылается на индексированное представление с помощью подсказки NOEXPAND, производительность запроса может быть гораздо выше, чем у запроса ко всей таблице.

Ограничения для инструкции ALTER TABLE...SWITCH с индексированными представлениями

Если инструкция Transact-SQL ALTER TABLE...SWITCH используется для передачи поднаборов данных между исходной и целевой таблицами, на которые ссылается индексированное представление, таблицы и индексы должны отвечать требованиям, указанным в разделе Эффективная передача данных с использованием переключения секций. См. раздел «Требования к таблицам и индексам» в этой теме.

Дополнительные сведения о перемещении секций с заданными индексированными представлениями см. в подразделе «Дополнительные требования к перемещению секций» раздела Эффективная передача данных с использованием переключения секций.

Типичным сценарием, где могут использоваться индексированные представления, выровненные по секциям, является сценарий с большим хранилищем данных. В большом хранилище данных большая таблица фактов обычно секционирована по датам, а статистические запросы часто выполняются по таблице фактов, соединенной с несколькими таблицами измерений. Включение или исключение секций таблицы с помощью инструкции ALTER TABLE … SWITCH может дать следующие преимущества:

  • Быстрая архивация устаревших данных самой старой секции таблицы фактов.

  • Быстрое включение новых данных в самую новую секцию большой таблицы фактов.

Следующий рисунок показывает, как работают индексированные представления, выровненные по секциям, когда новые данные вносятся в самую новую секцию таблицы фактов. Новая секция, включаемая в таблицу фактов, несет за собой статистическое выражение, которое создается для новой секции.

Переключение индексированных представлений, выровненных по секциям

Примеры

Следующие примеры показывают, как использовать инструкцию ALTER TABLE … SWITCH для исключения старой секции таблицы, вместе с которой уходит и часть индексированного представления, выровненного по секциям.

В этом примере таблица фактов [f_sales] содержит данные о продажах за 2006 год. Данные о продажах секционируются по кварталам. Часто запросы формируют сводные данные об объемах продаж по датам и магазинам, а для ускорения статистического запроса, который был определен для таблицы фактов, создается индексированное представление. Предположим, что данные за первый квартал 2006 года должны быть перенесены в архивную таблицу, в то время как индексированное представление для таблицы фактов остается правильным и может использоваться.

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

Как показано в предыдущем примере, после переключения секции все данные из секции 1 таблицы [f_sales] и индексированное представление [v_f_sales_sumamt] переносятся в соответствующую таблицу [sales_archive] и индексированное представление [v_sales_archive_sumamt].

Данные о сумме продаж за июль 2006 года по датам и магазинам можно получить с помощью запроса либо к таблице [f_sales], либо индексированному представлению [v_f_sales_sumamt], как показано в следующих двух запросах. Результаты в обоих случаях будут одинаковы, но запрос к индексированному представлению выполняется значительно быстрее, так как индексированное представление материализует предварительно вычисленные статистические выражения и сокращает число строк на порядок, как показано в следующем примере.

-- 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

Переключение секции с помощью инструкции ALTER TABLE…SWITCH — это быстрая операция, применимая только к метаданным. Если индексированные представления индексируются так же, как исходная и целевая таблицы, то переключение секций позволяет переносить поднаборы данных из исходной таблицы в целевую, а также связывать части выровненных индексированных представлений. Целостность набора данных обеспечивается тем, что все объекты, связанные с исходной таблицей (такие как таблицы, индексы и индексированные представления), переключаются вместе с секцией. С помощью индексированных представлений для секционированной таблицы можно значительно ускорить выполнение статистических запросов, ссылающихся на таблицу.