domingo, 30 de marzo de 2014

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