Materialized View kullanımı ve Fast Refresh için basit bir örnek

Materialized view’lar(MV) genel olarak Data warehouse sistemlerinde kullanılır. Normal view’lar sadece sorguları saklayıp istenildiğinde dataları ilgili tablolardan belirtilen kriterlere göre getirirken, materialized view’larda sorguların yanı sıra bu sorguların sonucunda dönen datalar da tutulur. Yani bir nevi tablo görevi görür. Bunun kullanılma amacı ise; devasa datalara sahip tabloların sadece belirtilen kriterlere uyan datalarını önceden hazırlayıp hızlı bir şekilde sunmak.

Materialized view’ların sorguladığı tablolardaki datalar değiştiğinde değişikliklerin bu view’lara yansıması için bu materialized view’ların refresh edilmesi gerekiyor.

Birkaç refresh metodu bulunmaktadır ve mv create işleminde belirtilir. COMPLETE ve FAST. Refresh işlemi için DBMS_MVIEW.REFRESH prosedürü kullanılır.

Refresh Complete: MV refresh complete yapıldığında mv’ı oluşturan sorgu tekrar çalıştırılır ve gelen dataların tamamı mv’daki dataların yerine geçer. Refresh etmek için DBMS_MVIEW.REFRESH prosedürünün method parametresine C gönderilir.

execute DBMS_MVIEW.REFRESH( LIST => 'MV', METHOD => 'C' );

Refresh Fast: MV fast refresh yapabilmek için; mv’ın bağlı olduğu tablolar üzerine materialized view log’lar create edilir. MV log’lar yaratıldığı tablolar üzerinde değişen kayıtları PK bazında saklar. Fast refresh yapıldığında ise bu mv log’lardan sadece değişen kayıtları bularak bunları mv’a taşır. Refresh etmek için DBMS_MVIEW.REFRESH prosedürünün method parametresine F gönderilir.

execute dbms_mview.refresh( list => 'MV', method => 'F' );

Fast refresh opsiyonlarından otomatik ve manuel refresh işlemi için aşağıdaki gibi basit bir örnek yapalım:

İlk olarak test için bir tablo yaratılır ve materialized view log kullanabilmek için bu tablo üzerinde bir primary key tanımı yapılır. Daha sonra bu tablo üzerine bir Materialized view log(MV LOG) ve Materialized View (MV) create edilir.

SQL> create table tablo1 as select rownum r,'ROW'||rownum line from all_objects where rownum<=5;

Table created

SQL> alter table tablo1 add primary key(r);

Table altered

SQL> create materialized view log on tablo1 with primary key;

Materialized view log created

SQL> drop materialized view mv_tablo1;

Materialized view dropped

SQL> create materialized view mv_tablo1 refresh fast with primary key as select * from tablo1;

Materialized view created

SQL> select * from tablo1;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

SQL> select * from mv_tablo1;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

Daha sonra tablo üzerinde birkaç DML işlemi yapılır. Commit’ledikten sonra MV da dataların güncellenmediğini görülür.

SQL> update tablo1 set line='DUMMY' where r=1;

1 row updated

SQL> update tablo1 set line='ROW1' where r=2;

1 row updated

SQL> update tablo1 set line='ROW2' where r=1;

1 row updated

SQL> select * from tablo1;

R LINE

---------- -------------------------------------------

1 ROW2

2 ROW1

3 ROW3

4 ROW4

5 ROW5

SQL> select * from mv_tablo1 order by r;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

SQL> commit;

Commit complete

SQL> select * from tablo1;

R LINE

---------- -------------------------------------------

1 ROW2

2 ROW1

3 ROW3

4 ROW4

5 ROW5

SQL> select * from mv_tablo1 order by r;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

MV refresh işi aşağıdaki komut ile manuel çalıştırdıktan sonra güncellenen dataların MV’a geldiği görülür.

SQL> exec dbms_mview.refresh('MV_TABLO1');

PL/SQL procedure successfully completed

SQL> select * from mv_tablo1 order by r;

R LINE

---------- -------------------------------------------

1 ROW2

2 ROW1

3 ROW3

4 ROW4

5 ROW5

SQL>

Bu sefer aynı işlem MV create işleminde ON COMMIT ifadesi kullanılarak tekrarlanır.

Aynı şekilde tablo ve primary key tanımı yapıldıktan sonra MV Log ve MV create edilir. Farklı olarak MV create ederken refresh işleminin commit sonrası otomatik yapmasını için “ON COMMIT” ile create edilir.

SQL> create table tablo2 as select rownum r,'ROW'||rownum line from all_objects where rownum<=5;

Table created

SQL> alter table tablo2 add primary key(r);

Table altered

SQL> create materialized view log on tablo2 with primary key;

Materialized view log created

SQL> drop materialized view mv_tablo2;

Materialized view dropped

SQL> create materialized view mv_tablo2 refresh fast on commit with primary key as select * from tablo2;

Materialized view created

SQL> select * from tablo2;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

SQL> select * from mv_tablo2;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

Tablo üzerinde DML işlemleri yapılır.

SQL> update tablo2 set line='DUMMY' where r=1;

1 row updated

SQL> update tablo2 set line='ROW1' where r=2;

1 row updated

SQL> update tablo2 set line='ROW2' where r=1;

1 row updated

DML işlemleri sonrası Commit atılmadan tablo ve MV daki datalar sorguladığında değişen dataların doğal olarak mv’a yansımadığı görülür.

SQL> select * from tablo2;

R LINE

---------- -------------------------------------------

1 ROW2

2 ROW1

3 ROW3

4 ROW4

5 ROW5

SQL> select * from mv_tablo2 order by r;

R LINE

---------- -------------------------------------------

1 ROW1

2 ROW2

3 ROW3

4 ROW4

5 ROW5

Commit atıldıktan sonra mv datalarını kontrol edildiğinde dataların bu sefer MV’da güncellendiğini görülür. MV commit sonrası otomatik olarak refresh edilmiş oldu.

SQL> commit;

Commit complete

SQL> select * from mv_tablo2 order by r;

R LINE

---------- -------------------------------------------

1 ROW2

2 ROW1

3 ROW3

4 ROW4

5 ROW5

EK BILGI:

MV Log'ları sorgulamak için MLOG$_ objeleri kullanılır. DML işlemleri geldiğinde bu objede dml işlemi görmüş datalar görünmektedir.

Fast refresh sırasında on commit ifadesi kullanılıp kullanılmayan mv log’larda ufak bir farklılık görünür.

Otomatik refresh olan MV'ların MV Log'larında commit'lenmemiş dataları görürüz.

SQL> insert into tablo2 values(6,'ROW6');

1 row inserted

SQL> commit;

Commit complete

SQL> select * from mv_tablo2 order by r;

R LINE

---------- -------------------------------------------

1 ROW2

2 ROW1

3 ROW3

4 ROW4

5 ROW5

6 ROW6

6 rows selected

SQL> select * from mlog$_tablo2;