Cursus
Gevorderde SQL
4 uur
526.9K
Materialized views zijn een krachtige functie in SQL-databases die helpt om queryprestaties te optimaliseren door de resultaten van een query fysiek op schijf op te slaan, zodat queries sneller worden uitgevoerd omdat herberekening afneemt. Dit maakt ze vooral nuttig voor het afhandelen van complexe, resource-intensieve queries met joins, aggregaties en grote datasets.
Als je aan de slag gaat, raad ik DataCamp’s cursus Introduction to SQL en de skill track SQL Fundamentals aan om de basis van SQL te leren en hoe je data met queries ophaalt. De SQL Basics Cheat Sheet is een handig naslagwerk voor veelgebruikte SQL-functies voor filteren en aggregeren.
Wat zijn SQL-materialized views?
Materialized views zijn een speciaal type databaseobject dat de resultaten van een query fysiek opslaat, in plaats van ze on-the-fly te berekenen zoals reguliere views. Een reguliere SQL-view is een opgeslagen SQL-query die zijn resultaten elke keer dynamisch genereert wanneer je deze opent, terwijl een materialized view de data vooraf berekent en opslaat in een tabelachtige structuur.
Door de resultset op schijf op te slaan kunnen materialized views de belasting op een database aanzienlijk verlagen, de queryprestaties verbeteren en de verwerking van rekenintensieve bewerkingen stroomlijnen.
Een materialized view maken in SQL
Een materialized view maken doe je met de syntax CREATE MATERIALIZED VIEW, die per SQL-database iets kan verschillen. De volgende methoden laten zien hoe je materialized views maakt in SQL Server, PostgreSQL en Oracle.
Materialized view in PostgreSQL
In PostgreSQL kun je een materialized view maken met de volgende syntax. In dit voorbeeld maken we een materialized view met de naam sales_summary die per product de totale hoeveelheid en omzet aggregeert.
-- 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;Materialized view in SQL Server
In SQL Server heten materialized views "Indexed Views". De onderliggende tabellen moeten aan specifieke vereisten voldoen om een indexed view te kunnen maken, zoals het inschakelen van de optie WITH SCHEMABINDING.
De optie WITH SCHEMABINDING zorgt ervoor dat het schema niet kan veranderen zolang de indexed view bestaat. Er moet een unieke clustered index worden gemaakt om de view te materialiseren.
-- 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);
GOWil je meer leren over SQL Server? Bekijk dan onze skill track SQL Server Fundamentals om vertrouwd te raken met verschillende SQL-vaardigheden voor data-analyse.
Materialized view in Oracle
De syntax om materialized views te maken in Oracle lijkt op die van de PostgreSQL-database. We kunnen ook refresh-opties opgeven, zoals ON DEMAND of 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;Materialized view in gedistribueerde databases
Materialized views kunnen ook distributiemethoden specificeren voor betere prestaties in databases zoals Azure Synapse of Amazon Redshift die gedistribueerde datawarehouses ondersteunen.
Hash-distributie voor Amazon Redshift
De onderstaande query maakt een materialized view met de naam sales_summary die totale hoeveelheid en omzet per product_id aggregeert. De opties DISTSTYLE KEY en DISTKEY(product_id) zorgen ervoor dat data over nodes wordt verdeeld op basis van product_id, wat de prestaties verbetert voor queries die op deze kolom joinen.
-- 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;Round-robin-distributie voor Azure Synapse
Deze query maakt een materialized view genaamd sales_summary die totale hoeveelheid en omzet per product_id aggregeert. De ROUND_ROBIN-distributie verdeelt data gelijkmatig over nodes, wat handig is voor scenario’s die niet zwaar leunen op joins.
-- 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;Een materialized view verversen in SQL
Data in materialized views kan worden ververst om actueel te blijven met de onderliggende tabellen. De keuze van de verversingsmethode hangt af van de bedrijfsvereisten en prestatieoverwegingen van de betreffende database. Laten we de volgende manieren bekijken om materialized views te verversen.
Handmatige verversing
Bij handmatig verversen wordt de materialized view alleen ververst wanneer de gebruiker dit expliciet aanvraagt. Deze aanpak geeft de meeste controle over wanneer data wordt bijgewerkt en is geschikt voor situaties waarin data niet vaak verandert of updates buiten piekuren plaatsvinden.
De volgende query laat de handmatige verversingsmethode in PostgreSQL zien.
REFRESH MATERIALIZED VIEW sales_summary;Periodieke verversing
De materialized view wordt tijdens de verversingsperiode automatisch op opgegeven intervallen ververst, zodat data up-to-date is zonder tussenkomst van de gebruiker. Deze methode is nuttig voor tijdkritische toepassingen waar data redelijk actueel moet zijn.
Het onderstaande voorbeeld laat zien hoe je periodieke verversingen in Oracle kunt opnemen door ververingsschedules direct in de instructie CREATE MATERIALIZED VIEW te definiëren. De ververstijd staat ingesteld op uurlijkse intervallen.
-- 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;On-demand verversen
On-demand verversen vindt plaats zodra de onderliggende data verandert, meestal via een triggermechanisme. Zo bevat de materialized view altijd de bijgewerkte data. In PostgreSQL kun je bijvoorbeeld triggers instellen om de view te verversen wanneer er wijzigingen optreden in de onderliggende tabel.
Volledige vs. incrementele verversingen
Je kunt de data volledig of incrementeel verversen in materialized views. De onderstaande tabel vat de twee methoden en de use-cases samen.
| Type verversing | Omschrijving | Voordelen | Nadelen |
|---|---|---|---|
| Volledige verversing | Laadt de volledige dataset opnieuw en vervangt alle bestaande data in de view | - Eenvoudig te implementeren - Bouwt de hele view opnieuw op |
- Resource-intensief voor grote datasets - Langere ververstijden |
| Incrementele verversing | Werkt alleen de gewijzigde delen van de view bij | - Efficiënter doordat alleen gewijzigde data wordt verwerkt - Geschikt voor grote datasets met frequente wijzigingen |
- Vereist extra inrichting (bijv. logs om wijzigingen te volgen) - Niet altijd ondersteund voor alle queries |
Best practices voor materialized views
Bij het gebruik van materialized views is het belangrijk om de volgende werkwijzen te overwegen voor optimaal gebruik.
- De juiste queries materialiseren: Materialiseer complexe, resource-intensieve queries zoals joins, aggregaties en subqueries. Dergelijke queries profiteren van vooraf berekende resultaten, waardoor de belasting op de database afneemt.
- Balans tussen actualiteit en prestaties: Kies, afhankelijk van je use-case, de juiste verfrisstrategie, zoals handmatig, periodiek of on-demand. Gebruik incrementele verversing om de rekendruk te verlagen en monitor de frequentie van datamutaties om verfrisintervallen te bepalen.
- Materialized views inzetten voor query-intensieve workloads: Zet materialized views in voor BI-rapporten en dashboards waar snelle responstijden essentieel zijn. Je kunt ook indexen toevoegen op de kolommen die in materialized views worden gebruikt voor sneller filteren en sorteren.
Materialized views in verschillende databasesystemen
Zoals je hebt gezien, bieden verschillende databasesystemen uiteenlopende ondersteuning voor materialized views. De volgende tabel vat de unieke functies en beperkingen van materialized views in deze databases samen.
| Databasesysteem | Verversingsmethoden | Incrementele verversing | Automatische verversing | Speciale functies/beperkingen |
|---|---|---|---|---|
| PostgreSQL | Handmatig (REFRESH MATERIALIZED VIEW) | Nee | Nee | Geen native incrementele verversing. Handmatig plannen nodig. |
| SQL Server | Automatisch (Indexed Views) | Ja (automatische synchronisatie) | Ja |
Vereist |
| Oracle | Handmatig, On Commit, Gepland | Ja (Fast Refresh) | Ja | Ondersteunt fast refresh, partitionering en parallelisme. Vereist viewlogs. |
| Amazon Redshift | Handmatig, Gepland | Ja | Ja | Ondersteunt gedistribueerde data met hash- of round-robin-distributie. |
| MySQL | Niet native ondersteund | Nee | Nee | Workarounds nodig (bijv. tijdelijke tabellen, tools van derden). |
| Azure Synapse | Handmatig, Gepland | Ja | Ja | Maakt verschillende distributiestrategieën mogelijk voor optimalisatie. |
Als je SQL Server als voorkeursdatabase gebruikt, raad ik DataCamp’s cursus Introduction to SQL Server aan om de basis van Microsoft SQL Server voor data-analyse onder de knie te krijgen. Bekijk ook onze career track SQL Server Developer om te begrijpen hoe je queries optimaliseert en problemen in SQL Server oplost.
Aanvullende aandachtspunten
Hoewel SQL-materialized views nuttig zijn voor queryoptimalisatie, kennen ze ook enkele uitdagingen en beperkingen. Hieronder staan de meest voorkomende issues met materialized views en hoe je ermee omgaat.
- Opslagoverhead: Materialized views slaan queryresultaten fysiek op schijf op, wat de opslagbehoefte vergroot. Materialiseer daarom alleen views voor resource-intensieve queries en partitioneer materialized views voor grote datasets om onnodig opslagverbruik te voorkomen.
- Updatekosten en verversingsoverhead: Het synchroon houden van materialized views met de onderliggende tabellen kan resource-intensief zijn, vooral voor views die vaak updates nodig hebben of complexe berekeningen bevatten. Gebruik waar mogelijk incrementele verversing of stel passende verfrisintervallen in wanneer het databusagebruik lager is om de overhead te beperken.
- Dataconsistentie en synchronisatie: Materialized views kunnen verouderen als de onderliggende data vaak verandert, wat leidt tot verouderde gegevens. Kies daarom de juiste verfrisstrategie en monitor datamutaties om de strategie waar nodig aan te passen.
- Onderhoudsoverhead: Materialized views vereisen doorlopend onderhoud, zoals het instellen van passende ververisschema’s, het monitoren van opslaggebruik en het bijhouden van afhankelijkheden van de onderliggende tabellen. Gebruik hiervoor altijd geautomatiseerde ververisschema’s, monitor de systeemprestaties en stel alerts in voor mislukte verversingen.
Conclusie
Materialized views zijn nuttig in SQL-databases om queryprestaties te optimaliseren. Ze slaan de resultaten van een query fysiek op schijf op, wat snellere queryprestaties oplevert doordat herberekening afneemt. Deze functie maakt materialized views geschikt voor het afhandelen van complexe, resource-intensieve queries met joins, aggregaties en grote datasets. Begrijpen hoe je materialized views in verschillende databases implementeert, helpt je om je query- en databaseoptimalisatievaardigheden te versterken.
Wil je je SQL-vaardigheden verder ontwikkelen? Probeer dan DataCamp’s career track Associate Data Analyst in SQL om een bekwame data-analist te worden. De cursus Reporting in SQL helpt je bovendien om complexe rapporten en dashboards te bouwen voor een effectieve datavisualisatie. Tot slot kun je de SQL Associate Certification behalen om je beheersing van SQL voor businessvraagstukken aan te tonen en je te onderscheiden van andere professionals.
\n
\n
\n\n
\n\n
\nTechnical writer voor data science met praktische ervaring in data-analyse, business intelligence en data science. Ik schrijf praktische, op de industrie gerichte content over SQL, Python, Power BI, Databricks en data engineering, gebaseerd op analytisch werk in de echte wereld. Mijn schrijfwerk slaat een brug tussen technische diepgang en zakelijke impact, en helpt professionals om data om te zetten in onderbouwde beslissingen.
\nFAQs
Wat is een materialized view in SQL?
Een materialized view is een databaseobject dat het resultaat van een query fysiek opslaat en zo prestaties optimaliseert door herberekening te vermijden.
Hoe verschilt een materialized view van een reguliere view?
In tegenstelling tot reguliere views, die bij elke toegang data dynamisch ophalen, slaan materialized views data op als fysieke tabellen, wat snellere query-uitvoering mogelijk maakt.
Wat is het verschil tussen volledige en incrementele verversing?
Een volledige verversing berekent de hele view opnieuw, terwijl een incrementele verversing alleen de gewijzigde data bijwerkt, wat efficiënter is.
Welke databases ondersteunen materialized views?
PostgreSQL, SQL Server (indexed views), Oracle, Amazon Redshift en Azure Synapse Analytics ondersteunen materialized views, elk met verschillende functies en beperkingen, terwijl MySQL geen native ondersteuning biedt.
Verbruiken materialized views extra opslag?
Ja, omdat ze data fysiek opslaan, verhogen materialized views de opslagbehoefte.
Onderwerpen
