Consultas para ver atributos de tablas e índices particionados

Este post es para compartir un par de consultas que uso muy seguido y quiero dejar a mano en el blog, ya que no tengo un área de scripts.

Si usamos habitualmente la línea de comando para administrar una base de datos, tenemos a mano muchos scripts SQL para distintas tareas. Para consultas simples no es tan necesario, como por ejemplo ver los índices de una tabla. Pero cuando trabajamos con tablas particionadas, lleva un poco más volver a escribir esa consulta que nos muestra todos los datos que nos interesa. Tener a mano el script SQL es útil para ahorrarnos escribirlo de nuevo cada vez.

Actualmente hay muchas herramientas gráficas/web de administración que nos hace dejar de lado usar este tipo de scripts para consultar metadata de la base de datos. Usar scripts también puede ser un hábito adquirido con el tiempo (señal del paso del tiempo :)), aunque hay casos en que es necesario. Uno de ellos es cuando hay firewalls o medidas de seguridad que solo permiten acceso a la base de datos mediante una terminal. Aunque los firewalls se puedan esquivar usando túneles con ssh para utilitarios cliente/servidor (como SQL Developer o MySQL Workbench), para utilitarios que publican un acceso web es más complicado (Grid Control), y ahí vuelven a tener sentido los scripts.

Así que hecha la introducción, estos son un par de scripts útiles para ver información de una tabla particionada y sus índices. Cuando no hay particiones, estos datos se pueden sacar con una consulta simple sobre DBA_TABLES y DBA_IND_COLUMNS sin mucha complicación.

Para ilustrarlo mejor, un ejemplo usando estas tablas:

select table_name, num_rows
from dba_tables
where table_name like 'BIGTBL%';

TABLE_NAME                       NUM_ROWS
------------------------------ ----------
BIGTBL_PART_FACT                 34718511
BIGTBL_PART_ID                   41529989
BIGTBL                            5931843

3 rows selected.

Así vemos por separado sus particiones:

break on t.table_NAME on t.partitioning_type on t.partition_count on t.status
select t.table_NAME, t.partitioning_type tipo, t.partition_count particiones, t.status, c.COLUMN_NAME, c.COLUMN_POSITION
from DBA_PART_KEY_COLUMNS c, dbA_part_tables t
where t.table_name = c.name
  and c.owner = t.owner
  and t.table_name like 'BIGTBL%'
order by c.name, c.COLUMN_POSITION;

TABLE_NAME                     TIPO      PARTICIONES STATUS   COLUMN_NAME          COLUMN_POSITION
------------------------------ --------- ----------- -------- -------------------- ---------------
BIGTBL_PART_FACT               RANGE              50 VALID    NRO_FACT                           1
BIGTBL_PART_ID                 RANGE              50 VALID    ID                                 1

2 rows selected.

Juntamos ambas consultas en una sola:

col part_col for a30
select t.table_NAME, t.num_rows, p.partitioning_type particion, p.partition_count "#part", p.status,
       c.COLUMN_NAME part_col, c.COLUMN_POSITION pos
from dbA_tables t, DBA_PART_KEY_COLUMNS c, dbA_part_tables p
where t.table_name = p.table_name(+) and t.owner=p.owner(+)
  and t.table_name = c.name(+) and t.owner = c.owner(+)
  and t.table_name like 'BIGTBL%'
order by t.table_name, c.COLUMN_POSITION;

TABLE_NAME                       NUM_ROWS PARTICION      #part STATUS   PART_COL                              POS
------------------------------ ---------- --------- ---------- -------- ------------------------------ ----------
BIGTBL_PART_FACT                 34718511 RANGE             50 VALID    NRO_FACT                                1
BIGTBL_PART_ID                   41529989 RANGE             50 VALID    ID                                      1
BIGTBL                            5931843

3 rows selected.

La misma idea aplicada a los índices nos lleva a esta consulta, para ver los índices, saber si están particionados y algún dato más:

col index_name for a30
select c.index_name, uniqueness, i.partitioned, p.partitioning_type tipo, locality, c.column_name, column_position pos
from dba_ind_columns c, dba_indexes i, dba_part_indexes p
where i.index_name=c.index_name and c.index_owner=i.owner and c.table_name=i.table_name and c.table_owner=i.table_owner
  and i.owner=p.owner(+) and i.index_name=p.index_name(+) and i.table_name=p.table_name(+)
  and c.table_name='BIGTBL_PART_ID' 
order by c.index_name, c.column_position;  

INDEX_NAME                     UNIQUENES PAR TIPO      LOCALI COLUMN_NAME                 POS
------------------------------ --------- --- --------- ------ -------------------- ----------
IDX_BIGTBL_PART_2              NONUNIQUE NO                   COD_DOC                       1
IDX_BIGTBL_PART_2              NONUNIQUE NO                   COD_LOCAL                     2
IDX_BIGTBL_PART_ID             UNIQUE    YES RANGE     LOCAL  NRO_FACT                      1

3 rows selected.

Espero les sea útil