Partionamiento por Referencia
En un típico modelo maestro/detalle suele suceder que la clave para particionar esta solo en la tabla maestra y por lo tanto imposibilita que particionemos también la tabla detalle perdiendo así la oportunidad de aprovechar el partitioning. Una forma de solucionar eso es "bajar" la columna o columnas de particionamiento a la tabla detalle, con la consiguiente redundancia de información que eso provoca sumado al cambio de estructura de la tabla. A partir de 11g no se necesita cambiar el modelo de datos ya que se puede definir particiones por referencia. Veamos como funciona con un ejemplo:
Voy a crear 3 tablas: FACTURAS, ITEMS y CLIENTES. Las tablas FACTURAS e ITEMS van a estar particionadas. La tabla CLIENTES solo esta para completar el modelo reducido. La tabla FACTURAS actua como tabla maestra y usa particionamiento por rango sobre el campo fecha. La tabla ITEMS es tabla detalle de la tabla FACTURAS y dado que no tiene campo fecha vamos a aprovechar el nuevo feature para particionar referenciando a su tabla padre (FACTURAS).
rop@ROP11G> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
PL/SQL Release 11.1.0.7.0 - Production
CORE 11.1.0.7.0 Production
TNS for Solaris: Version 11.1.0.7.0 - Production
NLSRTL Version 11.1.0.7.0 - Production
rop@ROP11G> create table clientes (id_cliente number(8),
2 nombre varchar2(20),
3 direccion varchar2(50),
4 fecha_ingreso date,
5 constraint clientes_pk
6 primary key (id_cliente));
Tabla creada.
rop@ROP11G> create table facturas (id_factura number(8),
2 id_cliente number(8),
3 tipo char(1),
4 fecha date,
5 importe number(15,3),
6 constraint facturas_pk
7 primary key (id_factura))
8 partition by range (fecha)
9 (
10 partition facturas_200901 values less than (to_date('20090201','YYYYMMDD')),
11 partition facturas_200902 values less than (to_date('20090301','YYYYMMDD')),
12 partition facturas_200903 values less than (to_date('20090401','YYYYMMDD')),
13 partition facturas_200904 values less than (to_date('20090501','YYYYMMDD')),
14 partition facturas_200905 values less than (to_date('20090601','YYYYMMDD')),
15 partition facturas_200906 values less than (to_date('20090701','YYYYMMDD')),
16 partition facturas_max values less than (maxvalue)
17 ) ;
Tabla creada.
rop@ROP11G> create table items (id_item number(8),
2 id_factura number(8) not null,
3 detalle varchar2(50),
4 importe number(15,2),
5 constraint facturas_fk
6 foreign key (id_factura)
7 references facturas(id_factura))
8 partition by reference (facturas_fk);
Tabla creada.
rop@ROP11G>
Como vemos para crear ITEMS particionada por referencia debemos crear una constraint referencial sobre id_factura. Además dicha columna debe ser not null.
Haciendo algunas consultas sobre el catalogo:
rop@ROP11G> select table_name,
2 partitioning_type,
3 partition_count,
4 ref_ptn_constraint_name
5 from user_part_tables
6 where table_name = 'ITEMS';
TABLE_NAME PARTITION PARTITION_COUNT REF_PTN_CONSTRAINT_NAME
------------------------------ --------- --------------- ------------------------------
ITEMS REFERENCE 7 FACTURAS_FK
rop@ROP11G> select table_name,partition_name,high_value
2 from user_tab_partitions
3 where table_name in ('FACTURAS','ITEMS')
4 order by table_name,partition_name;
TABLE_NAME PARTITION_NAME HIGH_VALUE
------------------------------ ------------------------------ --------------------------------------------------------------------------------
FACTURAS FACTURAS_MAX MAXVALUE
FACTURAS FACTURAS_200901 TO_DATE(' 2009-02-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
FACTURAS FACTURAS_200902 TO_DATE(' 2009-03-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
FACTURAS FACTURAS_200903 TO_DATE(' 2009-04-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
FACTURAS FACTURAS_200904 TO_DATE(' 2009-05-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
FACTURAS FACTURAS_200905 TO_DATE(' 2009-06-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
FACTURAS FACTURAS_200906 TO_DATE(' 2009-07-01 00:00:00', 'SYYYY-MM-DD HH24:MI:SS', 'NLS_CALENDAR=GREGORIA
N')
ITEMS FACTURAS_MAX
ITEMS FACTURAS_200901
ITEMS FACTURAS_200902
ITEMS FACTURAS_200903
ITEMS FACTURAS_200904
ITEMS FACTURAS_200905
ITEMS FACTURAS_200906
14 filas seleccionadas.
Como se observa en el listado de arriba se crearon en la tabla ITEMS tantas particiones como en la tabla FACTURA. Eso se hizo automaticamente y en forma transparente.
Las constraints de referencia (fk's) y not null sobre la tabla ITEMS deben estar habilitadas y validadas y no pueden ser deferrables. Tampoco se puede particionar por referencia sobre columnas virtuales.
Particionamiento Compuesto
El partitioning existe desde la version 8. En esa versión solo se podía particionar por un nivel. Recien cuando apareció la versión 8i se agregó la funcionalidad que permite crear subparticiones lo cual aportó mayor granularidad. El tema era que solo permitia partionar/subparticionar por rango/hash. A partir de 9i se extendió la combinación de partición/subpartición a range/list (la posibilidad de particionar por list se introdujo en 9i). Tuvimos que esperar a 11g para no tener limitaciones para combinar particiones y subparticiones, es decir en 11g podemos usar cualquiera de las siguientes variantes:
* Range-range
* Range-hash
* Range-list
* List-range
* List-hash
* List-list
Veamos con un ejemplo, comparando 10g con 11g:
Voy a tratar de crear una tabla con particion por lista y subparticion por lista sobre una base 10g
rop@ROP10G> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for Solaris: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production
rop@ROP10G>
rop@ROP10G> create table movimientos (id_movimiento number(10),
2 id_caja number(8),
3 planta number(1),
4 ubicacion char(10),
5 estado char(2),
6 dia char(2),
7 mes char(2))
8 partition by list (planta)
9 subpartition by list (estado)
10 subpartition template
11 (
12 subpartition E1 values ('E1'),
13 subpartition E2 values ('E2'),
14 subpartition E3 values ('E3'),
15 subpartition E4 values ('E4'),
16 subpartition E5 values ('E5'))
17 (
18 partition mov_P1 values (1),
19 partition mov_P2 values (2),
20 partition mov_P3 values (3),
21 partition mov_P4 values (4)
22 ) ;
subpartition by list (estado)
*
ERROR en línea 9:
ORA-00922: falta la opción o no es válida
El intento, tal cual esperabamos, falló. Veamos ahora de hacerlo en 11g:
rop@ROP10G> conn rop@movi11d
Introduzca la contraseña: ******
Conectado.
rop@ROP11G> create table movimientos (id_movimiento number(10),
2 id_caja number(8),
3 planta number(1),
4 ubicacion char(10),
5 estado char(2),
6 dia char(2),
7 mes char(2))
8 partition by list (planta)
9 subpartition by list (estado)
10 subpartition template
11 (
12 subpartition E1 values ('E1'),
13 subpartition E2 values ('E2'),
14 subpartition E3 values ('E3'),
15 subpartition E4 values ('E4'),
16 subpartition E5 values ('E5'))
17 (
18 partition mov_P1 values (1),
19 partition mov_P2 values (2),
20 partition mov_P3 values (3),
21 partition mov_P4 values (4)
22 ) ;
Tabla creada.
La tabla se creo sin problemas. Veamos las vistas de catalogo para ver como quedó definida la nueva tabla:
rop@ROP11G> select table_name,partition_name,subpartition_name
2 from user_tab_subpartitions
3 where table_name = 'MOVIMIENTOS';
TABLE_NAME PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------ ------------------------------
MOVIMIENTOS MOV_P1 MOV_P1_E5
MOVIMIENTOS MOV_P1 MOV_P1_E4
MOVIMIENTOS MOV_P1 MOV_P1_E3
MOVIMIENTOS MOV_P1 MOV_P1_E2
MOVIMIENTOS MOV_P1 MOV_P1_E1
MOVIMIENTOS MOV_P2 MOV_P2_E5
MOVIMIENTOS MOV_P2 MOV_P2_E4
MOVIMIENTOS MOV_P2 MOV_P2_E3
MOVIMIENTOS MOV_P2 MOV_P2_E2
MOVIMIENTOS MOV_P2 MOV_P2_E1
MOVIMIENTOS MOV_P3 MOV_P3_E5
MOVIMIENTOS MOV_P3 MOV_P3_E4
MOVIMIENTOS MOV_P3 MOV_P3_E3
MOVIMIENTOS MOV_P3 MOV_P3_E2
MOVIMIENTOS MOV_P3 MOV_P3_E1
MOVIMIENTOS MOV_P4 MOV_P4_E5
MOVIMIENTOS MOV_P4 MOV_P4_E4
MOVIMIENTOS MOV_P4 MOV_P4_E3
MOVIMIENTOS MOV_P4 MOV_P4_E2
MOVIMIENTOS MOV_P4 MOV_P4_E1
20 filas seleccionadas.
rop@ROP11G> select table_name,partitioning_type,subpartitioning_type
2 from user_part_tables
3 where table_name = 'MOVIMIENTOS';
TABLE_NAME PARTITION SUBPART
------------------------------ --------- -------
MOVIMIENTOS LIST LIST
rop@ROP11G>
Si bien ahora no tenemos limitación en la combinación habría que pensar cual de esas combinaciones tiene sentido y si se puede aplicar a una situación real.
.JPG)
Hola Pablo, buen artículo.
ResponderEliminarEn tu ejemplo creas una tabla con un campo fecha que admite nulos.
Cuando particionas, ¿dónde "van a parar" los registros que se insertan con un null en el campo de fecha?
Gracias =)
La inserta en la particion miscelanea, en el ejemplo seria la particion FACTURAS_MAX. Te paso un ejemplito sencillo para mostrarte
ResponderEliminarrop@DESA10G> truncate table facturas;
Tabla truncada.
Ahora se que no tengo ningun registro en la tabla. Voy a insertar uno con el criterio que me sugeriste vos, osea con la fecha en null:
rop@DESA10G> insert into facturas values (1,1,'A',null,1);
1 fila creada.
rop@DESA10G> select count(1) from facturas partition (facturas_max);
COUNT(1)
----------
1
rop@DESA10G>
Como se ve, la unica fila insertada (es la que insertamos con fecha en null) va a parar a la particion FACTURAS_MAX
Muchas gracias.
ResponderEliminar