I am going to design a DW and I heard about materialized views. Actually I want to create a view and it should update automatically when base tables are changed. Can anyone explain with an query example..
我打算设计一个DW，我听说了物化视图。实际上，我想创建一个视图，当基表发生变化时，它应该自动更新。谁能用查询示例进行解释?
5 个解决方案
#1
103
They're called indexed views in SQL Server - read these white papers for more background:
它们在SQL Server中被称为索引视图——阅读这些白皮书了解更多的背景:
- Creating an Indexed View
- 创建一个索引视图
- Improving Performance with SQL Server 2008 Indexed Views
- 使用SQL Server 2008索引视图改进性能
Basically, all you need to do is:
基本上，你需要做的就是:
- create a regular view
- 创建一个普通视图
- create a clustered index on that view
- 在该视图上创建集群索引
and you're done!
和你做的!
The tricky part is: the view has to satisfy quite a number of constraints and limitations - those are outlined in the white paper. If you do this - that's all there is. The view is being updated automatically, no maintenance needed.
棘手的部分是:视图必须满足相当多的约束和限制——这些都在白皮书中概述。如果你这么做了，这就是全部。视图正在自动更新，不需要维护。
Additional resources:
额外的资源:
- Creating and Optimizing Views in SQL Server
- 在SQL Server中创建和优化视图
- SQL Server Indexed Views
- SQL Server索引视图
#2
30
Although purely from engineering perspective, indexed views sound like something everybody could use to improve performance but the real life scenario is very different. I have been unsuccessful is using indexed views where I most need them because of too many restrictions on what can be indexed and what cannot.
虽然纯粹从工程的角度来看，索引视图听起来像是每个人都可以用来提高性能的东西，但实际情况却大不相同。我在最需要索引视图的地方使用了索引视图，但没有成功，因为对哪些可以被索引，哪些不能。
If you have outer joins in the views, they cannot be used. Also, common table expressions are not allowed... In fact if you have any ordering in subselects or derived tables (such as with partition by clause), you are out of luck too.
如果视图中有外部连接，则不能使用它们。同样，公共表表达式是不允许的……事实上，如果在子选择或派生表(如使用partition by子句)中有任何排序，那么您也不太走运。
That leaves only very simple scenarios to be utilizing indexed views, something in my opinion can be optimized by creating proper indexes on underlying tables anyway.
这只留下了非常简单的场景来利用索引视图，在我看来，可以通过在底层表上创建适当的索引来进行优化。
I will be thrilled to hear some real life scenarios where people have actually used indexed views to their benefit and could not have done without them
我很高兴听到一些现实生活中的场景，在这些场景中，人们实际上已经使用了索引视图，如果没有它们，就不可能实现
#3
13
You might need a bit more background on what a Materialized View actually is. In Oracle these are an object that consists of a number of elements when you try to build it elsewhere.
您可能需要更多关于物化视图的背景知识。在Oracle中，当您尝试在其他地方构建时，这些对象包含许多元素。
An MVIEW is essentially a snapshot of data from another source. Unlike a view the data is not found when you query the view it is stored locally in a form of table. The MVIEW is refreshed using a background procedure that kicks off at regular intervals or when the source data changes. Oracle allows for full or partial refreshes.
MVIEW实质上是来自另一个源的数据快照。与视图不同的是，当您查询视图时，数据不会在本地以表的形式存储。MVIEW将使用一个后台过程进行刷新，该过程在定期启动或源数据更改时启动。Oracle允许全部或部分刷新。
In SQL Server, I would use the following to create a basic MVIEW to (complete) refresh regularly.
在SQL Server中，我将使用以下命令创建一个基本的MVIEW来定期(完成)刷新。
First, a view. This should be easy for most since views are quite common in any database Next, a table. This should be identical to the view in columns and data. This will store a snapshot of the view data. Then, a procedure that truncates the table, and reloads it based on the current data in the view. Finally, a job that triggers the procedure to start it's work.
首先,一个视图。这对大多数人来说应该很容易，因为视图在任何数据库中都很常见，比如下一个表。这应该与列和数据中的视图相同。这将存储视图数据的快照。然后，一个将表截断并基于视图中的当前数据重新加载的过程。最后，启动该过程的作业开始工作。
Everything else is experimentation.
其他的都是实验。
#4
2
When indexed view is not an option, and quick updates are not necessary, you can create a hack cache table:
当索引视图不是选项，并且不需要快速更新时，您可以创建一个hack缓存表:
select * into cachetablename from myviewname
alter table cachetablename add primary key (columns)
-- OR alter table cachetablename add rid bigint identity primary key
create index...
then sp_rename view/table or change any queries or other views that reference it to point to the cache table.
然后，sp_rename视图/表或更改任何查询或其他引用它指向缓存表的视图。
schedule daily/nightly/weekly/whatnot refresh like
安排每日/每夜/每周/的刷新
begin transaction
truncate table cachetablename
insert into cachetablename select * from viewname
commit transaction
NB: this will eat space, also in your tx logs. Best used for small datasets that are slow to compute. Maybe refactor to eliminate "easy but large" columns first into an outer view.
NB:这会占用空间，也会占用你的tx日志。最好用于小数据集，而这些数据集的计算速度很慢。也许重构可以首先将“容易但很大”的列排除到外部视图中。
#5
1
For MS T-SQL Server, I suggest looking into creating an index with the "include" statement. Uniqueness is not required, neither is the physical sorting of data associated with a clustered index. The "Index ... Include ()" creates a separate physical data storage automatically maintained by the system. It is conceptually very similar to an Oracle Materialized View.
对于MS T-SQL Server，我建议考虑使用“include”语句创建索引。不需要惟一性，与群集索引相关联的数据的物理排序也不需要惟一性。“指数……Include()“创建一个由系统自动维护的单独的物理数据存储。它在概念上非常类似于Oracle物化视图。
https://msdn.microsoft.com/en-us/library/ms190806.aspx
https://msdn.microsoft.com/en-us/library/ms190806.aspx
https://technet.microsoft.com/en-us/library/ms189607(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms189607(v = sql.105). aspx
#1
103
They're called indexed views in SQL Server - read these white papers for more background:
它们在SQL Server中被称为索引视图——阅读这些白皮书了解更多的背景:
- Creating an Indexed View
- 创建一个索引视图
- Improving Performance with SQL Server 2008 Indexed Views
- 使用SQL Server 2008索引视图改进性能
Basically, all you need to do is:
基本上，你需要做的就是:
- create a regular view
- 创建一个普通视图
- create a clustered index on that view
- 在该视图上创建集群索引
and you're done!
和你做的!
The tricky part is: the view has to satisfy quite a number of constraints and limitations - those are outlined in the white paper. If you do this - that's all there is. The view is being updated automatically, no maintenance needed.
棘手的部分是:视图必须满足相当多的约束和限制——这些都在白皮书中概述。如果你这么做了，这就是全部。视图正在自动更新，不需要维护。
Additional resources:
额外的资源:
- Creating and Optimizing Views in SQL Server
- 在SQL Server中创建和优化视图
- SQL Server Indexed Views
- SQL Server索引视图
#2
30
Although purely from engineering perspective, indexed views sound like something everybody could use to improve performance but the real life scenario is very different. I have been unsuccessful is using indexed views where I most need them because of too many restrictions on what can be indexed and what cannot.
虽然纯粹从工程的角度来看，索引视图听起来像是每个人都可以用来提高性能的东西，但实际情况却大不相同。我在最需要索引视图的地方使用了索引视图，但没有成功，因为对哪些可以被索引，哪些不能。
If you have outer joins in the views, they cannot be used. Also, common table expressions are not allowed... In fact if you have any ordering in subselects or derived tables (such as with partition by clause), you are out of luck too.
如果视图中有外部连接，则不能使用它们。同样，公共表表达式是不允许的……事实上，如果在子选择或派生表(如使用partition by子句)中有任何排序，那么您也不太走运。
That leaves only very simple scenarios to be utilizing indexed views, something in my opinion can be optimized by creating proper indexes on underlying tables anyway.
这只留下了非常简单的场景来利用索引视图，在我看来，可以通过在底层表上创建适当的索引来进行优化。
I will be thrilled to hear some real life scenarios where people have actually used indexed views to their benefit and could not have done without them
我很高兴听到一些现实生活中的场景，在这些场景中，人们实际上已经使用了索引视图，如果没有它们，就不可能实现
#3
13
You might need a bit more background on what a Materialized View actually is. In Oracle these are an object that consists of a number of elements when you try to build it elsewhere.
您可能需要更多关于物化视图的背景知识。在Oracle中，当您尝试在其他地方构建时，这些对象包含许多元素。
An MVIEW is essentially a snapshot of data from another source. Unlike a view the data is not found when you query the view it is stored locally in a form of table. The MVIEW is refreshed using a background procedure that kicks off at regular intervals or when the source data changes. Oracle allows for full or partial refreshes.
MVIEW实质上是来自另一个源的数据快照。与视图不同的是，当您查询视图时，数据不会在本地以表的形式存储。MVIEW将使用一个后台过程进行刷新，该过程在定期启动或源数据更改时启动。Oracle允许全部或部分刷新。
In SQL Server, I would use the following to create a basic MVIEW to (complete) refresh regularly.
在SQL Server中，我将使用以下命令创建一个基本的MVIEW来定期(完成)刷新。
First, a view. This should be easy for most since views are quite common in any database Next, a table. This should be identical to the view in columns and data. This will store a snapshot of the view data. Then, a procedure that truncates the table, and reloads it based on the current data in the view. Finally, a job that triggers the procedure to start it's work.
首先,一个视图。这对大多数人来说应该很容易，因为视图在任何数据库中都很常见，比如下一个表。这应该与列和数据中的视图相同。这将存储视图数据的快照。然后，一个将表截断并基于视图中的当前数据重新加载的过程。最后，启动该过程的作业开始工作。
Everything else is experimentation.
其他的都是实验。
#4
2
When indexed view is not an option, and quick updates are not necessary, you can create a hack cache table:
当索引视图不是选项，并且不需要快速更新时，您可以创建一个hack缓存表:
select * into cachetablename from myviewname
alter table cachetablename add primary key (columns)
-- OR alter table cachetablename add rid bigint identity primary key
create index...
then sp_rename view/table or change any queries or other views that reference it to point to the cache table.
然后，sp_rename视图/表或更改任何查询或其他引用它指向缓存表的视图。
schedule daily/nightly/weekly/whatnot refresh like
安排每日/每夜/每周/的刷新
begin transaction
truncate table cachetablename
insert into cachetablename select * from viewname
commit transaction
NB: this will eat space, also in your tx logs. Best used for small datasets that are slow to compute. Maybe refactor to eliminate "easy but large" columns first into an outer view.
NB:这会占用空间，也会占用你的tx日志。最好用于小数据集，而这些数据集的计算速度很慢。也许重构可以首先将“容易但很大”的列排除到外部视图中。
#5
1
For MS T-SQL Server, I suggest looking into creating an index with the "include" statement. Uniqueness is not required, neither is the physical sorting of data associated with a clustered index. The "Index ... Include ()" creates a separate physical data storage automatically maintained by the system. It is conceptually very similar to an Oracle Materialized View.
对于MS T-SQL Server，我建议考虑使用“include”语句创建索引。不需要惟一性，与群集索引相关联的数据的物理排序也不需要惟一性。“指数……Include()“创建一个由系统自动维护的单独的物理数据存储。它在概念上非常类似于Oracle物化视图。
https://msdn.microsoft.com/en-us/library/ms190806.aspx
https://msdn.microsoft.com/en-us/library/ms190806.aspx
https://technet.microsoft.com/en-us/library/ms189607(v=sql.105).aspx
https://technet.microsoft.com/en-us/library/ms189607(v = sql.105). aspx