Ejecutar DROP PARTITION evitando UNUSABLE INDEXES | ExpoDBA

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.