Ejecutar DROP PARTITION evitando UNUSABLE INDEXES
Ejecutar DROP PARTITION evitando UNUSABLE INDEXES
Al ejecutar sentencias DDL (ADD, DROP, MOVE, TRUNCATE, SPLIT, MERGE, EXCHANGE and COALESCE PARTITION) sobre particiones de tablas, hay que tener especial atención sobre los indices que tiene la tabla en cuestión.
Las tablas particionadas pueden tener 3 tipos de indices.
LOCAL: Son indices particionados con la misma clave que la tabla.
GLOBAL: Son indices NO particionados sobre tablas particionadas.
GLOBAL PARTITIONED: Son indices particionados sobre tablas particionadas pero que su particionamiento puede ser distinto al de su tabla correspondiente.
Cuando un indice se crea como el de una tabla no particionada, entonces este indice se crea como GLOBAL.
¿Que casos hay que tener en cuenta?
Los indices LOCAL no necesitan mantenimiento, cuando borramos particiones de una tabla, los indices LOCAL continuan en estado USABLE.
TODOS los indices GLOBAL, tanto los particionados como los no particionados, necesitan tener algunas consideraciones al momento de borrar particiones de sus tablas, porque los mismos quedan en estado UNUSABLE.
Para evitar esta situación, se puede agregar al momento del borrado la clausula UPDATE GLOBAL INDEXES.
Al agregar esta clausula no se invalida el indice, pero tambien reduce la performance del DDL.
En el caso de un DROP PARTITION otra opción es borrar los registros de la partición antes de realizar el drop partition, pero es mucho más trabajo (recomiendo el update global indexes :) )
A esta clausula también se le puede agregar la opción de PARALLEL para optimizar el operación.
Ej: alter table prueba drop partition p1 update global indexes parallel (degree 4);
A Continuación les dejo un caso de prueba en el cual se genera una tabla, y se borran particiones para probar los casos explicados.
/*Creamos una tabla particionada por RANGE */
SQL> create table lauta.t_prueba (nombre varchar2(30),
2 apellido varchar2(30),
3 edad number(3))
4 partition by range (edad)
5 (partition p1 values less than (10) ,
6 partition p2 values less than (20) ,
7 partition p3 values less than (30) ,
8 partition p4 values less than (40) ,
9 partition p5 values less than (MAXVALUE)) ;
Table created.
-- Creamos un indice LOCAL PARTITIONED
SQL> CREATE INDEX lauta.prueba_local_idx ON lauta.t_prueba (edad) LOCAL;
Index created.
--Creamos un indice GLOBAL PARTITIONED
SQL> CREATE INDEX lauta.prueba_GLOBAL_part_idx ON lauta.t_prueba(edad, apellido)
2 GLOBAL PARTITION BY RANGE(edad)
3 (PARTITION p1 VALUES LESS THAN(20),
4 PARTITION p2 VALUES LESS THAN(MAXVALUE));
Index created.
--Creamos un indice GLOBAL NOPARTITIONED
SQL> CREATE INDEX lauta.prueba_GLOBAL_no_part_idx ON lauta.t_prueba(nombre);
Index created.
--Insertamos algunos registros en la tabla para tener datos.
SQL> insert into lauta.t_prueba values ('coco', 'alberto', 7);
1 row created.
SQL> insert into lauta.t_prueba values ('roto', 'asdro', 15);
1 row created.
SQL> insert into lauta.t_prueba values ('pepe', 'greeenn', 35);
1 row created.
SQL> insert into lauta.t_prueba values ('saco', 'adsdcye', 56);
1 row created.
SQL> insert into lauta.t_prueba values ('ert', 'tttt', 2);
1 row created.
SQL> insert into lauta.t_prueba values ('trettt', 'ttnpas', 1);
1 row created.
SQL> insert into lauta.t_prueba values ('¤lhhrr', 'aorsuar', 24);
1 row created.
SQL> insert into lauta.t_prueba values ('grrvua', 'asmorilo', 27);
1 row created.
SQL> commit;
Commit complete.
--Verificamos en la DBA_INDEXES que indices están creados y sus estados
SQL> select index_name, index_type, status
2 from dba_indexes
3 where owner = 'LAUTA';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PRUEBA_GLOBAL_NO_PART_IDX NORMAL VALID
PRUEBA_GLOBAL_PART_IDX NORMAL N/A
PRUEBA_LOCAL_IDX NORMAL N/A
-- Vemos que los particionados GLOBAL y LOCAL figuran con estado N/A, es porque para ver el estado, lo verificamos en la dba_ind_partitions
SQL> select index_name, partition_name, status
2 from dba_ind_partitions
3 where index_owner = 'LAUTA';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PRUEBA_LOCAL_IDX P1 USABLE
PRUEBA_LOCAL_IDX P2 USABLE
PRUEBA_LOCAL_IDX P3 USABLE
PRUEBA_LOCAL_IDX P4 USABLE
PRUEBA_LOCAL_IDX P5 USABLE
PRUEBA_GLOBAL_PART_IDX P1 USABLE
PRUEBA_GLOBAL_PART_IDX P2 USABLE
7 rows selected.
--Procedemos a borrar la partición p1 para ver que pasa con los indices
SQL> alter table lauta.t_prueba drop partition p1;
Table altered.
SQL> select index_name, index_type, status
2 from dba_indexes
3 where owner = 'LAUTA';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PRUEBA_GLOBAL_NO_PART_IDX NORMAL UNUSABLE
PRUEBA_GLOBAL_PART_IDX NORMAL N/A
PRUEBA_LOCAL_IDX NORMAL N/A
SQL> select index_name, partition_name, status
2 from dba_ind_partitions
3 where index_owner = 'LAUTA';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PRUEBA_LOCAL_IDX P2 USABLE
PRUEBA_LOCAL_IDX P3 USABLE
PRUEBA_LOCAL_IDX P4 USABLE
PRUEBA_LOCAL_IDX P5 USABLE
PRUEBA_GLOBAL_PART_IDX P1 UNUSABLE
PRUEBA_GLOBAL_PART_IDX P2 UNUSABLE
6 rows selected.
--Como podemos ver, solo los GLOBAL quedaron en UNUSABLE.
--Procedemos a hacer el rebuild de todas las particiones del indice particionado y el indice no particionado
SQL> alter index lauta.PRUEBA_GLOBAL_PART_IDX rebuild partition p1;
Index altered.
SQL> alter index lauta.PRUEBA_GLOBAL_PART_IDX rebuild partition p2;
Index altered.
SQL> alter index lauta.PRUEBA_GLOBAL_NO_PART_IDX rebuild;
Index altered.
-- Verificamos que todo quedo en estado Correcto
SQL> select index_name, index_type, status
2 from dba_indexes
3 where owner = 'LAUTA';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PRUEBA_GLOBAL_NO_PART_IDX NORMAL VALID
PRUEBA_GLOBAL_PART_IDX NORMAL N/A
PRUEBA_LOCAL_IDX NORMAL N/A
SQL> select index_name, partition_name, status
2 from dba_ind_partitions
3 where index_owner = 'LAUTA';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PRUEBA_GLOBAL_PART_IDX P2 USABLE
PRUEBA_GLOBAL_PART_IDX P1 USABLE
PRUEBA_LOCAL_IDX P2 USABLE
PRUEBA_LOCAL_IDX P3 USABLE
PRUEBA_LOCAL_IDX P4 USABLE
PRUEBA_LOCAL_IDX P5 USABLE
6 rows selected.
-- Ahora procedemos a realizar el borrado de la particion p2 con la clausula GLOBAL INDEXES.
SQL> alter table lauta.t_prueba drop partition p2 update global indexes;
Table altered.
-- Verificamos que todo continúa usable y no hubo perdida de servicio.
SQL> select index_name, index_type, status
2 from dba_indexes
3 where owner = 'LAUTA';
INDEX_NAME INDEX_TYPE STATUS
------------------------------ --------------------------- --------
PRUEBA_GLOBAL_NO_PART_IDX NORMAL VALID
PRUEBA_GLOBAL_PART_IDX NORMAL N/A
PRUEBA_LOCAL_IDX NORMAL N/A
SQL> select index_name, partition_name, status
2 from dba_ind_partitions
3 where index_owner = 'LAUTA';
INDEX_NAME PARTITION_NAME STATUS
------------------------------ ------------------------------ --------
PRUEBA_GLOBAL_PART_IDX P2 USABLE
PRUEBA_GLOBAL_PART_IDX P1 USABLE
PRUEBA_LOCAL_IDX P3 USABLE
PRUEBA_LOCAL_IDX P4 USABLE
PRUEBA_LOCAL_IDX P5 USABLE
En este ejemplo se mostró que funciona correctamente la opcion UPDATE GLOBAL INDEXES sin necesidad de hacer un rebuild de los indices post ejecución de una operación DDL como es el DROP sobre una tabla particionada con indices GLOBAL.