Particionamiento de tablas en SQLServer 2005

Particionamiento de tablas en SQLServer 2005
Microsoft SQL Server
josimac Sun, 05/18/2008 - 01:05

Hola a todos.

Es sabido por todo el mundo de las capacidades de particionamiento que posee Oracle desde sus mas antiguos releases. En Sql Server 2000 existia una especie de "chapuza" para poder hacerlo mediante restricciones "CHECK" en los campos y utilizando vistas mediante UNIONS.

El panorama en SQL Server 2005 a mejorado un poco respecto a eso.
No puedo dar fe absoluta sobre las ventajas de utilizar dicho particionamiento (lo usé en una tabla de hechos con pocos millones de registros) pero al menos es una tecnología más reciente que las "vistas".

PASO 1: Crear una función de partición
CREATE PARTITION FUNCTION [MiFuncionDeParticion] (char(15))
AS RANGE LEFT FOR VALUES ('VALOR1', 'VALOR2', 'VALOR3')

PASO 2: Crear un schema para la partición con el grupo de archivos SQL a utilizar
CREATE PARTITION SCHEME [MiNombreDeSchema]
AS PARTITION [MiFuncionDeParticion] ALL TO ([PRIMARY]) <------- Nombre del grupo de archivos de SQL Server.

PASO 3: Se crea la tabla que va a ser particionada
CREATE TABLE [dbo].[MiTabla] (
[CAMPO1] [int] NOT NULL,
[CAMPO2] [int] NOT NULL,
[CAMPO3] [char](15) COLLATE Modern_Spanish_CI_AS NOT NULL,
[CAMPO4] [char](15) COLLATE Modern_Spanish_CI_AS NOT NULL,
[CAMPO5] [float] NULL
) ON [MiNombreDeSchema] ([CAMPO3])

Existen mas valores de configuración para realizar este tipo de particiones.
Si alquien quiere saber mas...... en la documentación de SQL hay más información y si no, pues podeis preguntar lo que querais.

Saludos,

Submitted by Anonimo (not verified) on Thu, 03/04/2010 - 19:44

Yo tengo una tabla con 45 millones de registros. Como hago para hacer la particíon con SQL Server 2005? En el ejemplo veo que creas la tabla pero se puede hacer sobre una ya creada?

Submitted by Carlos on Fri, 03/05/2010 - 22:51

Las tablas particionadas almacenan los registros separados por particiones, por lo que pasar de una tabla normal a una particionada implicaría mover físicamente todos los datos. No creo que exista ninguna opcion de ALTER TABLE que te permita hacer esto, y si la hubiera seguramente lo que haría es recrear la tabla completa.

Precisamente por el gran volumen de registros que tienes yo te recomendaría crear una tabla vacía particionada, y hacer después una migración de los datos insertándolos en esta tabla particionada, y sin tocar la original, por si algo no sale como esperabas.

Esto además, dependiendo de las condiciones del entorno, te permitirá hacer alguna prueba de rendimiento sobre ambas tablas para comprobar que el particionamiento realmente te beneficia.

Ya nos explicarás..

 

Submitted by josimac on Sun, 03/21/2010 - 22:23

[quote=carlos]

Las tablas particionadas almacenan los registros separados por particiones, por lo que pasar de una tabla normal a una particionada implicaría mover físicamente todos los datos. No creo que exista ninguna opcion de ALTER TABLE que te permita hacer esto, y si la hubiera seguramente lo que haría es recrear la tabla completa.

Precisamente por el gran volumen de registros que tienes yo te recomendaría crear una tabla vacía particionada, y hacer después una migración de los datos insertándolos en esta tabla particionada, y sin tocar la original, por si algo no sale como esperabas.

Esto además, dependiendo de las condiciones del entorno, te permitirá hacer alguna prueba de rendimiento sobre ambas tablas para comprobar que el particionamiento realmente te beneficia.

Ya nos explicarás..

 

[/quote]

Exacto...
Particionar una tabla que ya contiene registros los veo una temeridad y más con semejante volumen de registros.
En cualquier caso, creo que no se puede hacer sobre una tabla ya existente.

Saluditos,

Submitted by Anonimo (not verified) on Tue, 04/06/2010 - 20:55

Ok.

Voy a realizar la prueba.

Se puede realizar partición sobre cualquier tipo de datos?

Por ej me serviria realizar la particion por un campo "usuario" que es un varchar.

Si me aparecen nuevos usuarios en la tabla, puedo agregar nuevas particiones?

Submitted by Carlos on Wed, 04/07/2010 - 11:27

Para particionar la tabla has de definir los rangos en los que se han de agrupar los registros. Para ello se utiliza la función de particionamiento, que te permite definir estos grupos a partir de los valores de campos de los registros.

Estos campos pueden ser de diferentes tipos de datos. Puedes utilizar campos varchar, de fecha, enteros, etc.

Los nuevos registros entrarán en la partición que les toque según tengas definida la función de particionamiento.

Lo normal es crear rangos, por fechas, o por orden alfabético o numérico.

Aunque se puede hacer, yo no te aconsejaría crear una partición para cada usuario. Primero por lo que tu mismo dices, con cada nuevo usuario tendrías que crear una nueva partición. Segundo, seguro que la distribución de registros por usuario no es muy homogénea, lo más eficiente es que no haya demasiadas diferencias de volumen entre las particiones.

Submitted by josimac on Mon, 05/31/2010 - 11:30

Hola Xóchitl Selene

Los valores que pongas en la función de partición son utilizados como rangos SIEMPRE.
Es decir, si miras el siguiente ejemplo:

Crear una función de partición
CREATE PARTITION FUNCTION [MiFuncionDeParticion] (char(1))
AS RANGE RIGHT FOR VALUES ('A', 'G', 'M', 'Z')

Se comportará creando 5 grupos:
1.) < 'A'
2.) >= 'A' y < 'G' (es decir, de la A a la F)
3.) >= 'G' y < 'M'
4.) >= 'M' y < 'Z'
5.) >= 'Z'

Esto te funcionará seguro.

Saludos,

Submitted by Xóchitl Selene (not verified) on Mon, 05/31/2010 - 17:31

Muchas gracias josimac me ha servido mucho :).

 

Tengo otra consulta..

Para hacer un particionamiento vertical como sería??.. Entiendo en teoría q ese particionamiento es para dividir una tabla en varias con menos campos en una relación 1:1, y que juntándolas se forma la misma tabla inicial.. Pero esto significa crear nuevas tablas con los campos divididos??.. No se puede hacer esto de forma lógica tal como se hace en el particionamiento horizontal?? o cómo podría hacerlo para no tener q crear nuevas tablas?

 

De antemano muchas gracias..

Submitted by josimac on Mon, 05/31/2010 - 20:09

Hola de nuevo.

Si me permites la pregunta, ¿para que quieres hacer algo así?
Desde el punto de vista transaccional vas ha incrementar las escrituras en bbdd.
Desde el punto de vista de reporting o dw te verás en la obligación de hacer un diseño lógico del tipo snow-flake con lo que eso comporta (más joins para desnormalizar las tablas).

Si lo que quieres es "particionar" esos datos para , por ejemplo, crear diferentes orígenes de datos que alimenten un cubo OLAP puedes hacerlo mediante vistas de la tabla.

Si nada de que lo que te he dicho te convence.... entonces necesito que me contestes la primera pregunta que te hago.

Saludos,

Submitted by Xóchitl Selene (not verified) on Mon, 05/31/2010 - 20:37

 

 

Gracias. Es parte de una práctica de bases de datos distribuidas :). Supongo que tendré que recurrir a la creación de las nuevas tablas.

Por otra parte, regresando al script de particionamiento.. despues de crear la funcion y el esquema de partición he intentado crear la tabla de la siguiente manera:

 

CREATE TABLE articulos
(
  ArtID int identity(1,1) NOT NULL,
  ArtLetra char(1) COLLATE Modern_Spanish_CI_AS NOT NULL,
  CONSTRAINT PK_Art PRIMARY KEY(ArtID)
) ON practicalAbcDistributionScheme(ArtLetra);
GO

 

pero al ejecutar me muestra el siguiente mensaje:

 

La columna 'ArtLetra' es una columna de particionamiento del índice 'PK_Art'. Las columnas de particionamiento de un índice único deben ser un subconjunto de la clave de índice.

 

--

 

le quito la linea que hace referencia al campo llave (  CONSTRAINT PK_Art PRIMARY KEY(ArtID) )

y el script es ejecutado de manera correcta.. por qué sucede eso??

 

Mi solución es declarar los campos sin llaves y después modificarlas para indicarles los campos llave.. pero como se hace para no tener que hacerlo así?

 

 

 

 

 

Submitted by josimac on Tue, 06/01/2010 - 09:54

Cuando declaras el campo "ArtID" como autoincremental le estás asignando una primary key. Cuando la función de particionamiento genere más de una tabla vas a tener codigos iguales en distintas tablas que no van a servirte para nada.

Si quieres utilizar claves surrogadas las vas a tener que gestionar en tiempo de carga, es decir, calcularlas tu. Creo que se queja de eso.

Submitted by Javi (not verified) on Wed, 05/05/2010 - 19:22

Hola,

 

Mi caso es parecido sólo que las particiones están en función de la fecha. Tenemos una tabla ya particionada con unos 40 mill de registros. El caso es que sólo debemos guardar 6 meses, quizás me explico mejor con un ejemplo. Pongamos que estamos en Junio del 2010, mi tabla tendrá 6 particiones ordenadas por fecha que serán las correspondientes a enero, febrero, marzo, ..., y junio, pero al llegar julio, debemos desprendernos de enero para así seguir teniendo 6 particiones que irían desde febrero hasta julio.

¿Me podríais orientar cómo puedo hacer este proceso de reciclado de particiones?

 

Muchas gracias.

Submitted by Carlos on Fri, 05/07/2010 - 17:05

Tendrás que hacerlo mediante un script que elimine la partición del mes más antiguo, y cree la del nuevo mes.

Es fácil decirlo, pero no tan trivial de implementar. La eliminación de la partición de ha de hacer de una manera eficiente, sobretodo la tabla particionada es grande, como es tu caso. Hay que evitar que se termine haciendo un delete de cada registro.

Te enlazo algunas entradas del blog de Dan Guzman, que presenta una solución para mover la partición a una tabla auxiliar, sobre la que después se puede hacer un truncate, e incluye los scripts para hacerlo:

Sliding Window Table Partitioning

Automating Sliding Window Maintenance

Automating RANGE RIGHT Sliding Window

Te enlazo de paso Partition Details and Row Counts, que te puede ser de utilidad para obtener información de las particiones.

Submitted by harold (not verified) on Mon, 08/24/2015 - 20:34

quiero que me ayudes en un examen k tengo pero la ocacion es k no e ido a clases por motivos problemas ....