materialized view

5 Sep

materialized view adalah sebuah database object yang berisi hasil dari sebuah query, yang
membedakan dengan view adalah hasil query disimpan secara fisik.

syntax MATERIALIZED VIEW :
CREATE MATERIALIZED VIEW <view_name>
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS (<Sql Query >)

1. menyiapkan kelinci percobaan
SQL> conn scott/tiger
SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> insert into dept values(41,’IT’,’Surabaya’);
SQL> insert into dept values(42,’IT’,’Jakarta’);
SQL> commit;
SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
42 IT             Jakarta
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

2. memberi grant untuk create VIEW dan MATERIALIZED VIEW
SQL> conn system
SQL> grant create any VIEW to scott;
SQL> grant drop any VIEW to scott;
SQL> grant create any MATERIALIZED VIEW to scott;
SQL> grant drop any MATERIALIZED VIEW to scott;

SQL> conn scott/tiger
SQL> create view view_dept as select * from  dept;
SQL> create materialized view mv_dept as select * from scott.dept;

3. Rowid dept & view_dept sama, sedangkan mv_dept tidak sama
SQL> select ROWID from dept;
ROWID
——————
AAAMfKAAEAAAAAQAAA
AAAMfKAAEAAAAAQAAB
AAAMfKAAEAAAAAQAAC
AAAMfKAAEAAAAAQAAD
AAAMfKAAEAAAAANAAA
AAAMfKAAEAAAAANAAB

6 rows selected.

SQL>  select ROWID from view_dept;
ROWID
——————
AAAMfKAAEAAAAAQAAA
AAAMfKAAEAAAAAQAAB
AAAMfKAAEAAAAAQAAC
AAAMfKAAEAAAAAQAAD
AAAMfKAAEAAAAANAAA
AAAMfKAAEAAAAANAAB

6 rows selected.

SQL> select ROWID from mv_dept;
ROWID
——————
AAANDsAAEAAAAG0AAC
AAANDsAAEAAAAG0AAD
AAANDsAAEAAAAG0AAE
AAANDsAAEAAAAG0AAF
AAANDsAAEAAAAG0AAA
AAANDsAAEAAAAG0AAB

6 rows selected.

SQL>

4. apakah MATERIALIZED VIEW, terupdate ?, jika isi table dihapus
SQL> delete dept where deptno = 42;
SQL> commit;
SQL> select * from dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from view_dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from mv_dept;

DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
42 IT             Jakarta

10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

5. agar MATERIALIZED VIEW, terupdate manual.
SQL> execute dbms_mview.refresh( ‘mv_dept’ );
SQL> select * from mv_dept;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

6. contoh syntac MATERIALIZED VIEW lainnya
SQL> CREATE MATERIALIZED VIEW mv_dept2
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS SELECT * from dept;

7. test MATERIALIZED VIEW contoh no 6

SQL> delete dept where deptno = 41;
SQL> select * from mv_dept;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from mv_dept2;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
— mv_dept dan mv_dept2 sama – sama belum terupdate

SQL> commit;
SQL> select * from mv_dept;
DEPTNO DNAME          LOC
———- ————– ————-
41 IT             Surabaya
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

SQL> select * from mv_dept2;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON

— mv_dept belum terupdate, mv_dept2 sudah terupdate sebab telah di-commit

8. contoh syntac MATERIALIZED VIEW lainnya, update dengan interval waktu
SQL> CREATE MATERIALIZED VIEW mv_dept3
BUILD IMMEDIATE
REFRESH FAST START WITH SYSDATE NEXT SYSDATE + (1/72)
AS SELECT * from dept;

9. test contoh no 8
SQL> insert into dept values(43,’IT’,’Semarang’);
SQL> commit;

SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sun 07-Sep-2008 19:03:29
SQL> select to_char(sysdate + 1/72, ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sun 07-Sep-2008 19:23:38

— kurang lebih data akan terupdate setelah 20 menit kemudian

SQL> select to_char(sysdate , ‘Dy DD-Mon-YYYY HH24:MI:SS’) as “Time” from dual;
Time
————————
Sun 07-Sep-2008 19:23:19
SQL> select * from mv_dept3;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
43 IT             Semarang

10. test contoh no 8, dengan memeriksa job kapan diupdate
SQL> insert into dept values(44,’IT’,’MADIUN’);
SQL> COMMIT;

SQL> select * from mv_dept3;
DEPTNO DNAME          LOC
———- ————– ————-
10 ACCOUNTING     NEW YORK
20 RESEARCH       DALLAS
30 SALES          CHICAGO
40 OPERATIONS     BOSTON
43 IT             Semarang

SQL> conn system
Enter password:
SQL> SELECT
SUBSTR(job,1,4) “Job”, SUBSTR(log_user,1,5)          “User”,
SUBSTR(schema_user,1,5)                              “Schema”,
SUBSTR(TO_CHAR(last_date,’Dy DD-Mon-YYYY HH24:MI:SS’),1,30) “Last Date”,
SUBSTR(TO_CHAR(next_date,’Dy DD-Mon-YYYY HH24:MI:SS’),1,30) “Next Date”,
SUBSTR(broken,1,2) “B”, SUBSTR(failures,1,6)        “Failed”,
SUBSTR(what,1,20)                                    “Command”
FROM dba_jobs;

Job  User  Schem Last Date                Next Date                B Failed
—- —– —– ———————— ———————— – ——
Command
——————–
1    SYSMA SYSMA Sun 07-Sep-2008 21:05:54 Sun 07-Sep-2008 21:06:54 N 0
EMD_MAINTENANCE.EXEC
21   MVIEW MVIEW Sun 07-Sep-2008 20:23:02 Sun 07-Sep-2008 21:23:02 N 0
dbms_refresh.refresh
41   SCOTT SCOTT Sun 07-Sep-2008 21:03:09 Sun 07-Sep-2008 21:23:09 N 0
dbms_refresh.refresh

—terlihat data akan terupdate setelah 20 menit kemudian

2 Responses to “materialized view”

  1. andy May 20, 2011 at 1:48 am #

    Saya coba membuat MV spt di bawah ini :

    CREATE MATERIALIZED VIEW ps_id_dept_tbl3
    BUILD IMMEDIATE
    REFRESH FORCE START WITH SYSDATE NEXT SYSDATE + 1 / ((24*60)/5) /*per 5 menit*/
    WITH ROWID
    AS
    SELECT * FROM hprodadm.ps_id_dept_tbl@hcmslink;

    Kemudian saya lihat waktunya :

    select sysdate from dual

    5/20/2011 8:23:46 AM

    Namun ketika saya select dari dba_jobs-nya, muncul 2 dbms_refresh.refresh.
    Dan rentang waktu antara 2 dbms_refresh.refresh itu pun tidak 5 menit, melainkan 1 menit.

    SELECT
    SUBSTR(job,1,4) “Job”,
    SUBSTR(log_user,1,5) “User”,
    SUBSTR(schema_user,1,5) “Schema”,
    SUBSTR(TO_CHAR(last_date,’Dy DD-Mon-YYYY HH24:MI:SS’),1,30) “Last Date”,
    SUBSTR(TO_CHAR(next_date,’Dy DD-Mon-YYYY HH24:MI:SS’),1,30) “Next Date”,
    SUBSTR(broken,1,2) “BROKEN”,
    SUBSTR(failures,1,6) “Failed”,
    SUBSTR(what,1,20) “Command”
    FROM dba_jobs;

    Job User Schema Last Date Next Date BROKEN Failed Command

    1 SYSMA SYSMA Fri 20-May-2011 08:23:16 Fri 20-May-2011 08:24:16 N 0 EMD_MAINTENANCE.EXEC
    21 SYSTE SYSTE Fri 20-May-2011 08:21:11 Fri 20-May-2011 08:26:11 N 0 dbms_refresh.refresh
    22 SKU_I SKU_I Fri 20-May-2011 08:19:55 Fri 20-May-2011 08:24:55 N 0 dbms_refresh.refresh
    24 SKU_I SKU_I Fri 20-May-2011 08:20:21 Fri 20-May-2011 08:25:21 N 0 dbms_refresh.refresh

    Padahal kalau saya coba query berikut ini :

    select SYSDATE from dual
    select SYSDATE + 1 / ((24*60)/5) from dual

    Sudah benar bahwa perbedaannya adalah 5 menit.

    Apakah ada yg salah dg MV saya mas?
    Mohon pencerahannya.

    Trims

    Regards,
    Andy

    • setijoagus May 21, 2011 at 2:10 am #

      kalau lihat mv-nya sich ya nggak ada yang salah
      tetapi kl lihat hasil select jobnya kok agak ngawur, coba di shutdown dulu service oraclenya terus diperiksa ulangdengan perintah select obnya,apakah tetap ?

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: