1. 物化視圖由于是物理真實(shí)存在的，故可以創(chuàng)建索引。




--為基表創(chuàng)建MLOG
--創(chuàng)建物化視圖時(shí)應(yīng)先創(chuàng)建存儲(chǔ)的日志空間，否則建MV時(shí)報(bào)錯(cuò)
--ORA-23413: table "SCOTT"."EMP" does not have a materialized view log
create materialized view log on scott.emp
tablespace test
/


--創(chuàng)建物化視圖
create materialized view test_mv
tablespace test
parallel (degree 4)
build immediate refresh fast
enable query rewrite
as
select * from scott.emp
/


--查看一下結(jié)果，果然很符合物化視圖的定義，一個(gè)表+一個(gè)視圖
SQL> select object_name, object_type from user_objects where object_name = 'TEST_MV';
OBJECT_NAME??? OBJECT_TYPE
-----------??? --------
TEST_MV??? ??? TABLE
TEST_MV??? ??? MATERIALIZED VIEW

SQL> select mview_name, container_name from user_mviews;
MVIEW_NAME?????? CONTAINER_NAME
---------------- ------------------------------
TEST_MV????????? TEST_MV? （這就是那個(gè)存儲(chǔ)表）

--查看MLOG的情況
--注意：MLOG的所屬和MV的所屬并不是同一個(gè)
SQL> select log_owner, master, log_table from dba_mview_logs
LOG_OWNER??? MASTER??? LOG_TABLE
---------------------------------
SCOTT??? ??? EMP??? MLOG$_EMP （MLOG其實(shí)也就是一個(gè)表）

SQL> desc scott.mlog$_emp;
Name??? ??? ??? Type
-------------------------------
EMPNO??? ??? ??? NUMBER(4)
SNAPTIME$$??? ??? DATE
DMLTYPE$$??? ??? VARCHAR2(1)
OLD_NEW$$??? ??? VARCHAR2(1)
CHANGE_VECTOR$$??? ??? RAW(255)



--刪除MLOG
drop materialized view log on 物化視圖所依賴的表名;?
--刪除物化視圖
drop materialized view 物化視圖名;