Oracle – Linux – Network

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