Particiones en Mysql Y Oracle

Particiones en Mysql

Se puede particionar una tabla de 5 maneras diferentes:

• Por rango: para construir nuestras particiones especificamos rangos de valores. Por ejemplo, podríamos segmentar los datos en 12 particiones: una para los contratos de 1950 a 1960, otra para los años 60, los 70, 80, 90, la década del 2000 y la década actual

• ALTER TABLE contratos  
• PARTITION BY RANGE(YEAR(fechaInicio)) (  
•     PARTITION partDecada50 VALUES LESS THAN (1960),  
•     PARTITION partDecada60 VALUES LESS THAN (1970),  
•     PARTITION partDecada70 VALUES LESS THAN (1980),  
•     PARTITION partDecada80 VALUES LESS THAN (1990),  
•     PARTITION partDecada90 VALUES LESS THAN (2000),  
•     PARTITION partDecada00 VALUES LESS THAN (2010),  
•     PARTITION partDecada10 VALUES LESS THAN MAXVALUE  
• );  

  Por listas: para construir nuestras particiones especificamos listas de valores concretos.

1. ALTER TABLE contratos  
2. PARTITION BY LIST(YEAR(fechaInicio)) (  
3.     PARTITION partDecada50 VALUES IN (1950, 1951, 1952, 1953, 1954, 1955, 1956, 1957, 1958, 1959),  
4.     PARTITION partDecada60 VALUES IN (1960, 1961, 1962, 1963, 1964, 1965, 1966, 1967, 1968, 1969),  
5.     PARTITION partDecada70 VALUES IN (1970, 1971, 1972, 1973, 1974, 1975, 1976, 1977, 1978, 1979),  
6.     PARTITION partDecada80 VALUES IN (1980, 1981, 1982, 1983, 1984, 1985, 1986, 1987, 1988, 1989),  
7.     PARTITION partDecada90 VALUES IN (1990, 1991, 1992, 1993, 1994, 1995, 1996, 1997, 1998, 1999),  
8.     PARTITION partDecada00 VALUES IN (2000, 2001, 2002, 2003, 2004, 2005, 2006,  
9. 2007, 2008, 2009),  
10.     PARTITION partDecada10 VALUES IN (2010, 2011, 2012, 2013, 2014, 2015, 2016,  
11. 2017, 2018, 2019)  
12. );  

  Por hash: MySQL se encarga de distribuir las tuplas automáticamente usando una operación de módulo. Sólo hay que pasarle una columna o expresión que resulte en un entero (el hash) y el número de particiones que queramos crear.

1. ALTER TABLE contratos  
2. PARTITION BY HASH(YEAR(fechaInicio))  
3. PARTITIONS 7;  

  Por clave: similar a la partición por hash, pero en este caso no necesitamos pasarle un entero; MySQL utilizará su propia función de hash para generarlo. Si no se indica ninguna columna a partir de la que generar el hash, se utiliza la clave primaria por defecto.

1. ALTER TABLE contratos  
2. PARTITION BY KEY()  
3. PARTITIONS 7;  

  Compuesta: podemos combinar los distintos métodos de particionado y crear particiones de particiones

Por último, un pequeño ejemplo de cómo afectaría el particionado a una consulta sencilla como obtener el número total de tuplas que cumplen una condición. Estas son las estadísticas de la consulta sin particionado (ni índices)

1. EXPLAIN SELECT COUNT(*)  
2. FROM contratos  
3. WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'  

4.    select_type	table	type	key	rows	Extra
SIMPLE	contratos	ALL		239796	Using where

Y este el resultado de añadir las particiones (nótese la palabra clave PARTITIONS para que nos muestre también la información relativa a las particiones)

1. EXPLAIN PARTITIONS SELECT COUNT(*)  
2. FROM contratos  
3. WHERE fechaInicio BETWEEN '1950-01-01' AND '1955-12-31'  

4.    select_type	table	partitions	type	key	rows	Extra
SIMPLE	contratos	partDecada50	ALL		8640	Using where

Como véis, el número de tuplas que MySQL tiene que comprobar se ve disminuido en 2 órdenes de magnitud.

Particiones En Oracle

Tipos de Particionamiento

Basicamente, el particionamiento se realiza utilizando una clave de particionado (partitioning key), que determina en qué 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.

• Particionamiento Range: la clave de particionado viene determinada por un rango de valores, que determina la partición donde se almacenara un valor.
• Particionamiento 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.
• Particionamiento List: la clave de particionado es una lista de valores, que determina cada una de las particiones.
• Particionamiento Composite: los particionamientos anteriores eran del tipo simple (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).
• Particionamiento 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).
• Particionamiento Reference: particiona una tabla del mismo modo en que lo está la tabla a la que apunta mediante una FK, de este modo es muy fácil particionar del mismo modo 2 tablas "maestro-detalle", pues solo tengo que definir el particionamiento en la tabla maestra (la q tiene la PK) y en la tabla hija (la de la FK) usar PARTITION BY REFERENCE. Es nuevo en 11g.
• Particionamiento 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 particionamiento a las aplicaciones que utilicen la base de datos (por ejemplo, en las sentencias sql de inserción deberemos de indicar en qué partición insertamos los datos). Es nuevo en 11g.

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 útil en muchas situaciones.

Ejemplos de cada tipo de Particionamiento

Particionado Range

El Particionamiento Range requiere que los registros estén identificados por un “partition key”, y 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 es interesante cuando se tienen datos con un rango lógico y que pueden ser distribuidos por este. Ej. Mes del Año o un valor numérico.

Particionado Hash

En el Particionamiento Hash se aplica una función hash a la partition key, y el resultado de dicha función hash determina a qué partición irá el registro. Es una forma automática de balancear la E/S con el particionamiento. 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);

Lo q dice el manual de Oracle del particionamiento hash:

·         Cuando usar particionamiento hash: básicamente para repartir la E/S (cuando no existe un sistema de almacenamiento q haga striping), y paralelizar las consultas ...

·         Añadir una partición: Oracle elige una de las q ya existen y la divide en 2.

·         Unir 2 particiones (coalesce): lo contrario del caso anterior (Oracle elige 2 y las funde en una).

·         Como crear una tabla particionada e índices: aquí explica q con "create index ... local;" Oracle crea un índice con el mismo particionamiento q la tabla correspondiente (además se puede especificar el tablespace y/o los nombres de las particiones).

Particionado List

El Particionamiento List 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, 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.

Particionamiento Composite

El Particionamiento Composite combina 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 Interval

El Particionamiento Interval ha sido introducido en la versión 11g para habilitar la creación dinámica de particiones al particionar por rango (RANGE). 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 el particionamiento Interval, Oracle crea las particiones de forma automática cuando lo necesita. 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

El Particionamiento System es una de las nuevas funcionalidades introducida en la version 11g, y también se denomina particionamiento interno o de sistema. En este particionado Oracle no realiza la gestión del lugar donde se almacenarán 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 cuando nos interesa particionar en función de la lógica de la aplicación.

Uso de columnas virtuales para particionar

En la versión 11g se pueden definir en las tablas columnas virtuales (no existen físicamente). Ademas estas columnas se pueden utilizar para realizar particionado sobre ellas. La forma de crear una tabla con columnas de este tipo sería la siguiente:
create table t (c1 int,
c2 varchar2(10),
c3 date,
c3_v char(1)
generated always as
(to_char(c3,'d')) virtual
)
partition by list (c3_v)
(partition p1 values ('1'),
partition p2 values ('2'),
partition p3 values ('3'),
partition p4 values ('4'),
partition p5 values ('5'),
partition p6 values ('6'),
partition p7 values ('7') );