La Función de Particionamiento, el primer paso
Lo primero que deberemos hacer antes de poder particionar tablas e índices en SQL Server, es crear una Función de Particionamiento, la cual, tomará como entrada un único campo de la tabla a particionar (ojo, que puede valer también una columna calculada - computed column), e identificará los valores frontera que definirán el conjunto de particiones deseado. Esto se ve muy claro con un ejemplo:
|
CREATE PARTITION FUNCTION [pfDatos] (datetime)
AS RANGE RIGHT FOR VALUES
('20100101','20100201','20100301');
GO
|
La anterior Función de Particionamiento define tres valores frontera para un valor de entrada de tipo fecha, por lo tanto, tenemos las siguientes cuatro particiones:
- Partición 1: Valores < 20100101 (Diciembre 2010 y meses anteriores)
- Partición 2: 20100101 <= Valores < 20100201 (Enero 2010)
- Partición 3: 20100201 <= Valores < 20100301 (Febrero 2010)
- Partición 4: 20100301 <= Valores (Abril 2010 y meses posteriores)
A nivel de sintaxis, el siguiente ejemplo es bastante representativo. Podemos cambiar la palabra reservada RIGHT por LEFT, lo cual sirve para indicar qué partición deseamos que contenga cada valor frontera (la partición que queda a su izquierda o la que queda a su derecha). No hay mayor secreto.
Una vez creada una Función de Particionamiento, es posible modificarla para añadir o quitar particiones, utilizando ALTER PARTITION FUNCTION SPLIT o ALTER PARTITION FUNCTION MERGE (añadiremos o quitaremos particiones de una en una, no hay sintaxis para afectar a varias particiones en un único ALTER). Es importante entender las funciones de particionamiento como algo dinámico, que podemos modificar con el paso del tiempo, para ajustar a las necesidades actuales de cada momento.
Deberemos tener en cuenta, que la ejecución de estas operaciones (SPLIT y MERGE) está especialmente recomendado sobre particiones vacías, aprovechando en dicho caso una mejora de rendimiento, propia de poder evitar escanear las páginas de datos de las particiones para mover los datos correspondientes. Pero ahora no es el mejor momento de contar mayores detalles de esto.
El Esquema de Particionamiento, el segundo paso
Ahora es necesario crear el Esquema de Particionamiento, el cual, nos permitirá asignar a cada partición, el Grupo de Ficheros (FileGroup) que deseamos que utilice para almacenar sus datos. Téngase en cuenta, que al crear el Esquema de Particionamiento, tenemos que asociarlo a una Función de Particionamiento. No es necesario que cada partición utilice un Grupo de Ficheros (FileGroup) exclusivo para ella. De hecho, en muchos casos, se utiliza un único FileGroup para todas las particiones. La decisión, es una cuestión de Diseño, que habrá que analizar en cada caso.
A continuación, se muestra un ejemplo sencillo, a través del cual se asignan todas las particiones a un mismo Grupo de Ficheros (FileGroup).
|
CREATE PARTITION SCHEME [psDatos]
AS PARTITION [pfDatos] ALL TO ([PRIMARY]);
|
Sin embargo, también es posible asignar cada partición a un Grupo de Ficheros (FileGroup) específico, sean todos los Grupos de Ficheros distintos, o incluso repitiendo algunos Grupo de Ficheros (FileGroup). Hasta es posible crear el Esquema de Particionamiento especificando Grupos de Ficheros de sobra (ej: la Función de Particionamiento define cuatro particiones, y el Esquema de Particionamiento define cinco o más Grupos de Ficheros). Veamos un ejemplo:
|
CREATE PARTITION SCHEME [psDatos]
AS PARTITION [pfDatos] TO (FG1, FG1, FG1, FG2, FG3); |
En el anterior ejemplo, hemos repetido varias veces el Grupo de Ficheros FG1, y además hemos indicado cinco Grupos de Ficheros, cuando la Función de Particionamiento tan sólo define cuatro particiones. Esto podría servir, si en un futuro creamos una nueva partición (con ALTER PARTITION FUNCTION SPLIT), de tal modo, que dicha nueva partición utilizará el Grupo de Ficheros que sobra.
Si tenemos varios Grupos de Ficheros de sobra, sólo uno estará marcado como NEXT USED. Si no tenemos ningún Grupo de Ficheros de sobra, podemos añadir un Grupo de Ficheros adicional a nuestro Esquema de Particionamiento utilizando la sentencia ALTER PARTITION SCHEME, o si lo deseamos, podemos reutilizar un Grupo de Ficheros ya utilizado en el Esquema de Particionamiento, y con la sentencia ALTER PARTITION SCHEME marcarlo como NEXT USED.
El detalle del Grupo de Ficheros (FileGroup) marcado como NEXT USED tiene gran importancia, ya que antes de poder ejecutar un SPLIT, es necesario que un Grupo de Ficheros esté marcado como NEXT USED. Ejemplo: Creamos un Función de Particionamiento, y seguidamente, creamos un Esquema de Particionamiento para la misma, mapeando todas las particiones al mismo Grupo de Ficheros. En la creación de este Esquema de Particionamiento, se marcará como NEXT USED el Grupo de Fichero especificado en su creación, por lo que podremos realizar un SPLIT sin problemas. Sin embargo, si intentamos realizar un segundo SPLIT, ningún Grupo de Ficheros estará marcado como NEXT USED, por lo que fracasará, debiendo ejecutar un ALTER PARTITION SCHEME antes de poder realizar el SPLIT. Moraleja: Al utilizar un Esquema de Particionamiento que mapea todas las particiones al mismo Grupo de Ficheros, después de cada SPLIT se debe realizar un ALTER PARTITION SCHEME para marcar el NEXT USED.
Crear las tablas e índices particionados
Ahora ya estamos en situación de poder crear tablas e índices particionados, indicando en su creación qué Esquema de Particionamiento deseamos utilizar y el campo empleado para particionar. Existen multitud de posibilidades. Por ejemplo, podemos tener una tabla particionada con un Esquema de Particionamiento, y cada uno de sus índices, particionados con otros Esquemas de Particionamiento diferentes, por el mismo, o sin particionar. De nuevo es una cuestión de diseño, cuyo detalle se escapa del alcance del presente artículo.
Como ejemplo, vamos a incluir la creación de una tabla con un índice, de tal modo que ambos objetos utilizan el mismo Esquema de Particionamiento. Esto es uno de los requisitos para poder realizar un movimiento rápido de particiones entre tablas particionadas con ALTER TABLE SWITCH PARTITION (hay muchos más requisitos, como que las tablas origen y destino deben mantener exactamente el mismo esquema, los mismos campos y en el mismo orden, nulidades de campos, etc.).
|
CREATE TABLE dbo.tblDatos
(
ID UNIQUEIDENTIFIER NOT NULL
,DESCRIPCION VARCHAR(200) NOT NULL
,FECHA DATETIME NOT NULL
,CONSTRAINT PK_tblDatos PRIMARY KEY NONCLUSTERED (ID, FECHA) ON psDatos(FECHA)
) ON psDatos(FECHA);
|
Ahora que ya tenemos una tabla particionada, podemos insertar datos en ella. Si deseamos poder conocer en qué partición se almacena cada fila, podemos utilizar la función $PARTITION, como se muestra en el siguiente ejemplo.
|
SELECT *, $PARTITION.pfDatos(FECHA) [Partition] FROM dbo.tblDatos |
Si deseamos mover una partición de una tabla particionada a otra, podemos utilizar la sentencia ALTER TABLE SWITCH PARTITION, pero antes de nada, deberemos revisarnos bien la documentación, ya que como comentamos antes esto conlleva varios requisitos (ej: las tablas origen y destino deben tener exactamente el mismo esquema, la partición destino debe estar vacía, la partición origen debe ser un subconjunto de la partición origen, etc). Así, si cumplimos todos los requisitos, podremos utilizar la sentencia ALTER TABLE SWITCH PARTITION indicando cuál es la tabla y partición origen, y también la tabla y partición destino. A continuación se muestra un ejemplo.
|
ALTER TABLE dbo.tblDatos SWITCH PARTITION 2 TO dbo.tblDatosHistoricos PARTITION 2 |
Tenemos más posibilidades, como por ejemplo, es posible realizar un ALTER TABLE SWITCH PARTITION para mover una tabla existente no particionada, a una partición de una tabla particionada existente. Esto por ejemplo, resulta de gran utilidad para realizar cargas rápidas de datos en entornos de Data Warehouse con SELECT INTO y ALTER TABLE SWITCH PARTITION (ej: tenemos una tabla de hechos particionada, y para cargar los datos de un nuevo periodo de tiempo, realizamos un SELECT INTO para crear una tabla con los datos que deseamos cargar, y seguidamente, hacemos el ALTER TABLE SWITCH PARTITION para asignar la tabla recién creada como una partición más de la tabla de hechos: la ventaja, la mejora de rendimiento de la operación de registro mínimo propia del SELECT INTO, y la inmediatez del ALTER TABLE SWITCH PARTITION).
Código de ejemplo y Vídeo DEMO
Para finalizar, aprovecho para colgar tanto una presentación como un código de ejemplo, el cual incluye comentarios autoexplicativos. Dicho código, crea un par de tablas particionadas, con sus correspondientes Funciones de Particionamiento y Esquemas de Particionamiento, inserta datos de ejemplo, mueve una partición de una tabla a otra con ALTER TABLE SWITCH PARTITION, y reajusta las particiones haciendo los SPLIT y MERGE necesarios sobre las Funciones de Particionamiento. Tanto la presentación como el código de ejemplo, están disponibles para descarga a través del siguiente ZIP.
El video es una demostración en vivo del código de ejemplo adjunto, ejecutado paso a paso sobre una base de datos recién creada. Este es el primer Video que se cuelga en Portal GuilleSQL, y de momento, la verdad que no hemos conseguido la calidad de video que queríamos, pero al intentar generarlo con mayor calidad, el portátil no daba de sí... Sorrys, para el próximo video, intentaremos que quede mejor !!
Poco más. Como siempre, espero que os guste.
