Actividad #16 particiones de mysql y oracle

Particionamiento de Oracle.

El Particionamiento es una técnica utilizada para distribuir el almacenamiento de tablas, índices y vistas materializadas en más de estructura de almacenamiento que se administren de forma independiente. Esta división de almacenamiento es lo que llamamos Particionamiento.


¿Cuál es el beneficio de particionar?
Ya hemos escuchado el dicho "Divide y vencerás", pues eso es lo que lograremos con el Particionamiento, logrando mejorar el rendimiento, disponibilidad y mejor manejabilidad de los datos en la base de datos.

Oracle ofrece esta utilidad desde la versión 8i y desde que se introdujo con cada nueva versión han aplicado mejoras y nuevas técnicas de particionamiento.

En la imagen se muestra la tabla 1 que no esta particionada y la tabla 2 que se muestra particionada. La tabla 1 tiene se guarda en una sola partición mientras que la tabla 2 se almacena en tres particiones que agrupan la información por mes.

Este particionamiento se lo realiza utilizando una clave de particionado (Partitioning Key) que es la que identifica a que parte de la partición se almacenara el dato, en el ejemplo anterior la tabla particionada usa como clave de particionamiento el campo MES para direccionar a que partición se almacenara el dato. Esta llave sir durante la inserción o actualización del registro.

Oracle ofrece tres técnicas básicas de particionamiento según su la necesidad de la organización y es totalmente transparente para las aplicaciones que accedan a los datos y estas son:

Range Partitioning.- Esta técnica usa un rango de valores para determinar a qué partición de datos se almacenara el registro. Este es el método más común de particionar los datos y se usa a menudo como clave de partición los campos fechas.

List Partitioning.- Para esta técnica se define una lista de valores para la clave de particionado, la ventaja de este particionamiento es que podemos agrupar y organizar los datos que tengan una relación común, como por ejemplo podemos definir como clave de partición el dato de PAIS.

Hash Partitioning.- Esta se basa en un algoritmo hash sobre la columna que se definió como clave de partición, esto es una técnica que distribuye a los datos en las particiones de manera equitativa y es usado cuando no se encuentra claros criterios de particionados.

A partir de esta tres técnica básica se definieron dos técnica mas que no son nada más que la combinación de las anteriores y estas son:

Composite Partitioning.- Esta es una técnica que se compone de las tres técnicas básicas anteriormente mencionadas, con esto podemos seleccionar para un primer nivel de partición una técnica y para el otro nivel de partición otra técnica, las combinaciones que podemos realizar son:

·                     Range-Range Partitioning

·                     Range-Hash Partitioning

·                     Range-List Partitioning

·                     List-Range Partitioning

·                     List-Hash Partitioning

·                     List-List Partitioning

En la imagen que se muestra dos ejemplos particionamiento compuesto Range-Hash y Range-List.

Ejemplo del uso de la partición Range
Como describimos hace un momento, esta técnica se basa en que se asigna la partición según el rango de valores en que coincida la clave de partición. Supongamos que tenemos una tabla de ventas con información del 2009 al 2011 y deseamos realizar el particionamiento tipo rango utilizando como llave la fecha, definiendo para cada año una partición.

CREATE TABLE pos_ventas ( id_venta NUMBER, fecha DATE, valor NUMBER ) PARTITION BY RANGE (fecha) ( PARTITION pos_ventas_p1 VALUES LESS THAN (TO_DATE('01/01/2010','DD/MM/YYYY')) TABLESPACE ts_datos2009, PARTITION pos_ventas_p2 VALUES LESS THAN (TO_DATE('01/01/2011','DD/MM/YYYY')) TABLESPACE ts_datos2010, PARTITION pos_ventas_p3 VALUES LESS THAN (TO_DATE('01/01/2012','DD/MM/YYYY')) TABLESPACE ts_datos2011 );

Lo que realizamos es crear particiones por años, para esto seleccionamos como clave de particionamiento al campo fecha y se definió para cada partición un tablespace, con esto logramos una clara distribución de los datos.
Cada una de estas particiones se las puede administrar de manera independiente, ¿a que me refiero? Que podemos trabajarlos como estructuras independientes como si fueran tablas diferentes. Podemos establece definiciones de storage para cada partición, borrar una partición (Solo afectara a los datos contenidos dentro de la partición) y truncar la partición.
Si queremos ver que datos están contenidos en alguna de las particiones de la tabla Oracle nos proporciona la forma de hacerlo usando la clausula PARTITION(Nombre de la partición):

SELECT * FROM pos_ventas PARTITION(pos_ventas_p1);

Ejemplo del uso de la partición List
Este método se caracteriza por que la clave de particionamiento se define por una lista de valores. Tenemos una tabla de ventas, pero queremos realizar la división por el código de almacén de tal manera que tengamos los agrupemos por sector norte, sur y centro.

CREATE TABLE pos_ventas ( id_venta NUMBER, id_almacen VARCHAR2(5), fecha DATE, valor NUMBER ) PARTITION BY LIST (id_almacen) ( PARTITION pos_norte VALUES ('10','20','30') TABLESPACE ts_datos_nor, PARTITION pos_sur VALUES ('40','50') TABLESPACE ts_datos_sur, PARTITION pos_centro VALUES ('90','120','130','150') TABLESPACE ts_datos_cen );

Vemos que las agrupaciones de las particiones dependerán de la lista de datos que se defina en la sección VALUES y que también podemos definir tablespaces o no para cada una de las particiones.


Ejemplo del uso de la partición Hash
Esta es una forma de distribución equitativa de datos definiendo un campo como llave de particionado para que la función Hash nos indique a que partición irá el dato.
Tenemos la tabla detalle de ventas y vamos a usar id_producto como clave de particionamiento para la función hash.

CREATE TABLE pos_ventas_detalles ( id_venta NUMBER, id_detalle_venta NUMBER, id_producto VARCHAR2(20), cantidad number, valor number ) PARTITION BY HASH (id_producto) PARTITIONS 4;

De esta manera definimos que se crearan hasta 4 particiones para la tabla POS_VENTAS_DETALLES y la clave de particionado esta definida por el campo ID_PRODUCTO, de esta manera distribuiremos de manera equitativa los datos. Con esto de manera implícita se crearan las particiones hasta un máximo de 4 particiones, pero también podemos especificar de manera especifica el nombre de las particiones con sus respectivos tablespaces.

CREATE TABLE pos_ventas_detalles ( id_venta NUMBER, id_detalle_venta NUMBER, id_producto VARCHAR2(20), cantidad number, valor number ) PARTITION BY HASH (id_producto)  ( PARTITION pos_vta_det_p1 TABLESPACE ts_datos_01, PARTITION pos_vta_det_p2 TABLESPACE ts_datos_02 );

   
¿Cuando se necesita particionar una tabla?
Hay varios criterios a considerar para determinar si se necesita particionar una tabla:

·                     El tamaño de la tabla supera los 2G de tamaño.

·                     La tabla contiene información histórica que tiende a ser modificada, un ejemplo es en la facturación donde se genera mucha información por año y mucha de esta información se mantiene para análisis de los históricos, dicha información podríamos dividirla en dos particiones una histórica y otra actual.

MySQL 

Particionar tablas en MySQL nos permite rotar la información de nuestras tablas en diferentes particiones, consiguiendo así realizar consultas más rápidas y recuperar espacio en disco al borrar los registros. El uso más común de particionado es según fecha (date).

Para ver si nuestra base de datos soporta particionado simplemente ejecutamos:

SHOW VARIABLES LIKE '%partition%';

A continuación veremos un ejemplo de cómo particionar una tabla por mes y posteriormente borrar o modificar su información.

Crear particiones

1.- Creamos la tabla reports:

CREATE TABLE reports (
  id int(10) NOT NULL AUTO_INCREMENT,
  date datetime NOT NULL,
  report TEXT,
  PRIMARY KEY (id,date)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

Como se puede ver hemos añadido como índice de tabla el campo date, esto es necesario si luego queremos particionar por fecha.

2.- Ahora que tenemos la tabla creada vamos a particionar por mes:

ALTER TABLE reports PARTITION BY RANGE(TO_DAYS(date))(
  PARTITION p201111 VALUES LESS THAN (TO_DAYS("2011-12-01")),
  PARTITION p201112 VALUES LESS THAN (TO_DAYS("2012-01-01")),
  PARTITION p201201 VALUES LESS THAN (TO_DAYS("2012-02-01")),
  PARTITION p201202 VALUES LESS THAN (TO_DAYS("2012-03-01")),
  PARTITION p201203 VALUES LESS THAN (TO_DAYS("2012-04-01")),
  PARTITION p201204 VALUES LESS THAN (TO_DAYS("2012-05-01")),
  PARTITION p201205 VALUES LESS THAN (TO_DAYS("2012-06-01")),
  PARTITION pDefault VALUES LESS THAN MAXVALUE
);

La última partición (pDefault) tendrá todos los registros que no entren en las particiones anteriores. De esta manera nos aseguramos que la información nunca dejará de insertarse en la tabla.

Borrar particiones

Lo bueno de trabajar con particiones es que podemos borrar rápidamente registros sin tener que recorrer toda la tabla e inmediatamente recuperar el espacio en disco utilizado por la tabla.

Por ejemplo si queremos borrar la partición más antigua simplemente ejecutamos:

ALTER TABLE reports DROP PARTITION p201111;

Añadir particiones

En el ejemplo anterior las 2 últimas particiones creadas han sido:

PARTITION p201205 VALUES LESS THAN (TO_DAYS("2012-06-01")),
PARTITION pDefault VALUES LESS THAN MAXVALUE

El problema es que todos los INSERTs que se hagan después de mayo de 2012 se insertarán enpDefault. La solución sería añadir particiones nuevas para cubrir los próximos meses:

ALTER TABLE reports REORGANIZE PARTITION pDefault INTO (
  PARTITION p201206 VALUES LESS THAN (TO_DAYS("2012-07-01")),
  PARTITION pDefault VALUES LESS THAN MAXVALUE);

En el caso que no tuvieramos una partición del tipo pDefault simplemente ejecutamos:

ALTER TABLE reports ADD PARTITION (PARTITION p201206 VALUES LESS THAN (TO_DAYS("2012-07-01")));

Consultar particiones

Para consultar información de particiones creadas en una tabla así como también los registros que contiene cada una ejecutamos:

SELECT PARTITION_NAME,TABLE_ROWS FROM information_schema.PARTITIONS WHERE TABLE_NAME='reports';