Memakai Materialized View Di Oracle TimesTen


Satu kekurangan dari MySQL yang terasa saat saya beralih ke database tersebut adalah tidak adanya fasilitas materialized view.   Fasilitas materialized view pertama kali diperkenalkan oleh Oracle Database, baru kemudian diikuti oleh database lainnya.   Hingga saat ini, Oracle MySQL masih belum mendukung materialized view (beberapa berusaha mensimulasikannya melalui trigger!).

Lalu, apa itu materialized view?   Apa bedanya dengan view biasa?

Untuk menunjukkan perbedaannya, saya akan membuat tabel seperti berikut ini:

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

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

Command> INSERT INTO produk VALUES ('AAA', 'Produk A');
1 row inserted.

Command> INSERT INTO produk VALUES ('BBB', 'Produk B');
1 row inserted.

Command> INSERT INTO produk VALUES ('CCC', 'Produk C');
1 row inserted.

Command> INSERT INTO produk VALUES ('DDD', 'Produk D');
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'AAA', 10);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'BBB', 20);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '001', 'CCC', 30);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '002', 'AAA', -5);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '003', 'BBB', -10);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '003', 'CCC', -20);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'AAA', 5);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'BBB', 4);
1 row inserted.

Command> INSERT INTO stok VALUES (SYSDATE, '004', 'CCC', 3);
1 row inserted.

Pada contoh tabel di atas, saya memiliki daftar 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:

Command> 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;
< AAA, Produk A, 10 >
< BBB, Produk B, 14 >
< CCC, Produk C, 13 >
< DDD, Produk D, 0 >
4 rows found.

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:

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

Command> SELECT * FROM persediaan;
< AAA, Produk A, 10 >
< BBB, Produk B, 14 >
< CCC, Produk C, 13 >
< DDD, Produk D, 0 >
4 rows found.

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:

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

Command> CREATE MATERIALIZED VIEW sync_persediaan AS
       > SELECT kodeProduk, SUM(jumlah) jumlah, COUNT(*) entri
       > FROM stok
       > GROUP BY kodeProduk;
3 rows materialized.

Command> SELECT * FROM sync_persediaan;
< AAA, 10, 3 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

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:

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

Command> 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).

Untuk mengatasi masalah kinerja yang timbul akibat perubahan tabel sumber (materialized table),  TimesTen memungkinkan apa yang disebut sebagai asynchronous materialized view.   Sebagai contoh, saya membuat sebuah asynchronous materialized view dengan memberikan perintah berikut ini:

Membuat asynchronous materialized view

Membuat asynchronous materialized view

Materialized view bernama async_persediaan di atas akan diperbaharui setiap 10 menit.   Bila tidak ada perubahan tabel sumber pada saat materialized view diperbaharui, maka tidak akan ada yang dikerjakan.   Dengan demikian, perubahan pada tabel sumber tidak akan langsung mempengaruhi asynchronous materialized view.

Pada contoh ini, bila pengguna melakukan perubahan jumlah stok (akibat menambah atau menghapus faktur), maka isi dari async_persediaan tidak akan berubah selama interval 10 menit belum tercapai.   Hal ini biasanya tidak menjadi masalah, kecuali bila pengguna tiba-tiba membuka halaman untuk melihat persediaan (atau mencetak laporan)!   Untuk mengatasi hal tersebut, saya dapat memaksa agar asynchronous materialized view segera diperbaharui saat halaman persediaan akan ditampilkan, dengan menggunakan perintah SQL REFRESH MATERIALIZED VIEW seperti berikut ini:

Command> SELECT * FROM async_persediaan;
< AAA, 30, 4 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

Command> REFRESH MATERIALIZED VIEW async_persediaan;

Command> SELECT * FROM async_persediaan;
< AAA, 50, 5 >
< BBB, 14, 3 >
< CCC, 13, 3 >
3 rows found.

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: