Materialized View


Oracle Database selain menawarkan fitur OLTP (Online Transaction Processing), juga mendukung data Warehousing. Secara singkatnya, aku bisa bilang kalau OLTP adalah proses database sehari-hari dimana data di-input oleh user dan disimpan di tabel relational, sedangkan data warehousing adalah proses penyimpanan data untuk keperluan query dan analisa. Ada beberapa karakteristik data warehousing yang berbeda dari OLTP, misalnya, memiliki banyak index, sedikit join, dan tabel yang tidak ter-normalisasi.

Data yang dipakai dalam data warehousing adalah data historical berbulan-bulan atau bertahun-tahun yang lalu. Oleh sebab itu, kebanyakan fact table dalam data warehousing memiliki ukuran yang sangat besar. Salah satu cara untuk meningkatkan performance dalam data warehousing adalah dengan membuat summary. Implementasi tabel summary ini pada database Oracle dapat dicapai dengan menggunakan materialized view. Berikut ini adalah contoh SQL untuk membuat materialized view:

CREATE MATERIALIZED VIEW
LOG ON products
WITH SEQUENCE, ROWID
(prod_id, prod_name)
INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW
LOG ON sales
WITH SEQUENCE, ROWID
(prod_id, quantity_sold, amount_sold)
INCLUDING NEW VALUES;


CREATE MATERIALIZED VIEW
products_sales
BUILD IMMEDIATE
REFRESH FAST
ENABLE QUERY REWRITE
AS
SELECT
p.prod_id,
sum(quantity_sold)
as quantity_sold,
sum(amount_sold)
as amount_sold
FROM sales s, products p
WHERE
s.prod_id = p.prod_id
GROUP BY
p.prod_id;

Tabel sales di schema SH (Sales History, demo schema bawaan Oracle Database) adalah tabel yang isinya besar yang mencapai 1.016.271 record. Setiap query yang mengandung SUM, AVG, dan sebagainya yang umum dipergunakan dalam reporting pasti akan menggunakan banyak resource jika harus di-proses ulang setiap kali request. Pada contoh di atas, aku menyertakan klausa ENABLE QUERY REWRITE untuk mengaktifkan query rewrite. Aku juga menyertakan klausaREFRESH FAST. Ini berarti, aku ingin materialized view tersebut nilainya diperbaharui secara otomatis jika ada perubahan pada tabel yang membentuknya, yaitu tabel products dan tabel sales. Perubahan ini akan di-apply secara incremental berdasarkan data dari log yang sudah buat untuk tabel sales dan tabelproducts sehingga proses refresh bisa lebih singkat.

Materialized view akan dipakai secara otomatis melalui teknik yang disebut query rewrite tanpa perlu referensi ke materialized view bersangkutan di statement SQL. Dengan demikian, operasi materialized view berlangsung secara transparan bagi user yang memberikan statement SQL. Sebuah query SQL akan melalui beberapa proses untuk menentukan apakah ia akan mengalami query rewrite atau tidak. Oracle akan melalui perbandingan full text untuk menentukan apakah query SQL sesuai dengan query di materialized view (perbandingan ini tidak menyertakan whitespace). Jika tidak ketemu, ia akan membandingkan bagian FROM dari query SQL dan query di materialized view. Jika masih belum ketemu juga, maka ia akan menggunakan general query rewrite.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s