物化视图(Oracle与PostgreSQL对比)

本文涉及的产品
云原生数据库 PolarDB MySQL 版,Serverless 5000PCU 100GB
简介: 物化视图(Oracle与PostgreSQL对比)

一、什么是物化视图

物化视图是一种特殊的物理表,“物化”(Materialized)视图是相对普通视图而言的。普通视图是虚拟表,应用的局限性大,任何对普通视图的查询,数据库都实际上转换为视图SQL语句的查询,这样对整体查询性能的提高,并没有实质上的好处。


二、与普通视图对比

类型\对比项

物理结构

时效性

有无索引

视图

虚拟表

实时

无索引

物化视图

物理表

非实时

可以创建索引

三、物化视图作用

用于预先计算,并保存表连接或表聚集等耗时较多操作的结果。以便于在执行查询时,避免在基表上进行这些耗时的操作(基表上进行耗时操作是不是会影响其他用户使用这个基表的效率,毕竟基表查询肯定很频繁?或者单纯从Oracle性能角度出发,希望利用物化视图减少基表非常耗时的操作,从而提升数据库性能?),从而快速得到结果。

四、物化视图SQL脚本

4.1 Oracle

4.1.1 创建

-- 创建视图
create materialized view [view_name]
refresh [fast|complete|force]
[
on [commit|demand]  |
start with (start_time) next (next_time)
]
as
{创建物化视图时用的查询语句}
-- 创建索引
create unique index index_name on view_name (列名1,列名2...);

4.1.2 修改

-- 修改视图
alter materialized view [view_name] 
refresh [fast|complete|force]
[ 
on [commit|demand] | 
start with (start_time) next (next_time) 
]
-- 修改索引
alter materialized view mv_name add constraint

4.1.3 删除

drop materialized view [if exists] view_name;

4.1.4 查询

select * from mv_name;

4.2 PostgreSQL

4.2.1 创建

-- 创建视图
CREATE MATERIALIZED VIEW [ IF NOT EXISTS ] table_name
    [ (column_name [, ...] ) ]
    [ USING method ]
    [ WITH ( storage_parameter [= value] [, ... ] ) ]
    [ TABLESPACE tablespace_name ]
    AS query
    [ WITH [ NO ] DATA ]
-- 创建索引
create unique index index_name on view_name (列名1,列名2...);

4.2.2 修改

-- 修改视图
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    action [, ... ]
ALTER MATERIALIZED VIEW name
    [ NO ] DEPENDS ON EXTENSION extension_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    RENAME [ COLUMN ] column_name TO new_column_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    RENAME TO new_name
ALTER MATERIALIZED VIEW [ IF EXISTS ] name
    SET SCHEMA new_schema
ALTER MATERIALIZED VIEW ALL IN TABLESPACE name [ OWNED BY role_name [, ... ] ]
    SET TABLESPACE new_tablespace [ NOWAIT ]
where action is one of:
    ALTER [ COLUMN ] column_name SET STATISTICS integer
    ALTER [ COLUMN ] column_name SET ( attribute_option = value [, ... ] )
    ALTER [ COLUMN ] column_name RESET ( attribute_option [, ... ] )
    ALTER [ COLUMN ] column_name SET STORAGE { PLAIN | EXTERNAL | EXTENDED | MAIN }
    ALTER [ COLUMN ] column_name SET COMPRESSION compression_method
    CLUSTER ON index_name
    SET WITHOUT CLUSTER
    SET ACCESS METHOD new_access_method
    SET TABLESPACE new_tablespace
    SET ( storage_parameter [= value] [, ... ] )
    RESET ( storage_parameter [, ... ] )
    OWNER TO { new_owner | CURRENT_ROLE | CURRENT_USER | SESSION_USER }-- 

4.2.3 删除

DROP MATERIALIZED VIEW [ IF EXISTS ] name [, ...] [ CASCADE | RESTRICT ];

4.2.4 查询

select * from mv_name;

五、物化视图刷新

5.1 Oracle

语法:DBMS_SNAPSHOT.REFRESH( 'v_materialized_foo_tbl','f');

(默认刷新方式是force on demand)


5.1.1 刷新模式

  1. fast:一种增量刷新,在上次刷新的基础上,对此次在基表做的DML修改进行刷新。只刷新上次刷新以后做的修改;
  2. complete:完全刷新。是对整个物化视图进行的刷新;
  3. force:Oracle在刷新时会判断是否可以采用fast刷新,如果不可以会采用complete刷新(肯定会刷新。若上次刷新之后的操作丢失,无法做fast刷新,于是做全表刷新)
  4. never:不会使用刷新机制刷新

5.1.2 刷新时机

  1. on demand在用户需要刷新的时候刷新,这里就要求用户自己动手去刷新数据了(也可以使用job定时刷新);
  2. on commit当主表中有数据提交的时候,立即刷新MV中的数据;
  3. start … next 从指定的时间开始,每隔一段时间(由next指定)就刷新一次;


5.2 PostgreSQL

语法:REFRESH MATERIALIZED VIEW [ CONCURRENTLY ] name [ WITH [ NO ] DATA ]

(默认刷新方式是with data)

5.2.1 刷新模式

1.全量更新: 直接去基础表里面查询数据,刷新过程中会对该物化视图的所有的select操作阻塞,但刷新效率快。

2.增量更新(concurrently):刷新要具备有唯一字段索引,将基础表查询出来的数据和现在物化视图表进行对比,填充差量数据,但刷新过程中不会对该物化视图的select操作进行阻塞,但刷新比全量更新慢。


5.2.2 刷新设置

  1. with data创建物化视图后,视图可查询数据;
  2. with no data:创建物化视图后,如果无数据则视图不可查询;


六、注意事项

  1. 不要处理运算结果,因为可能有其它功能需要做聚合运算;
  2. 需要保存原SQL,如果数据库格式化SQL,会导致很难读懂和修改;


[参考文献]:

  1. Basic Materialized Views
  2. CREATE MATERIALIZED VIEW
  3. 物化视图 materialized view
  4. PostgreSQL视图和物化视图
  5. PostgreSQL视图和物化视图
  6. PG 物化视图
相关实践学习
使用PolarDB和ECS搭建门户网站
本场景主要介绍基于PolarDB和ECS实现搭建门户网站。
阿里云数据库产品家族及特性
阿里云智能数据库产品团队一直致力于不断健全产品体系,提升产品性能,打磨产品功能,从而帮助客户实现更加极致的弹性能力、具备更强的扩展能力、并利用云设施进一步降低企业成本。以云原生+分布式为核心技术抓手,打造以自研的在线事务型(OLTP)数据库Polar DB和在线分析型(OLAP)数据库Analytic DB为代表的新一代企业级云原生数据库产品体系, 结合NoSQL数据库、数据库生态工具、云原生智能化数据库管控平台,为阿里巴巴经济体以及各个行业的企业客户和开发者提供从公共云到混合云再到私有云的完整解决方案,提供基于云基础设施进行数据从处理、到存储、再到计算与分析的一体化解决方案。本节课带你了解阿里云数据库产品家族及特性。
相关文章
|
1月前
|
Oracle 关系型数据库 分布式数据库
PolarDB常见问题之PolarDB(Oracle兼容版) 执行命令报错如何解决
PolarDB是阿里云推出的下一代关系型数据库,具有高性能、高可用性和弹性伸缩能力,适用于大规模数据处理场景。本汇总囊括了PolarDB使用中用户可能遭遇的一系列常见问题及解答,旨在为数据库管理员和开发者提供全面的问题指导,确保数据库平稳运行和优化使用体验。
|
1月前
|
SQL 运维 关系型数据库
基于AnalyticDB PostgreSQL的实时物化视图研发实践
AnalyticDB PostgreSQL企业数据智能平台是构建数据智能的全流程平台,提供可视化实时任务开发 + 实时数据洞察,让您轻松平移离线任务,使用SQL和简单配置即可完成整个实时数仓的搭建。
441 1
|
1月前
|
存储 关系型数据库 数据库
postgresql|数据库|提升查询性能的物化视图解析
postgresql|数据库|提升查询性能的物化视图解析
238 0
|
1月前
|
关系型数据库 分布式数据库 数据库
PolarDB PostgreSQL版:Oracle兼容的高性能数据库
PolarDB PostgreSQL版是一款高性能的数据库,具有与Oracle兼容的特性。它采用了分布式架构,可以轻松处理大量的数据,同时还支持多种数据类型和函数,具有高可用性和可扩展性。它还提供了丰富的管理工具和性能优化功能,为企业提供了可靠的数据存储和处理解决方案。PolarDB PostgreSQL版在数据库领域具有很高的竞争力,可以满足各种企业的需求。
|
8月前
|
存储 安全 关系型数据库
PostgreSQL物化视图增量更新扩展 -- pg_ivm
PostgreSQL不支持物化视图增量更新,需要定期执行REFRESH MATERIALIZED VIEW命令刷新物化视图。Incremental View Maintenance (IVM)是一种使物化视图保持最新的方法,其中只计算增量更改并将其应用于视图,而不是REFRESH MATERIALIZED VIEW那样从头开始重新计算内容。当只更改视图的一小部分时,IVM可以比重新计算更高效地更新物化视图。
652 0
|
4天前
|
SQL 关系型数据库 PostgreSQL
【sql】PostgreSQL物化视图表使用案例
【sql】PostgreSQL物化视图表使用案例
7 0
|
8天前
|
运维 Cloud Native 关系型数据库
云原生数据仓库AnalyticDB产品使用合集之PostgreSQL版是否直接支持实时物化视图
阿里云AnalyticDB提供了全面的数据导入、查询分析、数据管理、运维监控等功能,并通过扩展功能支持与AI平台集成、跨地域复制与联邦查询等高级应用场景,为企业构建实时、高效、可扩展的数据仓库解决方案。以下是对AnalyticDB产品使用合集的概述,包括数据导入、查询分析、数据管理、运维监控、扩展功能等方面。
|
1月前
|
人工智能 Oracle 关系型数据库
一篇文章弄懂Oracle和PostgreSQL的Database Link
一篇文章弄懂Oracle和PostgreSQL的Database Link
|
1月前
|
存储 Oracle 关系型数据库
Oracle物化视图:数据的“快照”艺术
【4月更文挑战第19天】Oracle物化视图是预计算的查询结果集,存储为实际表,提升查询性能,简化复杂查询。它们用于快速访问、封装复杂逻辑,加速数据汇总和报表生成。然而,需注意基础数据稳定性、刷新策略及存储成本。物化视图是高效数据管理的工具,但需适时优化使用。
|
1月前
|
SQL Oracle 关系型数据库
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)
常用数据库的分页语句(mySQL、oracle、PostgreSQL、SQL Server)

热门文章

最新文章