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

Vía : nvtechnotes.wordpress.com

Relacionados:

Segunda parte… Manejo de tablas grandes en Oracle 10g

Ultima Parte… Manejo de tablas grandes en Oracle 10g