Oracle创建物化视图 朱雀 2024-03-23 15:43 42阅读 0赞 #### 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 {创建物化视图用的查询语句} 各项关键字说明如下: <table> <thead> <tr> <th>名称</th> <th>用途</th> <th>含义</th> </tr> </thead> <tbody> <tr> <td>materialized</td> <td>物化视图关键字</td> <td>物理化</td> </tr> <tr> <td>build immediate(默认)</td> <td>初始数据方式</td> <td>物化视图首次创建后,就填充数据。</td> </tr> <tr> <td>build deferred</td> <td>初始数据方式</td> <td>首次创建物化视图,不填充数据。</td> </tr> <tr> <td>on fast</td> <td>刷新方式</td> <td>增量更新,只会刷新自上次刷新以后的修改内容。</td> </tr> <tr> <td>on complete</td> <td>刷新方式</td> <td>全部刷新。相当于重新执行一次创建视图的查询语句。</td> </tr> <tr> <td>on force(默认)</td> <td>刷新方式</td> <td>由oracle在需要进行刷新操作时,当可以使用fast模式时,数据刷新将采用fast方式;否则使用complete方式。</td> </tr> <tr> <td>on commit</td> <td>刷新时间(模式)</td> <td>在基表数据事务提交时,立即刷新对应物化视图。</td> </tr> <tr> <td>on demand(默认)</td> <td>刷新时间(模式)</td> <td>(oracle的默认类型)在用户需要刷新的时候进行刷新操作。这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新)</td> </tr> <tr> <td>start with (start_time) next (next_time)</td> <td>刷新时间</td> <td>从指定的时间开始,每隔一段时间(由next指定)就刷新一次</td> </tr> </tbody> </table> ## 物化视图的创建 ## -- 创建一张物化视图表,要求在事务提交后,立刻自动刷新物化视图数据。 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,可查看此视图的相应创建语句。 ![4232557d38d34fbc923ce41b6a6542f8.png][] 点击 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 [4232557d38d34fbc923ce41b6a6542f8.png]: https://image.dandelioncloud.cn/pgy_files/images/2024/03/23/82cb2bbb5b8944c58f0b78777fd682a7.png
还没有评论,来说两句吧...