Materialized View
Bugüne kadar yazdığım makaleleri inceledim ve materialized view'larla ilgili bir şey yazmadığımı fark ettim. Yine eminim ki internet üzerinde materialized view'larla ilgili aramalar oldukça fazla yapılmakta.
Materialized view da diğer bir veritabanı objesi olan view gibidir ve objedir. View'lar bir pointer'dır ve birlikte yaratıldığı sorguya gelen SELECT ifadesini ya da kimi zaman DELETE ifadesini yönlendirir (DELETE'in koşulları var elbet). Materialized view'lar da bir pointer'dır ama view'lar gibi bizim tanımladığımız sorguya point etmezler. Onların yönlendiği yer bizim verdiğimiz sorgudan oluşan bir alandır. Aslında başka bir obje olan tablolar gibi düşünebilirsiniz. View'lar sadece data dictionary'de yer kaplarken, materialized view'lar hem tanım olarak data dictionary'de yer kaplar hem de fiziksel olarak. Bir örnek vermem gerekirse;
[oracle@db112 datapump]$ sqlplus scott/tiger@orcl
SQL*Plus: Release 11.2.0.1.0 Production on Wed Sep 7 13:45:50 2011
Copyright (c) 1982, 2009, Oracle. All rights reserved.
Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
With the Partitioning, OLAP and Data Mining options
SQL> create table emp_new as select * from emp;
Table created.
SQL> insert into emp_new select * from emp_new;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp_new;
COUNT(*)
----------
28
SQL> set serveroutput on;
SQL> declare
2 no_rows NUMBER;
3 mv_size NUMBER;
4 begin
5 DBMS_MVIEW.ESTIMATE_MVIEW_SIZE('MV_OGAN',
6 'select * from scott.emp_new', no_rows, mv_size);
7 dbms_output.put_line('Satir Sayisi : ' || no_rows);
8 dbms_output.put_line('MV Boyutu : ' || mv_size);
9 end;
10 /
Satir Sayisi : 28
MV Boyutu : 3808
PL/SQL procedure successfully completed.
Yukarıdaki gibi bir materialized view yaratsaydım satır sayısı 28, boyutu ise 3808 olacaktı (bu değer byte'tır). DBMS_MVIEW.ESTIMATE_MVIEW_SIZE prosedürünün sentaksı ise;
DBMS_MVIEW.ESTIMATE_MVIEW_SIZE (
statement_id IN VARCHAR2,
select_clause IN VARCHAR2,
num_rows OUT NUMBER,
num_size OUT NUMBER);
Daha önce yarattığımız bir tabloyu da materialized view'ın içeriği olarak yaratabiliriz.
SQL> CREATE MATERIALIZED VIEW EMP_NEW
2 ON PREBUILT TABLE
3 REFRESH FORCE
4 ENABLE QUERY REWRITE
5 AS
6 SELECT * FROM SCOTT.EMP;
Materialized view created.
SQL> select object_name, object_id, object_type
2 from user_objects
3 where object_name = 'EMP_NEW';
OBJECT_NAME OBJECT_ID OBJECT_TYPE
EMP_NEW 78479 TABLE
EMP_NEW 78480 MATERIALIZED VIEW
Bir materialized view yukarıdaki şekilde yaratılıyor ama benim fazladan yaptığım iş bunu bir öncül tablodan yaratmak oldu. Materialized view prebuilt bir tablodan yalnızca yapısını alıyor, bir örnek;
SQL> drop table emp_new purge;
Table dropped.
SQL> create table emp_new
2 as
3 select * from emp;
Table created.
SQL> insert into emp_new
2 select * from emp_new;
14 rows created.
SQL> /
28 rows created.
SQL> /
56 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp_new;
COUNT(*)
------------
112
SQL> select coun(*) from emp;
COUNT(*)
------------
14
SQL> CREATE MATERIALIZED VIEW EMP_NEW
2 on prebuilt table
3 refresh force
4 enable query rewrite
5 as
6 select * from emp;
Materialized view created.
SQL> select count(*) from emp_new;
COUNT(*)
------------
112
SQL> drop table emp_new;
drop table emp_new
*
ERROR at line 1:
ORA-12083: must use DROP MATERIALIZED VIEW to drop "SCOTT"."EMP_NEW"
SQL> drop materialized view emp_new;
Materialized view dropped.
SQL> select count(*) from emp_new;
COUNT(*)
------------
112
SQL> drop table emp_new purge;
Table dropped.
Bir materialized view yaratabilmek için view ile aynı şartlarda olmalıyız. Yani elimizde sadece bir SELECT cümlesi olması yetiyor (CREATE MATERIALIZED VIEW hakkınızın da olması gerekiyor tabiiki).
SQL> CREATE MATERIALIZED VIEW OGAN_MV
2 REFRESH FORCE
3 ENABLE QUERY REWRITE
4 AS
5 SELECT * FROM EMP;
Materialized view created.
Şimdiye kadar size iki tip materialized view gösterdim. Bir tanesi prebuilt table üzerinde yarattığımız, diğer ise sanki bir view yaratırmış gibi yaratıp, yalnızca bir sorgu gönderdiğimiz materialized view'dı. Peki bu ikisi arasında nasıl bir fark var? Prebuilt olduğu zaman ne oluyor, olmadığı zaman ne oluyor? Sıradaki örnekte bunu göreceğiz, ardından çıktının ne olduğunu açıklayacağım.
SQL> create table emp_new
2 as
3 select * from emp;
Table created.
SQL> insert into emp_new
2 select * from emp_new;
14 rows created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
----------
14
SQL> select count(*) from emp_new;
COUNT(*)
----------
28
SQL> create materialized view emp_new
2 on prebuilt table
3 refresh force
4 enable query rewrite
5 as
6 select * from emp;
Materialized view created.
SQL> create materialized view ogan_mv
2 refresh force
3 enable query rewrite
4 as
5 select * from emp;
Materialized view created.
SQL> select substr(mview_name,1,30) mviewname, staleness
2 from user_mviews
3 where mview_name in ('emp_new','ogan_mv');
no rows selected
SQL> select substr(mview_name,1,30) mviewname, staleness
2 from user_mviews
3 where mview_name in ('EMP_NEW','OGAN_MV');
MVIEWNAME STALENESS
------------------------------ -------------------
EMP_NEW UNKNOWN
OGAN_MV FRESH
EMP_NEW bir tabloyu referans aldığı için materialized view içeriğinin garantisini Oracle veremedi ve staleness alanına UNKNOWN (bilinmeyen) yazdı. Halbuki ogan_mv objesini direkt olarak tablodan ve o anki haliyle oluşturduğumuz için FRESH (taze) olarak gösteriyor.
Materialized view'ların REFRESH özelliğini bu örneklerde FORCE olarak gösterdim. FORCE olduğu durumda gördüğümüzü gösteriyorum;
SQL> insert into emp(empno)
2 values(9999);
1 row created.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
------------
15
SQL> select count(*) from ogan_mv;
COUNT(*)
------------
14
Burada gördüğünüz gibi "select * from emp" olarak yarattığımız ogan_mv hala eski haliyle duruyor.
SQL> exec dbms_mview.refresh(list=> 'OGAN_MV');
PL/SQL procedure successfully completed.
SQL> select count(*) from ogan_mv;
COUNT(*)
------------
15
DBMS_MVIEW paketindeki REFRESH prosedürü ile materialized view'a son halini veriyoruz. Peki başka ne tür refresh özelliği var? Bir diğeri de FAST refresh özelliğidir ve materialized view'ların çok daha hızlı refresh olmasını sağlar. REFRESH FORCE olarak yaratırsak, bir materialized view'ı yeniden oluştururken içerisindeki sorgunun tamamı üzerinden oluşturur. REFRESH FAST dersek, bir materialized view LOG üzerindeki değişiklikleri takip ederek materialized view'ı refresh eder (yeniden günceller). Bir örnek;
SQL> create materialized view log on emp;
Materialized view log created.
SQL> create materialized view ogan_mv_emp
2 refresh fast
3 enable query rewrite
4 as
5 select * from emp;
Materialized view created.
SQL> select count(*) from ogan_mv_emp;
COUNT(*)
------------
15
SQL> insert into emp(empno)
2 values(1919);
1 row inserted.
SQL> commit;
Commit complete.
SQL> select count(*) from emp;
COUNT(*)
------------
16
SQL> select count(*) from ogan_mv_emp;
COUNT(*)
------------
15
SQL> EXEC DBMS_MVIEW.REFRESH('OGAN_MV_EMP');
PL/SQL procedure successfully completed.
SQL> select count(*) from ogan_mv_emp;
COUNT(*)
------------
16
Buradaki refresh işlemini materialized view log sayesinde tamamladık ama örneğimiz çok ufak ve değişikliğimiz bir tane olduğu için farkı gözlemlemek biraz zor oldu. Milyonlarca kayıt ve değişiklik içerisinde FORCE ve FAST'ın, materialized view LOG'un farkı da ortaya çıkıyor.
Bu durumda materialized view'ların yeniden oluşturulması gerektiğini anlıyoruz. View'larda da ana tabloya yeni bir sütun eklediğinde yeniden yaratmamız gerekiyordu çünkü biz biliyoruz ki her ne kadar bir view'ı SELECT * FROM X ile yaratsakta data dictionary içerisinde bir view *'ın açılmış halini, yani bütün sütunlarını saklar. Bu da ana tabloya yeni bir sütun eklendiği zaman view'ı güncelleme ihtiyacını doğurur. Materialized view'lar da durum aynı. Bir örnekle devam edelim;
SQL> select substr(mview_name,1,30) mviewname, staleness, compile_state
2 from user_mviews
3 where mview_name in ('OGAN_MV_EMP','OGAN_MV','EMP_NEW');
MVIEWNAME STALENESS COMPILE_STATE
------------------------------------------------------------------------------
OGAN_MV STALE VALID
OGAN_MV_EMP STALE VALID
EMP_NEW STALE VALID
Dikkat ederseniz hepsi uygun ve güncel durumda (prebuilt MV olan EMP_NEW bile!!). Şimdi ise sırada bir güncelleme var...
SQL> alter session set nls_date_format='DD/MM/YYYY HH24:MI:SS';
Session altered.
SQL> drop table emp;
Table dropped.
SQL> select substr(mview_name,1,30) mviewname, staleness, compile_state
2 from user_mviews;
MVIEWNAME STALENESS COMPILE_STATE
------------------------------------------------------------------------------
OGAN_MV NEEDS_COMPILE NEEDS_COMPILE
OGAN_MV_EMP NEEDS_COMPILE NEEDS_COMPILE
EMP_NEW NEEDS_COMPILE NEEDS_COMPILE
Materialized view'ın altında bulunan emp tablosunun drop edildiğini Oracle fark etti ve önceden tanımladığımız 3 materialized view da geçersiz konumuna düştü. Hatırlayınız -ki OCA veya OCP sınavında gelen bir sorudur- bir view'ın altında yatan obje drop edildiği zaman view da drop edilmiş olmaz, invalid konuma geçer. Bu durumda view'lar için yeniden yaratmak, materialized view'lar içinse compile etmek gerekmektedir.
SQL> alter materialized view OGAN_MV COMPILE;
Materialized view altered.
SQL> alter materialized view EMP_NEW COMPILE;
Materialized view altered.
SQL> alter materialized view OGAN_MV_EMP COMPILE;
Materialized view altered.
SQL> select substr(mview_name,1,30) mviewname, staleness, compile_state
2 from user_mviews;
MVIEWNAME STALENESS COMPILE_STATE
----------------------------------------------------------------------------------------------
OGAN_MV COMPILATION_ERROR COMPILATION_ERROR
OGAN_MV_EMP COMPILATION_ERROR COMPILATION_ERROR
EMP_NEW COMPILATION_ERROR COMPILATION_ERROR
Sizce neden compilation_error yazısını görüyoruz? Çünkü ortalarda bir EMP tablosu yok! :) Hemen geri alalım (bu arada veritabanın recyclebin ve flashback_on YES durumda).
SQL> flashback table emp to before drop;
Flashback complete.
Şu anda materialized view'larımız kullanılamaz durumda.
SQL> alter materialized view OGAN_MV COMPILE;
Materialized view altered.
SQL> alter materialized view EMP_NEW COMPILE;
Materialized view altered.
SQL> alter materialized view OGAN_MV_EMP COMPILE;
Materialized view altered.
SQL> select substr(mview_name,1,30) mviewname, staleness, compile_state
2 from user_mviews;
MVIEWNAME STALENESS COMPILE_STATE
----------------------------------------------------------------------------------------------
OGAN_MV UNUSABLE VALID
OGAN_MV_EMP UNUSABLE VALID
EMP_NEW UNUSABLE VALID
Tabloyu çöp kutusundan geri getirdiğimiz için staleness kısmı kullanılamaz hale geldi. Her ne kadar compile_state kısmı valid olsada. Bu durumda materialized view'ı refresh etmemiz gerekiyor.
SQL> exec dbms_mview.refresh('OGAN_MV');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('EMP_NEW');
PL/SQL procedure successfully completed.
SQL> exec dbms_mview.refresh('OGAN_MV_EMP');
BEGIN dbms_mview.refresh('OGAN_MV_EMP'); END;
*
ERROR at line 1:
ORA-23413: table "SCOTT"."EMP" does not have a materialized view log
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
Tabloyu drop ettiğimiz zaman üzerindeki materialized view log da düşürülmüş oldu ve refresh, bu MV için başarısız oldu. Şimdi bir materialized view log yaratmak istersek neler olacak bakalım?
SQL> create materialized view log on emp;
Materialized view log created.
SQL> exec dbms_mview.refresh('OGAN_MV_EMP');
BEGIN dbms_mview.refresh('OGAN_MV_EMP'); END;
*
ERROR at line 1:
ORA-12034: materialized view log on "SCOTT"."EMP" younger than last refresh
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2558
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2771
ORA-06512: at "SYS.DBMS_SNAPSHOT", line 2740
ORA-06512: at line 1
Olmadı :) Materialized view'ı baştan yaratmak gerekiyor. Son duruma bakalım mı?
SQL> select substr(mview_name,1,30) mviewname, staleness, compile_state
2 from user_mviews;
MVIEWNAME STALENESS COMPILE_STATE
----------------------------------------------------------------------------------------------
OGAN_MV FRESH VALID
OGAN_MV_EMP UNUSABLE VALID
EMP_NEW FRESH VALID
Datawarehousing (Veriambarı) konusu içerisinde yer alan materialized view'ların nasıl oluşturulduğunu ve kullanıldığını görmüş olduk.
İyi çalışmalar dilerim.
Ogan
Hiç yorum yok:
Yorum Gönder