Corso
SQL intermedio
4 ore
526.1K
Le viste materializzate sono una potente funzionalità dei database SQL che aiuta a ottimizzare le prestazioni delle query memorizzando fisicamente su disco i risultati di una query, offrendo tempi di risposta più rapidi grazie alla riduzione dei ricalcoli. Sono particolarmente utili per gestire query complesse e pesanti che coinvolgono join, aggregazioni e dataset di grandi dimensioni.
Per iniziare, ti consiglio il corso Introduction to SQL di DataCamp e il percorso di competenze SQL Fundamentals per imparare le basi di SQL e come estrarre dati con le query. La SQL Basics Cheat Sheet sarà una guida utile per le funzioni SQL più comuni per filtrare e aggregare i dati.
Cosa sono le viste materializzate in SQL?
Le viste materializzate sono un tipo speciale di oggetto del database che memorizza fisicamente i risultati di una query invece di calcolarli al volo come fanno le viste normali. Mentre una vista SQL tradizionale è una query salvata che genera i suoi risultati dinamicamente ogni volta che viene consultata, una vista materializzata pre-calcola e memorizza i dati in una struttura simile a una tabella.
Memorizzando il result set su disco, le viste materializzate possono ridurre significativamente il carico su un database, migliorare le prestazioni delle query e semplificare l’elaborazione di operazioni computazionalmente intensive.
Creare una vista materializzata in SQL
Il processo di creazione di una vista materializzata prevede l’uso della sintassi CREATE MATERIALIZED VIEW, che varia leggermente tra i diversi database SQL. I metodi seguenti mostrano come creare viste materializzate in SQL Server, PostgreSQL e Oracle.
Vista materializzata in PostgreSQL
In PostgreSQL puoi creare una vista materializzata usando la seguente sintassi. Questo esempio crea una vista materializzata chiamata sales_summary che aggrega la quantità totale e il ricavo per ciascun prodotto.
-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Vista materializzata in SQL Server
In SQL Server, le viste materializzate sono chiamate "Indexed Views". Le tabelle sottostanti devono soddisfare requisiti specifici per creare una indexed view, come l’abilitazione dell’opzione WITH SCHEMABINDING.
L’opzione WITH SCHEMABINDING garantisce che lo schema non possa cambiare mentre esiste la indexed view. Per materializzare la vista deve essere creato un indice cluster univoco.
-- Create an indexed view with schema binding to summarize sales data
CREATE VIEW sales_summary
WITH SCHEMABINDING
AS
SELECT product_id,
COUNT_BIG(*) AS record_count,
SUM(ISNULL(quantity, 0)) AS total_quantity,
SUM(ISNULL(price, 0) * ISNULL(quantity, 0)) AS total_revenue
FROM sales
GROUP BY product_id;
GO
-- Create a unique clustered index to materialize the view
CREATE UNIQUE CLUSTERED INDEX IX_sales_summary
ON sales_summary (product_id);
GOSe vuoi saperne di più su SQL Server, ti consiglio il nostro percorso di competenze SQL Server Fundamentals per familiarizzare con diverse abilità SQL per l’analisi dei dati.
Vista materializzata in Oracle
La sintassi per creare viste materializzate in Oracle è simile a quella del database PostgreSQL. Possiamo anche specificare opzioni di refresh, come ON DEMAND o ON COMMIT.
-- Create a materialized view to summarize sales data
CREATE MATERIALIZED VIEW sales_summary
BUILD IMMEDIATE
REFRESH FAST ON DEMAND
AS
SELECT product_id, SUM(quantity) AS total_quantity, SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Vista materializzata nei database distribuiti
Le viste materializzate possono anche specificare metodi di distribuzione per migliori prestazioni in database come Azure Synapse o Amazon Redshift che supportano data warehouse distribuiti.
Distribuzione Hash per Amazon Redshift
La query seguente crea una vista materializzata chiamata sales_summary che aggrega quantità totale e ricavo per product_id. Le opzioni DISTSTYLE KEY e DISTKEY(product_id) garantiscono che i dati siano distribuiti tra i nodi in base a product_id, migliorando le prestazioni per le query che effettuano join su questa colonna.
-- Create a materialized view with key-based distribution for efficient joins
CREATE MATERIALIZED VIEW sales_summary
DISTSTYLE KEY
DISTKEY(product_id)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Distribuzione Round-Robin per Azure Synapse
Questa query crea una vista materializzata chiamata sales_summary che aggrega quantità totale e ricavo per product_id. La distribuzione ROUND_ROBIN ripartisce uniformemente i dati tra i nodi, utile in scenari che non fanno grande affidamento sui join.
-- Create a materialized view with round-robin distribution for balanced data storage
CREATE MATERIALIZED VIEW sales_summary
WITH (DISTRIBUTION = ROUND_ROBIN)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Aggiornare una vista materializzata in SQL
I dati nelle viste materializzate possono essere aggiornati per restare allineati alle tabelle sottostanti. La scelta del metodo di refresh dipende dai requisiti di business e dalle considerazioni sulle prestazioni del database specifico. Vediamo i seguenti metodi di aggiornamento dei dati delle viste materializzate.
Refresh manuale
Nel refresh manuale, la vista materializzata viene aggiornata solo quando l’utente lo richiede esplicitamente. Questo approccio offre il massimo controllo su quando i dati vengono aggiornati, risultando adatto a scenari in cui i dati cambiano di rado o gli update avvengono nelle ore di minor carico.
La query seguente mostra il metodo di refresh manuale in PostgreSQL.
REFRESH MATERIALIZED VIEW sales_summary;Refresh periodico
La vista materializzata viene aggiornata automaticamente a intervalli specificati durante il periodo di refresh, garantendo dati aggiornati senza intervento dell’utente. Questo metodo è utile per applicazioni sensibili al tempo in cui i dati devono essere relativamente recenti.
L’esempio seguente mostra come includere refresh periodici in Oracle definendo le pianificazioni di refresh direttamente nell’istruzione CREATE MATERIALIZED VIEW. L’orario di refresh è impostato a intervalli orari.
-- Create a materialized view to aggregate sales data
-- Set to refresh completely every hour
CREATE MATERIALIZED VIEW sales_summary
REFRESH COMPLETE START WITH (SYSDATE) NEXT (SYSDATE + 1/24)
AS
SELECT product_id,
SUM(quantity) AS total_quantity,
SUM(price * quantity) AS total_revenue
FROM sales
GROUP BY product_id;Refresh on-demand
Il refresh on-demand avviene ogni volta che i dati sottostanti cambiano, di solito tramite un meccanismo di trigger. Questo assicura che la vista materializzata contenga sempre i dati aggiornati. Ad esempio, in PostgreSQL è possibile configurare trigger per aggiornare la vista quando si verificano modifiche nella tabella sottostante.
Refresh completi vs incrementali
Puoi aggiornare i dati in modalità completa o incrementale nelle viste materializzate. La seguente tabella riassume i due metodi e i relativi casi d’uso.
| Tipo di refresh | Descrizione | Vantaggi | Svantaggi |
|---|---|---|---|
| Refresh completo | Ricarica l’intero dataset, sostituendo tutti i dati esistenti nella vista | - Semplice da implementare - Ricostruisce l’intera vista |
- Intensivo in termini di risorse per dataset grandi - Tempi di refresh più lunghi |
| Refresh incrementale | Aggiorna solo le parti della vista che sono cambiate | - Più efficiente, elabora solo i dati modificati - Adatto a dataset grandi con cambiamenti frequenti |
- Richiede configurazioni aggiuntive (ad es., log per tracciare le modifiche) - Non sempre supportato per tutte le query |
Best practice per le viste materializzate
Quando utilizzi le viste materializzate, è importante considerare le seguenti pratiche per un uso ottimale.
- Scegliere le query giuste da materializzare: Materializza query complesse e ad alto consumo di risorse come join, aggregazioni e subquery. Tali query traggono vantaggio da risultati precomputati, riducendo il carico sul database.
- Bilanciare freschezza dei dati e prestazioni: In base al tuo caso d’uso, scegli la strategia di refresh appropriata, come metodi manuali, periodici o on-demand. Usa il refresh incrementale per ridurre il carico computazionale e monitora la frequenza delle modifiche ai dati per definire gli intervalli di refresh.
- Usare le viste materializzate per ottimizzare carichi di lavoro ricchi di query: Sfrutta le viste materializzate per report e dashboard di BI dove i tempi di risposta rapidi sono essenziali. Puoi anche indicizzare le colonne usate nelle viste materializzate per filtraggio e ordinamento più veloci.
Viste materializzate nei diversi sistemi di database
Come hai visto, i diversi sistemi di database offrono un supporto variabile per le viste materializzate. La tabella seguente riassume le caratteristiche e le limitazioni delle viste materializzate in questi database.
| Sistema di database | Metodi di refresh | Refresh incrementale | Refresh automatico | Funzionalità/Limitazioni speciali |
|---|---|---|---|---|
| PostgreSQL | Manuale (REFRESH MATERIALIZED VIEW) | No | No | Manca il refresh incrementale nativo. È necessaria una pianificazione manuale. |
| SQL Server | Automatico (Indexed Views) | Sì (Sincronizzazione automatica) | Sì |
Richiede |
| Oracle | Manuale, On Commit, Pianificato | Sì (Fast Refresh) | Sì | Supporta fast refresh, partizionamento e parallelismo. Richiede view log. |
| Amazon Redshift | Manuale, Pianificato | Sì | Sì | Supporta dati distribuiti con distribuzione hash o round-robin. |
| MySQL | Non supportato nativamente | No | No | Necessarie soluzioni alternative (ad es., tabelle temporanee, strumenti di terze parti). |
| Azure Synapse | Manuale, Pianificato | Sì | Sì | Consente diverse strategie di distribuzione per l’ottimizzazione. |
Se utilizzi SQL Server come database principale, ti consiglio il corso di DataCamp Introduction to SQL Server per padroneggiare le basi di Microsoft SQL Server per l’analisi dei dati. Dai anche un’occhiata al nostro percorso professionale SQL Server Developer per capire come ottimizzare le query e risolvere i problemi in SQL Server.
Altri aspetti da considerare
Sebbene le viste materializzate in SQL siano utili per l’ottimizzazione delle query, presentano anche alcune sfide e limitazioni. Di seguito sono riportati i problemi più comuni con le viste materializzate e come affrontarli.
- Overhead di archiviazione: Le viste materializzate memorizzano fisicamente i risultati delle query su disco, aumentando i requisiti di archiviazione. Per evitare di consumare spazio inutilmente, materializza solo le viste per query ad alto consumo di risorse e partiziona le viste materializzate per dataset di grandi dimensioni.
- Costi di aggiornamento e overhead di refresh: Mantenere le viste materializzate sincronizzate con le tabelle sottostanti può essere oneroso in termini di risorse, soprattutto per viste che richiedono aggiornamenti frequenti o coinvolgono calcoli complessi. Per ridurre l’overhead di refresh, usa il refresh incrementale dove supportato o imposta intervalli di refresh appropriati quando l’utilizzo del database è più basso.
- Coerenza e sincronizzazione dei dati: Le viste materializzate possono diventare obsolete se i dati sottostanti cambiano frequentemente, portando a problemi di dati stantii. Per evitare questo problema, scegli la strategia di refresh appropriata e monitora i cambiamenti dei dati per adattare la strategia di conseguenza.
- Overhead di manutenzione: Le viste materializzate richiedono manutenzione continua, come impostare pianificazioni di refresh appropriate, monitorare l’uso dello storage e tracciare le dipendenze dalle tabelle sottostanti. Per superare questa sfida, usa sempre pianificazioni di refresh automatizzate, monitora le prestazioni del sistema e configura avvisi per i refresh non riusciti.
Conclusione
Le viste materializzate sono utili nei database SQL per ottimizzare le prestazioni delle query. Memorizzano fisicamente su disco i risultati delle query, offrendo tempi di risposta più rapidi grazie alla riduzione dei ricalcoli. Questa caratteristica rende le viste materializzate utili per gestire query complesse e ad alto consumo di risorse che coinvolgono join, aggregazioni e grandi dataset. Capire come implementare le viste materializzate nei diversi database ti aiuterà a migliorare le tue capacità di ottimizzazione di query e database.
Se vuoi far crescere le tue competenze SQL, ti consiglio di provare il percorso professionale di DataCamp Associate Data Analyst in SQL per diventare un data analyst competente. Il corso Reporting in SQL ti aiuterà anche a realizzare report e dashboard complessi per una presentazione efficace dei dati. Infine, dovresti ottenere la SQL Associate Certification per dimostrare la tua padronanza nell’uso di SQL per risolvere problemi di business e distinguerti tra gli altri professionisti.
Technical writer di Data Science con esperienza pratica in data analytics, business intelligence e data science. Scrivo contenuti pratici e orientati al settore su SQL, Python, Power BI, Databricks e data engineering, basati su lavoro di analytics reale. La mia scrittura unisce profondità tecnica e impatto sul business, aiutando i professionisti a trasformare i dati in decisioni sicure.
FAQ
Cos’è una vista materializzata in SQL?
Una vista materializzata è un oggetto del database che memorizza fisicamente il risultato di una query, ottimizzando le prestazioni evitando i ricalcoli.
In cosa una vista materializzata è diversa da una vista normale?
A differenza delle viste normali, che recuperano i dati dinamicamente a ogni accesso, le viste materializzate memorizzano i dati come tabelle fisiche, consentendo un’esecuzione delle query più veloce.
Qual è la differenza tra refresh completo e incrementale?
Un refresh completo ricalcola l’intera vista, mentre un refresh incrementale aggiorna solo i dati modificati, migliorando l’efficienza.
Quali database supportano le viste materializzate?
PostgreSQL, SQL Server (indexed views), Oracle, Amazon Redshift e Azure Synapse Analytics supportano le viste materializzate, ciascuno con funzionalità e limitazioni diverse, mentre MySQL non ha supporto nativo.
Le viste materializzate consumano spazio di archiviazione aggiuntivo?
Sì, poiché memorizzano i dati fisicamente, le viste materializzate aumentano i requisiti di archiviazione.
Argomenti
