Mustafa Bektaş Tepe

Materialized View diğer viewlerden farklı olarak sadece data dictionary de tutulmuyor bundan farklı olarak fiziksel olarak veride tutan view/objedir. Materialized View ile referans aldığımız sql’in o anki verilerini fiziksel olarak tutarız ve ihtiyacımıza göre view ın verisini değişik opsiyonlarla güncelleyebiliriz.

Tekrar edecek olursak viewler bir sql sorgusunun saklanma şeklidir, Materialized Viewler ise hem sql sorgusu hemde veriden oluşmaktadır. Bu nedenle Materialized View verileri replicate(kopyasini almak) etmektedir. Peki replicate neden gerekebilir ?

Aslında daha çok DW uygulamalrında sıkça rastlarız ama bunun dışında veritabanı transferlerindede kullanılabilir. Örneğin salı günkü verilerden yola çıkarak, çarşamba günü rapor hazırlama işlemleri bu şekilde daha hızlı gerçekleştirilebilir. Salı akşamı sorgular çalıştırılır, Materialized Viewler güncellenir, çarşamba günü sorgular çalıştırıldığında Ana tablo yerine bu Materialized View lerden ilgili bilgiler çekilmiş olur. Bu da bizim kaynakları kullanma performansımızı arttıracak. Fakat bu durumda güncel verilerden yararlanmamış oluyoruz, sadece önceki güne ait verilerden yararlanmış oluyoruz. Tabi istersek bunu önleme yöntemleri de mevcut, Materialized View de.

Yani Materialized Viewler daha çok aşağıdaki konulara çözüm olur.

• Kayıt sayınız artıp, rapor sorgularınız çok geciktiğinde.
• Birden fazla tablodan sorgu almak sizi zorladığında.
• Özet tablolarınızı periyodik olarak doldurmak istediğinizde.
• Anlık olmayan raporlarınızda, yani 1 gün gecikmeli ya da ayda bir verdiğiniz raporlarda veya periyodik güncellenmesi gereken verilerde.

Materialized View oluşturmak için Sintaksis  aşağıdaki gibidir;

- Normal
CREATE MATERIALIZED VIEW view-name
BUILD [IMMEDIATE | DEFERRED]
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

-- Pre-Built
CREATE MATERIALIZED VIEW view-name
ON PREBUILT TABLE
REFRESH [FAST | COMPLETE | FORCE ]
ON [COMMIT | DEMAND ]
[[ENABLE | DISABLE] QUERY REWRITE]
AS
SELECT ...;

2 çeşit BUILD(veri yükleme) opsiyonu vardır.

• IMMEDIATE : Materialized view oluşturulurken veride doldurulur.
• DEFERRED : Materialized view ilk refresh sırasında doldurulur.

3 farklı REFRESH opsiyonu vardır.

• FAST : Materialized view loğu kullanılarak refresh yapılır. Bunun için materialized view logu bulunmak zorundadır.
• COMPLETE : Kullanılan materialized view sorgusuna göre materialized view içindeki veriler tamamen silinir, yeniden doldurulur.
• FORCE : İlk önce fast refresh denenir o çalışmazsa complete refresh çalıştırılır.

Refresh işlemi aşağıdaki iki yöntemden birinden sonra başlatılır(tetiklenir)

• ON COMMIT : Refresh işlemi materialized viewin bağlı olduğu tablolardan birinde veri değişikliği olduğunda ve buna commit gönderdiğinde olur.
• ON DEMAND : Yenileme işlemi manuel olarak ya da bir görev zamanlayıcısı(schedule) kullanılarak yapılır.

Aşagıdaki viewde materialized view oluşturulurken veri doldurulur. Force refresh yapılıp yenileme işlemide manuel yapılacaktır.

CREATE MATERIALIZED VIEW emp_mv
BUILD IMMEDIATE 
REFRESH FORCE
ON DEMAND
AS
SELECT * FROM emp@db1.world;

Aşağıda viewde ise refresh complete yapısını kullandık, bunun anlamı belirttiğimiz periyodik sürelerde Materialized View’in tekrar doldurulacağıdır. start with sysdate ile Materialized View’in derhal yaratilacagini ve next sysdate+7 diyerek tekrar doldurulacağı zamanı belirtiyoruz.

CREATE MATERIALIZED VIEW customer_mv
REFRESH COMPLETE
START WITH SYSDATE NEXT SYSDATE+7
AS    SELECT e.FIRST_NAME,
         e.LAST_NAME,
         j.JOB_TITLE,
         d.DEPARTMENT_NAME
    FROM HR.DEPARTMENTS d
         INNER JOIN HR.EMPLOYEES e
            ON e.EMPLOYEE_ID = d.MANAGER_ID
               AND d.DEPARTMENT_ID = e.DEPARTMENT_ID
         INNER JOIN HR.JOBS j
            ON j.JOB_ID = e.JOB_ID
   WHERE e.SALARY > 7500
ORDER BY j.JOB_TITLE;

Materialized view log oluşturma

Complete refresh kullanıldığı zaman materialized view içeriği tamamen silinir ve yeniden doldurulur.Bu da zaman ve network açısından bir maliyete sebep olur. Materialized View Log, sorguda kullanılan ana tablolardaki değişiklikleri kontrol ederek bunları kayıt altına alır.Refresh esnasında da yalnızca bu kayıtlar materialized viewe aktarılır.
Fast refresh kullanabilmek için materialized view log oluşturmak zorunludur. Kayıtları kontrol altına almaktan kastımız ise materialized view’ı oluşturan tablolara bir değişiklik yapıldığında(DML işlemleri yapıldığında), bu değişikliklerden etkilenen satırlarla ilgili bilgiler materialized view log’a kaydedilir. Bunu sağlayanda materialized view log’la oluşturulan bir “after row”  trigger’ıdır.

Materialized view log’unu eğer master tablo üzerine yaptığımız değişikliklerin hemen materialized view’lar üzerinde güncellenmesini istiyorsak yaratmalıyız. Materialized view log u yaratıldığında adı “MLOG$_tablo_adı” olarak belirlenir. Materialized view log’lar yaratıldığı tablolar üzerinde değişen kayıtları PK veya rowid bazında saklar. Fast refresh yapıldığında ise bu materialized view log’lardan sadece değişen kayıtları bularak bunları materialized view ine taşır.

CREATE MATERIALIZED VIEW LOG ON scott.emp
TABLESPACE users
WITH PRIMARY KEY
INCLUDING NEW VALUES;

Refresh Materialized view

Materialized view refresh mekanizması ON COMMIT ile kurulmuşsa materialized view yapısında değişiklik yapılmadığı sürece otomatik refresh edilir. ON COMMIT yoğun bir işlemdir ve ihtiyaç olduğunda kullanılmalıdır.
Materialized view schedular belirli zaman aralıklarında çalıştırılabileceği gibi mauel olarakda çalıştırılabilir.

BEGIN
   DBMS_REFRESH.make(
     name                 => 'SCOTT.MINUTE_REFRESH',
     list                 => '',
     next_date            => SYSDATE,
     interval             => '/*1:Mins*/ SYSDATE + 1/(60*24)',
     implicit_destroy     => FALSE,
     lax                  => FALSE,
     job                  => 0,
     rollback_seg         => NULL,
     push_deferred_rpc    => TRUE,
     refresh_after_errors => TRUE,
     purge_option         => NULL,
     parallelism          => NULL,
     heap_size            => NULL);
END;
/

BEGIN
   DBMS_REFRESH.add(
     name => 'SCOTT.MINUTE_REFRESH',
     list => 'SCOTT.EMP_MV',
     lax  => TRUE);
END;
/

EXEC DBMS_MVIEW.refresh('EMP_MV');

Query Rewrite

Materialized Viewde en önemli özelliklerden biriside Query Rewrite özelliğidir. Bu özellik ENABLE ve DISABLE olmak üzere 2 opsiyona sahiptir ve özelliği session bazında yada materialized view oluşturulurken belirleyebiliriz. Query Rewrite parametresinin enable yapılması durumunda materialized view’in içindeki sql çalıştırıldığında CBO ( Cost Based Optimizer ) bu sorgunun materialized view’den çalışmasına karar verirse yazılan sorguyu materialized viewi işaret edecek şekilde  değiştirir ve sorguyu yapan farkında olmadan materialized viewi kullanılmış olur.

Eğer bu özellik enable edilmez ise, materialized view’den yararlanmak için sorgu yazılırken materialized view direk işaret edilmelidir, aksi takdirde direk ana tablodan veriler çekilir.

CREATE MATERIALIZED VIEW emp_aggr_mv
BUILD IMMEDIATE
REFRESH FORCE
ON DEMAND
ENABLE QUERY REWRITE
AS
    SELECT department_id,SUM(salary) AS sal_by_dept 
        FROM hr.employees 
    GROUP BY department_id 
    ORDER BY 2 DESC;

EXEC DBMS_STATS.gather_table_stats(USER, 'EMP_AGGR_MV');

NOT : Session bazında Ouery Rewrite özelliğni aşağıdaki gibi enable yapabiliriz.

--ALTER SESSION SET QUERY_REWRITE_INTEGRITY = TRUSTED; 
--ALTER SESSION SET QUERY_REWRITE_ENABLED = TRUE;

ON PREBUILT TABLE

ON PREBUILT TABLE opsiyonu ile bir tabloyu referans alabiliriz tablonun yapısının materialized view’e geçmesini sağlar. Yani bir nevi create table as select (CTAS) cümleciğidir. Tablo ile materialized view ayni isimde ve aynı scmema olmak zorundalar.

CREATE TABLE sales_sum_table
   (month VARCHAR2(8), state VARCHAR2(40), sales NUMBER(10,2));

CREATE MATERIALIZED VIEW sales_sum_table
   ON PREBUILT TABLE WITH REDUCED PRECISION
   ENABLE QUERY REWRITE
   AS SELECT t.calendar_month_desc AS month, 
             c.cust_state_province AS state,
             SUM(s.amount_sold) AS sales
      FROM times t, customers c, sales s
      WHERE s.time_id = t.time_id AND s.cust_id = c.cust_id
      GROUP BY t.calendar_month_desc, c.cust_state_province;

NOT : Materialized view’lar içlerindeki data’lar değiştiğinde invalid hale düşerler. Bu yüzden eğer içlerindeki datalar değişirse compile edilmeleri gerekir..Invalid olan materialized view’ları dba_objects’ten bulabileceğimiz gibi dba_mviews adlı tablodan da bulabiliriz.

select * from dba_objects where  object_type like 'MATERIALIZED VIEW';

select * from dba_mviews where compile_state!=’VALID’;  

https://oracle-base.com/articles/misc/materialized-views
http://www.guvenyilmaz.com/?p=1139
http://www.dba-oracle.com/t_materialized_views_on_prebuilt_table.htm
https://sungur.wordpress.com/2008/07/16/materialized-view/
Yararlı olması Dilegiyle …
Yazar : Mustafa Bektaş Tepe