1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> VLDB and Partitioning Guide
Aparte de la dirección donde vienen varios capítulos relativos al particionamiento me gustaría destacar estos otros:
· Tipos de Particiones
· Recomendaciones para escoger una estrategía
· Varios ejemplos de creación de particiones
2. El Particionamiento en Oracle es un tema muy denso. Hay un manual completo dedicado a las particiones y sus ventajas dentro de las BBDD muy grandes (VLDB). En el examen se dedican tres objetivos a este tema por la cantidad de requisitos a cubrir. Estos son los tres objetivos que cubren las particiones en el examen:
[ ] Administer partitioned tables and indexes using appropriate methods and keys (este objetivo)
[ ] Perform partition maintenance operations
[ ] Maintain indexes on a partitioned table
Este objetivo concreto trata de cubrir las diferentes opciones que tenemos para crear tablas e índices particionados. Además, debemos saber qué tipo de partición es la más adecuada para nuestro propósito, así como la clave o claves de partición que debemos utilizar.
3. Empezamos creando diferentes tipos de tablas particionadas. En los ejemplos que vamos a ir haciendo hay que prestar atención al tipo de particionamiento que hacemos y el por qué. Existen tres tipos de tablas particionadas: RANGE, HASH y LIST. Además, están se pueden combinar entre sí. En la documentación se nos explican varias razones por las cuales un método puede ser mejor en una u otra ocasión (Recommendations for Choosing a Partitioning Strategy)
-- Hacemos los ejemplos sobre el esquema SH
-- Creamos varios TABLESPACES para nuestras pruebas
CREATE TABLESPACE DW01 DATAFILE '/u01/app/oracle/oradata/OCM/dw0101.dbf' size 100M;
CREATE TABLESPACE DW02 DATAFILE '/u01/app/oracle/oradata/OCM/dw0201.dbf' size 100M;
CREATE TABLESPACE DW03 DATAFILE '/u02/app/oracle/oradata/OCM/dw0301.dbf' size 100M;
CREATE TABLESPACE DW04 DATAFILE '/u02/app/oracle/oradata/OCM/dw0401.dbf' size 100M;
CREATE TABLESPACE DW05 DATAFILE '/u01/app/oracle/oradata/OCM/dw0501.dbf' size 100M;
CREATE TABLESPACE DW06 DATAFILE '/u01/app/oracle/oradata/OCM/dw0601.dbf' size 100M;
CREATE TABLESPACE DW07 DATAFILE '/u02/app/oracle/oradata/OCM/dw0701.dbf' size 100M;
ALTER USER SH QUOTA UNLIMITED ON DW01;
ALTER USER SH QUOTA UNLIMITED ON DW02;
ALTER USER SH QUOTA UNLIMITED ON DW03;
ALTER USER SH QUOTA UNLIMITED ON DW04;
ALTER USER SH QUOTA UNLIMITED ON DW05;
ALTER USER SH QUOTA UNLIMITED ON DW06;
ALTER USER SH QUOTA UNLIMITED ON DW07;
-- Creamos una tabla particionada de tipo RANGE
-- · Este tipo de particionado es adecuado para guardar datos históricos (en este caso de ventas)
-- · Las busquedas que hagamos sobre "time_id" se beneficiarán del "Partition Pruning" (acceso sólo a ciertas particiones)
-- · El mantenimiento de esta tabla es sencillo, pudiendo crear nuevas particiones para nuevos datos y borrar las antiguas
-- · Hemos especificado un TBS distinto para cada partición, de esta forma podemos eliminar TBS facilmente
CREATE TABLE SALES_RANGE
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
( PARTITION sales_q1_2013 VALUES LESS THAN (TO_DATE('2013/04/01','YYYY/MM/DD')) TABLESPACE DW01,
PARTITION sales_q2_2013 VALUES LESS THAN (TO_DATE('2013/07/01','YYYY/MM/DD')) TABLESPACE DW02,
PARTITION sales_q3_2013 VALUES LESS THAN (TO_DATE('2013/10/01','YYYY/MM/DD')) TABLESPACE DW03,
PARTITION sales_q4_2013 VALUES LESS THAN (TO_DATE('2014/01/01','YYYY/MM/DD')) TABLESPACE DW04
);
-- Consultamos ciertos atributos de la tabla que hemos creado con sus particiones
-- Las vistas más consultadas son estas:
-- · DBA_TAB_PARTITIONS
-- · DBA_PART_TABLES
-- · DBA_TABLES
-- · DBA_SEGMENTS
SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME FROM USER_TAB_PARTITIONS;
SELECT TABLE_NAME, PARTITIONING_TYPE, PARTITION_COUNT FROM USER_PART_TABLES;
-- Una nueva funcionalidad que trae Oracle 11g se conoce como "Interval Partitioning"
-- Oracle crear automática las particiones cuando lo necesita en base a un "intervalo"
-- Nosotros seleccionamos que se cree una partición cada mes con la clausula "INTERVAL(NUMTOYMINTERVAL(1, 'MONTH')"
CREATE TABLE SALES_INTERVAL
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id)
INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'))
( PARTITION p0 VALUES LESS THAN (TO_DATE('1-1-2011', 'DD-MM-YYYY')),
PARTITION p1 VALUES LESS THAN (TO_DATE('1-1-2012', 'DD-MM-YYYY')),
PARTITION p2 VALUES LESS THAN (TO_DATE('1-7-2012', 'DD-MM-YYYY')),
PARTITION p3 VALUES LESS THAN (TO_DATE('1-1-2013', 'DD-MM-YYYY')) );
-- Comprobamos el número de particiones que tenemos
SELECT TABLE_NAME, PARTITION_NAME, TABLESPACE_NAME FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_INTERVAL';
-- Insertamos varios registros en la tabla más alla del límite máximo de todas las particiones (1-1-2013)
INSERT INTO SH.SALES_INTERVAL VALUES (1, 1, TO_DATE('2-1-2013', 'DD-MM-YYYY'), 'A', 1, 1, 1);
INSERT INTO SH.SALES_INTERVAL VALUES (1, 1, TO_DATE('2-7-2013', 'DD-MM-YYYY'), 'A', 1, 1, 1);
-- Volvemos a revisar el número de particiones para descubrir que se han creado dos nuevas (Ej. SYS_P93 y SYS_P94)
-- Sólo es necesario crear dos particiones para los nuevos registros porque sólo hay dos meses "nuevos"
SELECT TABLE_NAME, PARTITION_NAME, HIGH_VALUE FROM USER_TAB_PARTITIONS WHERE TABLE_NAME='SALES_INTERVAL';
-- Obtenemos los registros de la nueva partición
SELECT * FROM INTERVAL_SALES PARTITION (SYS_P93);
-- El segundo tipo de particionado es HASH
-- · Utilizaremos HASH cuando no tenemos una división lógica o una clara visión de negocio de los datos
-- · Se suele utilizar para repartir los datos equitativamente entre varios particiones y/o dispositivos
-- · Es recomendable que la clave de partición sea UNIQUE o casi única
-- · En nuestro ejemplo indicamos que las particiones se asignen a los 4 TBS con algoritmo Round-Robin
CREATE TABLE EMP_HASH (
EMPNO NUMBER(4) NOT NULL,
ENAME CHAR(10),
JOB CHAR(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2),
PROJNO NUMBER,
LOADSEQ NUMBER)
PARTITION BY HASH (EMPNO)
PARTITIONS 4
STORE IN (DW01, DW02, DW03, DW04);
-- El último tipo de particiones es LIST
-- · Utilizaremos este tipo cuando queremos dividir las particiones en base a valores discretos
-- · En particiones de tipo LIST sólo podemos usar una clave de partición, al contrario que los otro métodos
CREATE TABLE ACCOUNTS_LIST
( id NUMBER
, account_number NUMBER
, customer_id NUMBER
, branch_id NUMBER
, region VARCHAR(2)
, status VARCHAR2(1)
)
PARTITION BY LIST (region)
( PARTITION p_northwest VALUES ('OR', 'WA')
, PARTITION p_southwest VALUES ('AZ', 'UT', 'NM')
, PARTITION p_northeast VALUES ('NY', 'VM', 'NJ')
, PARTITION p_southeast VALUES ('FL', 'GA')
, PARTITION p_northcentral VALUES ('SD', 'WI')
, PARTITION p_southcentral VALUES ('OK', 'TX')
);
4. Hemos visto ejemplo de particionados únicos, pero también tenemos la posibilidad de combinar dos estrategías distintas. Se le conoce como COMPOSITE PARTITIONING o particionamiento compuesto. Estas son las distintas combinaciones que podemos tener:
· Range-Range
· Range-Hash
· Range-List
· List-Range
· List-Hash
· List-List
No vamos a probar todas las combinaciones ya que en la documentación están todas. Pero si quieres probar la más típicas.
-- Creamos una partición compuesta de tipo Range-Hash
-- · Indicado para datos históricos de gran volumen
-- · Se puede utilizar "Partition Pruning" a nivel de Range y "Partition-wise Joins" a nivel de Hash
CREATE TABLE SALES_RANGE_HASH
( prod_id NUMBER(6)
, cust_id NUMBER
, time_id DATE
, channel_id CHAR(1)
, promo_id NUMBER(6)
, quantity_sold NUMBER(3)
, amount_sold NUMBER(10,2)
)
PARTITION BY RANGE (time_id) SUBPARTITION BY HASH (cust_id)
SUBPARTITIONS 8 STORE IN (DW01, DW02, DW03, DW04)
( PARTITION sales_q1_2006 VALUES LESS THAN (TO_DATE('01-APR-2006','dd-MON-yyyy'))
, PARTITION sales_q2_2006 VALUES LESS THAN (TO_DATE('01-JUL-2006','dd-MON-yyyy'))
, PARTITION sales_q3_2006 VALUES LESS THAN (TO_DATE('01-OCT-2006','dd-MON-yyyy'))
, PARTITION sales_q4_2006 VALUES LESS THAN (TO_DATE('01-JAN-2007','dd-MON-yyyy'))
);
-- El siguiente ejemplo más extenso no por ello más complicado se trata de una tabla particionada Range-List
-- · Fijaros que tenemos una tabla histórica de ventas particionada por trimestres
-- · Nos interesa, como requisito de negocio, didivir las particiones en areas geográficas concretas
CREATE TABLE SALES_RANGE_LIST
(
deptno number,
item_no varchar2(20),
txn_date date,
txn_amount number,
state varchar2(2)
)
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
(PARTITION q1_1999 VALUES LESS THAN (TO_DATE('1-APR-1999','DD-MON-YYYY')) TABLESPACE DW01
(SUBPARTITION q1_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q1_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q1_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q1_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q1_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q1_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('1-JUL-1999','DD-MON-YYYY')) TABLESPACE DW02
(SUBPARTITION q2_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q2_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q2_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q2_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q2_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q2_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q3_1999 VALUES LESS THAN (TO_DATE('1-OCT-1999','DD-MON-YYYY')) TABLESPACE DW03
(SUBPARTITION q3_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q3_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q3_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q3_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q3_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q3_1999_southcentral VALUES ('OK', 'TX')
),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY')) TABLESPACE DW04
(SUBPARTITION q4_1999_northwest VALUES ('OR', 'WA'),
SUBPARTITION q4_1999_southwest VALUES ('AZ', 'UT', 'NM'),
SUBPARTITION q4_1999_northeast VALUES ('NY', 'VM', 'NJ'),
SUBPARTITION q4_1999_southeast VALUES ('FL', 'GA'),
SUBPARTITION q4_1999_northcentral VALUES ('SD', 'WI'),
SUBPARTITION q4_1999_southcentral VALUES ('OK', 'TX')
)
);
-- Podemos listar todas las subparticiones que hemos creado (24)
SELECT
TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
FROM
USER_TAB_SUBPARTITIONS
WHERE
TABLE_NAME='SALES_RANGE_LIST'
ORDER BY 2,3;
-- Como habéis visto, la definición del ejemplo anterior repite continuamente el mismo grupo de subparticiones
-- Podemos crear una tabla particionado definiendo una plantilla para las subparticiones (así simplificamos el DDL)
CREATE TABLE SALES_RANGE_LIST_TEMPLATE
( deptno number, item_no varchar2(20),
txn_date date, txn_amount number, state varchar2(2))
PARTITION BY RANGE (txn_date)
SUBPARTITION BY LIST (state)
SUBPARTITION TEMPLATE
(SUBPARTITION northwest VALUES ('OR', 'WA') TABLESPACE DW01,
SUBPARTITION southwest VALUES ('AZ', 'UT', 'NM') TABLESPACE DW02,
SUBPARTITION northeast VALUES ('NY', 'VM', 'NJ') TABLESPACE DW03,
SUBPARTITION southeast VALUES ('FL', 'GA') TABLESPACE DW04,
SUBPARTITION northcentral VALUES ('SD', 'WI') TABLESPACE DW05,
SUBPARTITION southcentral VALUES ('OK', 'TX') TABLESPACE DW06,
SUBPARTITION others VALUES (DEFAULT) TABLESPACE DW07
)
(PARTITION q1_1999 VALUES LESS THAN ( TO_DATE('01-APR-1999','DD-MON-YYYY')),
PARTITION q2_1999 VALUES LESS THAN ( TO_DATE('01-JUL-1999','DD-MON-YYYY')),
PARTITION q3_1999 VALUES LESS THAN ( TO_DATE('01-OCT-1999','DD-MON-YYYY')),
PARTITION q4_1999 VALUES LESS THAN ( TO_DATE('1-JAN-2000','DD-MON-YYYY'))
);
-- Comprobamos las particiones que hemos creado
SELECT
TABLE_NAME, PARTITION_NAME, SUBPARTITION_NAME, TABLESPACE_NAME
FROM
USER_TAB_SUBPARTITIONS
WHERE
TABLE_NAME='SALES_RANGE_LIST_TEMPLATE'
ORDER BY 2,4;
5. Hasta ahora hemos creado varias tablas particionadas pero siempre hemos utilizado una única clave o columna para dividir las particiones, pero podemos utilizar más de una.
-- Creamos una tabla particionada sobre varias claves -- · El segundo campo "month" se evalua cuando el primero no es suficiente para determinar la partición CREATE TABLE SALES_MULTI ( year NUMBER, month NUMBER, day NUMBER, amount_sold NUMBER) PARTITION BY RANGE (year,month) (PARTITION before2001 VALUES LESS THAN (2001,1), PARTITION q1_2001 VALUES LESS THAN (2001,4), PARTITION q2_2001 VALUES LESS THAN (2001,7), PARTITION q3_2001 VALUES LESS THAN (2001,10), PARTITION q4_2001 VALUES LESS THAN (2002,1), PARTITION future VALUES LESS THAN (MAXVALUE,0));
6. En el siguiente ejemplo quiero combinar varias características ya que no merece la pena verlas por separado.
-- Creamos una tabla con varios aspectos distintivos
-- · Habilitamos la compresión a nivel de tabla, por lo que todas particiones se crearán con COMPRESS
-- · Habilitamos movimiento de filas (ENABLE ROW MOVEMENT) para migrar las filas cuando se modifique la clave
-- · Podemos usar columnas virtuales como la clave de la partición (total_amount)
-- · Con "SEGMENT CREATION IMMEDIATE" forzamos la creación de los segmentos aunque no insertemos registros
-- · Habilitamos PX sobra la tabla (PARALLEL)
-- · Deshabilitamos la generación de REDO (NOLOGGING)
CREATE TABLE SALES_COMBO
( prod_id NUMBER(6) NOT NULL
, cust_id NUMBER NOT NULL
, time_id DATE NOT NULL
, channel_id CHAR(1) NOT NULL
, promo_id NUMBER(6) NOT NULL
, quantity_sold NUMBER(3) NOT NULL
, amount_sold NUMBER(10,2) NOT NULL
, total_amount AS (quantity_sold * amount_sold)
)
SEGMENT CREATION IMMEDIATE
COMPRESS
PARTITION BY RANGE (time_id) INTERVAL (NUMTOYMINTERVAL(1,'MONTH'))
SUBPARTITION BY RANGE(total_amount)
SUBPARTITION TEMPLATE
( SUBPARTITION p_small VALUES LESS THAN (1000)
, SUBPARTITION p_medium VALUES LESS THAN (5000)
, SUBPARTITION p_large VALUES LESS THAN (10000)
, SUBPARTITION p_extreme VALUES LESS THAN (MAXVALUE)
)
(PARTITION sales_before_2007 VALUES LESS THAN
(TO_DATE('01-JAN-2007','dd-MON-yyyy'))
)
ENABLE ROW MOVEMENT
PARALLEL NOLOGGING;
7. Al igual que tenemos tablas particionadas, también podemos particionar los índices. Cuando creamos un índice sobre una tabla particionada puede ser de uno de estos tipos:
· Local Partitioned: Cada clave de una partición del índice apunta a una única partición de la tabla del índice
· Global Partitioned: Cada clave de una mima partición pueden a apuntar a varias particiones distinas de la tabla
· Global Nonpartitioned: Tenemos índice no particionado sobre una tabla particionada
Dentro de los índices particionados localmente, tenemos dos tipos:
· Local prefixed: El índice se particiona sobre un prefijo de las columnas del índice
· Local nonprefixed: El índice no está particionado sobre un prefijo de las columnas del índice
-- Creamos un índice global particionado sobre la tabla SALES_RANGE (tipo RANGE)
CREATE INDEX amount_sold_ix ON sales_range(amount_sold)
GLOBAL PARTITION BY RANGE(amount_sold)
( PARTITION p_100 VALUES LESS THAN (100)
, PARTITION p_1000 VALUES LESS THAN (1000)
, PARTITION p_10000 VALUES LESS THAN (10000)
, PARTITION p_100000 VALUES LESS THAN (100000)
, PARTITION p_1000000 VALUES LESS THAN (1000000)
, PARTITION p_greater_than_1000000 VALUES LESS THAN (maxvalue)
);
-- La creación de un índice LOCAL es mucho más sencilla, ya que las particiones son las mismas que en la tabla
-- Las particiones se crean EMPNO pero el índice está creado sobre DEPTNO => El índice es LOCAL NONPREFIXED
· El índice está particionado sobre EMPNO (aunque no lo indiquemos) porque lo obtiene de la tabla particionada
CREATE INDEX LOC_DEPT_IX ON EMP_HASH(DEPTNO) LOCAL;
-- Pongo un ejemplo de lo que sería un índice LOCAL PREFIXED (índice y particiones sobre EMPNO)
CREATE INDEX LOC_EMPNO_IX ON EMP_HASH(EMPNO) LOCAL;
Respecto a qué índice es más apropiado para qué situación, os recomiendo leer esta sección donde dan unas directrices bastante adecuadas para ello. Expongamos un caso concreto bastante sencillo. Supongamos que tenemos una tabla de registros de llamadas telefónicas. Este tipo de tablas suelen tener millones de registros y además suelen tener un requisito de negocio muy claro que es el número de meses de retención de estos registros. La estrategía habitual consiste en particionar la tabla por fecha y tener un índice local sobre la fecha, para poder mantener la tabla con bastante facilidad y no tener que recrear el índice cada vez que purgamos particiones viejas. Además, podemos tener índices globales sobre otros campos en función de los requisitos de negocio que manejemos.
8. Una funcionalidad nueva de Oracle 11g se llama Reference Partitioning. Nos permite clonar el tipo de particionado de una tabla referencia por una Foreign Key. Es mucho más sencillo explicarlo con un ejemplo que con texto.
-- Creamos varios tablespace para almacenar las tablas particionadas de prueba
CREATE TABLESPACE TBS1 DATAFILE SIZE 20M;
CREATE TABLESPACE TBS2 DATAFILE SIZE 20M;
CREATE TABLESPACE TBS3 DATAFILE SIZE 20M;
CREATE TABLESPACE TBS4 DATAFILE SIZE 20M;
-- Creamos una tabla de productos (muy similar a la tabla OE.PRODUCTS)
CREATE TABLE HASH_PRODUCTS
( PRODUCT_ID NUMBER(6) PRIMARY KEY
, PRODUCT_NAME VARCHAR2(50)
, PRODUCT_DESCRIPTION VARCHAR2(2000)
, CATEGORY_ID NUMBER(2)
, WEIGHT_CLASS NUMBER(1)
, WARRANTY_PERIOD INTERVAL YEAR TO MONTH
, SUPPLIER_ID NUMBER(6)
, PRODUCT_STATUS VARCHAR2(20)
, LIST_PRICE NUMBER(8,2)
, MIN_PRICE NUMBER(8,2)
, CATALOG_URL VARCHAR2(50)
, CONSTRAINT PRODUCT_STATUS_LOV_DEMO
CHECK (PRODUCT_STATUS IN ('ORDERABLE'
,'PLANNED'
,'UNDER DEVELOPMENT'
,'OBSOLETE')
) )
PARTITION BY HASH (PRODUCT_ID)
PARTITIONS 4
STORE IN (TBS1, TBS2, TBS3, TBS4);
-- Aquí es donde creamos una tabla con Reference Partitioning
-- Indicamos que el particionado de la clave debe ser el mismo que tiene HASH_PRODUCTS sobre el campo PRODUCT_ID
CREATE TABLE PART_ORDER_ITEMS (
ORDER_ID NUMBER(12) PRIMARY KEY,
LINE_ITEM_ID NUMBER(3),
PRODUCT_ID NUMBER(6) NOT NULL,
UNIT_PRICE NUMBER(8,2),
QUANTITY NUMBER(8),
CONSTRAINT PRODUCT_ID_FK
FOREIGN KEY (PRODUCT_ID) REFERENCES HASH_PRODUCTS(PRODUCT_ID))
PARTITION BY REFERENCE (PRODUCT_ID_FK);
-- Limpiamos el entorno
DROP TABLE PART_ORDER_ITEMS;
DROP TABLE HASH_PRODUCTS;
DROP TABLESPACE TBS1 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS2 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS3 INCLUDING CONTENTS AND DATAFILES;
DROP TABLESPACE TBS4 INCLUDING CONTENTS AND DATAFILES;