Oracle物化视图的用法与总结,及索引视图与查询

 

 

Oracle物化视图的用法与总计

作者的MYSQL学习体会(十大器晚成卡塔 尔(英语:State of Qatar) 视图

自个儿的MYSQL学习体会(风度翩翩卡塔尔简单语法

自身的MYSQL学习心得(二)数据类型宽度

自家的MYSQL学习心得(三卡塔 尔(英语:State of Qatar)查看字段长度

自己的MYSQL学习心得(四卡塔尔数据类型

自身的MYSQL学习心得(五)运算符

自个儿的MYSQL学习体会(六卡塔 尔(阿拉伯语:قطر‎函数

本身的MYSQL学习心得(七卡塔 尔(英语:State of Qatar)查询

自家的MYSQL学习体会(八卡塔 尔(英语:State of Qatar) 插入 更新 删除

自己的MYSQL学习心得(九卡塔 尔(英语:State of Qatar)索引

我的MYSQL学习体会(十卡塔尔自定义存款和储蓄进程和函数

本人的MYSQL学习体会(十四)触发器

本身的MYSQL学习心得(十八卡塔尔权限管理

自家的MYSQL学习心得(十五卡塔 尔(英语:State of Qatar)备份和还原

自己的MYSQL学习体会(十六)日志

本人的MYSQL学习心得(十九卡塔 尔(阿拉伯语:قطر‎优化

本人的MYSQL学习体会(十八卡塔尔复制

 

这豆蔻梢头篇《作者的MYSQL学习心得(十后生可畏卡塔 尔(英语:State of Qatar)》将会讲课MYSQL的视图

 

选择视图的说辞是如何?

1、安全性:平时是那般做的:创制一个视图,定义好该视图所操作的数目。

之后将客户权限与视图绑定,那样的点子是行使到了四个表征:grant语句能够针对视图进行付与权限。

2、查询质量进步

3、有浑圆的效果与利益要求后,要求改换表的构造而招致工作量不小,那么可以动用设想表的花样达到少改良的机能。

那是在其实支付中相比较实惠的

4、复杂的查询必要,能够拓宽难点解释,然后将创设多个视图获取数据。将视国际图书馆协会联合会合起来就能够得到要求的结果了。


始建视图

开创视图的语法

CREATE [OR REPLACE] [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
    VIEW view_name [(column_list)]
    AS select_statement
    [WITH [CASCADED | LOCAL] CHECK OPTION]

里头,CREATE:表示新建视图;

REPLACE:表示替换原来就有视图

ALGORubiconITHM :表示视图选拔算法

view_name :视图名

column_list:属性列

select_statement:表示select语句

[WITH [CASCADED | LOCAL] CHECK OPTION]参数表示视图在创新时保险在视图的权限节制以内

 

可选的ALGO锐界ITHM子句是对正规SQL的MySQL扩充。

ALGORAV4ITHM可取四个值:ME冠道GE、TEMPTABLE或UNDEFINED。

如果未有ALGOKoleosITHM子句,暗中认可算法是UNDEFINED(未定义的卡塔 尔(英语:State of Qatar)。算法会影响MySQL管理视图的不二秘诀。

对于ME昂科威GE,会将引用视图的说话的公文与视图定义合併起来,使得视图定义的某豆蔻年华有的代表语句的相应部分。

对于TEMPTABLE,视图的结果将被停放不常表中,然后采取它推行语句。

对此UNDEFINED,MySQL本身采用所要使用的算法。假使只怕,它趋势于ME奥迪Q5GE并不是TEMPTABLE,

那是因为ME奥迪Q5GE经常更使得,何况假如选拔了有时表,视图是不可更新的。

 

LOCAL和CASCADED为可选参数,决定了检讨测验的节制,暗中认可值为CASCADED。

本子 视图的数据出自于五个表

CREATE TABLE student (stuno INT ,stuname NVARCHAR(60))

CREATE TABLE stuinfo (stuno INT ,class NVARCHAR(60),city NVARCHAR(60))

INSERT INTO student VALUES(1,'wanglin'),(2,'gaoli'),(3,'zhanghai')

INSERT INTO stuinfo VALUES(1,'wuban','henan'),(2,'liuban','hebei'),(3,'qiban','shandong')

-- 创建视图
CREATE VIEW stu_class(id,NAME,glass) AS SELECT student.`stuno`,student.`stuname`,stuinfo.`class`
FROM student ,stuinfo WHERE student.`stuno`=stuinfo.`stuno`


SELECT * FROM stu_class

图片 1


翻开视图

翻看视图必定要有SHOW VIEW权限

翻开视图的方法包涵:DESCSportageIBE、SHOW TABLE STATUS、SHOW CREATE VIEW

DESCLacrosseIBE查看视图基本音信

DESCRIBE 视图名

DESCRIBE stu_class

图片 2

结果显示了视图的字段定义、字段的数据类型、是不是为空、是还是不是为主/外键、私下认可值和额外音讯

DESCCRUISERIBE平常都简写成DESC

 

SHOW TABLE STATUS语句查看查看视图基本音信

查看视图的新闻能够透过SHOW TABLE STATUS的诀要

SHOW TABLE STATUS LIKE 'stu_class'

Name       Engine  Version  Row_format    Rows  Avg_row_length  Data_length  Max_data_length  Index_length  Data_free  Auto_increment  Create_time  Update_time  Check_time  Collation  Checksum  Create_options  Comment
---------  ------  -------  ----------  ------  --------------  -----------  ---------------  ------------  ---------  --------------  -----------  -----------  ----------  ---------  --------  --------------  -------
stu_class  (NULL)   (NULL)  (NULL)      (NULL)          (NULL)       (NULL)           (NULL)        (NULL)     (NULL)          (NULL)  (NULL)       (NULL)       (NULL)      (NULL)       (NULL)  (NULL)          VIEW   

COMMENT的值为VIEW表达该表为视图,其余的音讯为NULL表达那是一个虚表,假使是基表那么会基表的新闻,那是基表和视图的区分

 

SHOW CREATE VIEW语句查看视图详细新闻

SHOW CREATE VIEW stu_class

View       Create View                                                                                                                                                                                                                                                               character_set_client  collation_connection
---------  ------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  --------------------  --------------------
stu_class  CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`localhost` SQL SECURITY DEFINER VIEW `stu_class` AS select `student`.`stuno` AS `id`,`student`.`stuname` AS `name`,`stuinfo`.`class` AS `class` from (`student` join `stuinfo`) where (`student`.`stuno` = `stuinfo`.`stuno`)  utf8                  utf8_general_ci     

执行结果展现视图的称呼、创设视图的口舌等消息

 

在VIEWS表中查阅视图的详细消息

在MYSQL中,INFORMATION_SCHEMA VIEWS表存款和储蓄了有关数据库中的视图的音信

透过对VIEWS表的查询能够查看数据库中保有视图的详细新闻

SELECT * FROM `information_schema`.`VIEWS`

TABLE_CATALOG  TABLE_SCHEMA  TABLE_NAME  VIEW_DEFINITION                                                                                                                                                                                                                         CHECK_OPTION  IS_UPDATABLE  DEFINER         SECURITY_TYPE  CHARACTER_SET_CLIENT  COLLATION_CONNECTION
-------------  ------------  ----------  --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------  ------------  ------------  --------------  -------------  --------------------  --------------------
def            school        stu_class   select `school`.`student`.`stuno` AS `id`,`school`.`student`.`stuname` AS `name`,`school`.`stuinfo`.`class` AS `class` from `school`.`student` join `school`.`stuinfo` where (`school`.`student`.`stuno` = `school`.`stuinfo`.`stuno`)  NONE          YES           root@localhost  DEFINER        utf8                  utf8_general_ci     

这几天实例下独有三个视图stu_class

图片 3


改敬爱图

矫注重图是指校订数据库中留存的视图,当基本表的少数字段发生变化时,能够经过更动视图来维持与基本表的生龙活虎致性。

MYSQL中经过CREATE O哈弗 REPLACE VIEW 语句和ALTE福睿斯语句来改注重图

语法如下:

ALTER OR REPLACE [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

该语句用于转移原来就有视图的概念。其语法与CREATE VIEW雷同。当视图一纸空文时创设,存在时开展订正

 

矫爱护图

DELIMITER $$

CREATE OR REPLACE VIEW `stu_class` AS 
SELECT
  `student`.`stuno`   AS `id`
FROM (`student` JOIN `stuinfo`)
WHERE (`student`.`stuno` = `stuinfo`.`stuno`)$$

DELIMITER ;

 

经过DESC来查看改过之后的视图定义

DESC stu_class

图片 4

能够看出只询问叁个字段

 

ALTE冠道语句改良视图

ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}]
VIEW view_name [(column_list)]
AS select_statement
[WITH [CASCADED | LOCAL] CHECK OPTION]

此地首要字前边边的大器晚成致,这里不做牵线

 

选用ALTEPRADO语句更正视图 stu_class

ALTER VIEW  stu_class AS SELECT stuno FROM student;

使用DESC查看

DESC stu_class

图片 5

图片 6


创新视图

更新视图是指通过视图来插入、更新、删除表数据,因为视图是虚表,个中未有数据。

经过视图更新的时候都以转到基表进行更新,假诺对视图扩张照旧去除记录,实际上是对基表扩张或删除记录

 

咱俩先修改一下视图定义

ALTER VIEW  stu_class AS SELECT stuno,stuname FROM student;

询问视图数据

图片 7

 

UPDATE

UPDATE stu_class SET stuname='xiaofang' WHERE stuno=2

询问视图数据

图片 8

履新成功

 

INSERT

INSERT INTO stu_class VALUES(6,'haojie')

图片 9

布署成功

 

DELETE

DELETE FROM stu_class WHERE stuno=1

图片 10

删除成功

 

当视图中包括如下内容的时候,视图的更新操作将不可能被实践

(1卡塔 尔(英语:State of Qatar)视图中蕴藏基本中被定义为非空的列

(2卡塔尔国定义视图的SELECT语句后的字段列表中使用了数学表达式

(3卡塔 尔(英语:State of Qatar)定义视图的SELECT语句后的字段列表中选拔聚合函数

(4卡塔尔国定义视图的SELECT语句中选择了DISTINCT、UNION、TOP、GROUP BY 、HAVING子句

 


剔除视图

除去视图使用DROP VIEW语法

DROP VIEW [IF EXISTS]
view_name [, view_name] ...
[RESTRICT | CASCADE]

DROP VIEW能够删除1个或四个视图。必得在各样视图上存有DROP权限

能够利用主要字IF EXISTS来堤防因不设有的视图而失误

 

删除stu_class视图

DROP VIEW IF EXISTS stu_class 

假设名字为 stu_class 的视图存在则删除

 

利用SHOW CREATE VIEW语句查看结果

SHOW CREATE VIEW stu_class 

Query: -- update stu_class set stuname='xiaofang' where stuno=2; -- delete from stu_class where stuno=1 -- select * from stu_class; -- ...

Error Code: 1146
Table 'school.stu_class' doesn't exist

Execution Time : 0 sec
Transfer Time  : 0 sec
Total Time     : 0.004 sec
---------------------------------------------------

该视图空头支票,删除成功

 


总结

SQLSERVER里其实跟MYSQL无差异于,也可能有消息架构视图的

音讯架构视图 (Transact-SQL)

消息架构视图是 SQL Server 提供的三种得到元数据的章程之大器晚成。  

音信架构视图提供单身于系统表的里边 SQL Server 元数据视图。

就算已经对功底种类表实行了举足轻重的改变,音信架构视图仍旧可使应用程序平常干活。

SQL Server 中蕴藏的音讯框架结构视图切合 ISO 标准中的音信架构定义。 

新闻架构视图的数额是贮存在在系统数据库Resource 数据库里面

mssqlsystemresource.mdf 

利用INFORMATION_SCHEMA视图来拼接 SQL 语句 

 

如有不对的地点,招待大家拍砖o(∩_∩)o 

正文版权归作者全体,未经小编同意不得转发。

本文出处: 

本文出处: 

物化视图(material view卡塔 尔(阿拉伯语:قطر‎是哪些?

物化视图是归纳贰个查询结果的数据库对象,它是长间距数据的的地头副本,也许用来变化基于数据表求和的汇总表
物化视图存储基于远程表的数目,也足以称之为快速照相(近似于MSSQL Server中的snapshot,静态快照卡塔尔国。对于复制,物化视图允许你在本土维护远程数据的别本,这个别本是只读的。
万生龙活虎您想校订本地别本,必得用高等复制的效率。当您想从三个表或视图中取出数据时,你能够用从物化视图中收取。
对于数据饭店,成立的物化视图常常状态下是聚众视图,单一表聚合视图和连接视图。(那么些是依据本地的基表也许视图的聚焦卡塔 尔(阿拉伯语:قطر‎。
物化视图,说白了,正是物理表,只不过那张表经过oracle的中间机制得以按时更新,将大器晚成都部队分大的耗费时间的表连接用物化视图实现,会增高查询的频率。当然要开采查询重写选项;

粗略,正是全部实体表的视图,况且以此视图还足以听闻三种急需和攻略进行刷新。其他还会有叁个要命关键的效应
询问重写(query rewrite) .查询重写可以在少数时候增进你的询问速度。
所谓查询重写,简来说之,正是oracle 的询问优化器开掘存个物化视图的语法和你的SQL大约,那么就能够平昔访谈物化视图,实际不是你本来查询中关于的源表。

 

 

物化视图能干什么?

大概说,你能拿物化视图做哪些用。
前文简单说了下,此处列出有个别第一而想详细的功效:

  • 可以提升查询速度,那第一是因为物化视图存款和储蓄了实在的数额,其次具备查询重写功效。最终,物化视图具备实体表,你也足以在地点建设构造目录,同理可得轮廓上作为三个表用就足以了。
  • 简化了付出职分,意思是支付的人手不经常,不须要直接关心部分sql的特性,而由此dba的不竭,使用查询重写来完结质量的进步。
  • 裁减了工作量,因为物化视图能够定义二种刷新方式:立时刷新,按需刷新。所谓按需刷新正是你和煦手动刷新,或者是准时刷新;所谓马上刷新,即视图主表发生变化的时候,视图立时刷新内容。 你能够依据自个儿的配备状态,应用处境和急需来决定刷新的诀窍。
  • 刷新量的利落约束,你能够长足是刷新(只刷新变化的卡塔尔国,也得以全刷新。看您的内需。

  平时听Oracle的同班提起来物化视图,物化视图的效用之风度翩翩就是足以兑现查询重写,听上去有黄金时代种壮烈上的以为,
  SQL Server也可能有相同于Oracle物化视图的遵循,只可是叫做索引视图。
  说真话,依然物化视图听上去比较合适,与普通视图比,物化视图正是一向将数据存储起来了
  SQL Server中的索引视图也许有着查询重写的功效,
  所谓的询问重写,便是黄金年代旦符合条件的数码在索引视图上,并且查询列都饱含在在索引视图上,那时得以平昔通过查询索引视图来代表基于原始表的查询

先是次通过索引视图优化SQL语句,以致境遇的意气风发部分主题材料,记录一下。

物化视图能够分成以下三种档案的次序

  1. 含蓄集中的物化视图;
  2. 只含有连接的物化视图;
  3. 嵌套物化视图。

二种物化视图的火速刷新的限量规范有很大差距,而对于别的地点则分别超级小。成立物化视图时方可钦定两种增选,下面临两种关键的抉择举办简要表明:

  • 创办格局(BuildMethods卡塔 尔(阿拉伯语:قطر‎:包括BUILD IMMEDIATE和BUILD DEFERRED两种。
    BUILD IMMEDIATE是在创设物化视图的时候就改动数据。
    BUILD DEFELacrosseRED则在开立刻不生成数据,现在根据需求在改换数据。默认为BUILD IMMEDIATE

  • 询问重写(QueryRewrite卡塔 尔(英语:State of Qatar):包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。
    各自建议成立的物化视图是还是不是襄协助调查询重写。查询重写是指当对物化视图的基表进行查询时,Oracle会自动推断能无法通过询问物化视图来得到结果,若是能够,则幸免了汇聚或一连操作,而直接从曾经总结好的物化视图中读取数据。私下认可为DISABLE QUERY REWRITE

  • 刷新(Refresh):指当基表发生了DML操作后,物化视图曾几何时选取哪一种艺术和基表举办联合。刷新的方式有三种:ON DEMAND和ON COMMIT。ON DEMAND和ON COMMIT物化视图的区分在于其刷新方法的不等,ON DEMAND指物化视图在顾客须求的时候实行刷新,能够手工通过DBMS_MVIEW.REFRESH等办法来进展刷新,也得以透过JOB依期开展刷新,即更新物化视图,以确认保障和基表数据的大器晚成致性;而ON COMMIT是说,豆蔻梢头旦基表有了COMMIT,即专门的学业提交,则任何时候刷新,立时更新物化视图,使得数据和基表风度翩翩致。对基表,平时的COMMIT在0.01秒内足以完毕,但在有了ON COMMIT视图后,居然要6秒。速度下降了相当多倍。ON COMMIT视图对基表的熏陶可以预知风姿潇洒斑。

 

 

物化视图,依照分裂的关键性可以有例外的归类:

  1. 按刷新格局分:FAST/COMPLETE/FORCE
  2. 按刷新时间的两样:ON DEMAND/ON COMMIT
  3. 按是不是可更新:UPDATABLE/READ ONLY
  4. 按是还是不是扶持查询重写:ENABLE QUEWranglerY REW科雷傲ITE/DISABLEQUERAV4Y REW牧马人ITE

暗许情状下,要是没内定刷新方法和刷新方式,则Oracle默认为FORCE和DEMAND
瞩目:设置REFRESH ON COMMIT的物化视图不可能访谈远端对象。
在确立物化视图的时候能够内定OCR-VDER BY语句,使生成的数量遵照一定的逐风流罗曼蒂克进行封存。但是那几个语句不会写入物化视图的定义中,并且对今后的刷新也船到江心补漏迟。

反之亦然惯例,先上代码做一个测量试验碰着

讲话解析

物化视图有三种刷新方式:COMPLETE、FAST和 FORCE

  • 完全刷新(COMPLETE卡塔尔会去除表中全部的笔录(若是是单表刷新,只怕会使用TRUNCATE的措施卡塔 尔(阿拉伯语:قطر‎,然后依据物化视图中查询语句的定义再一次生成物化视图。
  • 快快刷新(FAST卡塔尔国接受增量刷新的机制,只将自上次刷新之后对基表举办的享有操作刷新到物化视图中去。FAST必得创建基于主表的视图日志。对于增量刷新选项,假使在子查询中设有解析函数,则物化视图不起功效。
  • 采用FORCE方式,Oracle会自动决断是不是满足急速刷新的规范化,纵然满意则举行飞快刷新,不然进行完全刷新。

Oracle物化视图的神速刷新机制是透过物化视图日志姣好的。Oracle通过叁个物化视图日志还足以支撑多少个物化视图的长足刷新。物化视图日志依照不相同物化视图的顿时刷新的急需,能够构造建设为ROWID或PLX570IMAEscortY KEY类型的。仍是可以接收是或不是包蕴SEQUENCE、INCLUDING NEW VALUES以及钦命列的列表。

--创建两张表,一张表头,一张明细,仅仅作为DEMO使用
CREATE TABLE HeadTable
(
    HeadId      INT PRIMARY KEY  ,
    HeadInfo    VARCHAR(50)      ,
    DataStatus  TINYINT          ,
    CreateDate  Datetime
)
GO

CREATE TABLE DetailTable
(
    HeadId      INT           ,
    DetailId    INT identity(1,1) PRIMARY KEY ,
    DatailInfo  VARCHAR(50)
)
GO

--写入数据
DECLARE @i int = 0
WHILE @i<200000
BEGIN
    INSERT INTO HeadTable values (@i,NEWID(),RAND()*10,GETDATE()-RAND()*100)
    INSERT INTO DetailTable(HeadId,DatailInfo) VALUES (@i,NEWID())
    SET @i=@i 1
END
GO

新近付出递交过来多个询问计算的SQL,说是质量有标题,原本试行须求4-5分钟,这一个事情本人对品质必要又相比较critical,期待是在1s之内
在用尽各类方式之后(实施布署,总结新闻,索引,改写SQL,有的时候表拆分卡塔 尔(阿拉伯语:قطر‎,依然未有实质性的改换,
在察看SQL自身的性子之后,
有以下几脾性状
  1,查询语句全部为多表join,可是各类表自己的数码并非比非常的大,百万级
  2,查询结果在主表上贰个超级大的年月限定的数量进行Count的成团操作
  3,几张表之间除了连接条件,首假若展开了豆蔻梢头部分比较复杂的逻辑运算(上边截图能够见见,没有多少IO,CPU时间却相当的高卡塔尔国
    可是表的接连几日方式都以inner join,首要品质点就在于表关联之间的Hash join之间的逻辑运算,参考下图(是实行铺排的后生可畏有的卡塔尔

物化视图操作示例

-- 物化视图操作示例

-- 删除物化视图
DROP MATERIALIZED VIEW V_RPT_ACT_ACTION;


-- 创建物化视图

/**
 1.创建方式(BuildMethods):包括BUILD IMMEDIATE和BUILD DEFERRED两种。
 BUILD IMMEDIATE是在创建物化视图的时候就生成数据。
 BUILD DEFERRED则在创建时不生成数据,以后根据需要在生成数据。默认为BUILD IMMEDIATE。

 2. 查询重写(QueryRewrite):包括ENABLE QUERY REWRITE和DISABLE QUERY REWRITE两种。
 分别指出创建的物化视图是否支持查询重写。
 查询重写是指当对物化视图的基表进行查询时,
 Oracle会自动判断能否通过查询物化视图来得到结果,如果可以,则避免了聚集或连接操作,
 而直接从已经计算好的物化视图中读取数据。默认为DISABLE QUERY REWRITE。

 */
CREATE MATERIALIZED VIEW V_RPT_ACT_ACTION  
  BUILD IMMEDIATE  
  DISABLE QUERY REWRITE
AS  
  SELECT * FROM T_ACT_ACTION;

-- 查询物化视图    
SELECT * FROM V_RPT_ACT_ACTION ORDER BY V_RPT_ACT_ACTION.ACTION_VALUE;

-- 刷新物化视图 刷新时间间隔。每1天刷新一次,时间为凌晨10点
ALTER MATERIALIZED VIEW V_RPT_ACT_ACTION 
REFRESH FORCE ON DEMAND 
START WITH SYSDATE NEXT TO_DATE(CONCAT(TO_CHAR(SYSDATE 1,'DD-MM-YYYY'),' 22:00:00'),'DD-MM-YYYY HH24:MI:SS');

-- 使用DBMS_MVIEW.REFRESH 手工刷新,刷新物化视图,可以放在存储过程中刷新,存储过程不用写EXEC
-- DBMS_MVIEW.REFRESH('V_RPT_ACT_ACTION'); 


-- EXEC DBMS_MVIEW.REFRESH('V_RPT_ACT_ACTION');  
-- EXEC DBMS_REFRESH.REFRESH('V_RPT_ACT_ACTION');

-- 完全刷新
-- EXEC DBMS_MVIEW.REFRESH(LIST => 'V_RPT_ACT_ACTION',METHOD => 'c');  
-- EXEC DBMS_MVIEW.REFRESH('V_RPT_ACT_ACTION','C');  

--快速刷新   
-- 表 "BOEREMS"."T_ACT_ACTION" 不带实体化视图日志

-- EXEC DBMS_MVIEW.REFRESH(LIST => 'V_RPT_ACT_ACTION',METHOD => 'f');  
-- EXEC DBMS_MVIEW.REFRESH('V_RPT_ACT_ACTION','F');  

SELECT SYS_GUID() FROM DUAL;

-- 查询物化视图上次刷新时间
-- LAST_REFRESH_DATE  上次刷新时间

SELECT * FROM USER_MVIEWS 
    WHERE MVIEW_NAME = 'V_RPT_ACT_ACTION';

-- 手动刷新物化视图
BEGIN
   DBMS_MVIEW.REFRESH('V_RPT_ACT_ACTION');
END;

CREATE OR REPLACE PROCEDURE USP_RVW_REFRESH_VIEW

AS

 BEGIN

     -- 刷新物化视图
    DBMS_OUTPUT.PUT_LINE('######');

    -- DBMS_MVIEW.REFRESH('V_RPT_ACT_ACTION'); 

    DBMS_MVIEW.REFRESH('V_RPT_OBJECTIVE_QUESTIONS','C');

    -- EXECUTE IMMEDIATE 'DROP MATERIALIZED VIEW V_RPT_OBJECTIVE_QUESTIONS';   

    DBMS_MVIEW.REFRESH('V_RPT_COURSE_STUDENT','C');
    DBMS_MVIEW.REFRESH('V_RPT_EXAM_STUDENT','C');

    -- CALL DBMS_MVIEW.REFRESH('V_RPT_COURSE_STUDENT ','C');
    -- CALL DBMS_MVIEW.REFRESH('V_RPT_EXAM_STUDENT ','C');

    DBMS_OUTPUT.PUT_LINE('###### FINISH');

END USP_RVW_REFRESH_VIEW;

 

图片 11

 

  通过总括音信发掘,该SQL语句的情理IO并不高,表明索引未有啥难点,通过索引矫正IO或许改良空间很有限
  时间入眼费用在SQL语句的编写翻译和的Hash join 运算
  尝试改写依靠SQL之后(纯改写SQL和正视性有时表拆分语句卡塔 尔(阿拉伯语:قطر‎,发掘仍旧很难绕过Hash join,首假诺表之间的逻辑运算最为耗费时间
  最后想到能够先将运算好的的数码物理存款和储蓄起来,然后改写查询的SQL语句完成等价的查询,
  防止每一回查询都做复杂的逻辑运算,应该能够有极大的精雕细刻,于是就悟出了索引视图

索引视图创制

图片 12

那正是说怎样成立索引视图呢?语法上跟创制普通视图差距超级小,然而不容许现身select *,表名上要抬高Scheme,因为此处不是专程说索引视图的,细节就相当的少说了。

  

CREATE VIEW V_IndexViewTest WITH SCHEMABINDING
AS
    SELECT H.HeadId,H.CreateDate,H.DataStatus,D.DetailId,D.DatailInfo
    FROM dbo.HeadTable H INNER JOIN 
                dbo.DetailTable D ON H.HeadId = D.HeadId
    WHERE H.DataStatus = 0
GO

创办索引视图改写SQL

索引视图要求创制的率先个名列唯黄金时代集中索引,所以如下,创造三个唯黄金时代的聚集索引

  在领收取来原始的查询SQL,创造索引视图,并在索引视图上开创unique cluster index和合理性的nocluster index
  通过索引视图改写原始的询问总计SQL语句,
  改写后的SQL语句是二个索引视图代替原始的4张表,与别的一个物理表join,发现成效上尚无其他改正,
  观看改写后的SQL语句的实施安顿,开采跟原始SQL同样,并不曾走索引视图上的目录,也许说是用到索引视图,偶尔间以为好心塞,实在没招了
  试行陈设照旧是漫漫一大段,然后照旧是某个个Hash Join.参照他事他说加以考察下图,履行布置跟本文第二个截图如出一辙

 图片 13

图片 14

  对于其它索引,可以跟在表上创制索引同样

  按道理,索引视图固化结果集,而且依据气象做了过滤,结果集是原有查询的一片段而已,
  用同样的询问条件从索引视图做询问总结,走索引视图代价必然要比原始SQL低,通过强制不开展with(noexpand)提醒,证实了这一个推测
  如下是威胁不张开索引视图的总计音讯,能够看见完全达到了预期的1S以内

  图片 15

  图片 16

 

图片 17

 

 

查询重写

  当然索引视图亦非绝非代价,在一定水准上牺牲了多少写入的频率和冗余存款和储蓄,来换取查询的功效
  以前大约介绍过索引视图的风流罗曼蒂克部分特色

  上边说了,查询重写就是将依赖原始表的询问语句,直接在索引视图上查询落成,那么就来看一下询问重写是怎么样体统的?
  上面来察看这么三个询问,SQL很引人瞩目地是基于原始表做的询问,跟普通查询齐驱并驾,
  可是观望实施布署就能够开掘:
  那个实践陈设走了二个索引查找,首先很精通,HeadTable上的CreateDate是不曾索引的,这里走的目录正是V_IndexViewTest上的CreatDate列上的目录
  相当于在索引视图上开创的第贰个目录。

 

  图片 18

索引视图被实行(expand)的原因剖判

  若是,查询语句这么写,如下,在询问条件中扩大了三个索引视图中从不的列,那时候询问就不会被重写,间接走的是依据原始表的查询,跟普通查询并驾齐驱。
  其实原理轻便通晓,因为视图中并不含有HeadInfo那些列,如果在查询列上增添这么些字段,视图中是从未这一个字段的,那只能依照原始表做询问了。

  最要紧的标题在于,未有强制不展开索引视图(with(noexpand)提醒卡塔尔国的景观下,为什么没有走索引视图呢?

  图片 19

  那么些主题素材的确压抑了片刻,整个半天都在想这几个标题,因为索引视图本人用的就少,现身此难题更是浑浑噩噩,可是谷歌(Google卡塔尔一下,还会有一大把看似的主题材料
  如下是在google的时候查到的,原版的书文是罗马尼亚(罗曼ia卡塔尔语,大约意思如下,非直译。

 

  参谋链接:

 

索引视图的特别(查询用索引视图代替而不走原始的底蕴表卡塔尔是二个万分昂贵的操作,由此优化器试图透过别的方法便捷转移(生成推行布署卡塔尔
后生可畏经优化器产生了一个相对优化的推行计划,就足以尽快甘休(不必继续生成任何履行布署卡塔 尔(英语:State of Qatar)
主题材料就在于:继续生成此外推行布置的代价要压倒已生成的实践布署的代价
听起来有些别扭,
事先举过这么二个事例,比方身为花3分钟找到贰个对峙优化的奉行布署,那些实施安排成功SQL的奉行必要2分钟
与花10分钟的时候找到八个最优化的实施安插,固然这些实施安排成功SQL的实行恐怕只必要0.5分钟,即使后面一个的实行安顿更优,但全体代价更加大
优化器的入眼指标是飞快找到三个丰盛好的推行陈设(而非总是变化最佳的施行布置卡塔尔国

何以查询会被重写

使用索引视图本人实际不是三个昂贵的操作,
只是与潜在索引视图中相称逻辑查询树确实三个代价很大的一言一动,在询问涉及的视图在优化器优化在此以前已经被举行
于是优化器并不知道你的查询是还是不是未被了索引视图,他见到的只是进展的查询树
那些浅显地讲正是:
让sqlserver知道,叁个询问,能够用索引视图中的结果等价代替视图逻辑中原始的基表,是三个代价非常的大的长河
因为SQL Server依照原有的底工表,生成大器晚成种实践计划之后,就不去推断是还是不是足以用索引视图做等价代替。

  上边大家见到了,对于切合的查询,查询是会被重写的,也正是询问直接基于索引视图来落到实处,那么为啥会一向基于视图来贯彻吗?
  依旧处于质量上的寻思,因为索引视图在创立唯大器晚成的聚焦索引之后,视图就“固化了”原始表的结果集,
  那时候的视图与平时视图最大的差别便是,视图中直接存款和储蓄了数据小编,而非三个询问,
  那时的视图中的数据集,相当于依靠原表的三个“子集”,因为是子集,这么些结果集必然小于原始表,
  那么相仿的询问字段和询问条件,不但能够减去表与表之间的链接操作,且结果集更加小,从那么些视图上询问,
  同等条件下可以越来越快地回来结果,所以查询重写也就简单理解了。
  那个时候只要查询字段和询问条件相通,基于原始表的查询和一向查询索引视图是相似的,如下截图

   当然红皮书里有更详实的牵线,里面索引视图相关的有个别逻辑完结和深入分析

  图片 20

 

 

最后多逼逼一句:
  上边包车型地铁黄皮书实在看不懂,只是谷歌出来讲详细音讯请看这一个仿照效法资料,
  这里只是从气象去推测优化器在面对索引视图的一部分表征,了然到内在机制的生机勃勃对特征,可能潜在的标题,以至对应的解决办法
  这里又提开源软件了,别讲不开源了,即便是开源了,除了钱的要素,在本领上,有几人得以动一下这几个大型软件的主干代码?
  作者并非在黑开源软件啊,只是听有些人会说,开源好哎,有源码怎么什么,认为够装13的,
  本国除了bat等个别几家商铺做过校正源码,做过开源数据库的定制,试问还恐怕有多少家能够校正数据库源码然后上坐蓐应用

 

  最近在学MySQL,继续SQL Server下去只怕踏马要无业了,开源,对大大多厂家来讲,钱才是一个金锭因素吗,尼玛跑题了。

索引视图曾几何时更新

   

  上边说了询问重写,假如基准允许,基于原始表的查询会直接从索引视图上来完成。
  大概有人会不放心,究竟数据都以依附物理表做增加和删除改的,而索引视图中的数据又是大要存在的,这就就能够有三个忧虑,基于视图的查询会不会不规范?
  毕竟是本身好好的叁个询问,你暗中认可给本身一定到索引视图上,查询结果会跟原始表查询同黄金年代吗?

  那么就要求证一下,索引视图中的数据是什么样立异的。
  大家做那样三个测量试验,在基表,也正是DetailTable中查询一条数据,看见到底在试行布署中生出了什么样
  能够料定地察看,不止是王DetailTable中写入了一条数据,同一时候,基于索引视图的询问也往索引视图中写入了一条数据,
  因而得以放心地使用索引视图而毋庸忧郁索引视图中的数据和基表的多少不风流洒脱致的难点。

  可是要在乎的正是,那时的写,是写入基表的同事,也写入了索引视图,对写入的影响是早晚有点的,假诺对写入成效必要超高,就要三思而行一点了。
  其实索引视图也是生龙活虎种冗余写来完毕查询功效的增高的。

图片 21

 

 

变动基于视图的询问

  上边说了,有个别基于视图的询问,是一直定位到视图,从视图中查询结果再次回到的,如下图

 图片 22

只是假若实在不想从视图中询问,作者便是想比较一下原始表和基于视图查询的(成效上的卡塔 尔(阿拉伯语:قطر‎区别,该如何是好?
本条也好办,能够通过查询提醒,将查询来基于原始表完结,也正是实行这些索引视图了
OPTION (EXPAND VIEWS)这些查询提醒就是将视图张开,从原始表进行查询,暗许处境下是不进行的
如截图,可以强制展开索引视图,从原始表查询

图片 23

 

那就是说功效相比较吧?如下截图,粗看起来,那个频率差异依然挺大的,可知,SQL Server私下认可选取下,载效用上恐怕有必然的虚拟的

图片 24

 

  这里从索引视图查询,一是减掉了表之间的join,而是索引视图的结果集更加小,从中筛选相符条件的数额功能就可以越来越好一些。
  所以,私下认可意况下是会从视图查询来对SQL进行询问重写的。  

    索引视图的查询提示:with(noexpand) 强制不开展,OPTION (EXPAND VIEWS)强制张开

总结:

  本文粗浅地分析了SQL Server 中的索引视图以至索引视图带给的查询重写作用,通过索引视图固化基表的结果集,
  可以在自然水准上提升查询功用,尤其是在相当的大的多表join的时候,直接将原来结果存为叁个索引视图,
  通过对索引视图查询来压缩表之间的join和IO来进步效用,不失为意气风发种优化增选。
  需求注意的是,SQL Server的目录视图限定非常多,具体能够参谋链接丛书大概MSND,并非享有的情况都能够使用索引视图来促成。

 

自己技艺水平还很菜,说的异形还请支出,多谢。

 

 

本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:Oracle物化视图的用法与总结,及索引视图与查询

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。