Lewati ke konten utama

Materialized View SQL: Meningkatkan Kinerja Kueri

Pahami bagaimana materialized view meningkatkan kinerja kueri di database SQL. Pelajari keterbatasan dan praktik terbaik materialized view di berbagai database.
· 9 mnt Baca

Jelajahi bersama AI

Materialized view adalah fitur kuat di database SQL yang membantu mengoptimalkan kinerja kueri dengan menyimpan hasil kueri secara fisik di disk, sehingga menawarkan kinerja kueri lebih cepat dengan mengurangi penghitungan ulang. Ini membuatnya sangat berguna untuk menangani kueri kompleks dan intensif sumber daya yang melibatkan join, agregasi, dan set data besar.

Saat kita mulai, saya merekomendasikan untuk mengikuti kursus Introduction to SQL DataCamp dan jalur keterampilan SQL Fundamentals untuk mempelajari dasar-dasar SQL dan cara mengekstrak data menggunakan kueri. SQL Basics Cheat Sheet juga akan menjadi panduan yang membantu untuk fungsi-fungsi SQL umum dalam memfilter dan mengagregasi data.

Apa itu SQL Materialized Views?

Materialized view adalah jenis objek database khusus yang menyimpan hasil kueri secara fisik alih-alih menghitungnya secara langsung seperti view biasa. Sementara view SQL biasa adalah kueri SQL yang disimpan dan menghasilkan hasilnya secara dinamis setiap kali diakses, materialized view melakukan prahitung dan menyimpan data dalam struktur seperti tabel.

Dengan menyimpan himpunan hasil di disk, materialized view dapat secara signifikan mengurangi beban pada database, meningkatkan kinerja kueri, dan membantu merampingkan pemrosesan operasi yang secara komputasional intensif.

Membuat Materialized View di SQL

Proses membuat materialized view melibatkan penggunaan sintaks CREATE MATERIALIZED VIEW, yang sedikit berbeda di berbagai database SQL. Metode berikut menunjukkan cara membuat materialized view di SQL Server, PostgreSQL, dan Oracle.

Materialized view di PostgreSQL

Di PostgreSQL, Anda dapat membuat materialized view menggunakan sintaks berikut. Contoh ini membuat materialized view bernama sales_summary yang mengagregasi total kuantitas dan pendapatan setiap produk.

-- 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 di SQL Server

Di SQL Server, materialized view disebut "Indexed Views." Tabel dasar harus memenuhi persyaratan tertentu untuk membuat indexed view, seperti mengaktifkan opsi WITH SCHEMABINDING.

Opsi WITH SCHEMABINDING memastikan skema tidak dapat berubah selama indexed view ada. Sebuah unique clustered index harus dibuat agar view tersebut termaterialisasi.

-- 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);
GO

Jika Anda ingin mempelajari lebih lanjut tentang SQL Server, saya sarankan melihat jalur keterampilan SQL Server Fundamentals kami untuk membiasakan diri dengan berbagai keterampilan SQL untuk analisis data.

Materialized view di Oracle

Sintaks untuk membuat materialized view di Oracle mirip dengan database PostgreSQL. Kita juga dapat menentukan opsi refresh, seperti ON DEMAND atau 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 di database terdistribusi

Materialized view juga dapat menentukan metode distribusi untuk kinerja yang lebih baik pada database seperti Azure Synapse atau Amazon Redshift yang mendukung gudang data terdistribusi.

Hash Distribution untuk Amazon Redshift

Kueri di bawah ini membuat materialized view bernama sales_summary yang mengagregasi total kuantitas dan pendapatan berdasarkan product_id. Opsi DISTSTYLE KEY dan DISTKEY(product_id) memastikan data didistribusikan ke seluruh node berdasarkan product_id, meningkatkan kinerja untuk kueri yang melakukan join pada kolom ini.

-- 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 Distribution untuk Azure Synapse

Kueri ini membuat materialized view bernama sales_summary yang mengagregasi total kuantitas dan pendapatan berdasarkan product_id. Distribusi ROUND_ROBIN menyebarkan data secara merata ke seluruh node, yang berguna untuk skenario yang tidak terlalu bergantung pada 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;

Menyegarkan Materialized View di SQL

Data dalam materialized view dapat disegarkan agar tetap mutakhir dengan tabel dasarnya. Pilihan metode refresh bergantung pada kebutuhan bisnis dan pertimbangan kinerja dari database tertentu. Mari kita lihat metode penyegaran data berikut untuk materialized view.

Refresh manual

Dalam refresh manual, materialized view hanya disegarkan ketika diminta secara eksplisit oleh pengguna. Pendekatan ini memberikan kontrol paling besar atas waktu pembaruan data, sehingga cocok untuk skenario di mana data jarang berubah, atau pembaruan dilakukan di luar jam sibuk.

Kueri berikut menunjukkan metode refresh manual di PostgreSQL.

REFRESH MATERIALIZED VIEW sales_summary;

Refresh berkala

Materialized view disegarkan secara otomatis pada interval yang ditentukan selama periode refresh, memastikan data tetap terbaru tanpa intervensi pengguna. Metode ini berguna untuk aplikasi yang peka waktu di mana data perlu relatif mutakhir.

Contoh di bawah ini menunjukkan cara menyertakan refresh berkala di Oracle dengan mendefinisikan jadwal refresh langsung di pernyataan CREATE MATERIALIZED VIEW. Waktu refresh diatur pada interval per jam.

-- 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

Refresh on-demand terjadi setiap kali data dasar berubah, biasanya melalui mekanisme trigger. Ini memastikan bahwa materialized view selalu berisi data yang diperbarui. Misalnya, di PostgreSQL, trigger dapat disetel untuk menyegarkan view ketika terjadi perubahan di tabel dasar.

Refresh penuh vs. inkremental

Anda dapat menyegarkan data secara penuh atau inkremental pada materialized view. Tabel berikut merangkum kedua metode dan kasus penggunaannya.

Jenis RefreshDeskripsiKelebihanKekurangan
Refresh PenuhMemuat ulang seluruh dataset, menggantikan semua data yang ada di view- Mudah diterapkan
- Membangun ulang seluruh view		- Intensif sumber daya untuk dataset besar
- Waktu refresh lebih lama
Refresh InkrementalMemperbarui hanya bagian view yang berubah- Lebih efisien, hanya memproses data yang dimodifikasi
- Cocok untuk dataset besar dengan perubahan yang sering		- Memerlukan penyiapan tambahan (misalnya, log untuk melacak perubahan)
- Tidak selalu didukung untuk semua kueri

Praktik Terbaik untuk Materialized View

Saat menggunakan materialized view, penting untuk mempertimbangkan praktik berikut untuk penggunaan yang optimal.

  • Memilih Kueri yang Tepat untuk Dimaterialisasi: Materialisasikan kueri yang kompleks dan intensif sumber daya seperti join, agregasi, dan subkueri. Kueri semacam ini akan mendapat manfaat dari hasil prahitung yang mengurangi beban pada database.
  • Menyeimbangkan Kebaruan Data dan Kinerja: Bergantung pada kasus penggunaan Anda, pilih strategi refresh yang sesuai, seperti metode refresh manual, berkala, atau on-demand. Gunakan refresh inkremental untuk mengurangi beban komputasi dan pantau frekuensi perubahan data untuk menentukan interval refresh.
  • Menggunakan Materialized View untuk Mengoptimalkan Beban Kerja yang Berat Kueri: Manfaatkan materialized view untuk laporan BI dan dasbor di mana waktu respons cepat sangat penting. Anda juga dapat mengindeks kolom yang digunakan dalam materialized view untuk pemfilteran dan pengurutan yang lebih cepat.

Materialized View di Berbagai Sistem Database

Seperti yang telah Anda lihat, sistem database yang berbeda menawarkan dukungan yang bervariasi untuk materialized view. Tabel berikut merangkum fitur unik dan keterbatasan materialized view di database tersebut.

Sistem DatabaseMetode RefreshRefresh InkrementalRefresh OtomatisFitur/Keterbatasan Khusus
PostgreSQLManual (REFRESH MATERIALIZED VIEW)TidakTidakTidak memiliki refresh inkremental native. Penjadwalan manual diperlukan.
SQL ServerOtomatis (Indexed Views)Ya (Sinkronisasi Otomatis)Ya

Memerlukan WITH SCHEMABINDING. Dukungan kueri terbatas.

OracleManual, On Commit, TerjadwalYa (Fast Refresh)YaMendukung fast refresh, partisi, dan paralelisme. Memerlukan log view.
Amazon RedshiftManual, TerjadwalYaYaMendukung data terdistribusi dengan distribusi hash atau round-robin.
MySQLTidak Didukung Secara NativeTidakTidakMembutuhkan solusi alternatif (misalnya, tabel sementara, alat pihak ketiga).
Azure SynapseManual, TerjadwalYaYaMemungkinkan berbagai strategi distribusi untuk optimalisasi.

Jika Anda menggunakan SQL Server sebagai database pilihan, saya sarankan mengikuti kursus Introduction to SQL Server DataCamp untuk menguasai dasar-dasar Microsoft SQL Server untuk analisis data. Selain itu, lihat juga jalur karier SQL Server Developer kami untuk memahami cara mengoptimalkan kueri dan menelusuri masalah di SQL Server.

Hal Lain yang Perlu Dipertimbangkan

Meskipun materialized view SQL berguna untuk optimalisasi kueri, ada juga tantangan dan keterbatasannya. Berikut adalah masalah umum dengan materialized view dan cara menanganinya.

  • Beban Penyimpanan: Materialized view menyimpan hasil kueri secara fisik di disk, yang meningkatkan kebutuhan penyimpanan. Untuk menghindari penggunaan ruang penyimpanan yang tidak perlu, hanya materialisasikan view untuk kueri yang intensif sumber daya dan partisi materialized view untuk dataset besar.
  • Biaya Pembaruan dan Beban Refresh: Menjaga materialized view tetap sinkron dengan tabel dasar dapat intensif sumber daya, terutama untuk view yang memerlukan pembaruan sering atau melibatkan perhitungan kompleks. Untuk menghindari beban refresh, gunakan refresh inkremental jika didukung atau atur interval refresh yang sesuai saat penggunaan database lebih rendah.
  • Konsistensi Data dan Sinkronisasi: Materialized view dapat menjadi kedaluwarsa jika data dasar sering berubah, yang menyebabkan masalah data basi. Untuk menghindari masalah ini, pilih strategi refresh yang sesuai dan pantau perubahan data untuk menyesuaikan strategi refresh sesuai kebutuhan.
  • Beban Pemeliharaan: Materialized view memerlukan pemeliharaan berkelanjutan, seperti menetapkan jadwal refresh yang sesuai, memantau penggunaan penyimpanan, dan melacak dependensi pada tabel dasar. Untuk mengatasi tantangan ini, selalu gunakan jadwal refresh otomatis, pantau kinerja sistem, dan atur peringatan untuk refresh yang gagal.

Kesimpulan

Materialized view berguna di database SQL untuk mengoptimalkan kinerja kueri. Mereka menyimpan hasil kueri secara fisik di disk, menawarkan kinerja kueri yang lebih cepat dengan mengurangi penghitungan ulang. Fitur ini membuat materialized view berguna untuk menangani kueri kompleks yang intensif sumber daya yang melibatkan join, agregasi, dan set data besar. Memahami cara menerapkan materialized view di berbagai database akan membantu Anda meningkatkan keterampilan pengoptimalan kueri dan database.

Jika Anda ingin meningkatkan keterampilan SQL, saya sarankan mencoba jalur karier Associate Data Analyst in SQL dari DataCamp untuk menjadi analis data yang andal. Kursus Reporting in SQL juga akan membantu Anda mahir membangun laporan dan dasbor kompleks untuk penyajian data yang efektif. Terakhir, Anda sebaiknya memperoleh SQL Associate Certification untuk menunjukkan penguasaan Anda dalam menggunakan SQL untuk memecahkan masalah bisnis dan tampil menonjol di antara para profesional lainnya.

Allan Ouko

Penulis

Allan Ouko

LinkedIn
Penulis teknis Data Science dengan pengalaman langsung dalam analitik data, business intelligence, dan data science. Saya menulis konten praktis berfokus industri tentang SQL, Python, Power BI, Databricks, dan rekayasa data, yang berakar pada pekerjaan analitik dunia nyata. Tulisan saya menjembatani kedalaman teknis dan dampak bisnis, membantu para profesional mengubah data menjadi keputusan yang meyakinkan.

FAQs

Apa itu materialized view dalam SQL?

Materialized view adalah objek database yang menyimpan hasil kueri secara fisik, mengoptimalkan kinerja dengan menghindari penghitungan ulang.

Apa perbedaan materialized view dengan view biasa?

Berbeda dengan view biasa, yang mengambil data secara dinamis pada setiap akses, materialized view menyimpan data sebagai tabel fisik, memungkinkan eksekusi kueri yang lebih cepat.

Apa perbedaan antara refresh penuh dan inkremental?

Refresh penuh menghitung ulang seluruh view, sedangkan refresh inkremental hanya memperbarui data yang berubah, sehingga lebih efisien.

Database mana yang mendukung materialized view?

PostgreSQL, SQL Server (indexed views), Oracle, Amazon Redshift, dan Azure Synapse Analytics mendukung materialized view, masing-masing dengan fitur dan keterbatasan berbeda, sementara MySQL tidak memiliki dukungan native.

Apakah materialized view mengonsumsi penyimpanan tambahan?

Ya, karena menyimpan data secara fisik, materialized view meningkatkan kebutuhan penyimpanan.

Topik

SQLRekayasa Data

Terkait

blogs

40 Pertanyaan Wawancara DBMS Teratas di 2026

blogs

Tutorial Korelasi di R

blogs

Spaghetti Plot dan Jalur Badai

Lihat Selengkapnya →