Day 29: Materialized View


Learning In HomeOriginal Date: 10 Februari 2009

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 klausa REFRESH 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 tabel products 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.

Perihal Solid Snake
I'm nothing...

Apa komentar Anda?

Please log in using one of these methods to post your comment:

Logo WordPress.com

You are commenting using your WordPress.com account. Logout / Ubah )

Gambar Twitter

You are commenting using your Twitter account. Logout / Ubah )

Foto Facebook

You are commenting using your Facebook account. Logout / Ubah )

Foto Google+

You are commenting using your Google+ account. Logout / Ubah )

Connecting to %s

%d blogger menyukai ini: