索引如何在视图上工作？
有人可以用简单的英语向我解释视图上的索引是如何工作的吗？我对表上的索引有一个相当简单的理解；为视图建立索引与让基础表上的索引自然地发挥作用有何不同？
Can someone please explain to me in simple English how an index on a view works? I have a fairly simple understanding of indexes on tables; how would indexing a view work differently from just letting the indexes on the underlying tables do their thing naturally?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
假设您有一个将表限制为某些行的视图：
现在，如果您在此视图上创建索引，则该索引仅包含热门产品。您可以将其与将视图结果存储在临时表中进行比较。这对于具有多个连接的复杂查询非常有用；基本上他们的输出被缓存。
索引视图的一大缺点是每次基础表数据更改时都会重新创建索引视图。这将索引视图的使用限制为不经常更改的数据，通常在数据仓库或商业智能环境中。
Say you have a view that limits the table to certain rows:
Now if you create an index on this view, the index only contains hot products. You can compare it to storing the result of the view in a temporary table. This can be very useful for complicated queries with multiple joins; basically their output is cached.
The big disadvantage of indexed views is that they are recreated every time the underlying table data changes. That restricts the use of indexed views to data that does not change often, typically in a data warehouse or business intelligence environment.
请参阅 http://msdn.microsoft.com/en-us /library/aa258260(SQL.80).aspx
视图从正确的视图转换为表。视图定义用于更新该表。
Oracle 将这些称为“物化视图”。
See http://msdn.microsoft.com/en-us/library/aa258260(SQL.80).aspx
The view is transformed from a proper view into a table. The view definition is used to update this table.
Oracle calls these "materialized views".
视图本身不是真实的或“持久的”，并且没有性能优势。这只是一个宏已扩展。
添加索引并且它物理存在（持久化），因此优化器将考虑使用它。那么它就不是宏了。
我确信 Oracle 将它们称为“物化视图”，这是一个更好的名字。
相关的仅供参考：计算列有一个 PERSISTED 选项可以做同样的事情......
A view by itself is not real or "persisted", and has no performance benefit. It's simply a macro that's expanded.
Add an index and it physically exists (persisted), so the optimiser will consider using it. It's not a macro then.
I'm sure Oracle calls them "materialised views" which is a better name.
A related FYI: a computed column has a PERSISTED option that does the same thing...