oracle 表与视图的性能
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/6524550/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Performance of Tables vs. Views
提问by BillMan
Recently started working with a database in which the convention is to create a view for every table. If you assume that there is a one to one mapping between tables and views, I was wondering if anyone could tell me the performance impacts of doing something like this. BTW, this is on Oracle.
最近开始使用一个数据库，其中约定为每个表创建一个视图。如果您假设表和视图之间存在一对一映射，我想知道是否有人可以告诉我执行此类操作对性能的影响。顺便说一句，这是在 Oracle 上。
回答by OMG Ponies
Assuming the question is about non-materialized views -- Really depends on the query that the view is based on, and what is being done to it. Sometimes, the predicates can be pushed into the view query by the optimizer. If not, then it wouldn't be as good as against the table itself. Views are built on top of tables -- why would you expect that the performance would be better?
假设问题与非物化视图有关——实际上取决于视图所基于的查询以及对其执行的操作。有时，优化器可以将谓词推送到视图查询中。如果不是，那么它不会像对抗桌子本身那样好。视图建立在表之上——你为什么期望性能会更好？
Layering views, where you build one view on top of another, is a bad practice because you won't know about issues until run time. It's also less of a chance that predicate pushing will occur with layered views.
分层视图（将一个视图构建在另一个视图之上）是一种不好的做法，因为直到运行时您才会知道问题。分层视图也不太可能发生谓词推送。
Views can also be updateable -- they aren't a reliable means to restricting access to resources if someone has INSERT/UPDATE/DELETE privileges on the underlying tables.
视图也可以是可更新的——如果有人对基础表具有 INSERT/UPDATE/DELETE 权限，它们就不是限制对资源访问的可靠方法。
Materialized views are as good as tables, but are notoriously restrictive in what they support.
物化视图与表一样好，但众所周知，它们支持的内容受到限制。
回答by Stephanie Page
You don't explain what you're doing in the views? A 1:1 with the tables sounds like you are using the views more like synonyms than a view. IOW, are the views = "SELECT * FROM table", then you'll see no performance hit except on hard parse.
你没有在视图中解释你在做什么？与表的 1:1 听起来像是您使用的视图更像是同义词而不是视图。IOW，views = "SELECT * FROM table"，那么除了硬解析之外，你不会看到性能受到影响。
If you are joining to other tables or placing filter clauses in them which prevent predicate pushing than you're bound to see a major hit sometime.
如果您要加入其他表或在其中放置过滤器子句以防止谓词推送，那么您一定会在某个时候看到重大打击。
回答by Gary Myers
The only pain I have had with views is a distributed query over a DB link. The local optimizer gets some details about the remote object, but the view doesn't tell it about any indexes so you can get some kooky plans.
我对视图的唯一痛苦是通过数据库链接进行分布式查询。本地优化器获取有关远程对象的一些详细信息，但视图不会告诉它有关任何索引的信息，因此您可以获得一些古怪的计划。
I've heard about some places that use it as a standard since they can easily 're-order' the columns in a view. Not a big benefit in my opinion by YMMV
我听说有些地方使用它作为标准，因为他们可以轻松地“重新排序”视图中的列。YMMV 在我看来没什么大好处
