Materialized View Di Oracle

apa itu materialized view?   Apa bedanya dengan view biasa?

Untuk mengetahui perbedaanya kita akan mengaplikasinya ke dalam table database.. kita akan buat 2 buah table dan dari table tersebut akan kita buat materialized view nya.

CREATE TABLE produk (kode CHAR(3) PRIMARY KEY,nama VARCHAR2(50) NOT NULL );

CREATE TABLE stok (
          tanggal DATE NOT NULL DEFAULT SYSDATE PRIMARY KEY,
          nomorFaktur CHAR(3) NOT NULL,
         kodeProduk CHAR(3) NOT NULL,
          jumlah NUMBER NOT NULL,
          FOREIGN KEY (kodeProduk) REFERENCES produk(kode)
        );
INSERT INTO produk VALUES ('CCC', 'Produk C');
INSERT INTO produk VALUES ('DDD', 'Produk D');
INSERT INTO stok VALUES (SYSDATE, '002', 'AAA', -5);
INSERT INTO stok VALUES (SYSDATE, '003', 'BBB', -10);
INSERT INTO stok VALUES (SYSDATE, '003', 'CCC', -20);

Pada tabel di atas, ada  perubahan produk (misalnya, bertambah akibat pembelian dan berkurang akibat penjualan) pada tabel stok.   Biasanya, saya sering kali menampilkan nama produk beserta jumlah yang tersedia berdasarkan perubahan stok tersebut.   Untuk itu, saya dapat menggunakan query SQL berikut ini:

SELECT p.kode, p.nama, COALESCE(SUM(s.jumlah),0) 
FROM produk p LEFT JOIN stok s ON p.kode = s.kodeProduk 

GROUP BY p.kode, p.nama;

Banyak database sudah mendukung apa yang disebut sebagai view (atau disebut juga non-materialized view).   Biasanya view dipakai untuk menyederhanakan SQL yang menggabungkan beberapa tabel sehingga dapat diakses melalui view tersebut.   Sebagai contoh, untuk membuat sebuah view berdasarkan query di atas, maka saya dapat memberikan perintah seperti berikut ini:

CREATE VIEW persediaan AS
SELECT p.kode, p.nama, COALESCE(SUM(s.jumlah),0) jumlah FROM
produk p LEFT JOIN stok s ON p.kode = s.kodeProduk

GROUP BY p.kode, p.nama;

Pada contoh di atas, saya sudah membuat sebuah view baru yang diberi nama persediaan.   Sifat view adalah non-materialized sehingga tidak ada data yang ‘disimpan’ pada view tersebut.   Setiap kali query dilakukan pada view persediaan, maka SQL asalnya yang melakukan agregasi SUM akan selalu dikerjakan kembali!   Sebagai buktinya, lihat hasil explain yang men-select dari view persediaan:

EXPLAIN SELECT * FROM persediaan;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             SOLID.PRODUK
  IXNAME:              PRODUK
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                2
  LEVEL:               3
  OPERATION:           TblLkRangeScan
  TBLNAME:             SOLID.STOK
  IXNAME:              TTFOREIGN_2
  INDEXED CONDITION:   S.KODEPRODUK >= P.KODE
  NOT INDEXED:         <NULL>

  STEP:                3
  LEVEL:               2
  OPERATION:           MergeJoin(Left OuterJoin)
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   P.KODE = S.KODEPRODUK
  NOT INDEXED:         <NULL>

  STEP:                4
  LEVEL:               1
  OPERATION:           GroupBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Berbeda dengan view, sebuah materialized view akan ‘menyimpan’ nilai dari tabel sumber (disebut jadi detail table).   Sebagai contoh, kode SQL berikut ini akan membuat sebuah synchronous materialized view:

CREATE MATERIALIZED VIEW sync_persediaan AS

SELECT kodeProduk, SUM(jumlah) jumlah, COUNT(*) entri

FROM stok

GROUP BY kodeProduk;

Karena beberapa keterbatasan, saya tidak menghasilkan materialized view yang sama seperti non-materialized view di atas.   Setiap kali saya men-select dari sync_persediaan, saya akan memperoleh hasil yang sama tanpa adanya query ulang dari tabel sumber.   Dengan demikian, proses membaca dari materialized view bukanlah sesuatu yang berat, seperti yang ditunjukkan oleh hasil explain berikut ini:

EXPLAIN SELECT * FROM sync_persediaan;

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkSerialScan
  TBLNAME:             SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Lalu, kapan sebuah materialized view diperbaharui?   Setiap kali saya melakukan perubahan pada tabel sumber (disebut juga detail table). Sebagai contoh, bila saya menambahkan sebuah item baru untuk stok, maka sync_persediaan akan diperbaharui, seperti yang ditunjukkan pada hasil explain berikut ini:

EXPLAIN INSERT INTO stok VALUES (SYSDATE, '005', 'DDD', 10);

Query Optimizer Plan:

  STEP:                1
  LEVEL:               1
  OPERATION:           RowLkInsert
  TBLNAME:             STOK
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                2
  LEVEL:               2
  OPERATION:           RowLkSerialScan
  TBLNAME:             SOLID.SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         SOLID.SYNC_PERSEDIAAN.KODEPRODUK = 'DDD'

  STEP:                3
  LEVEL:               1
  OPERATION:           OneGroupGroupBy
  TBLNAME:             <NULL>
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

  STEP:                4
  LEVEL:               1
  OPERATION:           RowLkUpdView
  TBLNAME:             SOLID.SYNC_PERSEDIAAN
  IXNAME:              <NULL>
  INDEXED CONDITION:   <NULL>
  NOT INDEXED:         <NULL>

Kesimpulannya: Gunakan materialized view bila query untuk pada materialized view tersebut sering dilakukan.   Bila perubahan pada tabel sumber (detail table) terlalu sering, lebih baik menggunakan view (disebut juga non-materialized view).

Baca juga  : Tuning SQL Oracle