Oracle Materialized View

https://dotblogs.com.tw/keep_walking_claire/2017/07/07/materialized_view

--Create Materialized View (不會自動更新資料)

CREATE MATERIALIZED VIEW Material_View_Name

[TABLESPACE TBS_XXX]

REFRESH [ON DEMAND (預設)/ COMMIT] 

        [COMPLETE / FORCE (預設) / FAST] 

        [WITH Primary Key (預設)/ ROWID / ROWID / OBJECT ID]

[START WITH SYSDATE NEXT (SYSDATE+1)]

AS

SELECT SQL;

-- Create Materialized View (Table Commit 就會更新資料)

CREATE MATERIALIZED VIEW Material_View_Name

REFRESH [COMPLETE / FORCE (預設) / FAST]

ON COMMIT

[WITH ROWID / WITH PRIMARY KEY (預設)]

AS

SELECT SQL;

--Create Materialized View (定期自動更新資料)

CREATE MATERIALIZED VIEW Material_View_Name

REFRESH [COMPLETE / FORCE (預設) / FAST]

START WITH SYSDATE+0 

NEXT TRUNC(SYSDATE)+1

[WITH ROWID / WITH PRIMARY KEY (預設)]

AS

SELECT SQL;

--查詢 Materialized View 狀態

SELECT MVIEW_NAME

     , REFRESH_METHOD

     , LAST_REFRESH_TYPE

     , LAST_REFRESH_DATE

     , QUERY

  FROM USER_MVIEWS WHERE mview_name='MVIEW_NAME';

--手動Refresh

BEGIN

  DBMS_MVIEW.REFRESH( 'MVIEW_NAME' );

END;

--重新編譯

ALTER MATERIALIZED VIEW MVIEW_NAME COMPILE;