Lo nuevo en Particionamiento de tablas (Partitioning)

La version 11g ofrece nuevas funcionalidades para crear y mantener tablas particionadas. En esta primera nota les voy a mostrar como usar el particionamiento por intervalos, las columnas virtuales para particionar y el particionamiento de sistema.

Usando partionamiento por intervalos

Una de la tareas mas habituales cuando se trabaja con particionamiento es la creación de nuevas particiones, por ejemplo, en un tipico particionamiento por año y mes hay que ir creando de antemano las particiones con el transcurso de tiempo. Generalmente se crea una partición delimitada por MAXVALUE y todo lo que se pase de la ultima partición definida explicitamente va a parar a la partición definida con delimitador máximo, si se llegaran juntar mas de un mes se requerira realizar split para emplolijar la tabla. Si bien todas estas tareas se pueden automatizar de forma tal de programar la creación automatica de las particiones, resulta mas efectivo que directamente Oracle cree la particion adecuada solo cuando se necesite, bajo demanda. En 11g se puede definir un intervalo con lo cual se le define la directiva precisa de como realizar el partionamiento. Como siempre, todo se entiende mejor con un sencillo ejemplo de uso:

Primero voy a crear una tabla, tal cual la crearia en 10g (sin intervalos):

rop@ROP111> CREATE TABLE T_10G 
  2     (C1 NUMBER(38,0), 
  3      C2 VARCHAR2(10), 
  4      C3 DATE
  5     ) 
  6  PARTITION BY RANGE (C3)  
  7  (PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

Tabla creada.

Ahora voy a usar la nueva sintaxis y le voy a definir el intervalo usando la función NUMTOYMINTERVAL(1,'MONTH'), para que cree una particion por año,mes.

rop@ROP111> CREATE TABLE T_11G 
  2     (C1 NUMBER(38,0), 
  3      C2 VARCHAR2(10), 
  4      C3 DATE
  5     ) 
  6  PARTITION BY RANGE (C3) INTERVAL (NUMTOYMINTERVAL(1,'MONTH')) 
  7  (PARTITION P0902 VALUES LESS THAN (TO_DATE('2009-03-01 00:00:00','YYYY-MM-DD HH24:MI:SS')));

Tabla creada.

rop@ROP111> insert into t_10g values (1,'A',to_date('2009-05-01','YYYY-MM-DD'));
insert into t_10g values (1,'A',to_date('2009-05-01','YYYY-MM-DD'))
            *
ERROR en línea 1:
ORA-14400: la clave de partición insertada no corresponde a ninguna partición

rop@ROP111> insert into t_11g values (1,'A',to_date('2009-05-01','YYYY-MM-DD'));

1 fila creada.

Vamos a consultar el diccionario para ver que efectivamente se haya agregado en forma automatica la nueva partición:

rop@ROP111> select partition_name,high_value from user_tab_partitions where table_name = 'T_11G';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------------------------------------
P0902                          TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

SYS_P44                        TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

Como se ve, se agregó la partición SYS_P44. El nombre que le asignó es un nombre generado por el sistema y no sigue el formato deseado asi que lo voy a renombrar.

rop@ROP111> alter table t_11g rename partition SYS_P44 to p0905;

Tabla modificada.

Ahora, voy a insertar una fila mas con fecha del 1.1.2010:

rop@ROP111> insert into t_11g values (2,'B',to_date('2010-01-01','YYYY-MM-DD'));

1 fila creada.

rop@ROP111>  select partition_name,high_value from user_tab_partitions where table_name = 'T_11G';

PARTITION_NAME                 HIGH_VALUE
------------------------------ ----------------------------------------------------------------------
P0902                          TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

P0905                          TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

SYS_P45                        TO_DATE(' 2010-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDA
                               R=GREGORIAN')

Como se ve, se agregó otra partición para "acomodar" la nueva fila a su correspondiente partición.

También podemos consultar la información de como se definió el intervalo usando la nueva columna INTERVAL:

  1* select table_name,partitioning_type,interval from user_part_tables
rop@ROP111> /
TABLE_NAME                     PARTITION INTERVAL
------------------------------ --------- --------------------------------------------------
T_10G                          RANGE
T_11G                          RANGE     NUMTOYMINTERVAL(1,'MONTH')

rop@ROP111> 

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.

Usando columnas virtuales para particionar

Hace unos años estuve en un proyecto en una empresa de comunicaciones, donde se usaba una tabla que guardaba información volátil con una ventana semanal. El modelo ya estaba definido y se borraban diariamente las filas de forma tal de siempre mantener 6 dias hacia atrás. A medida que los datos insertados diariamente se incrementaban, la depuración (borrado físico) se fue haciendo mas complicada y demoraba cada vez mas tiempo (además de generase mas redo y undo). La tabla no estaba particionada y me pidieron de armar un particionamiento por dias de semana para asi truncar la particion del dia, en lugar de borrar los registros. El problema fue que la tabla tenia la fecha de insert del registro pero no tenia el dia de la semana. Como en ese momento la base era 10g la solución de menos impacto fue agregar una columna de tipo char(1) que se actualizaba con un trigger en cada insert. El trigger lo unico que hacia era obtener el dia de la semana del sysdate y lo guardaba en la nueva columna. En 11g se podría haber resuelto en forma mas transparente con solo agregar una columna virtual a la tabla. A continuación les muestro un ejemplo:

rop@ROP111> create table t (c1 int,
  2                  c2 varchar2(10),
  3                  c3 date,
  4                  c3_v char(1)
  5                  generated always as
  6                 (to_char(c3,'d')) virtual
  7                 )
  8  partition by list (c3_v)
  9  (partition p1 values ('1'),
 10   partition p2 values ('2'),
 11   partition p3 values ('3'),
 12   partition p4 values ('4'),
 13   partition p5 values ('5'),
 14   partition p6 values ('6'),
 15   partition p7 values ('7')
 16  );

Tabla creada.

Ahora obtengo el dia actual:

rop@ROP111> select sysdate from dual;
SYSDATE
---------
11-FEB-09

y la función a aplicar para particionar para el dia actual:

rop@ROP111> select to_char(sysdate,'d') from dual;
T
-
4

Voy a insertar 2 filas. Notar que no le paso nada a la columna virtual:

rop@ROP111> insert into t (c1,c2,c3) values (1,'A',sysdate);
1 fila creada.

rop@ROP111> ed
Escrito file afiedt.buf
  1* insert into t (c1,c2,c3) values (2,'B',sysdate+2)
rop@ROP111> /

1 fila creada.

rop@ROP111> commit;
Confirmación terminada.

Analizo la tabla para luego mostrar que las filas se insertaron en la partición correcta

rop@ROP111> analyze table t compute statistics;

Tabla analizada.

rop@ROP111> select partition_name,num_rows from user_tab_partitions
  2  where table_name = 'T';

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                      0
P2                                      0
P3                                      0
P4                                      1
P5                                      0
P6                                      1
P7                                      0

7 filas seleccionadas.

Haciendo un describe, la columna virtual se ve como una columna mas:

rop@ROP111> desc t
 Nombre                                                            ¿Nulo?   Tipo
 ----------------------------------------------------------------- -------- --------------------------------------------
 C1                                                                         NUMBER(38)
 C2                                                                         VARCHAR2(10)
 C3                                                                         DATE
 C3_V                                                                       CHAR(1)

Para finalizar vamos a ver en el diccionario la función aplicada y la columna virtual agregada:

rop@ROP111> select column_name,data_type,data_default,virtual_column,internal_column_id,segment_column_id 
  2         from user_tab_cols where table_name = 'T';


COLUMN_NAME                    DATA_TYPE       DATA_DEFAULT         VIR INTERNAL_COLUMN_ID SEGMENT_COLUMN_ID
------------------------------ --------------- -------------------- --- ------------------ -----------------
C1                             NUMBER                               NO                   1                 1
C2                             VARCHAR2                             NO                   2                 2
C3                             DATE                                 NO                   3                 3
C3_V                           CHAR            TO_CHAR("C3",'d')    YES                  4

Usando particionamiento de Sistema

El ultimo "11g new feature" de partitioning es el denominado partitioning interno o de sistema. Con este tipo de particionamiento se delega a la capa de aplicación la determinación de a cual partición se inserta una fila.
Voy a crear una tabla particionada por sistema y voy a intentar insertar una fila sin definir a que partición tiene que ir.

rop@ROP111> create table t (c1 int,
  2                  c2 varchar2(10),
  3                  c3 date)
  4  partition by system
  5  (partition p1,
  6   partition p2,
  7   partition p3
  8  );

Tabla creada.

rop@ROP111> insert into t values (1,'A',sysdate);
insert into t values (1,'A',sysdate)
            *
ERROR en línea 1:
ORA-14701: Se debe utilizar el nombre de partición ampliada o la variable ligada para DML en tablas particionadas
mediante el método Sistema

Oracle no permite insertar una fila si no se explicita a que partición debe asociarse.
Ahora le voy a insertar 2 filas con la nueva sintaxis, la primera en la partición p3 y la segunda en la partición p1.

rop@ROP111> insert into t partition (p3) values (1,'A',sysdate);

1 fila creada.

rop@ROP111>  insert into t partition (p1)  values (2,'B',sysdate-2);

1 fila creada.

Ya que le aclaramos en el mismo insert en que partición grabar, no hubo problema.
Para finalizar voy a mostrar que las filas se insertaron correctamente:

rop@ROP111> analyze table t compute statistics;

Tabla analizada.

rop@ROP111> ed
Escrito file afiedt.buf

  1  select partition_name,num_rows from user_tab_partitions
  2* where table_name = 'T'
rop@ROP111> /

PARTITION_NAME                   NUM_ROWS
------------------------------ ----------
P1                                      1
P2                                      0
P3                                      1

rop@ROP111> select table_name,partitioning_type from user_part_tables where table_name = 'T';

TABLE_NAME                     PARTITION
------------------------------ ---------
T                              SYSTEM

rop@ROP111>

Este tipo de particionamiento puede aplicarse a ciertos tipos de aplicaciones que requieran tener control total de donde insertar las filas y aprovechar las virtudes del particionamiento.