Vistas Materializadas y Temporales en SQL

Pésima explicación para esta clase

MySQL no tiene soporte nativo para vistas materializadas, por eso la palabra reservada MATERIALIZE no funciona, se puede simular su comportamiento creando una nueva tabla y por medio del uso de triggers ir actualizando la información de esta tabla. Motores de bases de datos relacionales como PostgreSQL u Oracle si tienen soporte para vistas materializadas.

Está explicación nos deja claro que la clase 26 no debería llamarse Vistas Materializadas en SQL, si no solo Vistas en SQL. Mucho cuidado con estos detalles, porque de algún modo nos inducen a errores conceptuales bárbaros. Este curso necesita una curaduría urgente!

Esto de estar usando chatGPT es lo mejor que me ha pasado!

Las vistas normales y las vistas materializadas son dos tipos de vistas en bases de datos relacionales, pero tienen diferencias clave en cuanto a su funcionamiento, almacenamiento y uso. **Vistas Normales** 1. **Definición**: Una vista normal es una consulta almacenada en la base de datos que se ejecuta cada vez que se accede a la vista. 2. **Almacenamiento**: No ocupan espacio físico adicional en la base de datos, solo el espacio necesario para almacenar la definición de la consulta. 3. **Actualización**: Las vistas normales se actualizan en tiempo real. Cada vez que se consulta la vista, se ejecuta la consulta subyacente. 4. **Rendimiento**: Pueden ser lentas si las consultas subyacentes son complejas o si los datos cambian con frecuencia. 5. **Uso**: Útiles para simplificar consultas complejas, proporcionar abstracción y mejorar la seguridad al controlar el acceso a los datos. **Vistas Materializadas** 1. **Definición**: Una vista materializada almacena el resultado de una consulta en la base de datos como una tabla física. 2. **Almacenamiento**: Ocupan espacio adicional en la base de datos porque almacenan los resultados de la consulta. 3. **Actualización**: No se actualizan en tiempo real. Se actualizan periódicamente según un horario predefinido o mediante un comando explícito (REFRESH). 4. **Rendimiento**: Pueden mejorar el rendimiento en consultas repetitivas y complejas, ya que los resultados están precomputados. 5. **Uso**: Útiles para informes y análisis donde los datos no cambian con frecuencia, y el rendimiento de la consulta es crítico. **Comparación Resumida** **Característica** **Vista Normal** **Vista Materializada** **Almacenamiento** Solo definición de consulta Resultados de consulta **Actualización** En tiempo real Periódicamente o por comando **Rendimiento** Puede ser lento Mejor rendimiento en consultas **Uso** Abstracción y seguridad Informes y análisis **Ejemplo de Creación** **Vista Normal**: CREATE VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; **Vista Materializada**: CREATE MATERIALIZED VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; Para refrescar una vista materializada: REFRESH MATERIALIZED VIEW nombre\_vista; **Conclusión** La elección entre una vista normal y una materializada depende de los requisitos específicos de tu aplicación en términos de rendimiento, frecuencia de actualización de datos y espacio de almacenamiento disponible. Las vistas normales son ideales para casos donde los datos cambian con frecuencia y se necesita la información más actualizada, mientras que las vistas materializadas son más adecuadas para consultas intensivas en datos que no cambian con frecuencia y donde el rendimiento es crucial.

De primeras no habia entendio la diferencia entre las vistas normales y las vistas materializadas. Segun entendí, la diferencia es que una vista normal, pese a que puedes volver a revisarla, lo que almacena es la definicion de la consulta la cual ejecuta de nuevo, a eso se refiere la profesora al mencionar que si es un query complejo no queremos estar ejecutandolo cada vez con una vista normal. Sin embargo, las vistas normales al ser ejecutadas cada vez, brindan informacion actualizada y actual. Mientras que las vistas materializadas permanecen en el punto en el tiempo que realizaste la consulta, con los datos como estaban en ese momento.

### Índices en SQL **Definición**: Un índice es una estructura adicional que permite a la base de datos realizar búsquedas más rápidas. Funciona como el índice de un libro, que te permite encontrar información rápidamente sin tener que leer cada página. ### ¿Por qué Usar Índices? 1. **Mejorar la Velocidad de las Consultas**: Los índices pueden acelerar la ejecución de consultas SELECT que buscan registros basados en una o más columnas. 2. **Eficiencia en Filtrado y Ordenación**: Ayudan en operaciones que implican filtros (`WHERE`), ordenación (`ORDER BY`) y uniones (`JOIN`). ### Cómo Crear un Índice La sintaxis básica para crear un índice es: sqlCopiar código`CREATE INDEX nombre_del_indice ON` nombre\_de\_la\_tabla (columna1, columna2, ...); ### Ejemplo Práctico Supongamos que tienes una tabla llamada `estudiantes`: sqlCopiar código`CREATE TABLE` estudiantes ( ` id INT PRIMARY` KEY, ` nombre VARCHAR(100`), ` apellido VARCHAR(100`), ` edad INT`, ` email VARCHAR(100`) ); Y realizas muchas consultas basadas en la columna `apellido`. Sin un índice, la base de datos tiene que revisar cada fila para encontrar coincidencias. ### Crear un Índice en la Columna `apellido` Para acelerar las consultas basadas en `apellido`, puedes crear un índice: sqlCopiar código`CREATE INDEX idx_apellido ON` estudiantes (apellido); ### Beneficios del Índice Después de crear el índice, una consulta como: sqlCopiar código`SELECT * FROM estudiantes WHERE apellido = 'Garcia'`; Será mucho más rápida, ya que la base de datos utiliza el índice `idx_apellido` para localizar las filas correspondientes. ### Consideraciones al Usar Índices 1. **Espacio Adicional**: Los índices ocupan espacio adicional en disco. 2. **Coste en las Operaciones de Escritura**: Insertar, actualizar o eliminar datos puede ser más lento porque la base de datos también tiene que actualizar los índices. 3. **Elección de Columnas para Índices**: Es importante elegir cuidadosamente las columnas a indexar, normalmente aquellas que se usan frecuentemente en cláusulas `WHERE`, `ORDER BY` o `JOIN`. ### Tipos de Índices * **Índices Únicos (**`UNIQUE INDEX`**)**: Garantizan que todos los valores en la columna indexada sean únicos. * **Índices Compuestos**: Pueden incluir más de una columna, lo que es útil para consultas que filtran por múltiples columnas. sqlCopiar código`CREATE UNIQUE INDEX idx_email ON` estudiantes (email); `CREATE INDEX idx_nombre_apellido ON` estudiantes (nombre, apellido); ### Conclusión Los índices son una herramienta poderosa para optimizar el rendimiento de las bases de datos, especialmente en consultas frecuentes y con grandes volúmenes de datos. Sin embargo, deben ser usados con cuidado debido al coste adicional en almacenamiento y las operaciones de escritura.

Una vista materializada en PostgreSQL es una vista que almacena físicamente los resultados de una consulta. Esto puede mejorar el rendimiento al acceder a datos complejos o grandes conjuntos de datos, ya que los resultados no tienen que ser recalculados cada vez que se accede a la vista. ```js -- Crear una vista materializada CREATE MATERIALIZED VIEW mv_students AS SELECT studentid, firstname, lastname FROM students; -- Actualizar la vista materializada REFRESH MATERIALIZED VIEW mv_students; -- Eliminar la vista materializada DROP MATERIALIZED VIEW IF EXISTS mv_students; ```CREATE MATERIALIZED VIEW mv\_students AS SELECT studentid, firstname, lastname FROM students;

En el minuto 0:47 en la pantalla aparece: CREATE VIEW "NAME", per debió aparecer: CREATE TEMP VIEW "NAME" (como dijo la instructora). Por ejemplo: ```js CREATE TEMP VIEW vw_t_courses_col AS SELECT course_id, instructor_id FROM courses ```CREATE TEMP VIEW vw\_t\_courses\_col AS SELECT course\_id, instructor\_id FROM courses

Las vistas normales y las vistas materializadas no son compatibles con todos los motores de bases de datos SQL de la misma manera. Aquí te dejo una descripción de cómo funcionan en algunos de los motores de bases de datos SQL más populares: **Vistas Normales** Las vistas normales son compatibles con prácticamente todos los motores de bases de datos SQL. La sintaxis puede variar ligeramente, pero el concepto es el mismo. **Ejemplos:** **MySQL:** CREATE VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; **PostgreSQL:** CREATE VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; **SQL Server:** CREATE VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; **Oracle:** CREATE VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; **Vistas Materializadas** Las vistas materializadas no son compatibles con todos los motores de bases de datos. Oracle y PostgreSQL las soportan de manera nativa, mientras que MySQL y SQL Server no las soportan directamente, pero pueden emularlas mediante tablas temporales o utilizando otras técnicas. **Ejemplos:** **Oracle:** CREATE MATERIALIZED VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; Para refrescar: REFRESH MATERIALIZED VIEW nombre\_vista; **PostgreSQL:** CREATE MATERIALIZED VIEW nombre\_vista AS SELECT columna1, columna2 FROM tabla WHERE condición; Para refrescar: REFRESH MATERIALIZED VIEW nombre\_vista; **MySQL y SQL Server:** No tienen soporte directo para vistas materializadas, pero se pueden utilizar otras técnicas: **MySQL:** Puedes emular una vista materializada creando una tabla y llenándola con los resultados de la consulta, y luego actualizando esa tabla periódicamente. CREATE TABLE nombre\_vista\_materializada AS SELECT columna1, columna2 FROM tabla WHERE condición; \-- Para refrescar (puede ser mediante un evento o procedimiento almacenado): TRUNCATE TABLE nombre\_vista\_materializada; INSERT INTO nombre\_vista\_materializada SELECT columna1, columna2 FROM tabla WHERE condición; **SQL Server:** Puedes usar una combinación de tablas e índices para obtener un comportamiento similar a una vista materializada. Otra opción es usar tablas indexadas (Indexed Views), que funcionan de manera similar. CREATE TABLE nombre\_vista\_materializada AS SELECT columna1, columna2 FROM tabla WHERE condición; \-- Para refrescar: TRUNCATE TABLE nombre\_vista\_materializada; INSERT INTO nombre\_vista\_materializada SELECT columna1, columna2 FROM tabla WHERE condición; **Resumen** * **Vistas Normales**: Compatibles con casi todos los motores de bases de datos. * **Vistas Materializadas**: Soportadas nativamente por Oracle y PostgreSQL. MySQL y SQL Server pueden emularlas mediante tablas e índices, pero no tienen soporte directo. La elección de usar vistas normales o materializadas dependerá del motor de base de datos que estés utilizando y de tus necesidades específicas de rendimiento y actualización de datos.

Caro 👩‍💻 la palabra reservada "Materialize" no se encuentra porque no hace parte de los motores de bases de datos relacionales

Te explico con ejemplos sencillos y cotidianos. \### Vistas Materializadas Imagina una tienda de productos: ```sql \-- Tabla base de ventas CREATE TABLE ventas ( id INT, producto\_id INT, cantidad INT, precio DECIMAL(10,2), fecha\_venta DATE ); \-- Vista materializada de ventas mensuales CREATE MATERIALIZED VIEW resumen\_ventas\_mensual AS SELECT DATE\_TRUNC('month', fecha\_venta) as mes, SUM(cantidad \* precio) as total\_ventas, COUNT(\*) as numero\_transacciones FROM ventas GROUP BY DATE\_TRUNC('month', fecha\_venta); \-- Refrescar la vista REFRESH MATERIALIZED VIEW resumen\_ventas\_mensual; ``` \### Vistas Temporales Ejemplo con una biblioteca: ```sql \-- Vista temporal de libros prestados CREATE TEMPORARY VIEW libros\_prestados AS SELECT l.titulo, e.nombre as estudiante, p.fecha\_prestamo, p.fecha\_devolucion FROM prestamos p JOIN libros l ON p.libro\_id = l.id JOIN estudiantes e ON p.estudiante\_id = e.id WHERE p.devuelto = false; ``` \### Casos de Uso Simple: 1\. \*\*Restaurante\*\* ```sql \-- Vista materializada para el menú del día CREATE MATERIALIZED VIEW menu\_del\_dia AS SELECT p.nombre as plato, p.precio, p.categoria, i.cantidad as ingredientes\_disponibles FROM platos p JOIN inventario i ON p.id = i.plato\_id WHERE i.cantidad > 0; ``` 2\. \*\*Escuela\*\* ```sql \-- Vista temporal de asistencia diaria CREATE TEMPORARY VIEW asistencia\_hoy AS SELECT c.nombre as clase, COUNT(a.estudiante\_id) as presentes, COUNT(\*) - COUNT(a.estudiante\_id) as ausentes FROM clases c LEFT JOIN asistencias a ON c.id = a.clase\_id WHERE DATE(a.fecha) = CURRENT\_DATE GROUP BY c.nombre; ``` \### Diferencias Explicadas con Ejemplos: 1\. \*\*Vista Materializada\*\* (como una foto): ```sql \-- Reporte mensual de una tienda CREATE MATERIALIZED VIEW ventas\_por\_categoria AS SELECT categoria, COUNT(\*) as productos\_vendidos, SUM(precio) as total\_ingresos FROM productos p JOIN ventas v ON p.id = v.producto\_id GROUP BY categoria; ``` 2\. \*\*Vista Temporal\*\* (como una pizarra temporal): ```sql \-- Lista temporal de productos en oferta CREATE TEMPORARY VIEW ofertas\_del\_dia AS SELECT nombre, precio, precio \* 0.9 as precio\_con\_descuento FROM productos WHERE cantidad\_stock > 10; ``` \### Ejemplo Práctico de Supermercado: ```sql \-- Vista materializada para el inventario CREATE MATERIALIZED VIEW inventario\_actual AS SELECT p.nombre, p.categoria, SUM(i.cantidad) as stock\_total, COUNT(CASE WHEN i.cantidad < 10 THEN 1 END) as productos\_por\_acabarse FROM productos p JOIN inventario i ON p.id = i.producto\_id GROUP BY p.nombre, p.categoria; \-- Vista temporal para la caja registradora CREATE TEMPORARY VIEW ventas\_del\_dia AS SELECT cajero\_nombre, COUNT(\*) as transacciones, SUM(total) as total\_vendido FROM ventas WHERE DATE(fecha) = CURRENT\_DATE GROUP BY cajero\_nombre; ``` Estos ejemplos son más fáciles de entender porque: 1\. Usan conceptos cotidianos (tienda, biblioteca, restaurante) 2\. Tienen estructuras de datos simples 3\. Representan situaciones del mundo real 4\. Son aplicables a negocios pequeños y medianos La vista materializada es como tomar una foto del inventario (se actualiza cuando queremos), mientras que la vista temporal es como una lista de compras (existe solo mientras la necesitamos).

Resumen de la clase 📋 

Una vista es como una ventana a datos en una base de datos, donde puedes ver la información sin cambiarla. Una vista materializada almacena los resultados de una consulta, como una foto que se guarda para ver más tarde. Esto ayuda a acceder a datos más rápido. Una vista temporal es como un dibujo en la arena: solo existe mientras estás trabajando. Cuando cierras la sesión, desaparece. Estas herramientas son útiles para optimizar consultas en SQL.

No entiendo, cual es la diferencia de vistas temporales y materializadas Me quedo la duda

En este curso lo que si he aprendido es a usar chatgpt :( Nada conforme con estas clases.

MySQL y SQL Server no tienen soportado la creacion de vistas materializadas. Los unicos motores que lo pueden crear son PostgreSQL y Oracle. Se puede emular de otro modo.

en mysql no tiene soporte nativo para las vistas materializadas y es necesario simularlas es algo un poco complejo

Si funciona la palabra reservada, en caso contrario solo es de buscar la información en el manual o chatgpt

triger: El trigger `update_salary` tiene la funcionalidad de registrar los cambios de salario de los instructores en una tabla separada (`salary_changes`) cada vez que se actualiza el salario de un instructor y el nuevo salario es mayor que el antiguo. Esto permite mantener un historial de los cambios de salario para referencia futura o para auditoría. ### Detalles de la Funcionalidad 1. **Tipo de Trigger**: `AFTER UPDATE` * Este trigger se ejecuta después de que se actualiza un registro en la tabla `instructors`. 2. **Condición de Ejecución**: `if (new.salary > old.salary)` * El trigger verifica si el nuevo salario (`new.salary`) es mayor que el salario anterior (`old.salary`). 3. **Acción del Trigger**: `INSERT INTO salary_changes` * Si la condición es verdadera, el trigger inserta un nuevo registro en la tabla `salary_changes`. ### Campos Insertados en `salary_changes` * `instructorid`: El ID del instructor cuyo salario fue actualizado. * `old_salary`: El salario anterior del instructor antes de la actualización. * `new_salary`: El nuevo salario del instructor después de la actualización. * `change_date`: La fecha y hora en que ocurrió la actualización, utilizando la función `now()` para obtener la fecha y hora actual. ### Ejemplo de Uso Supongamos que tenemos la siguiente tabla `instructors`: sqlCopiar código`CREATE TABLE` instructors ( ` instructorid INT PRIMARY` KEY, ` firstname VARCHAR(50`), ` lastname VARCHAR(50`), ` email VARCHAR(100`), ` salary DECIMAL(10,2`) ); `INSERT INTO` instructors (instructorid, firstname, lastname, email, salary) `VALUES (1, 'John', 'Doe', '[email protected]', 50000.00`); Cuando se actualiza el salario de un instructor: sqlCopiar código`UPDATE` instructors `SET salary = 55000.00` `WHERE instructorid = 1`; Si el salario nuevo es mayor que el salario anterior, el trigger `update_salary` se activará y registrará este cambio en la tabla `salary_changes`: sqlCopiar código`CREATE TABLE` salary\_changes ( ` id INT AUTO_INCREMENT PRIMARY` KEY, ` instructorid INT`, ` old_salary DECIMAL(10,2`), ` new_salary DECIMAL(10,2`), change\_date DATETIME ); El resultado de la actualización sería un nuevo registro en la tabla `salary_changes`: sqlCopiar código`SELECT * FROM` salary\_changes; Salida: idinstructoridold\_salarynew\_salarychange\_date1150000.0055000.002024-07-15 17:45:00 ### Beneficios 1. **Auditoría**: Mantiene un registro histórico de los cambios de salario, útil para auditorías. 2. **Transparencia**: Permite a la administración monitorear incrementos salariales. 3. **Análisis**: Facilita el análisis de tendencias salariales y decisiones de recursos humanos. ### Resumen El trigger `update_salary` es una herramienta para automatizar el registro de cambios de salario, asegurando que cualquier incremento en el salario de un instructor se documente con precisión en una tabla dedicada. Esto ayuda a mantener un historial detallado y organizado de los cambios salariales.

```js CREATE MATERIALIZED VIEW productos_marcas AS SELECT p.id, p.nombre AS nombre_producto, p.descripcion, p.precio, m.nombre AS nombre_marca FROM productos p INNER JOIN marcas m ON p.marca_id = m.id; REFRESH MATERIALIZED VIEW productos_marcas; SELECT * FROM productos_marcas; /* Vistas Temporales en SQL Las vistas temporales, a diferencia de las materializadas, no almacenan físicamente los datos. Son vistas virtuales que se generan al vuelo cuando se ejecuta la consulta. Creación de una Vista Temporal*/ SELECT * FROM productos_vendedores; CREATE VIEW productos_vendedores AS SELECT p.id, p.nombre AS nombre_producto, p.descripcion, p.precio, v.nombre AS nombre_vendedor, v.email FROM productos p INNER JOIN vendedores v ON p.vendedor_id = v.id; SELECT * FROM productos_vendedores; ```

Caro 👩‍💻 la vista reservada "Materialize" no se encuentra porque no hace parte de los motores de bases de datos relacionales