Inicio > SQL > Recursividad en PL/SQL (II) – Implementación alternativa

Recursividad en PL/SQL (II) – Implementación alternativa

Ref. ”Recursividad en PL/SQL – Un ejemplo

Como bien se identifica en el ejemplo de la referencia, se está tratando con datos existentes en la BBDD que tienen estructura jerárquica. También se identifica una relación unívoca entre un ‘maestro’ / ‘detalle’ (‘Carpeta’ vs ‘Documento’) y operaciones donde si se actúa sobre la entidad ‘maestro’ implica acciones sobre la ‘detalle’ –operación de ‘DELETE’-.

Partiendo de esas dos premisas iniciales, a continuación se expone una propuesta de nuevo modelo de programación a partir del caso del ejemplo de la referencia que, aunque considerándose como un ejemplo muy acertado, permitirá ver otro tipo de soluciones más óptimas

1.- Uso del Modelo Relacional

Por definición, si un registro de ‘Carpeta’ es borrado, carece de sentido la existencia de registros asociados en ‘Documento’. Es más, por integridad referencial no es posible borrar una carpeta que tenga documentos vinculados.

Por lo tanto es recomendable modificar la relación establecida y definida a nivel de ‘CONSTRAINT’ de clave foránea -‘FK’- :

De esta forma, si se procede a la creación de esta ‘FK’ con ‘ON DELETE CASCADE’, en lugar de borrar todos los registros de ‘Documento’ que pertenecen al que se desea borrar de ‘Carpeta’, simplemente se procedería a modificar:

Consecuentemente, es recomendable aplicar este cambio.

2.- Búsqueda de información jerárquica

(consulta con Recursividad Implícita)

El lenguaje ‘SQL‘ permite el tratamiento y extracción de información de estructura jerárquica, mediante el uso de ‘CONNECT BY PRIOR …’.

Muestra de un ejemplo:

Teniendo esta forma de extracción, se procede a la definición de un nuevo modelo de codificación.

3.- Solución planteada

Partiendo de la consideración de las premisas anteriores, primero se plantea una solución basada en un caso de ‘excepción‘, y a continuación se presenta la solución final de implementación alternativa‘ para el ejemplo de referencia.

- Solución 1 (‘Excepción con error’)

Se trata de una solución simple y breve. Quedaría etiquetada como la mejor si no fuese por el ‘error‘ que genera. Se incluye por tanto sólo como ‘documentación añadida e informativa‘.

Se crea la siguiente función:

Entonces, simplemente se ejecutaría el siguiente comando/consulta:

Y es cuando se detecta el error:

Lo que indica que no se pueden ejecutar ‘funciones‘ con sentencias ‘DML’ (‘INSERT’, ‘DELETE’, …). Incidencia más que normal, ya que Oracle lo restringe por considerar la posibilidad de que si dentro de ésta se hiciesen cambios directos sobre los objetos consultados entonces se crearía un ‘conflicto auto-generado’ que distorsionaría el resultado por posible influencia directa.

No obstante, si la ‘función‘ tan sólo hiciese acciones de ‘consulta‘ o no interactuase contra los datos, esta solución se consideraría como la más simple, sencilla y efectiva .

– Solución final

Consiste en la utilización de la recogida de información jerárquica con el ‘CONNECT BY PRIOR …’ (y por supuesto utilizando el ‘ON DELETE CASCADE’ en la definición de la ‘FK’).

Creación del procedimiento siguiente:

Ejecución:

SQL> exec borrar_carpeta_rrq_2(1)

SQL> commit;

Funciona perfectamente y borra todo el árbol jerárquico con sus documentos asociados al igual que en el caso original, pero ‘con resultados de ejecución mejores‘.

– Resultados (sólo en tiempo de ejecución)

Según el ‘Ejemplo inicial‘ (para todo el árbol):

SQL> exec borrar_carpeta(1)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.04

Según las modificaciones sugeridas:

SQL> exec borrar_carpeta_rrq_2(1)

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.01

Como se puede observar, a nivel de tiempo de ejecución (aunque el ‘volumen de datos no sea muy significativo‘) la ganancia es más que aceptable. Y si se mostrasen más parámetros sobre ‘Tiempo de CPU‘, ‘uso de memoria‘ y ‘acceso a disco‘, aún se notaría más la mejora.

 

Referencias:

Categorías:SQL Etiquetas: , , , ,
  1. Aún no hay comentarios.
  1. marzo 1, 2011 en 00:11

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: