什么时候使用视图而不是表?

When should a View actually be used over an actual Table? What gains should I expect this to produce?

视图何时应该在实际的表上实际使用?我期望这能带来什么收获?

Overall, what are the advantages of using a view over a table? Shouldn't I design the table in the way the view should look like in the first place?

总的来说，在表上使用视图的优点是什么?难道我不该以视图最初的样子来设计这个表吗?

8 个解决方案

#1

Oh there are many differences you will need to consider

哦，你需要考虑很多不同之处

Views for selection:

视图选择:

1. Views provide abstraction over tables. You can add/remove fields easily in a view without modifying your underlying schema
2. 视图提供了对表的抽象。您可以在视图中轻松地添加/删除字段，而无需修改基础架构。
3. Views can model complex joins easily.
4. 视图可以很容易地建模复杂的连接。
5. Views can hide database-specific stuff from you. E.g. if you need to do some checks using Oracles SYS_CONTEXT function or many other things
6. 视图可以隐藏特定于数据库的东西。如果您需要使用oracle SYS_CONTEXT函数或其他很多东西进行检查。
7. You can easily manage your GRANTS directly on views, rather than the actual tables. It's easier to manage if you know a certain user may only access a view.
8. 您可以轻松地直接管理视图上的授权，而不是实际的表。如果您知道某个用户可能只访问某个视图，那么管理起来会更容易。
9. Views can help you with backwards compatibility. You can change the underlying schema, but the views can hide those facts from a certain client.
10. 视图可以帮助您实现向后兼容性。您可以更改底层模式，但是视图可以向某个客户隐藏这些事实。

Views for insertion/updates:

视图插入/更新:

1. You can handle security issues with views by using such functionality as Oracle's "WITH CHECK OPTION" clause directly in the view
2. 您可以通过在视图中直接使用Oracle的“with CHECK OPTION”子句等功能来处理视图的安全问题

Drawbacks

缺点

1. You lose information about relations (primary keys, foreign keys)
2. 您将丢失有关关系的信息(主键、外键)
3. It's not obvious whether you will be able to insert/update a view, because the view hides its underlying joins from you
4. 您是否能够插入/更新视图并不明显，因为视图隐藏了它的底层连接

#2

Views can:

视图可以:

• Simplify a complex table structure
• 简化复杂的表结构
• Simplify your security model by allowing you to filter sensitive data and assign permissions in a simpler fashion
• 通过允许您过滤敏感数据并以更简单的方式分配权限，简化您的安全模型
• Allow you to change the logic and behavior without changing the output structure (the output remains the same but the underlying SELECT could change significantly)
• 允许您在不改变输出结构的情况下更改逻辑和行为(输出保持不变，但底层的选择可能会发生显著变化)
• Increase performance (Sql Server Indexed Views)
• 提高性能(Sql Server索引视图)
• Offer specific query optimization with the view that might be difficult to glean otherwise
• 提供特定的查询优化与视图，否则很难收集

And you should not design tables to match views. Your base model should concern itself with efficient storage and retrieval of the data. Views are partly a tool that mitigates the complexities that arise from an efficient, normalized model by allowing you to abstract that complexity.

而且不应该设计与视图匹配的表。您的基本模型应该关注数据的有效存储和检索。视图在一定程度上是一种工具，它通过允许您抽象这种复杂性来减轻效率和规范化模型带来的复杂性。

Also, asking "what are the advantages of using a view over a table? " is not a great comparison. You can't go without tables, but you can do without views. They each exist for a very different reason. Tables are the concrete model and Views are an abstracted, well, View.

另外，要问“使用视图胜过使用表有什么好处?”这不是一个很好的比喻。你不能没有表格，但你可以没有视图。它们存在的原因各不相同。表是具体的模型，视图是抽象的视图。

#3

Views are acceptable when you need to ensure that complex logic is followed every time. For instance, we have a view that creates the raw data needed for all financial reporting. By having all reports use this view, everyone is working from the same data set, rather than one report using one set of joins and another forgetting to use one which gives different results.

当您需要确保每次都遵循复杂的逻辑时，视图是可以接受的。例如，我们有一个创建所有财务报告所需的原始数据的视图。通过让所有的报告都使用这个视图，每个人都在使用相同的数据集，而不是使用一组连接的报告，而另一个则忘记使用不同的结果。

Views are acceptable when you want to restrict users to a particular subset of data. For instance, if you do not delete records but only mark the current one as active and the older versions as inactive, you want a view to use to select only the active records. This prevents people from forgetting to put the where clause in the query and getting bad results.

当您希望将用户限制在特定的数据子集时，视图是可以接受的。例如，如果不删除记录，只将当前记录标记为活动记录，将旧版本标记为活动记录，则希望使用视图只选择活动记录。这可以防止人们忘记在查询中放置where子句并得到糟糕的结果。

Views can be used to ensure that users only have access to a set of records - for instance, a view of the tables for a particular client and no security rights on the tables can mean that the users for that client can only ever see the data for that client.

视图可以用来确保用户只能访问一组记录——例如,为特定的客户端和一个视图的表上没有保障权利的表可能意味着该客户机的用户只能看到客户的数据。

Views are very helpful when refactoring databases.

视图在重构数据库时非常有用。

Views are not acceptable when you use views to call views which can result in horrible performance (at least in SQL Server). We almost lost a multimillion dollar client because someone chose to abstract the database that way and performance was horrendous and timeouts frequent. We had to pay for the fix too, not the client, as the performance issue was completely our fault. When views call views, they have to completely generate the underlying view. I have seen this where the view called a view which called a view and so many millions of records were generated in order to see the three the user ultimately needed. I remember one of these views took 8 minutes to do a simple count(*) of the records. Views calling views are an extremely poor idea.

当您使用视图调用视图时，视图是不可接受的，这会导致糟糕的性能(至少在SQL Server中)。我们几乎失去了一个价值数百万美元的客户端，因为有人选择以这种方式抽象数据库，而且性能非常糟糕，而且经常超时。我们也必须为修复买单，而不是客户，因为性能问题完全是我们的错。当视图调用视图时，它们必须完全生成底层视图。我曾经见过这个视图叫做视图，它叫做视图，为了看到用户最终需要的三个，生成了数百万条记录。我记得其中一个视图花了8分钟做了一个简单的记录计数(*)。视图调用视图是一个非常糟糕的想法。

Views are often a bad idea to use to update records as usually you can only update fields from the same table (again this is SQL Server, other databases may vary). If that's the case, it makes more sense to directly update the tables anyway so that you know which fields are available.

视图通常是一个坏主意，用来更新记录，因为通常只能从同一个表更新字段(这是SQL Server，其他数据库可能不同)。如果是这样，那么直接更新表就更有意义了，这样您就知道哪些字段是可用的。

#4

Views are handy when you need to select from several tables, or just to get a subset of a table.

当您需要从多个表中进行选择，或者只是获取表的一个子集时，视图非常方便。

You should design your tables in such a way that your database is well normalized (minimum duplication). This can make querying somewhat difficult.

您应该以这样一种方式设计您的表，使您的数据库具有良好的标准化(最小复制)。这使得查询变得有些困难。

Views are a bit of separation, allowing you to view the data in the tables differently than they are stored.

视图是一种分离，允许您以不同于存储的方式查看表中的数据。

#5

A common practice is to hide joins in a view to present the user a more denormalized data model. Other uses involve security (for example by hiding certain columns and/or rows) or performance (in case of materialized views)

一种常见的做法是将连接隐藏到视图中，以向用户显示一个更非规范化的数据模型。其他用途包括安全性(例如隐藏某些列和/或行)或性能(如物化视图)

#6

You should design your table WITHOUT considering the views.
Apart from saving joins and conditions, Views do have a performance advantage: SQL Server may calculate and save its execution plan in the view, and therefore make it faster than "on the fly" SQL statements.
View may also ease your work regarding user access at field level.

您应该在不考虑视图的情况下设计您的表。除了保存连接和条件之外，视图还有一个性能优势:SQL Server可以在视图中计算和保存它的执行计划，因此比“on the fly”SQL语句要快。视图还可以简化您在字段级上的用户访问工作。

#7

First of all as the name suggests a view is immutable. thats because a view is nothing other than a virtual table created from a stored query in the DB. Because of this you have some characteristics of views:

首先，顾名思义，视图是不可变的。这是因为视图只不过是从数据库中存储的查询创建的虚拟表。正因为如此，你有一些观点的特点:

• you can show only a subset of the data
• 您只能显示数据的一个子集
• you can join multiple tables into a single view
• 您可以将多个表连接到一个视图。
• you can aggregate data in a view (select count)
• 可以在视图中聚合数据(选择count)
• view dont actually hold data, they dont need any tablespace since they are virtual aggregations of underlying tables
• 视图实际上不包含数据，它们不需要任何表空间，因为它们是底层表的虚拟聚合

so there are a gazillion of use cases for which views are better fitted than tables, just think about only displaying active users on a website. a view would be better because you operate only on a subset of the data which actually is in your DB (active and inactive users)

因此，有大量的用例认为视图比表更合适，只需考虑在网站上显示活动用户即可。视图会更好，因为您只对DB(活动用户和非活动用户)中的数据子集进行操作

check out this article

看看这篇文章

hope this helped..

希望这帮. .

#8

According to Wikipedia,

根据*,

Views can provide many advantages over tables:

• Views can represent a subset of the data contained in a table.
• 视图可以表示表中包含的数据的子集。

• Views can limit the degree of exposure of the underlying tables to the outer world: a given user may have permission to query the view, while denied access to the rest of the base table.

  视图可以限制底层表对外部世界的公开程度:给定的用户可能拥有查询视图的权限，而拒绝访问基表的其他部分。

• Views can join and simplify multiple tables into a single virtual table.

  视图可以将多个表合并并简化为一个虚拟表。

• Views can act as aggregated tables, where the database engine aggregates data (sum, average, etc.) and presents the calculated results as part of the data.

  视图可以充当聚合表，其中数据库引擎聚合数据(sum、average等)，并将计算结果作为数据的一部分呈现出来。

• Views can hide the complexity of data. For example, a view could appear as Sales2000 or Sales2001, transparently partitioning the actual underlying table.

  视图可以隐藏数据的复杂性。例如，视图可以显示为Sales2000或Sales2001，透明地划分实际的底层表。

• Views take very little space to store; the database contains only the definition of a view, not a copy of all the data that it presents.

  视图存储空间非常小;数据库只包含视图的定义，而不包含视图呈现的所有数据的副本。

• Views can provide extra security, depending on the SQL engine used.

  视图可以提供额外的安全性，这取决于使用的SQL引擎。