mynotes/oracle/Oracle-视图.md

物化视图

视图是一个虚拟表(也可以认为是一条语句)，基于它创建时指定的查询语句返回的结果集。每次访问它都会导致这个查询语句被执行一次。为了避免每次访问都执行这个查询，可以将这个查询结果集存储到一个物化视图(也叫实体化视图)。

物化视图与普通的视图相比的区别是物化视图是建立的副本，它类似于一张表，需要占用存储空间。而对一个物化视图查询的执行效率与查询一个表是一样的。

创建

语法：

CREATE MATERIALIZED VIEW view_name
[BUILD IMMEDIATE | BUILD DEFERRED ]
REFRESH [FAST|COMPLETE|FORCE] 
[
ON [COMMIT |DEMAND ] | START WITH (start_time) NEXT (next_time)
]
AS
subquery

BUILD IMMEDIATE 是在创建物化视图的时候就生成数据 BUILD DEFERRED 则在创建时不生成数据，以后根据需要再生成数据。 默认为 BUILD IMMEDIATE。

刷新(REFRESH)：指当基表发生了 DML 操作后，物化视图何时采用哪种方式和基表进行同步。 REFRESH 后跟着指定的刷新方法有三种：

• FAST 刷新采用增量刷新，只刷新自上次刷新以后进行的修改。
• COMPLETE 刷新对整个物化视图进行完全的刷新。
• FORCE 方式 刷新时会去判断是否可以进行快速刷新，如果可以则采用FAST方式，否则采用COMPLETE的方式。FORCE 是默认的方式。

刷新的模式有两种：

• ON DEMAND 指需要手动刷新物化视图(默认)。
• ON COMMIT 指在基表发生COMMIT操作时自动刷新。

-- 创建此物化视图后，当 T_ADDRESS 表发生变化时，MV_ADDRESS2 自动跟着改变。
create materialized view mv_address2 
refresh 
on commit
as
select ad.id,ad.name adname,ar.name ar_name 
from t_address ad,t_area ar 
where ad.areaid=ar.id;


CREATE MATERIALIZED VIEW MV_MOST_RETAIL_ORDERED_USER
REFRESH FORCE
ON DEMAND START WITH SYSDATE NEXT TRUNC(SYSDATE+1)+3/24
AS
SELECT
/******************************************************************************
  * 物化视图，每天凌晨3点执行，提供给CIS dblink 查询
  * 目的：统计门店最近90天零售下单最多的账号
  * 日期：2023-02-09
******************************************************************************/
  T.STORE_CODE, T.ACCOUNT, T.QTY, TRUNC(SYSDATE) AS CREATED_DATE
FROM (
       SELECT ROW_NUMBER() OVER(PARTITION BY SO.STORE_CODE ORDER BY COUNT(1) DESC,TO_NUMBER(U.SSOUSERNAME) ASC) RN,
           SO.STORE_CODE, U.SSOUSERNAME AS ACCOUNT, COUNT(1) AS QTY
       FROM T_SO_SALES_ORDER SO
              LEFT JOIN T_BI_USER U ON U.USERNAME=SO.CREATOR
       WHERE SO.DOCUMENT_TYPE='retail' AND SO.CREATE_DATE>TRUNC(SYSDATE)-90
         AND SO.STATUS NOT IN ('draft', 'freeze')
         AND SO.CREATOR IS NOT NULL AND SO.CREATOR NOT IN ('NDMS_SYSTEM')
         AND SO.DATA_SOURCE IS NOT NULL AND SO.DATA_SOURCE NOT IN ('job', 'B2B')
       GROUP BY U.SSOUSERNAME, SO.STORE_CODE
     ) T
WHERE T.RN=1;

删除

--删除物化视图
DROP MATERIALIZED VIEW MV_VIEW_NAME;