Oracle al igual que otros manejadores de bases de datos utiliza principalmente tres tipos de join entre tablas:
- HASH
- NESTED
- MERGE
El objetivo es identificar que tipo de sentencias SQL generan estos joins.
1.- HASH JOIN
La tabla 'PRUEBA1' va a contener únicamente un registro. La tabla 'PRUEBA2' va a contener 7 registros.
Una vez insertados los registros se realiza un join por el campo campo1 entre la tabla 'PRUEBA1' y 'PRUEBA2'. Se ejecuta el join regresando únicamente un registro
El plan de ejecución muestra que el join de las dos tablas produce tres operaciones: Una operación padre que es el HASH JOIN con el id 1 y dos hijos con los id 2 y 3 con accesos secuenciales a las tablas 'PRUEBA1' y 'PRUEBA2'
ID 1.- HASH JOIN
ID 2.- TABLA ACCESS FULL PRUEBA1
ID 3.- TABLA ACCESS FULL PRUEBA2
El HASH es un metodo eficiente para ejecutar un join cuando se realiza un acceso secuencial o 'full scan' entre las tablas involucradas. Este metodo unicamente requiere un ‘barrido’ o 'scan' por cada tabla, para recuperar todos los registros.
2.- NESTED JOIN
A diferencia del 'HASH JOIN', el 'NESTED JOIN' va a realizar un ‘barrido’ o 'scan' de la tabla secundaria, por cada registro de la tabla principal en el join. Por ser este tipo de join mas costoso, el optimizador lo utiliza cuando va a recuperar un volumen relativamente pequeño de registros y generalmente involucra uno o más índices.
Para el ejercicio de un 'NESTED JOIN', vamos a ocupar las mismas tablas del ejercicio anterior: 'PRUEBA1' y 'PRUEBA2', utilizando el mismo query. Se van a generar dos índices, 'IDX_PRUEBA1' utlizando el campo1 de la tabla 'PRUEBA1' y el índice 'IDX_PRUEBA2' utilizando el campo1 de la tabla 'PRUEBA2'.
Actualizamos estadisticas de las tablas PRUEBA1 y PRUEBA2 con el 'package' dbms_stats.
Se ejecuta el query anterior, donde se realiza un join entre las tablas 'PRUEBA1' y 'PRUEBA2' por los campos 'CAMPO1'.
A diferencia del ejercicio anterior, donde se genero un 'HASH JOIN', el manejador detecto los nuevos indices 'IDX_PRUEBA1' e 'IDX_PRUEBA2' generando un 'NESTED LOOP', donde se unen las dos tablas a traves de los registros recuperados por los índices. La operación padre 'NESTED LOOPS' con el id 1, va a ser el padre de las operaciones 'INDEX FULL SCAN' con el ID 2 y el 'INDEX RANGE SCAN' ID 3.
3.- MERGE JOIN
Este realiza dos operaciones
1.- Va a realizar un ordenamiento o ‘sort’ de los datos de las tablas involucradas en el join, conocidas como ‘inner’ y ‘outer’ tabl
2.- Una vez ordenados los datos, se realiza una comparación registro por registro de las dos tablas considerando los siguientes criterios:
1.- Va a realizar un ordenamiento o ‘sort’ de los datos de las tablas involucradas en el join, conocidas como ‘inner’ y ‘outer’ tabl
2.- Una vez ordenados los datos, se realiza una comparación registro por registro de las dos tablas considerando los siguientes criterios:
o Si la columna del inner join < columna del outer join, se avanza un registro en la tabla del inner join
o Si la columna del inner join = columna del outer join, se leen los registros que hicieron match
o Si la columna del inner join > columna del outer join, se avanza un registro en la tabla del outer join
El realizar el ‘sort’ de las tablas involucradas, es la parte más demandante en cuanto a recursos. Una vez ordenados los datos, la busqueda se realiza de forma relativamente rapida. Este tipo de join se da generalmente cuando se agregan operadores mayor (>), menor (<) y diferente (¡=)
Se muestra un ejemplo para la generación de un 'MERGE JOIN'. Se van a crear dos tablas ‘PRUEBA1’ y ‘PRUEBA2’ con los mismos datos, cada una con 9438 registros cada una, a partir de la tabla de sistema 'DBA_EXTENTS'.
Se va a generar un join entre las tablas 'PRUEBA1' y 'PRUEBA2' agregando el operador '<' en la claúsula 'WHERE a.extent_id < b.extent_id', para forzar la generación del 'MERGE JOIN'.
El plan de ejecución en el ID 1 muestra un 'HASH UNIQUE', originado por el select ‘DISTINCT a.extent_id’. Se muestra el 'MERGE JOIN' con el id 2. Este genero dos ‘hijos’:
o 'SORT JOIN' con el id 3 para ordenar los datos de la tabla ‘PRUEBA1’
o 'SORT JOIN' con el id 5 para ordenar los datos de la tabla ‘PRUEBA2’
El ordenamiento de los datos de las dos tablas, fue el proceso más costoso, como lo muestra el ID 5 ‘SORT JOIN’ que requirio 26M para ser realizado.
Una vez ordenados los datos, los ‘barridos’ o 'scans' de la tabla, requirieron un número menor de recursos como lo muestra la columna con el id 4 ‘TABLE ACCESS FULL’ con apenas 9437 KBS y la columna con el ID 6 ‘TABLE ACCESS FULL’ con 9438 KBS.
Conclusiones
Identificando el tipo de query podremos asignar los recursos de memoria de una forma más eficiente. Si tenemos un query de tipo 'NESTED JOIN' la instancia estará utilizando principalmente memoria 'SGA'. Si tenemos un query de tipo 'HASH JOIN' el tipo de memoria utilizadada será principalemnte 'PGA', que es un recurso mucha más limitado que la memoria 'SGA'. Si detectamos un tipo join tipo 'MERGE JOIN', sabremos que se esta ejecutando una sentencia con un consumo elevado de recursos, principalmente para la parte del 'SORT', en particular para el parámetro 'sort_area_size'. Se pueden utilizar, entre otras, como auxiliares en el monitoreo de la memoria ocupada por estos tipos de join las vistas dinámicas del sistema 'v$sgastat', 'v$pgastat', 'v$systat' y los campos 'pga_used_mem', 'pga_alloc_mem', 'pga_freeable_mem', y 'pga_max_mem' de la vista 'v$process'.
José Manuel Vizcaíno Culebra
Contacto servicios profesionales:
jose.vizcainoculebra@gmail.com
5532439143 Ciudad de México
No hay comentarios.:
Publicar un comentario