oracle-dba-视图

刺骨的言语ヽ痛彻心扉 2022-07-16 16:09 266阅读 0赞

视图  
CREATE \[OR REPLACE\] \[FORCE|NOFORCE\] VIEW view\_name \[别名\[,别名\]…\]  
AS  
\[WITH CHECK OPTION \[ CONSTRAINT 约束名\]\]  
\[WITH READ ONLY\]  
FORCE/NOFORCE：创建视图时，不论基表是否存在都创建该视图。NOFORCE相反。  
AS:说明下面是查询子句。用户定义视图  
WITH CHECK OPTION：当更新某一数据行时，必须满足where子句的条件  
WITH READ ONLY：设置该视图只读。说明无法进行DML操作

创建视图二种方式  
Create view accounting\_view as select \* from temp;  
Create or replace view sales\_view (“col1”,”col2”) as select name,id from temp;

查询数据字典中记录的视图定义  
Select view\_name from user\_views;  
查询视图account\_view定义的语句  
Select text from user\_views where view\_name=’ACCOUNTING\_VIEW’;  
查询ACCOUNTING\_VIEW视图的所有信息  
Select \* from accounting\_view;

使用视图的WITH子句  
Create view emp\_view as select \* from emp where job in (‘SALARY’,’MANAGER’);  
Where子句限制只选择表emp中的job为salary和manager的所有员工数据才可以插入，否则不允许插入。

视图的修改  
可以通过replace替换修改的视图

Oracle视图管理  
视图查询的内部过程  
 读取数据字典，获得该视图的定义，查找到该视图所引用的表  
 从数据字典中查询当前用户对于该视图所引用的表的权限。  
 执行定义该视图的sql语句，实现执行视图查询。

视图DML操作的限制  
简单视图  
简单视图从一个表读取数据，不包括函数和分组数据。简单视图可以进行DML操作，简单视图的dml操作直接转换成对定义它的表的dml操作  
复杂视图  
从多个表提取数据，包括函数和分组数据，复杂视图不一定能进行DML操作。  
ORACLE对复杂视图上进行DML操作加了限制：  
 如果复杂视图包含分组函数，group by|distinct关键字，就不能使用delete/UPDATE/INSERT。  
 如果复杂视图中的列包含表达式，或者有伪列ROWNUM，则不能使用复杂视图进行DML操作。

物化视图  
普通视图会重新执行创建视图的表的sql语句，当表多时，相当耗时。引入物化视图  
物化视图：具有物理存储的特殊视图，基于表，视图或者其他物化视图创建的。创建一个物化视图时，oracle会自动创建一个内部表来存放物化视图的数据。占用存储空间，可以进行分区和创建索引等操作。

查询重写  
对sql查询语句进行重写，当用户使用sql语句对基表进行查询时，如果已经创建了基于这些基表的物化视图，oracle将自动计算和使用物化视图来完成查询。此称为查询重写。  
查看query\_rewrite\_enabled的值,是否允许查询重写功能。  
Show parameter query\_rewrite\_enabled;

物化视图的同步  
物化视图基于基表创建，当基表变化时，需要同步数据以更新物化视图中的数据，保持他两数据一致。  
2种物化视图刷新方式  
 on commit:基表变化时，物化视图自动刷新。完成与基表的同步  
 on demand：需要手动同步物化视图和基表数据，此时必须DBMS\_MVIEW.REFRESH过程来同步物化视图

选择一种刷新方式，可以选择1种刷新类型完成数据同步。  
刷新类型：指刷新数据时如何实现基表与物化视图的同步，从而将基表的变化反映在物化视图中。

4种刷新类型  
 COMPLETE：重新执行创建物化视图的sql查询语句，无论基表中修改的数据量多少，都需要完成一次对物化视图的重新计算  
 FAST：使用每个基表的物化视图日志只同步变化的数据，节约查询时间成本  
 FORCE：显示fast类型更新数据，如果失败在使用complete类型刷新整个物化视图  
 NEVER：从不更新。对基表数据不变的物化视图有效。  
不指定刷新类型，默认force。  
注意:启动查询重写机制，oracle查询重写不一定实现。可以使用DBMS\_MVIEW程序包中的过程分析。

创建物化视图  
1:授权  
授予创建物化视图的权限，query rewrite权限,对创建物化视图所涉及的表的访问权限和创建表的权限。  
Grant create materialized view to scott;  
GRANT QUERY REWRITE TO SCOTT;  
GRANT CREATE ANY TABLE TO SCOTT;  
GRANT SELECT ANY TABLE TO SCOTT;  
2：创建物化视图日志  
物化视图日志是用户选择刷新类型为fast时要使用的。以同步基表的变化。  
Create materialized view log on dept;  
3:创建物化视图的语句  
CREATE materialized view mtrlview\_test  
Build immediate  
Refresh fast on commit  
Enable query rewrite  
As  
Select \* from temp;  
Build immediate :立即创建物化视图。Bulid deffered，使用该视图时创建  
Refresh fast on commit：刷新的数据类型为fast，刷新类型要求使用物化视图日志实现与基表数据的同步。on commit说明基表数据提交后立即更新物化视图  
Enable query rewrite：启动查询重写功能，  
4：删除物化视图  
Drop materialized view mtrlview\_test;

物化视图使用场景  
总结：物化视图是一种用于汇总，计算，复制以及发布数据的方案。  
物化视图适用于，数据仓库，决策支持，分布式计算，以及移动计算等环境  
 数据仓库：在数据仓库中往往需要存储对于基表的汇总或平均数据等。物化视图用与进行类似的计算来存储聚合后的数据。因为在数据仓库中的物化视图通常汇总数据，所以数据仓库中的物化视图也称为概要  
 分布式环境：在分布式环境中可以通过物化视图实现不同节点间的数据同步，使得同样的数据分布在不同的物理空间，更好的响应用户的查询，减少中心数据库服务器的负担。物化视图的复制功能使得用户可以把数据复制到远程节点，而数据同步能力有可以同步各个节点间的数据。从而实现数据的本地访问。  
用户物化视图(视图更新)中心服务  
 移动计算环境:利用物化视图的优化查询功能来节约查询时间，同时移动客户端使用物化视图下载一个数据子集，也可以定期地从中央服务器获得新数据，在客户端修改后发到中央服务器。