Tema 3.1 - SQL Avanzado

Consultas avanzadas - Cláusula With

La cláusula WITH es totalmente opcional y es una cláusula que permite asignar nombres a las subconsultas y puede ser usada en una sola vez o múltiples veces dentro de una misma sentencia. Para poder usar más de una vista dentro de una consulta usando WITH estas serán separadas por comas.

Los resultados devueltos por la cláusula son tablas temporales en memoria similares a las vistas es por esto que a cada una de las consultas  definidas en esta se conocen como vistas.

Cabe aclarar que las vistas creadas pueden usarse solo durante de la ejecución de la consulta a la cual pertenecen y estas pueden usarse en toda la consulta las veces que sea necesario siempre y cuando estas ya hayan sido definidas; es decir no pueden usarse en una parte de la consulta anterior a su definición.

El uso la cláusula las que probablemente permitan optimizar las ejecuciones de las consultas. No siempre garantiza la optimización de la consulta, pero lo que si permite es una mejor lectura y mantenimiento de las mismas. También se permite la relación entre las diferentes vistas creadas y por lo tanto crear vistas a partir de vistas anteriores, pero siempre se debe finalizar la sentencia WITH con una consulta a una de las vistas, la cual es la que muestra los resultados requeridos.

12
3
4

WITH nombre_asignado as (Query_subconsulta)
SELECT * FROM nombre_asignado

1
2
3

WITH vista_1 as (Query_subconsulta_vista_1),
     vista_2 as (Query_subconsulta_vista_2)
SELECT * FROM vista_1 INNER JOIN vista_2

Funciones de analítica

Las funciones analíticas se utilizan para realizar cálculos de valores agregados de un grupo de registros. La diferencia entre las funciones analíticas y las funciones de agregación es que en las primeras se devuelve un conjunto de registros basados en las funciones solicitadas y en la segunda se retorna un único valor basado en el grupo de registros procesado. Las funciones WHERE, GROUP BY y HAVING dentro de una consulta que contenga funciones analíticas se ejecutaran antes de realizar las operaciones analíticas. Y por último si se ejecuta la función ORDER BY.

Cláusula OVER

Esta cláusula es obligatoria en el ámbito de cualquier función analítica. Como palabra clave indica cómo se particiona u ordena la función analítica.

FIRST_VALUE

Esta función permite obtener el primer valor del conjunto ordenado de registros. Se debe tener en cuenta que los valores nulos siempre aparecen de primero, pero se pueden ignorar usando la función IGNORE NULLS.

1
2
3
4
5
6
7
8
9

SELECT
       department_name,
       manager_id,
       location_id,
       FIRST_VALUE(manager_id) IGNORE NULLS 
         OVER (
                PARTITION BY location_id ORDER BY location_id ASC
              ) AS lowest_in_dept
FROM    departments;

Toma el primer valor del campo MANAGER_ID haciendo la partición de los registros por los diferentes LOCATION_ID y mostrando los resultados ordenador por MANAGER_ID de forma ascendente.

LAST_VALUE

Esta función funciona de la misma manera que FIRST_VALUE pero con el último valor de un conjunto ordenado de valores.

1
2
3
4

SELECT
       department_name,manager_id,location_id,LAST_VALUE(manager_id) IGNORE NULLS 
         OVER (PARTITION BY location_id ORDER BY location_id asc) AS lowest_in_dept
FROM    departments;NER JOIN vista_2

RANK

Como función analítica, calcula el rango de cada fila devuelta de una consulta con respecto a las otras filas retornadas. La siguiente consulta asigna un nivel dando prioridad al campo LOCATION_ID y si estos son iguales toma como segundo parámetro el MANAGER_ID y si tanto LOCATION_ID como MANAGER_ID son iguales se asigna el mismo nivel. Esta prioridad se asigna en el ORDER BY

1
2
3
4
5
6
7

SELECT
       department_name,manager_id,location_id,
       RANK() 
         OVER (
                PARTITION BY location_id ORDER BY location_id ASC, manager_id ASC
              ) AS RANK
FROM    departments;

ROW_NUMBER

A diferencia de la función RANK este función analítica asigna un número a cada fila tomando como parámetro principal el valor por el cual se particiona.

1
2
3
4
5
6
7

SELECT
       department_name,manager_id,location_id,
       ROW_NUMBER()
         OVER (
                PARTITION BY location_id ORDER BY location_id ASC, manager_id ASC
              ) AS location_order
FROM   departments;

LAG

Esta función analítica retorna registros anteriores a la fila actual, haciendo posible comparar los valores actuales con valores anteriores.

1
2
3

SELECT department_name, MANAGER_ID,LOCATION_ID,
       LAG(MANAGER_ID, 1, 0) OVER(ORDER BY LOCATION_ID, manager_id) AS "Lag value"
FROM departments;

LEAD

La función LEAD hace lo mismo que la función LAG pero trayendo valores posteriores.

1
2
3

SELECT DEPARTMENT_NAME, MANAGER_ID,LOCATION_ID,
       LEAD(MANAGER_ID, 1, 0) OVER(ORDER BY LOCATION_ID, MANAGER_ID) AS "Lead value"
FROM DEPARTMENTS;

Índices

El índice es un instrumento que aumenta la velocidad de respuesta de la consulta, mejorando su rendimiento y optimizando su resultado, pero pueden deteriorar el rendimiento de los INSERT, UPDATE o DELETE. Antes de crear un índice se debe examinar el rendimiento del SQL y comparar el antes y el después.

Optimización

Es el encargado de generar la ruta de acceso necesaria para procesar las consultas SQL. Para cada sentencia SQL existe un numero finito de posibles planes de ejecución. Existen 2 optimizadores:

CBO

Selecciona el mejor plan de ejecución; el mejor plan es aquel que utiliza un mínimo de recursos y tiene un tiempo de respuesta menor; para seleccionar este plan se basa en estadísticas. Este se basa en las reglas básicas, pero teniendo en cuenta el estado actual de la base de datos: cantidad de memoria disponible, entradas/salidas, estado de la red. En ocasiones el mejor plan de ejecución puede no ser el plan más optimo y esto puede deberse a:

¿Qué significa el costo de un SQL?

El costo de un SQL es el costo total del plan de ejecución; por ende, el mejor plan de ejecución será aquel que tenga un menor costo. Los costos de un SQL no pueden ser comparado con el costo de otro SQL y tampoco puede ser comparado con el costo del mismo SQL en otro ambiente diferente. El optimizador es  sensible a cambios, tales como Patches y Updates del motor, configuración del servidor donde está la BD, sistema operativo; además puede ser influenciado por hints.

Con el fin de ayudar al optimizador que podemos hacer:

HINTS

Los hints son pistas que se dan al optimizador SQL de Oracle para que elabore el plan de ejecución de una sentencia DML, sentencias de manipulación de datos como select, insert, update, delete.

SELECT /*+ COMANDO-HINT */ ...

Planes de Ejecución

Es la representación de la ruta o pasos que debe seguir el motor para ejecutar un SQL. El explain plan muestra la ruta de ejecución estimada, la cual puede diferir del plan de ejecución real del SQL. El plan de ejecución nos proporciona muchos datos que pueden ser útiles para averiguar qué está ocurriendo al ejecutar una consulta, pero principalmente, de lo que nos informa es del tipo de optimizador utilizado, y el orden y modo de unir las distintas tablas si la instrucción utiliza algún join.

Operaciones de fila o grupo

Operaciones SQL