Particiones en MySQL & Oracle
Particionado de tablas 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';
Particionado de tablas en Oracle
Tipos de
Particionado en Oracle
El
particionado fue introducido por primera vez en la versión 8 de Oracle, como
una nueva característica DW para la gestión de grandes cantidades de
información, y para facilitar la tarea de los administradores de bases de
datos. Dependiendo de la versión de Oracle en la que estemos, tenemos
diferentes tipos de particionado disponibles:
- Oracle
8.0:
particionado Range.
- Oracle
8i:
además del particionado Range se añaden los tipos Hash y Composite.
- Oracle
9iR2/10g:
se amplían con el tipo List y se permiten nuevas combinaciones de tipos en
el particionado Composite.
- Oracle
11g:
se introducen las columnas virtuales para particionar(que no existen físicamente
en la tabla), así como el particionado de Sistema (donde podemos gestionar
directamente en que partición de la tabla se insertan los registros) y el
particionado por Intervalos.
Particionado de Tablas en
Oracle
Básicamente,
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 índices y de tablas organizadas por índices. Cada partición además
puede tener sus propias propiedades de almacenamiento. Las tablas particionadas
aparecen en el sistema como una única tabla, realizando el sistema la gestión automática
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 único método de particionado sobre una o más columnas.
Oracle nos permite utilizar métodos de particionado compuestos, utilizando
un primer particionado de un tipo determinado, y luego para cada partición,
realizar un segundo nivel de particionado utilizando otro método. 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 cómo 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).
·
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 está 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 función hash devuelve un valor automático
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 irán 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
)
)
