Oracle创建物化视图

朱雀 2024-03-23 15:43 230阅读 0赞

Oracle创建物化视图

  • 物化视图的语法
  • 物化视图的创建
    • 关于手动刷新
    • 创建手动刷新的物化视图
  • 查看物化视图
  • 删除物化视图

物化视图的语法

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

  1. create materialized view [view_name]
  2. [ build immediate | build deferred ]
  3. [ refresh fast | refresh complete| refresh force]
  4. [
  5. on commit | on demand 
  6. start with (start_time) next (next_time)
  7. ]
  8. as
  9. {创建物化视图用的查询语句}

各项关键字说明如下：
























































名称 用途 含义
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指定）就刷新一次

物化视图的创建

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

关于手动刷新

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

  1. next trunc(sysdate, 'dd') + 1 + 1/24 ;  -- 每天1点刷新 
  2. next trunc(sysdate, 'dd') + 1 + 3/24 ;  -- 每天3点刷新 
  3. -- 说明 trunc(sysdate,'dd') 表示取今天的日期。后面加1就是明天。再加N/24就是把时间确定到明天的N点 
  5. -- 物化视图在每天01:10进行刷新 
  6. next to_date(concat(to_char(sysdate + 1 , 'yyyymmdd'), '01:10:00'), 'yyyymmdd hh24:mi:ss') 
  8.  -- 隔一小时刷新一次 
  9. next trunc(sysdate, 'hh24') + 1/24  
  10. next trunc(sysdate, 'mi') + 1/24  
  12. -- 从明天一点开始刷新一次，之后都是每天一点刷新
  13. start with to_date('22-04-2023 01:00:00','dd-mm-yyyy hh24:mi:ss') next trunc(sysdate,'dd') + 1 + 1/24
  15. -- 从今天12:32开始刷新一次，往后都是每小时的32分开始刷新
  16. start with to_date('21-04-2023 12:32:00','dd-mm-yyyy hh24:mi:ss') next trunc(sysdate,'mi') + 1/24

创建手动刷新的物化视图

  1. -- 每天一点刷新
  2. create materialized view v_user_day  
  3. build immediate 
  4. refresh force 
  5. on demand 
  6. start with sysdate next trunc(sysdate,'dd') + 1 + 1/24
  7. as 
  8. select * from user_list
  10. -- 每小时刷新一次
  11. create materialized view v_user_hour  
  12. build immediate 
  13. refresh force 
  14. on demand 
  15. start with sysdate next trunc(sysdate,'mi') + 1/24
  16. as 
  17. select * from user_list

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

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

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

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

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

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

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

查看物化视图

  1. -- 查看物化视图的基本信息 
  2. SELECT * FROM ALL_MVIEWS
  4. SELECT * FROM DBA_MVIEWS 
  6. -- 查看物化视图的统计信息 显示物化视图中每一列的记录数、分布数据和平均数据等信息。
  7. SELECT * FROM USER_MVIEW_ANALYSIS

删除物化视图

  1. --删除物化视图日志
  2. drop materialized view log on test_table;
  4. --删除物化视图  
  5. drop materialized view V_USER_HOUR

发表评论

表情：
评论列表 （有 0 条评论，230人围观）

还没有评论，来说两句吧...

相关阅读

    相关 Oracle物化视图

    概述： > 物化视图是一种特殊的物理表，是将SQL中的查询结果提前抽取出来，存储在硬盘上，相当于再查的时候只查询了一张表，大大提高了读取效率 物化视图的类型： O

    矫情吗；*矫情吗；*/ 0 赞/ 207 阅读

    相关 Oracle-物化视图

    Oracle之物化视图 物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图的查询，Orac

    ゞ 浴缸里的玫瑰ゞ 浴缸里的玫瑰/ 0 赞/ 417 阅读

    相关 Oracle 物化视图

      1     简介 物化视图是包括一个查询结果的数据库对像，它是远程数据的的本地副本，或者用来生成基于数据表求和的汇总表。物化视图存储基于远程表的数据，也可以称为快照

    迈不过友情╰迈不过友情╰/ 0 赞/ 370 阅读

    相关 Oracle 物化视图

    一、[物化视图][Link 1]的一般用法物化视图是一种特殊的物理表，“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表，应用的局限性大，任何对视图

    妖狐艹你老母妖狐艹你老母/ 0 赞/ 2948 阅读

    相关 oracle 物化视图

    我们如果遇到需要从其它系统的数据库中取数据进行统计分析的问题，可疑选择使用[ORACLE][]的ODI工具进行抽数，但是对方提供的数据库用户下没有任何对象，只是有查询所有表的权

    小灰灰小灰灰/ 0 赞/ 517 阅读