Compartir a través de

  • Artículo

Conmutar particiones al definir vistas indizadas

El particionamiento de datos permite administrar y tener acceso a subconjuntos de los datos de forma rápida y eficaz, a la vez que mantiene la integridad de la totalidad de la colección de datos. Definir las vistas indizadas en datos divididos en particiones puede aumentar aún más la velocidad y la eficacia de sus consultas. Estas vistas definidas se llaman vistas indizadas alineadas por partición.

Una vista indizada está alineada por partición con la tabla a la que hace referencia si se dan las condiciones siguientes:

  • Las funciones de partición de los índices de la vista y la tabla indizadas:

    • Definen el mismo número de particiones.

    • Definen los mismos valores de límite para las particiones.

    • Los argumentos de las funciones de partición deben ser la misma columna.

  • La lista de proyecciones de la definición de vista incluye la columna de particionamiento (en lugar de una expresión que incluye la columna de particionamiento) de la tabla con particiones.

  • Allá donde la definición de vista realiza una agrupación, la columna de particionamiento es una de las columnas de agrupación incluida en la definición de la vista.

  • Allá donde la vista hace referencia a varias tablas (utilizando uniones, subconsultas, funciones, etc.), la vista indizada está alineada por partición con sólo una de las tablas con particiones.

En SQL Server 2008, las vistas indizadas alineadas por partición se pueden intercambiar junto con las tablas con particiones contra las que se definen las vistas. Las operaciones de mantenimiento que se realizan sobre particiones o subconjuntos de datos se realizan ahora de forma más eficaz porque estas operaciones sólo afectan a los datos necesarios en lugar de afectar a toda la tabla. Éstas son algunas ventajas adicionales obtenidas al crear particiones de los datos con vistas indizadas:

  • Mantenimiento automático. SQL Server 2008 mantiene automáticamente las vistas indizadas al ejecutar instrucciones INSERT, UPDATE o DELETE.

  • Mejora de la agregación.Se puede mejorar significativamente el rendimiento de la consulta de agregado si la vista indizada proporciona una reducción de la fila a través de la agregación lo bastante grande. En SQL Server 2008 Enterprise, cuando el optimizador de consultas asigna automáticamente la consulta de funciones agregadas a la vista indizada adecuada o la consulta hace referencia directamente a la vista indizada utilizando una sugerencia NOEXPAND, el rendimiento de las consultas puede ser más eficaz que cuando la consulta tiene acceso a toda la tabla.

Restricciones al utilizar la instrucción ALTER TABLE...SWITCH con vistas indizadas

Al utilizar la instrucción Transact-SQL ALTER TABLE...SWITCH para transferir subconjuntos de datos entre las tablas de origen y destino referenciadas por una vista indizada, las tablas e índices deben cumplir las condiciones enumeradas en Transferir datos de forma eficaz con cambios de particiones. Vea la sección Requisitos de tablas e índices de ese tema.

Para obtener más información sobre cómo mover las particiones con vistas de índice definidas, vea la sección "Requisitos adicionales para mover particiones" de Transferir datos de forma eficaz con cambios de particiones.

Un escenario típico en el que se pueden utilizar las vistas indizadas alineadas por partición es el de un almacén de datos grande. En un almacén de datos grande, las tabla de hechos grandes se suelen dividir en particiones por fecha, y las consultas de agregados a menudo se ejecutan contra la tabla de hechos combinada con tablas de varias dimensiones. Intercambiar una partición utilizando la instrucción ALTER TABLE ... SWITCH puede proporcionar las ventajas siguientes:

  • Archivar con rapidez los datos antiguos sacándolos de la partición más antigua de la tabla de hechos.

  • Introducir con rapidez datos nuevos en la partición más reciente de la tabla de hechos grande.

La ilustración siguiente muestra cómo trabaja una vista indizada alineada por partición cuando se introducen nuevos datos en la partición más reciente de la tabla de hechos. La nueva partición introducida en la tabla de hechos lleva consigo la agregación que se crea en la nueva partición.

Cambio de vistas de índices alineadas de partición

Ejemplos

El ejemplo siguiente muestra cómo utilizar la instrucción ALTER TABLE ... SWITCH para sacar una partición antigua de una tabla que lleva parte de una vista indizada alineada por partición con ella.

En este ejemplo, una tabla de hechos de ventas [f_sales] contiene un año de datos de ventas correspondientes al año que 2006. Se crean particiones de los datos de ventas por trimestres. Se ejecutan con frecuencia consultas que calculan la cantidad total de ventas por fecha y por almacén, y se crea una vista indizada para acelerar la consulta de agregados definida en la tabla de hechos. Ahora, los datos del primer trimestre de 2006 van a archivarse en una tabla de archivo, mientras que la vista indizada de la tabla de hechos se conserva correcta y utilizable.

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

Como se muestra en el ejemplo anterior, una vez intercambiada la partición, todos los datos de la partición 1 de la tabla [[f_sales] y de la vista indizada [v_f_sales_sumamt] se mueve a la tabla correspondiente [sales_archive] y a la vista indizada [v_sales_archive_sumamt].

Para obtener la cantidad de ventas por fecha y por almacén durante julio 2006, puede consultar la tabla [f_sales] o bien la vista indizada [v_f_sales_sumamt], tal como se muestra en las dos consultas siguientes. El resultado es el mismo en ambos casos, pero la ejecución sobre la vista indizada logra una significativa mejora del rendimiento de las consultas porque la vista indizada materializa los agregados precalculados y reduce el número de filas en un factor de 10, tal como se muestra en el ejemplo siguiente.

-- 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 conmutación de particiones utilizando la instrucción ALTER TABLE…SWITCH es una operación rápida y que sólo afecta a los metadatos. Cuando las vistas indizadas están alineadas por partición con las tablas de origen y de destino, la conmutación de particiones le permite llevar un subconjunto de los datos de la tabla de origen a la tabla de destino, y también asocia partes de las vistas indizadas alineadas. Se mantiene la integridad de la recopilación de datos porque todos los objetos asociados a la tabla de origen (tales como tablas, índices y vistas indizadas) están incluidos en el modificador de la partición. El uso de vistas indizadas en una tabla con particiones permite aumentar significativamente el rendimiento de las consultas de funciones agregadas que hacen referencia a la tabla.