Oracle创建物化视图

物化视图的语法

物化视图的创建语法，如下所示：

create materialized view [view_name]
[ build immediate | build deferred ]
[ refresh fast | refresh complete| refresh force]
[
on commit | on demand 
start with (start_time) next (next_time)
]
as
{创建物化视图用的查询语句}

各项关键字说明如下：

名称	用途	含义
materialized	物化视图关键字	物理化
build immediate（默认）	初始数据方式	物化视图首次创建后，就填充数据。
build deferred	初始数据方式	首次创建物化视图，不填充数据。
on fast	刷新方式	增量更新，只会刷新自上次刷新以后的修改内容。
on complete	刷新方式	全部刷新。相当于重新执行一次创建视图的查询语句。
on force（默认）	刷新方式	由oracle在需要进行刷新操作时，当可以使用fast模式时，数据刷新将采用fast方式；否则使用complete方式。
on commit	刷新时间（模式）	在基表数据事务提交时，立即刷新对应物化视图。
on demand（默认）	刷新时间（模式）	(oracle的默认类型)在用户需要刷新的时候进行刷新操作。这里就要求用户自己动手去刷新数据了（也可以使用job定时刷新）
start with (start_time) next (next_time)	刷新时间	从指定的时间开始，每隔一段时间（由next指定）就刷新一次

物化视图的创建

-- 创建一张物化视图表，要求在事务提交后，立刻自动刷新物化视图数据。
create MATERIALIZED VIEW v_user_list 
refresh force -- 由oracle决定该刷新的时候，采取何种方式执行
ON COMMIT  -- 刷新模式，触发点
AS 
SELECT * FROM users 

关于手动刷新

trunc(sysdate,‘dd’) 表示取现在的系统时间，精确到天
trunc(sysdate,‘hh24’) 表示取现在的系统时间，精确到小时
trunc(sysdate,‘mi’) 表示取现在的系统时间，精确到分钟

next trunc(sysdate, 'dd') + 1 + 1/24 ;  -- 每天1点刷新 
next trunc(sysdate, 'dd') + 1 + 3/24 ;  -- 每天3点刷新 
-- 说明 trunc(sysdate,'dd') 表示取今天的日期。后面加1就是明天。再加N/24就是把时间确定到明天的N点 

-- 物化视图在每天01:10进行刷新 
next to_date(concat(to_char(sysdate + 1 , 'yyyymmdd'), '01:10:00'), 'yyyymmdd hh24:mi:ss') 

 -- 隔一小时刷新一次 
next trunc(sysdate, 'hh24') + 1/24  
next trunc(sysdate, 'mi') + 1/24  

-- 从明天一点开始刷新一次，之后都是每天一点刷新
start with to_date('22-04-2023 01:00:00','dd-mm-yyyy hh24:mi:ss') next trunc(sysdate,'dd') + 1 + 1/24

-- 从今天12:32开始刷新一次，往后都是每小时的32分开始刷新
start with to_date('21-04-2023 12:32:00','dd-mm-yyyy hh24:mi:ss') next trunc(sysdate,'mi') + 1/24

创建手动刷新的物化视图

-- 每天一点刷新
create materialized view v_user_day  
build immediate 
refresh force 
on demand 
start with sysdate next trunc(sysdate,'dd') + 1 + 1/24
as 
select * from user_list

-- 每小时刷新一次
create materialized view v_user_hour  
build immediate 
refresh force 
on demand 
start with sysdate next trunc(sysdate,'mi') + 1/24
as 
select * from user_list

物化视图创建后，可以进这里查看。进入目录后，选中对应视图，然后右键选择View，可查看此视图的相应创建语句。

点击 V_USER_DAY，查看创建语句，发现语句变成如下。
build immediate 是默认的，所以不显示。刷新方式没变。原创建语句规定为每天一点刷新。
今天是4月21号，创建视图时已经刷新了一次，所以下一次刷新从明天一点开始，往后都是每天一点刷新。

create materialized view V_USER_DAY  
refresh force on demand 
start with to_date('22-04-2023 01:00:00','dd-mm-yyyy hh24:mi:ss') next trunc(sysdate,'dd') + 1 + 1/24
as 
select * from user_list

点击 V_USER_HOUR，查看创建语句，语句如下。
build immediate 同样不显示。执行创建语句时是11:32分，所以下一次刷新从12:32开始，往后都是每小时的32分刷新。

create materialized view V_USER_HOUR
refresh force on demand 
start with to_date('21-04-2023 12:32:00','dd-mm-yyyy hh24:mi:ss') next trunc(sysdate,'mi') + 1/24
as 
select * from user_list

在不同的时间，进去查看物化视图SQL语句的时候，start with 后面连带的时间也会发生变化。证明视图到了固定的时间就会自动刷新一次

创建物化视图后，还可以进入上图的Table目录下查看，当你点击View后，会发现它就是一个普通表。而当你点击Drop时，会报错，告诉你必须用drop materialized view 来删除。显然物化视图会有表的结构，会占据磁盘空间。但它不是一个真正的表。

查看物化视图

-- 查看物化视图的基本信息 
SELECT * FROM ALL_MVIEWS

SELECT * FROM DBA_MVIEWS 

-- 查看物化视图的统计信息 显示物化视图中每一列的记录数、分布数据和平均数据等信息。
SELECT * FROM USER_MVIEW_ANALYSIS 

删除物化视图

 --删除物化视图日志
drop materialized view log on test_table;

--删除物化视图  
drop materialized view V_USER_HOUR