Particiones en MySQL y Oracle

Particiones en 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 en pDefault. 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';
 
 

Particiones en Oracle

El particionado se realiza utilizando una clave de particionado (partitioning key), que determina en que partición de las existentes en la tabla van a residir los datos que se insertan. Oracle también permite realizar el particionado de indices y de tablas organizadas por indices. Cada partición ademas puede tener sus propias propiedades de almacenamiento. Las tablas particionadas aparecen en el sistema como una única tabla, realizando el sistema la gestión automatica de lectura y escritura en cada una de las particiones (excepto para el caso de la partición de Sistema introducida en la versión 11g). La definición de las particiones se indica en la sentencia de creación de las tablas, con la sintaxis oportuna para cada uno de los tipos.
  • Particionado Range: la clave de particionado viene determinada por un rango de valores, que determina la partición donde se almacenara un valor.
  • Particionado Hash: la clave de particionado es una función hash, aplicada sobre una columna, que tiene como objetivo realizar una distribución equitativa de los registros sobre las diferentes particiones. Es útil para particionar tablas donde no hay unos criterios de particionado claros, pero en la que se quiere mejor el rendimiento.
  • Particionado List: la clave de particionado es una lista de valores, que determina cada una de las particiones.
  • Particionado Composite: los particionados anteriores eran del tipo simples (single o one-level), pues utilizamos un unico método de particionado sobre una o mas columnas. Oracle nos permite utilizar metodos de particionado compuestos, utilizando un primer particionado de un tipo determinado, y luego para cada particion, realizar un segundo nivel de particionado utilizando otro metodo. Las combinaciones son las siguientes (se han ido ampliando conforme han ido avanzando las versiones): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).
  • Particionado Interval: tipo de particionado introducido igualmente en la versión 11g. En lugar de indicar los rangos de valores que van a determinar como se realiza el particionado, el sistema automáticamente creara las particiones cuando se inserte un nuevo registro en la b.d. Las técnicas de este tipo disponible son Interval, Interval List, Interval Range e Interval Hash (por lo que el particionado Interval es complementario a las técnicas de particionado vistas anteriormente).
  • Particionado System: se define la tabla particionada indicando las particiones deseadas, pero no se indica una clave de particionamiento. En este tipo de particionado, se delega la gestión del particionado a las aplicaciones que utilicen la base de datos (por ejemplo, en las sentencias sql de inserción deberemos de indicar en que partición insertamos los datos).
Referente al particionado, y como característica interesante, Oracle nos permite definir sentencias SQL del tipo DML haciendo referencia a las particiones. Es lo que llaman nombres de tabla con extension de partición (partition-extended table names). Por ejemplo, podremos hacer un select sobre una tabla particionada indicando en la sintaxis la partición de la queremos que se haga lectura. Por ejemplo:
 
SELECT * FROM schema.table PARTITION(part_name);
 
Esto es igualmente válido para las sentencias INSERT, UPDATE, DELETE, LOCK TABLE. Esta sintaxis nos proporciona una forma simple de acceder a las particiones individuales como si fueran tablas, y utilizarlas, por ejemplo, para la creación de vistas (utilizando la vista en lugar de la tabla), lo que nos puede ser util en muchas situaciones.

Particionado Range
 
Esta forma de particionamiento requiere que los registros estén identificado por un “partition key” relacionado por un predefinido rango de valores. El valor de las columnas “partition key” determina la partición a la cual pertenecerá el registro.
 
CREATE TABLE sales
  ( prod_id       NUMBER(6)
  , cust_id       NUMBER
  , time_id       DATE
  , channel_id    CHAR(1)
  , promo_id      NUMBER(6)
  , quantity_sold NUMBER(3)
  , amount_sold   NUMBER(10,2)
  )
 PARTITION BY RANGE (time_id)
 ( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))     TABLESPACE tsa
 , PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))    TABLESPACE tsb
 , PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))    TABLESPACE tsc
 , PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))    TABLESPACE tsd
 );
 
Este tipo de particionamiento esta mejor situado cuando se tiene datos que tienen rango lógicos y que pueden ser distribuidos por este. Ej. Mes del Año o un valor numérico.
 
Particionado Hash
 
Los registros de la tabla tienen su localización física determinada aplicando un valor hash a la columna del partition key. La funcion hash devuelve un valor automatico que determina a que partición irá el registro. Es una forma automática de balancear el particionado. Hay varias formas de construir este particionado. En el ejemplo siguiente vemos una definición sin indicar los nombres de las particiones (solo el número de particiones):

CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     PARTITION BY HASH(deptno) PARTITIONS 16;
 
Igualmente, se pueden indicar los nombres de cada particion individual o los tablespaces donde se localizaran cada una de ellas:
 
CREATE TABLE dept (deptno NUMBER, deptname VARCHAR(32))
     STORAGE (INITIAL 10K)
     PARTITION BY HASH(deptno)
       (PARTITION p1 TABLESPACE ts1, PARTITION p2 TABLESPACE ts2,
        PARTITION p3 TABLESPACE ts1, PARTITION p4 TABLESPACE ts3);
 
Particionado List
 
Este tipo de particionado fue añadido por Oracle en la versión 9, permitiendo determinar el particionado según una lista de valores definidos sobre el valor de una columna especifica.
 
CREATE TABLE sales_list (salesman_id NUMBER(5), salesman_name VARCHAR2(30),
sales_state VARCHAR2(20),
sales_amount NUMBER(10),
sales_date DATE)
PARTITION BY LIST(sales_state)
(
PARTITION sales_west VALUES('California', 'Hawaii'),
PARTITION sales_east VALUES ('New York', 'Virginia', 'Florida'),
PARTITION sales_central VALUES('Texas', 'Illinois')
PARTITION sales_other VALUES(DEFAULT)
);
 
Este particionado tiene algunas limitaciones, como que no soporta múltiples columnas en la clave de particionado (como en los otros tipos), los valores literales deben ser únicos en la lista, permitiendo el uso del valor NULL (aunque no el valor MAXVALUE, que si puede ser utilizado en particiones del tipo Range). El valor DEFAULT sirve para definir la partición donde iran el resto de registros que no cumplen ninguna condición de las diferentes particiones.
 
Particionado Composite
 
Este tipo de particionado es compuesto, pues se conjuga el uso de dos particionados a la vez. Veamos un ejemplo utilizando el tipo RANGE y el HASH. En primer lugar, hace un particionado del tipo RANGE utilizando rangos de años. En segundo lugar, para cada partición definida por cada año, hacemos un segundo particionado (subparticion) del tipo aleatorio (HASH) por el valor de otra columna:
 
  
  CREATE TABLE TAB2 (ord_id     NUMBER(10),
     ord_day    NUMBER(2),
     ord_month  NUMBER(2),
     ord_year   NUMBER(4)
     )
  PARTITION BY RANGE(ord_year)
  SUBPARTITION BY HASH(ord_id)
  SUBPARTITIONS 8
   ( PARTITION q1 VALUES LESS THAN(2001)
     ( SUBPARTITION q1_h1 TABLESPACE TBS1,
       SUBPARTITION q1_h2 TABLESPACE TBS2,
       SUBPARTITION q1_h3 TABLESPACE TBS3,
       SUBPARTITION q1_h4 TABLESPACE TBS4
     ),
     PARTITION q2 VALUES LESS THAN(2002) 
     ( SUBPARTITION q2_h5 TABLESPACE TBS5,
       SUBPARTITION q2_h6 TABLESPACE TBS6,
       SUBPARTITION q2_h7 TABLESPACE TBS7,
       SUBPARTITION q2_h8 TABLESPACE TBS8
     ),
     PARTITION q3 VALUES LESS THAN(2003) 
     ( SUBPARTITION q3_h1 TABLESPACE TBS1,
       SUBPARTITION q3_h2 TABLESPACE TBS2,
       SUBPARTITION q3_h3 TABLESPACE TBS3,
       SUBPARTITION q3_h4 TABLESPACE TBS4
     ),
     PARTITION q4 VALUES LESS THAN(2004)
     ( SUBPARTITION q4_h5 TABLESPACE TBS5,
       SUBPARTITION q4_h6 TABLESPACE TBS6,
       SUBPARTITION q4_h7 TABLESPACE TBS7,
       SUBPARTITION q4_h8 TABLESPACE TBS8
     )
   )
Las combinaciones permitidas son las siguientes (se han ido ampliando conforme han ido avanzando las versiones de Oracle): range-hash, range-list, range-range, list-range, list-list, list-hash y hash-hash (introducido en la versión 11g).

Particionado Composite en Oracle

Particionado Interval
 
El particionado Interval ha sido introducido en la versión 11g para habilitar un mantenimiento de particiones desasistido. Normalmente, cuando realizamos un particionado sobre una tabla, indicamos una lista de valores o rangos para crear de antemano las particiones. Posteriormente, ajustamos la definición de las particiones para incluir nuevas para nuevos rangos o valores. Con las particiones Interval, preparamos para que Oracle cree las particiones de forma automática cuando lo necesite. Básicamente, se define un intervalo y una directiva para decirle a Oracle como se tiene que comportar. Veamos un ejemplo:
 
CREATE TABLE T_11G(C1 NUMBER(38,0),
C2 VARCHAR2(10),
C3 DATE)
PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
(PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));
 
Hemos creado una partición base, y con lo especificado en Interval definimos como gestionar la creación automática de nuevas particiones. La posibilidad de definir un intevalo y que Oracle se encargue de crear las particiones a medida que se vayan necesitando resulta muy interesante para facilitar el mantenimiento y administración de particiones.
 
Particionado System
 
Una de las nuevas funcionalidades introducida en la version 11g es el denominado partitioning interno o de sistema. En este particionado Oracle no realiza la gestión del lugar donde se almacenaran los registros, sino que seremos nosotros los que tendremos que indicar en que partición se hacen las inserciones.

create table t (c1 int,
                c2 varchar2(10),
                c3 date)
     partition by system
    (partition p1,
     partition p2,
     partition p3);
 
Si hicieramos un insert sobre la tabla (por ejemplo, insert into t values (1,’A',sysdate);), daría error, siendo la instrucción a ejecutar correcta la siguiente:
 
insert into t partition (p3) values (1,’A',sysdate);
 
Puede ser util este particionado para aplicaciones donde nos interesa ser nosotros lo que gestionamos la forma en la que se realiza el particionado (lógica de aplicación).
 

Fuentes:

http://infodocs.net/articulo/mysql/particionado-de-tablas-en-mysql

http://mundogeek.net/archivos/2012/03/09/particiones-en-mysql/

http://www.dataprix.com/blogs/respinosamilla/particionado-tablas-oracle

4 comentarios:

  1. haz evaluado si una vez particionado se pueden generar vistas sobre estos datos y cual es la velocidad de estas vistas?.

    Saludos.

    ResponderEliminar
    Respuestas
    1. Hola Jonathan:
      He tenido la oportunidad de trabajar con tablas particionadas en otros manejadores de bases de datos (oracle, MSSQL, postgres) y ningun problema en la generación de las vistas. En general el particionamiento de tablas, siempre y cuando esté bien diseñado, ayuda en sobremanera el rendimiento de consultas tipo SELECT. Se recomienda mucho para tablas grandes. Saludos.

      Eliminar
  2. hola
    quisiera saber si es lo mismo la particion de base de datos y particion de tablas hay alguna diferencia

    ResponderEliminar
  3. Hola buenas noches,
    Haz realizado backup de solo una o más particiones de tablas y almacenarlas en otro filesystem??

    ResponderEliminar