Perform Partition Maintenance Operations

1. Documentación en Tahiti -> Oracle Database, 11g Release 2 (11.2) -> Masters Book List -> VLDB and Partitioning Guide -> 4 Partition Administration -> Maintaining Partitions
http://docs.oracle.com/cd/E11882_01/server.112/e25523/part_admin002.htm#i1006820

2. Las operaciones que podemos realizar con particiones son habitualmente añadir, borrar e intercambiar (EXCHANGE) particiones. Practicaremos ejemplos de cada una de ellas, aprovechando los ejercicios que tenemos del objetivo anterior.

-- Añadimos una nueva partición a una tabla particionada de tipo RANGE
-- Siempre que tengamos índices GLOBAL, es recomendable usar "UPDATE INDEXES"
--   · Con esta clausula no nos preocupamos de ver que índices quedan UNUSABLE
--   · Además, aumentamos el tiempo de disponibilidad de los índices GLOBAL
ALTER TABLE SALES_RANGE
  ADD PARTITION sales_q1_2014 VALUES LESS THAN (TO_DATE('01-04-2014', 'DD-MM-YYYY'))
  UPDATE INDEXES;

-- Añadimos una nueva partición a una tabla particionada de tipo HASH
ALTER TABLE EMP_HASH ADD PARTITION;

-- El mismo ejemplo pero sobre una partición de tipo LIST
ALTER TABLE ACCOUNTS_LIST ADD PARTITION p_nonmainland VALUES ('HI', 'PR');

-- También podemos insertar nuevas particiones a tablas compuestas
-- Veamos el ejemplo con una tabla RANGE-HASH
ALTER TABLE SALES_RANGE_HASH
  ADD PARTITION sales_q1_2007
  VALUES LESS THAN (TO_DATE('01-APR-2007','dd-MON-yyyy'));

-- Ejemplo con una tabla RANGE-LIST
ALTER TABLE SALES_RANGE_LIST
  ADD PARTITION q1_2000 VALUES LESS THAN ( TO_DATE('1-APR-2000','DD-MON-YYYY')) TABLESPACE DW01
         (SUBPARTITION q1_2000_northwest VALUES ('OR', 'WA'),
          SUBPARTITION q1_2000_southwest VALUES ('AZ', 'UT', 'NM'),
          SUBPARTITION q1_2000_northeast VALUES ('NY', 'VM', 'NJ'),
          SUBPARTITION q1_2000_southeast VALUES ('FL', 'GA'),
          SUBPARTITION q1_2000_northcentral VALUES ('SD', 'WI'),
          SUBPARTITION q1_2000_southcentral VALUES ('OK', 'TX')
         );

-- Si os acordáis, utilizamos una plantilla para crear una tabla como la anterior
-- Añadir particiones cuando tenemos una plantilla también es más fácil
ALTER TABLE SALES_RANGE_LIST_TEMPLATE
  ADD PARTITION q1_2000 VALUES LESS THAN ( TO_DATE('1-APR-2000','DD-MON-YYYY')) TABLESPACE DW01;

3. Con respecto a los índices, es obvio que no podemos añadir particiones a un índice LOCAL ya que la BD crea y borra las particiones automáticamente según modifiquemos la tabla base. Además, tampoco podemos añadir una partición a un índice GLOBAL porque la última partición tiene el límite de MAXVALUE y debemos dividirla con ALTER INDEX …. SPLIT PARTITION.

-- Añadimos una nueva partición a un índice GLOBAL con la clausula SPLIT
ALTER INDEX AMOUNT_SOLD_IX
  SPLIT PARTITION p_greater_than_1000000 AT (10000000) INTO (
    PARTITION p_10000000,
    PARTITION p_greater_than_10000000);

4. La operación COALESCE consiste en reducir el número de particiones HASH de una tabla

-- Reducimos en uno, el número de particiones HASH de la tabla EMP_HASH
-- Con "UPDATE INDEXES" nos seguramos de que no queden índices UNUSABLE
ALTER TABLE EMP_HASH COALESCE PARTITION UPDATE INDEXES;

5. Borrar particiones de una tabla es bastante sencillo. Lo único a tener en cuenta es como afecta a los índices que podamos tener sobre ella.

-- Borramos una partición sobre una tabla RANGE que contiene un índice GLOBAL
-- Tenemos dos métodos:
--   1. Borrar la partición y hacer un REBUILD de todas las particiones de los índices GLOBAL
--   2. Realizar primero un DELETE (actualiza el índice) y luego borrar la partición
--   3. Utilizar "UPDATE INDEXES"
-- Veamos el primer método
ALTER TABLE SALES_RANGE DROP PARTITION sales_q1_2014;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_100;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_1000;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_10000;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_100000;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_1000000;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_10000000;
ALTER INDEX AMOUNT_SOLD_IX REBUILD PARTITION P_GREATER_THAN_10000000;

-- El segundo método es más sencillo
DELETE FROM SALES_RANGE PARTITION (sales_q1_2014);
ALTER TABLE SALES_RANGE DROP PARTITION sales_q1_2014;

-- El tercer método, es el más aconsejable
ALTER TABLE SALES_RANGE DROP PARTITION sales_q1_2014 UPDATE INDEXES;

-- Una situación excepcional se produce con los índices gestionados automáticamente (INTERVAL)
-- Para resolver el problema pPodemos deshabilitar esta características y luego habilitarla
ALTER TABLE SALES_INTERVAL SET INTERVAL();
ALTER TABLE SALES_INTERVAL DROP PARTITION P3;
ALTER TABLE SALES_INTERVAL SET INTERVAL(NUMTOYMINTERVAL(1, 'MONTH'));

6. Intercambiar una partición (EXCHANGE) es una operación que nos permite sustituir la partición de una tabla particionada por una tabla normal. Este es un procedimiento muy rápido que suele usarse en entornos productivos para minimizar el tiempo de parada, ya que sólo se produce una operación sobre los metadatos de los segmentos implicados.

-- Creamos una tabla temporal que intercambiaremos con una partición de la tabla SALES_RANGE
CREATE TABLE SALES_RANGE_TEMP
  ( 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)
  );

-- Insertamos un registro de prueba
INSERT INTO SALES_RANGE_TEMP VALUES (1, 1, TO_DATE('01-01-2013', 'DD-MM-YYYY'), 'A', 1, 1, 1);
COMMIT;

-- Realizamos el EXCHANGE
--   · Hemos utilizado "INCLUDING INDEXES" para que se intercambien los índices locales
--   · "UPDATE INDEXES" nos sirve para que se actualicen los índices globales
ALTER TABLE SALES_RANGE
  EXCHANGE PARTITION sales_q1_2013
  WITH TABLE SALES_RANGE_TEMP
  INCLUDING INDEXES
  UPDATE INDEXES;

-- Validamos la operación
SELECT * FROM SALES_RANGE PARTITION (SALES_Q1_2013);

-- Borramos la tabla temporal
DROP TABLE SALES_RANGE_TEMP;

7. Nos puede interesar en alguna ocasión fusionar dos particiones (MERGE). No es una operación habitual, así que haremos un ejemplo sencillo.

-- La nueva partición contendrán los valores de LISTA de las otras dos particiones
ALTER TABLE ACCOUNTS_LIST
  MERGE PARTITIONS p_northwest, p_northeast
  INTO PARTITION p_north
  UPDATE INDEXES;

8. Existe muchas más operaciones que podemos realizar sobre particiones y muchos ejemplos que podríamos hacer. Os recomiendo revisar la documentación oficial con todos los ejemplos, los cuales son bastante claros. Algunos de ellos los hemos adaptado y realizado en este objetivo.