Nvidal’s Blog

Just another WordPress.com weblog

Manejo de tablas grandes en Oracle

Tablas Particionadas en Oracle

 

Es un esquema de organización de los datos con el cual podemos dividirla en múltiples objetos de almacenamientos llamados particiones de datos o rangos, dependiendo los valores puede ser dividido en uno o más columnas de la tabla. Cada particiones de datos es almacenado separadamente. Estos objetos almacenados pueden estar en diferentes tablespaces, en el mismo o en una combinación de ambos.

 

Esta nueva forma de almacenamiento de datos fue introducido por primera vez en 8i (8.1.7) como una nueva característica de Data Warehouse para manejo de grandes cantidades de información, realmente fue introducido para hacer más fácil la tarea de mantenimiento de tablas a los administradores de bases de datos. Cuando hay tablas con millones de registros la única forma de darles manteamiento era eliminando los registros utilizando la sentencia DELETE, durando esto un tiempo indefinido y  utilizando grandes recursos del sistema.  Con la introducción de esta nueva forma de organizar la información podemos hacerlo de una forma inteligente, de forma tal que al momento de hacerle mantenimiento solo ejecutar TRUNCATE TABLE …  PARTITION durando esto unos cuantos segundos. Las ventajas son numerosas pues también podemos hacer una búsqueda mucho más rápida sobre tabla particionada,  podemos ir directamente y buscar la información necesitada con solo ejecutar  SELECT …  FROM table PARTITION ( …..) where …, de esta forma solo buscara la información en la partición o particiones indicadas agilizando la búsqueda significativamente o simplemente hacer un backup de la partcion deseada.

 

Para crear una tabla particionada tenemos que antes analizar el  por qué y el cómo, primero debemos saber para qué queremos particional una tabla, las opciones pueden ser por organización, mantenimiento, distribución de la data, buscar más fácilmente la información, etc., luego de tener el por qué procedemos a analizar el cómo hacerlo para esto tenemos que tener en consideración las opciones dependiendo la versión de Oracle que tengamos, ej.

 

Oracle 8i, solo tiene tres forma de organizar la data, Range, Hash, Composite

 

Oracle 9iR2/10g, tiene Range, Hash, Composite, List

 

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 test164874 (

ord_day          NUMBER(2),

ord_month      NUMBER(2),

ord_year         NUMBER(4),

ord_id            NUMBER(10)

)

storage (initial 12k next 12k pctincrease 0 minextents 1)

PARTITION BY RANGE (ord_year,ord_month,ord_day)

(

PARTITION P1 VALUES LESS THAN (2001,3,31)   TABLESPACE PART1,

PARTITION P2 VALUES LESS THAN (2001,6,30)   TABLESPACE part2,

PARTITION P3 VALUES LESS THAN (2001,9,30)   TABLESPACE part3,

PARTITION P4 VALUES LESS THAN (2001,12,32) TABLESPACE part4

)

 

Este tipo de particionamiento esta 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.

 

 

Hash:

 

Los registros de la tabla tienen su localización física determinada aplicando un valor hash a la columna del partition key, este valor es provisto y determinado automáticamente.

 

CREATE TABLE tabpart2(
ord_id   NUMBER(5),
ord_date DATE
)
PARTITION BY HASH(ord_id)
(PARTITION P1 TABLESPACE tbs1,
 PARTITION P2 TABLESPACE tbs2,
 PARTITION P3 TABLESPACE tbs3,
 PARTITION P4 TABLESPACE tbs4
)

 

El particionamiento Hash es mejor utilizado en data que no se presta fácilmente a un particionamiento RANGE, pero que debe ser particionada por razones de performance. EL particionamiento HASH eventualmente distribuye la data entre un numero especifico de particiones. Los registros son alojados en las particiones basados en el valor hash del “partition key”.

 

Composite:

 

Este tipo de particionamiento es un compuesto del particiomaniento RANGE y el HASH.

 

Tiene dos tipo de particionamiento o dos partition key, la primera un range partition key y el hash partition key. Este tipo de particionamiento de tablas hace uso de subpaticiones vía el hash. Este consiste en un conjunto de particiones Range, las cuales están compuesta de particiones HASH.

 

 

CREATE TABLE TAB2(
     ord_id     NUMBER(10),
     ord_day    NUMBER(2),
     ord_month  NUMBER(2),
     ord_year   NUMBER(4)
     )
  PARTITION BY RANGE(ord_year,ord_month,ord_day)
  SUBPARTITION BY HASH(ord_id) 
  SUBPARTITIONS 8
   ( PARTITION q1 VALUES LESS THAN(2001,3,31)
     ( 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(2001,6,30)  
     ( 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(2001,9,30)  
     ( 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(2001,12,31) 
     ( SUBPARTITION q4_h5 TABLESPACE TBS5,
       SUBPARTITION q4_h6 TABLESPACE TBS6,
       SUBPARTITION q4_h7 TABLESPACE TBS7,
       SUBPARTITION q4_h8 TABLESPACE TBS8
     )
   )
/

 

El tipo de particionamiento Composite usa el método RANGE con cada partición y HASH con las subparticiones. Son ideales para almacenamiento de históricos y striping de datos.

 

 

List:

 

Oracle9i añade un nuevo método de particionamiento. Este nuevo método permite explícitamente un control sobre como los registros se guardan en las particiones, permitiendo especificar una lista de valores para el partition key.

 

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)
);

 

 

Características

 

– No soporta múltiples columnas

– Los valores literales deben ser únicos entre toda la lista

– NULL puede ser especificado como un valor literal de la partición

– MAXVALUE no puede ser especificado

– Toda la lista debe tener por lo menos un valor literal

 

 

Espere más informaciones sobre este tema.

marzo 23, 2009 - Posted by | Database, Oracle, Tecnologia

6 comentarios »

  1. Buenísimo….estoy averiguando sobre las formas de particionar bases de datos en Oracle, si tienes más información te agradecería mucho si la subieras….

    Comentario por Katty | agosto 12, 2009 | Responder

    • Gracias, Katty estoy preparando otro documento sobre tablas particionadas, posiblemente entre hoy y manana lo publique. Espero sean utiles a tus necesidades.

      Comentario por nvidal | agosto 13, 2009 | Responder

    • saludos kati, te estoy informando que ya actualice mi blog con nuevas informaciones sobre menejo de tablas grandes en oracle (partitions)

      Comentario por nvidal | septiembre 15, 2009 | Responder

  2. Hola Nicolás, antes que nada te felicito por el blog, ya he leído varios artículos y son muy completos.
    Quisiera realizar una pregunta sobre este tema:

    Tengo entendido que para usar el particionamiento de tablas se debe tener la versión Enterprise de Oracle y además abonar un paquete aparte que es el de Oracle Partitioning, entonces si uno no tiene acceso a dicho módulo, qué operaciones de mantenimiento recomendarías para realizar en una tabla de unos 50 millones de registro que por cada mes se le insertan unos 10 millones más.

    Muchas gracias.

    Comentario por Facundo | octubre 5, 2009 | Responder

    • Gracias fernando por leer y dar credito a lo que publico.
      Fernando, realmente tienes que tener Oracle Enterprise y la opcion de Partitioning la cual es una opcion que se fatura por separado.

      Para dar mantenimiento a una tabla de 50 millones de registros, el mantenimiento consiste en borrar un mes de informacion, 10 millones, segun me dices. Bueno la unica opcion es borrar con el comando DELETE.

      Algo que puedes hacer, pero esto solo es beneficioso si la cantidad de registros que se van a quedar en la tabla es menos de los que se van a borrar, es crear una nueva tabla hacer un insert select con los registros que vas a dejar, renombrar la tabla orginal con otro nombre y luego renombrar la nueva tabla al nombre de la original. Luego puedes sacar un backup de la tabla vieja y borrarla del sistema.

      Esto tiene desventajas, ej. Al momento de hacer el rename la aplicacion que usa la tabla no puede estar corriendo, solo tiene sentido si la cantidad de registros a borrar es mayor que los que se quedan en la tabla, los procedimientos dependientes se invalidan (hay que recompilarlo), etc

      La ventaja es que el tiempo de ejecucion es mucho mejor que un DELETE.

      Bueno, no me dices cuantos meses de informacion mantienes en la tabla y/o cuantos meses debes eliminar de la tabla al momento del mantenimiento.

      con el comando DELETE puedes hacer el mantenimiento pero debes siempre tener en consideracion que esta es la operacion mas lenta, trata de tener lo menos indices posibles sobre esta tabla, tomar en consideracion lo de hacer un insert select dependiendo la cantidad de registros a eliminar (lo que te comento arriba). Tener pendiente que cada vez que hacer una operacion de DELETE hay bloques que se quedan vacios y eso puede generar una fragmentacion de la tabla y/o indices, trata de hacer un MOVE a la tabla y un REBUILD al indice para arreglar esto. Tambien utilizar los tablespaces LOCAL de manejo UNIFORM, si usas 8i en adelante.

      Espero que esto te haya podido ayudar en algo, pues la opcion de PARTITION llego para eliminar estos problemas de mantenimientos en las tablas de grandes cantidades de informacion.

      Cualquier dudas, por favor contactarme por esta misma via e invitarte a que visites mi blog.
      Bye

      Comentario por nvidal | octubre 5, 2009 | Responder

      • Hola Nicolás, gracias por la respuesta tan rápida!.

        Quizás no me expresé bien al realizar la pregunta, creo que me faltaron datos.

        La base en la cual trabajo es una Oracle 11g.

        En este caso la tabla de 50 millones de registros no se puede borrar nunca y todos los meses se agregan 10 millones.
        Yo me refería a realizarle tareas de mantenimiento como por ejemplo reconstruir índices u otras, siempre y cuando sea beneficioso para la tabla y para las consultas a la misma.

        Por otra parte, de qué se tratan los tablespaces LOCAL de manejo UNIFORM?

        Muchas gracias de nuevo.

        Comentario por Facundo | octubre 6, 2009


Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: