viernes, 21 de septiembre de 2007

Exchange Partition

Exchange Partition permite cargar en tablas particionadas datos en forma rápida y con muy poco impacto para los usuarios que se encuentran activos.
En resumen, lo que hace la sentencia Exchange Partition es modificar el diccionario de datos y simular que los datos que ya tenemos cargados en una tabla, corresponden a una partición determinada de otra tabla.

Veamos un ejemplo muy sencillo para entender mejor éste tema:

SQL_10gR2> CREATE TABLE datos_1 AS
2 SELECT level id, timestamp'2000-11-02 09:00:00' fecha
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

Elapsed: 00:00:01.06

SQL_10gR2> CREATE TABLE datos_2 AS
2 SELECT level id, timestamp'2001-09-10 13:00:00' fecha
3 FROM dual
4 CONNECT BY level <= 100000 ;

Table created.

Lo que hicimos fue crear 2 tablas con distintas fechas en cada una de ellas.

Ahora creamos solamente la estructura de la tabla particionada en donde vamos a cargar los datos:

SQL_10gR2> CREATE TABLE test
2 ( id, fecha )
3 PARTITION BY RANGE ( fecha )
4 (
5 PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
6 PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
7 )
8 AS
9 SELECT 1, timestamp'2000-11-02 09:00:00'
10 FROM dual
11 WHERE 1 = 0 ;

Vamos a realizar un alter para modificar el diccionario de datos y relacionar cada una de las 2 tablas que creamos con la respectiva partición de la tabla TEST...

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2000
3 WITH table datos_1
4 WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.03

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2001
3 WITH table datos_2
4 WITHOUT VALIDATION ;

Table altered.

Elapsed: 00:00:00.02

SQL_10gR2> SELECT count(*)
2 FROM test ;

COUNT(*)
----------
200000

1 row selected.

SQL_10gR2> SELECT count(*)
2 FROM datos_1 ;

COUNT(*)
----------
0

1 row selected.

SQL_10gR2> SELECT count(*)
2 FROM datos_2 ;

COUNT(*)
----------
0

1 row selected.

Como podemos ver, con el Exchange Partition no tardamos casi nada en cargar los datos en la tabla particionada ya que en realidad no estamos cargando los datos, simplemente se modifica el diccionario de datos.

Pueden notar que agregué la sentencia WITHOUT VALIDATION. Que es ésto? WITHOUT VALIDATION suele ser una operación rápida porque sólo realiza modificaciones en el diccionario de datos. Si la tabla o tabla particionada que colocamos en el Exchange Partition tiene una primary key o unique constraint habilitado, entonces el Exchange Partition se realiza como WITH VALIDATION para mantener la integridad de las constraints.

Vamos a ejecutar nuevamente los 2 alter anteriores sin la sentencia WITHOUT VALIDATION...

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2000
3 WITH table datos_1 ;

Table altered.

Elapsed: 00:00:01.00

SQL_10gR2> ALTER TABLE test
2 EXCHANGE PARTITION year_2001
3 WITH table datos_2 ;

Table altered.

Elapsed: 00:00:01.05

Si hubiera ejecutado esos alter con un Trace, el reporte del Trace me mostraría, entre otras sentencias, las siguientes...

select 1
from
"DATOS_1" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 1 0.04 0.04 0 65 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 0 66 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DATOS_1 (cr=65 pr=0 pw=0 time=44582 us)


select 1
from
"DATOS_2" where TBL$OR$IDX$PART$NUM("TEST", 0, 3,1048576,"FECHA") != :1

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 1 0 0
Fetch 1 0.04 0.04 0 65 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 3 0.04 0.04 0 66 0 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 81 (recursive depth: 1)

Rows Row Source Operation
------- ---------------------------------------------------
0 TABLE ACCESS FULL DATOS_2 (cr=65 pr=0 pw=0 time=46957 us)

Antes que nada, notamos que los alter se ejecutaron en mayor tiempo, cierto? De las consultas que observamos del Trace, vemos que se está realizando un FULL SCAN de las tablas y que se está ejecutando una función en el WHERE de cada consulta. Imagínense si tenemos que realizar ésta clase de procesos en ambientes con gran volumen de datos y en donde el sistema se encuentra saturado por el I/O a disco.

Qué sucede si no tenemos los datos separados por año en distintas tablas, y en cambio, tenemos todos los datos en una misma tabla? Bueno, tomando como ejemplo la tabla TEST que acabamos de cargar, podríamos realizar lo siguiente...

SQL_10gR2> CREATE TABLE test_2
2 ( id, fecha )
3 PARTITION BY RANGE ( fecha )
4 (
5 PARTITION year_2000 VALUES LESS THAN ( timestamp'2000-12-02 00:00:00' ),
6 PARTITION year_2001 VALUES LESS THAN ( timestamp'2001-10-10 00:00:00' )
7 )
8 AS
9 SELECT *
10 FROM test ;

Table created.

Elapsed: 00:00:05.04

SQL_10gR2> DROP TABLE test ;

Table dropped.

SQL_10gR2> ALTER TABLE test_2 RENAME TO test ;

Table altered.

SQL_10gR2> SELECT count(*)
2 FROM test ;

COUNT(*)
----------
200000

1 row selected.

15 comentarios:

Anónimo dijo...

¿Se puede hacer si las tablas y/o particiones estan en diferentes tablespaces?

Leonardo Horikian dijo...

Hola,

Si, por supuesto. Se puede hacer.

Saludos.

diego micheli dijo...

Hola Leonardo. He visto que en algunos casos, en DW, cuando hay que cargar una tabla particionada, conviene hacerlo en una tabla aparte, y luego aplicar exchange partition. Exactamente que motiva esto? Entiendo que una de las razones es poder droppear y re-crear indices bitmap. Hay alguna otra razon por la que quede bloqueada la tabla para lectura? En mi caso tengo la tabla particionada por dia, e inserto varias veces por dia, siempre con +append. Entiendo que en este caso, insertar directamente tiene un impacto bajo en otras consultas que se esten ejecutando.
Gracias.

Leonardo Horikian dijo...

Hola Diego, "exchange partition" es un proceso muy rápido ya que, internamente, sólo se realiza un intercambio de segmentos y no se realiza ningún movimiento de datos. Hay casos en el cual nos beneficiaríamos de utilizarlo, pero hay otros casos en que simplemente no podemos hacerlo ya que el modelo de nuestra tabla y el cómo la tenemos particionada, nos impide hacerlo.

El "exchange partition" debería realizarse cuando no se estén realizando sentencias DML o consultas a la tabla (los cursores deben ser invalidados, etc).

El uso de APPEND está limitado a casos específicos. Lo estas utilizando correctamente? Podes leer el post "APPEND hint" para más detalles.

Saludos.

Anónimo dijo...

Leonardo,, primero que nada, muy bueno tu blog, te queria hacer una consulta, aplique tu guia para particionar una Tabla, pero me arroja el siguiente error ORA-14006 Nombre de Particion no Valido,, una consulta,, ¿Como creo la Particion??,, atento a tu respuesta
Marcelo
marcelom940@gmail.com

Leonardo Horikian dijo...

Hola Marcelo, si tenes las particiones creadas, seguramente el error que estas recibiendo se deba a que estas colocando nombres de particiones incorrectas en el comando que estas ejecutando.
Fijate en la nota que muestro un ejemplo de como crear unas particiones, sino enviame los comando que estas ejecutando y te ayudo.

Saludos.

marcelo dijo...

efectivamente, estaba creando las particiones con nombres numericos debiendo ser alfanumericos, pero ahora tengo otro inconveniente, tu sabes como instalar el JDeveloper 10g sobre WinXP SP2 me arroja un error al momento del chequeo de los requisitos, que dice espacio de intercambio en 0MB requerido 1535MB, tengo espacio en disco suficiente, al gual que en la memoria del PC, atento a tu respuesta, atte

Jose Villegas dijo...

Leonardo, como puedo volver a obtener mis tablas originales, es decir desvincularlas del Exchange partition. Sds

Leonardo Horikian dijo...

Hola Jose Villegas,

Por lo que me comentas, me imagino que ya tenes las particiones creadas e hiciste un exchange partition para cargar con datos esas particiones. Lo que queres hacer, si mal no entiendo, es volver a tener la tabla original en base a alguna de tus particiones.
Podrías realizar eso de la siguiente manera (siguiendo mi ejemplo...):

Veamos la cantidad de registros que existen en cada partición de la tabla TEST:

SQL_10gR2> SELECT count(*)
2 FROM test
3 PARTITION (year_2000);

COUNT(*)
----------
100000

SQL_10gR2> SELECT count(*)
2 FROM test
3 PARTITION (year_2001);

COUNT(*)
----------
100000

Bien, ahora podemos restaurar los datos en las tablas originales:

SQL_10gR2> INSERT INTO datos_1
2 SELECT *
3 FROM test
4 PARTITION (year_2000);

100000 rows created.

SQL_10gR2> INSERT INTO datos_2
2 SELECT *
3 FROM test
4 PARTITION (year_2001);

100000 rows created.

Luego puedes eliminar los datos de las particiones...

SQL_10gR2> ALTER TABLE test TRUNCATE PARTITION year_2000;

Table truncated.

SQL_10gR2> ALTER TABLE test TRUNCATE PARTITION year_2001;

Table truncated.

Listo! Espero que esto sea lo que estabas buscando.

Saludos

Anónimo dijo...

Hola Leonardo.
Primeramente felicitarte por el Blog!
Mi duda es sobre AÑADIR PARTICIONES y SUBPARTICIONES a una tabla ya particionada.
La tabla se creó tal que así:

create table ADMITACA.ITA_TH_MFI
(
campo_particion NUMBER(2) not null,
...
campo_SubParticion CHAR(5) not null,
...
)
PARTITION BY RANGE (campo_particion)
SUBPARTITION BY LIST (campo_SubParticion)
SUBPARTITION TEMPLATE(
SUBPARTITION AAA VALUES('AAA') TABLESPACE TSD_HECHOS,
SUBPARTITION AAA VALUES('BBB') TABLESPACE TSD_HECHOS,
...
SUBPARTITION LLL VALUES('LLL') TABLESPACE TSD_HECHOS,
SUBPARTITION MMM VALUES('MMM') TABLESPACE TSD_HECHOS,
SUBPARTITION OTROS VALUES (DEFAULT) TABLESPACE TSD_HECHOS
)
(
PARTITION ver_0 values less than (1),
PARTITION ver_1 values less than (2),
PARTITION ver_2 values less than (3),
...
PARTITION ver_30 values less than (31)
);

Y el caso es que debo crear nuevas SUBparticiones para, por ejemplo, valores NNN, OOO, PPP... del campo_SubParticion. El resto de particiones que ya hay hechas en el sistema, no se las desea tocar ni modificar.
Por favor, ¿me podrías ayudar con la sentencia que debería lanzar? No consigo encontrar en ningún sitio una sintaxis clar de lo que debería hacer. He visto como añadir particiones, pero en realidad lo que yo necesito en este caso son subparticiones.
Muchas gracias anticipadas.
Un cordial saludo. Jorge

Leonardo Horikian dijo...

Hola, hay una manera de agregar subparticiones a una partición determinada.

Veamos un ejemplo utilizando tu misma tabla:

SQL> create table ITA_TH_MFI
2 (
3 campo_particion NUMBER(2) not null,
4 campo_SubParticion CHAR(5) not null
5 )
6 PARTITION BY RANGE (campo_particion)
7 SUBPARTITION BY LIST (campo_SubParticion)
8 SUBPARTITION TEMPLATE
9 (
10 SUBPARTITION AAA VALUES('AAA'),
11 SUBPARTITION BBB VALUES('BBB'),
12 SUBPARTITION LLL VALUES('LLL'),
13 SUBPARTITION MMM VALUES('MMM')
14 )
15 (
16 PARTITION ver_0 values less than (1),
17 PARTITION ver_1 values less than (2),
18 PARTITION ver_2 values less than (3),
19 PARTITION ver_30 values less than (31)
20 );

Table created.

SQL> SELECT PARTITION_NAME, SUBPARTITION_NAME
2 FROM USER_TAB_SUBPARTITIONS
3 WHERE TABLE_NAME = 'ITA_TH_MFI'
4 /

PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
VER_0 VER_0_AAA
VER_0 VER_0_BBB
VER_0 VER_0_LLL
VER_0 VER_0_MMM
VER_1 VER_1_AAA
VER_1 VER_1_BBB
VER_1 VER_1_LLL
VER_1 VER_1_MMM
VER_2 VER_2_AAA
VER_2 VER_2_BBB
VER_2 VER_2_LLL
VER_2 VER_2_MMM
VER_30 VER_30_AAA
VER_30 VER_30_BBB
VER_30 VER_30_LLL
VER_30 VER_30_MMM

16 rows selected.

SQL> ALTER TABLE ITA_TH_MFI MODIFY PARTITION VER_0
2 ADD SUBPARTITION VER_0_NNN VALUES ('NNN');

Table altered.

SQL> ALTER TABLE ITA_TH_MFI MODIFY PARTITION VER_1
2 ADD SUBPARTITION VER_1_NNN VALUES ('NNN');

Table altered.

SQL> ALTER TABLE ITA_TH_MFI MODIFY PARTITION VER_2
2 ADD SUBPARTITION VER_2_NNN VALUES ('NNN');

Table altered.

SQL> ALTER TABLE ITA_TH_MFI MODIFY PARTITION VER_30
2 ADD SUBPARTITION VER_30_NNN VALUES ('NNN');

Table altered.

SQL> SELECT PARTITION_NAME, SUBPARTITION_NAME
2 FROM USER_TAB_SUBPARTITIONS
3 WHERE TABLE_NAME = 'ITA_TH_MFI'
4 /

PARTITION_NAME SUBPARTITION_NAME
------------------------------ ------------------------------
VER_0 VER_0_AAA
VER_0 VER_0_BBB
VER_0 VER_0_LLL
VER_0 VER_0_MMM
VER_0 VER_0_NNN
VER_1 VER_1_AAA
VER_1 VER_1_BBB
VER_1 VER_1_LLL
VER_1 VER_1_MMM
VER_1 VER_1_NNN
VER_2 VER_2_AAA
VER_2 VER_2_BBB
VER_2 VER_2_LLL
VER_2 VER_2_MMM
VER_2 VER_2_NNN
VER_30 VER_30_AAA
VER_30 VER_30_BBB
VER_30 VER_30_LLL
VER_30 VER_30_MMM
VER_30 VER_30_NNN

20 rows selected.

Espero te sea de utilidad.

Saludos

Kepler dijo...

Hola Leonardo, me gusta tu blog. Quería comentarte (y creo que no comentas nada al respecto) que hay que tener cuidado porque al hacer el ALTER TABLE xxx EXCHANGE PARTITION machacaremos los datos que hubiera en aquella partición.

Corrígeme si me equivoco please.

Un saludo.

Leonardo Horikian dijo...

Hola Kepler,

Al hacer "ALTER TABLE xxx EXCHANGE PARTITION" se borran automáticamente las tablas en las cuales teniamos los datos base cargados (En el caso de mi ejemplo, las tablas datos_1 y datos_2).
No se borran los datos de las particiones; sólo las tablas a las cuales le aplicamos el EXCHANGE PARTITION.

Saludos

Ana Cabello dijo...

Hola.
Tengo una duda con el exhange partition.
Cuando realizo el alter table .. exchange partition y realizo una consulta sobre la tabla particionada solo veo los datos de una partición (es decir de la que he hecho el exchange partition) no veo el resto, si no es haciendo referencia a la partición.
¿Hay alguna manera de hacer un select * from tabla_particion y ver todos los datos?
Gracias.

Un saludo
Ana

Leonardo Horikian dijo...

Hola Ana Cabello,

Por favor, muestrame la sentencia del EXCHANGE PARTITION que estas utilizando y las consultas que estas realizando sobre la tabla particionada.

Saludos