martes, 15 de diciembre de 2009

Como encontrar los "agujeros" (gaps) en las columnas que se llenan con valores de secuencias

Voy a mostrar un ejemplo de uso de las funciones analiticas "Analytical Functions" para mostrarles de que forma sencilla y elegante se pueden encontrar los gaps de valores en las columnas alimentadas por secuencias. Es sabido que el objeto secuencia no es transaccional, por lo tanto si una transaccion se descarta (rollback) el próximo valor de secuencia obtenido para la transacción se pierde. Tambien es común que las secuencias manejen un cache y por lo tanto ante una bajada de la base de datos tambien se pierden los valores cacheados que no habian sido consumidos. A veces se necesita saber cuales son los intervalos no cosecutivos por algún tema de negocio. Para hacer eso se podría usar un bloque procedureal con pl/sql, pero con funciones analiticas lo resolvemos de una manera mas simple y performante, veamos con un ejemplo desde cero como hacerlo:


rop@DESA11G> create table t (x int);

Tabla creada.

rop@DESA11G> create sequence t_seq;

Secuencia creada.



Una vez creada la tabla y la secuencia voy a llenar la tabla T con 1000 valores consecutivos:


rop@DESA11G> insert into t
2 select t_seq.nextval
3 from dual
4 connect by rownum <= 1000;
1000 filas creadas.



Ahora voy a eliminar filas para generar gaps y poder mostrar como funciona la senntencia para listar agujeros.


rop@DESA11G> delete from t
2 where (x between 100 and 150) or (x between 900 and 910);
62 filas suprimidas.

Ya tengo armado el ambiente asi que ahora simplemente ejecuto la sentencia
con FA para detectar los gaps:


rop@DESA11G> select *
2 from (select unique x,
3 lead(x) over (order by x) x_next
4 from t)
5 where x+1 != x_next;

X X_NEXT
---------- ----------
99 151
899 911

Se puede observar que se listaron los intervalos no cosecutivos que corresponden a las filas que se eliminaron en el ejemplo.

4 comentarios:

  1. hola.. muy bueno tu post: estoy interesada en un tutorial sobre las Analytical Functions.. donde puedo encontrarlo gracias!!!

    ResponderEliminar
  2. Hola, yo te recomiendo si te podes conseguir alguno de los dos libros de Tom Kyte, si los buscas por la web tambien los podes conseguir en pdf, donde se explica con ejemplos bien claros en que casos resulta conveniente usar funciones analiticas. Sino podes ver la documentacion oficial en otn.oracle.com en la parte de Datawarehousing. Tambien te comento que en proximas notas voy a mostrar mas casos donde se pueden aplicar FAs.

    ResponderEliminar
  3. Hola Pablo,
    Enhorabuena por tu entrada de blog. Viendo la forma en la que haces el insert he creado una select para hacer que recupere el primer valor libre (que es lo que yo necesitaba):

    SELECT CODIGO FROM(
    SELECT ROWNUM CODIGO
    FROM DUAL
    CONNECT BY ROWNUM <= 99999999
    MINUS
    SELECT X CODIGO
    FROM T
    ORDER BY CODIGO ASC
    )
    WHERE ROWNUM <= 1

    ResponderEliminar