logo标签列表

如何在SQL Server中创建物化视图？

sql-serverdatabase-designindexed-view
134

我打算设计一个数据仓库，听说过“物化视图”。事实上，我想创建一个视图，并且希望在基础表发生更改时它可以自动更新。有人能用查询示例来解释一下吗？

5个回答
177

在SQL Server中，它们被称为索引视图 - 阅读以下白皮书以了解更多背景知识:

基本上，您需要做的就是：

  • 创建一个常规视图
  • 在该视图上创建聚集索引

然后你就完成了！

关键部分是：视图必须满足相当多的约束和限制 - 这些在白皮书中有概述。如果您这样做了，那么这就是全部内容。视图将自动更新，无需维护。

其他资源：

谢谢您的回复。我得到了我想要的东西。我也想了解索引。我想知道在SQL Server中，当我准备好所有表结构时，是否有任何方法可以生成星型模式图？如果是，我该如何创建事实表？ - Deepak
6
在视图上建立聚集索引有许多限制。例如，该视图不能引用其他视图，也不能包含外连接。因此，许多需要更好性能的视图无法使用这种方法。尽管如此，这仍然是一个很好的答案。 - Jeff Wilson
4
正如相关问题中提到的那样，MSDN博客文章“https://blogs.msdn.microsoft.com/ssma/2011/06/20/migrating-oracle-materialized-view-to-sql-server/”强调了材料化视图和索引视图之间的一些关键区别。在我看来，最令人困扰的是无法指定刷新触发器：索引视图会在基本表更新时进行更新，这削弱了使用材料化视图的大部分性能优势。禁止连接、聚合、窗口函数和子查询使得索引视图几乎毫无意义，除非数据很少更改。 - Suncat2000
1
@Suncat2000 - 我同意这些限制和局限性是疯狂的。称它们为物化视图几乎是犯罪。等等，他们没有这么做。不过我对性能问题持有不同意见 - 数据更新的开销与更快的查询和同步结果进行了权衡。 - youcantryreachingme
54

虽然就工程角度而言，索引视图听起来像是每个人都可以用来提高性能的东西，但实际情况却非常不同。由于有太多可以被索引和不可以被索引的限制，我在最需要使用索引视图的地方一直没有成功。

如果视图中有外连接，则无法使用索引视图。此外，通用表达式也不允许...事实上，如果子查询或派生表中有任何排序（例如使用partition by子句），那么你将毫无办法。

这只留下了非常简单的场景可以利用索引视图，但我认为，通过在底层表上创建适当的索引，可以进行优化。

如果有人可以分享实际应用索引视图的经验，并且在没有它们的情况下无法完成任务，那我会非常高兴听到。

实际上，我曾经使用过索引视图（仅一次）来分区全文搜索索引。事实上，FTS索引无法进行分区，但是可以在同一张表的多个视图上创建单独的索引。虽然这是最后的选择。 - areyesram
8
请确保在使用索引视图的查询中添加(NOEXPAND)提示。您会注意到区别。使用索引视图的优势是限制记录选择，而不是“正确地为表建立索引”。否则，就会与您所说的一样。 - ajeh
3
是的，NOEXPAND 的重要性不容忽视！ - Simon_Weaver
@ajeh - 有限的记录选择并不是唯一的区别。我认为重点在于，在您的基础表上可能只有一个唯一的聚集索引。视图允许您针对相同的基础数据构建第二个唯一的聚集索引-因此，根据运行的查询，为查询优化器提供不同的检索数据的选择。还要记住，查询优化器可能会选择使用您的索引视图来满足系统内完全不相关的查询-从而提高整个系统的性能。 - youcantryreachingme
“真实场景” - 它们对于聚合查询非常有用。与必须扫描和聚合许多详细行不同，查询只需从可能小得多的预先计算结果中读取。 - Martin Smith
22

您可能需要更多了解什么是Materialized View。在Oracle中，这是一个由多个元素组成的对象，当您尝试在其他地方构建它时。

MVIEW本质上是来自另一个源的数据快照。与视图不同，查询视图时不会找到数据，而是以表格形式存储在本地。MVIEW使用后台过程定期刷新或在源数据更改时启动。Oracle允许全面或部分刷新。

在SQL Server中，我将使用以下内容创建基本的MVIEW以定期（完整）刷新。

首先是视图。对于大多数人来说，这应该很容易，因为视图在任何数据库中都很常见。

接下来是表格。这应该与视图在列和数据方面相同。这将存储视图数据的快照。

然后是一个过程，它截断表格并基于视图中的当前数据重新加载它。

最后，是触发该过程开始工作的作业。

其他所有内容都是实验。

6
你对SQL Server的评论是不正确的--在Oracle和SQL Server中，物化视图是非常不同的东西。在SQL Server中，一个带有唯一聚集索引的视图(也称为"物化视图")不能由用户更新，也不会存储在单独的用户创建的表中--它总是在更新期间由引擎更新，并且永远不会失步。不需要作业来存储数据的快照。 - ErikE
13
OP要求的可以很容易地通过创建索引视图来实现。这是SQL Server本机提供给Oracle物化视图最接近的方法。然而，如果您想/需要完全复制Oracle MVIEW的工作方式，Jason是正确的。Jason的方法还有助于在与Oracle MVIEW相同的场景下使用 - 例如，在进行报表表的非工作时间刷新时，您更关心数据库的负载而不是视图的更新情况（例如仅报告昨天的数字…）。 - user593806
6

当索引视图不可用且快速更新不是必需时，您可以创建一个 hack cache 表：

select * into cachetablename from myviewname
alter table cachetablename add primary key (columns)
-- OR alter table cachetablename add rid bigint identity primary key
create index...

然后使用sp_rename重命名视图/表或更改任何查询或其他引用它的视图以将其指向缓存表。

按照每日/夜间/每周等时间表进行刷新。

begin transaction
truncate table cachetablename
insert into cachetablename select * from viewname
commit transaction

NB: 这会占用空间，也会在日志中记录下来。最好用于计算缓慢的小型数据集。可能需要重新设计，在外部视图中先消除“简单但大型”的列。

4
这是一个完全相关的，被遗忘的替代品！干杯。PS - 你的第二个链接现在无论如何都会重定向到MS网站上的第一个URL。 - youcantryreachingme
网页内容由stack overflow 提供, 点击上面的
可以查看英文原文，
原文链接