ch8_索引和视图

响其中一张基本表； 不能在包含计算值、函数、聚集函数的视图列上修改； 权限，完整性约束。 例如：不能更新的视图 MovieProds：插入一个片名 一个制片人 不知证书号 S_G ( Sno , Gavg ) ：无法将对带有聚集函数定义的视图 的更新转换成对基本表SC的更新。

DBMS 执行视图更新
2+8p1+8p2 4+6p2 4+6p1 6-2p1-2p2
根据p1, p2的值，结合平均操作代价考虑如何建索引？
机械三P209 8.3.1 P213 8.4.1 8.4.2

习题：考虑关系Ships (name, class, launched)上的索引，这个关
系来自以前关于Battleships的习题，假定： 1. name是键 2. 存储关系Ships的磁盘页超过50 3. 关系按照类别class进行聚合，这样只需一次磁盘访问操作就 可以找到给定类别的船 4. 平均来说，每个类别包含5艘船，每年有25艘船下水 5. 在该关系上如下形式的查询SELECT * FROM Ships WHERE name=n 的概率为p1 6. 在该关系上如下形式的查询SELECT * FROM Ships WHERE class=c的概率为p2 7. 在该关系上如下形式的查询SELECT * FROM Ships WHERE launched=y的概率为p3 8. 插入一个新元组的概率为1-p1-p2-p3 假设访问索引和为插入查找空块的代价与上面例题相同，分 别考虑无索引、和在name、class、launched上分别以及组合建立 索引。对每一种索引组合，估算操作的平均代价。用关于p1,p2和 p3的函数说明最佳的索引方式是什么？
其等价的对基本表Movies的插入操作为：
注意：视图的插入实际上是基表的插入，再映射。由于插入的 元组studioname值为空，则无法再由基表反推出视图中新插入 的元组，所以插入错误！
例5.41的一种解决办法：
length
genre producerC#
默认值/NULL

从可更新视图中删除/修改元组
首先进行有效性检查，检查涉及的表、视图是否存在；
如存在从系统表中取出视图定义，把定义中的SELECT语
句与对视图进行的更新语句结合起来，转换成对基本表的 更新； 最后再执行这个修正后的更新。
行列子集视图ParamountMovies满足SQL的可更新条件，假设 对视图进行插入元组的操作：
2

索引
关系属性A上的索引是一种有助于包含比较A和
常量的查询的数据结构
建立索引的动机：当关系很大时，通过扫描关系中所 有元组来找出那些匹配给定查询条件的元组的代价太 高。 索引的声明 CREATE INDEX 索引名 ON 关系名(属性列表)


多重索引中的属性排列顺序决定查询依赖顺序
删除索引 DROP INDEX 索引名 索引不是任何SQL标准的内容，但商用DBMS中都提 供索引机制

索引的选择
设计索引要考虑的两个因素

有利于查询，但提高了更新操作的复杂性和消耗时间 代价 查询比更新多/连接条件中的属性/where子句中和常量 比较的属性，索引效率会更高

计算最佳索引
估算更新和查询数量的相对比例
索引占用磁盘空间(一般是独立块)，访问和更新索引需
要对磁盘块读写
例：仔细读懂题意
8.2 视图定义

视图和表
用CREATE
TABLE定义的实际存储在数据库中的关系，
又称“基本关系”或“基本表”；
视图并不以物理的形式存在，通过类似查询的表达方式
定义。

视图的定义
CREATE VIEW <视图名> AS <视图定义>;
其中，视图定义是一个SQL查询，可以涉及多个关系

P201例8.1/P211例5.37 为Paramount制片公司制作 的电影的名称和年份定义视图
1.
SELECT name FROM MovieExec WHERE cert#=23456; INSERT INTO MovieProd VALUES („Kill Bill‟, 2003, „Quentin Tarantino‟)
2.从Movies中删除一部电影，电影名为“Dumb&Dumber”,制作年 份为1994年，则DBMS只需要使用如下语句从MovieProd中删除 一个元组： DELETE FROM MovieProd WHERE title=„Dumb & Dumber‟ AND year=1994; 3. 插入一个元组到MovieExec中，其中cert#等于34567，name值为 “Max Bialystock”. INSERT INTO MovieProd SELECT title, year, „Max Bialystock‟ FROM Movies WHERE producerC#=34567; 4.从MovieExec中删除一个元组cert#=45678的元组，则DBMS也要 将MovieProd中的producerC#值为45678的元组删去。 DELETE FROM MovieProd WHERE (title, year) IN ( SELECT title, year FROM Movies) WHERE producerC#=45678;
物化视图

当一个视图经常被使用，则可以将它物化(materialize)，即 任何时间都保存它的值。 CREATE <视图定义> MATERIALIZED VIEW <视图名> AS

当基本表发生变化时，每次必须重新计算部分物化视图， 因此维护物化视图也要一定的代价。
例： 建立物化视图表示电影及其制片人。
4
例1:在单一属性上建索引
CREATE INDEX YearIndex ON Movies (year) 索引名 关系名 属性
例2: 在一组属性上建索引
CREATE INDEX KeyIndex ON Movies (title, year) 索引名 关系名 属性组
5

索引的利弊
提高该属性值已知的查询的速度 占空间，增删改时维护索引增加系统开销

索引的自动选择---贪心策略
初始情况，当没有选择索引时，逐一评估各候选索引
带来的收益。如果至少有一个索引可以带来正面收益 (比如能够减少查询的平均执行时间)，那么选择该索引； 然后再假设第一步中选出的索引是真实存在的情况下， 评估剩下来的候选索引，同样选出能够带来最大正面 收益的索引； 重复以上的步骤，直至没有索引能够带来正面收益。
例5.44 再看例5.41问题出在哪里？当对视图插入元组时，由 于只有t,y属性值，没有studioname属性值，而studioname 属性是参与构造从基本表到视图的一个必须属性，不能为 空，所以插入时必须为其指定正确值用于基本表同步。

不可更新视图
元组(„Greatest Show on Earth‟, „Cecil B.DeMille‟)的插入 操作不能完成。
删除/修改操作也传递到基本关系上执行，将视图的
WHERE子句中的条件用AND添加到删除/修改操作的 WHERE子句中 例5.42 删除视图中的元组，转成对基表元组的删除
例5.43 修改视图中的元组，转换成基表的修改

替换触发器
INSTEAD
OF代替AFTER或BEFORE，当一个事件唤醒 触发器时，触发器的操作将会取代事件本身而被执行， 可将视图更新操作替换指定成对基本表操作

物化视图的维护

对于某些简单的物化视图的更改是增量式的，即不需要从 定义重新计算和构造整个视图，只需要少量对基本表的查 询加上一些对物化视图的修改，就可以使得对于基本表的 更新操作能够反应到视图上。
当插入一部新电影到Movies中，则MovieProd视图的更新 例：假设需要再Movies中插入一部新的电影，片名“Kill Bill”，制 作年份2003年，制片人证书号producerC#=23456。则只需要在 MovieExec中查找cert#=23456的元组，找到该制片人的名字，据此对 视图MovieProd进行更新。

索引的分类
UNIQUE（唯一性）

被索引列在任意两行元组中的取值不能出现重复 行的物理顺序和索引顺序相同； 一表仅一个； 更新时移动数据
CLUSTERED（聚簇式）

NONCLUSTERED（非聚簇式）：

默认

包含索引值和指向数据行的指针，不要求行的物理顺序和索引顺 序相同； 一表可多个
给出三种操作的代价：Q1（给定影星信息的查询）、 Q2（给定电影信息的查询）和I（插入操作）。 假定执行Q1的时间比例为p1, 执行Q2的时间比例为p2, 因此执行I的时间比例为1-p1-p2
Action Q1 Q2 I
Average
No Index Star Index Movie Index Both Index 10 4 10 4 10 10 4 4 2 4 4 6
第8章 索引和视图 (清华版 P208 5.7.7和 P211 5.8)
8.1 SQL中的索引 P208

索引的意义
SELECT * FROM Movies WHERE year=1990 AND studioName=„Disney‟
假设Movies中共有10 000个元组，其中200个是 1990年制作，设想可否只从200个1990年制作的影片 中查询‘Disney‟公司的影片? 解决办法：以年代建索引加快查询速度
各种情况下各种动作的操作代价怎么算出的？ 时间比例 根据图中的结果，分析在各种前提下采用何种建