oracle 视图与新表的数据库性能

Database performance of view vs new table

提问by john

I am using an Oracle database that is having slow performance because of joins on some tables for getting results. I am considering making a new table that stores some of this data so that it can be retrieved quickly without having to perform the joins. Another alternative is to create a view for the joins I am performing and then always query the view for the data. What is the tradeoff in performance between using a new table versus creating a view? I figured a view would still need to run the join so it would not offer as good performance as a new table.

我使用的 Oracle 数据库由于连接某些表以获取结果而导致性能下降。我正在考虑制作一个新表来存储其中一些数据，以便可以快速检索而无需执行连接。另一种替代方法是为我正在执行的连接创建一个视图，然后始终在该视图中查询数据。使用新表与创建视图之间的性能权衡是什么？我认为视图仍然需要运行连接，因此它不会提供与新表一样好的性能。

Info on Oracle database view is here: - http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm- What is a View in Oracle?

Oracle 数据库视图的信息在这里： - http://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_8004.htm- Oracle 中的视图是什么？

Clarification based on responses below. The queries are mostly optimized already so I do not want to do optimization. Would prefer either a new table or a materialized view, but would like to know which might be better. I am interested in performance. Writing more code to keep the new table in sync with the old tables is not a problem. I will just add modification statements wherever modifications were done to old tables. I do not want to use a materialized view if it is slower than adding a new table.

基于以下回复的澄清。查询大多已经优化，所以我不想做优化。更喜欢新表或物化视图，但想知道哪个更好。我对表演很感兴趣。编写更多代码以保持新表与旧表同步不是问题。我只会在对旧表进行修改的地方添加修改语句。如果物化视图比添加新表慢，我不想使用它。

The difference is whether the refresh of the data is more efficient for materialized view or for a new table. For the new table, basically I will be adding update statements wherever there were updates to the old table. So when the user queries the new table, the data is already there (no further processing needed). But for a materialized view, if the view refreshes itself only when the user queries the view, then this might be slower.

区别在于数据的刷新对于物化视图还是新表更有效。对于新表，基本上我会在旧表有更新的地方添加更新语句。所以当用户查询新表时，数据已经存在（不需要进一步处理）。但是对于物化视图，如果视图仅在用户查询视图时刷新自身，那么这可能会更慢。

采纳答案by Justin Cave

A view is just a stored query so there should be no difference in performance between querying a view and issuing an identical query against the base tables.

视图只是一个存储查询，因此查询视图和针对基表发出相同查询之间的性能应该没有区别。

Creating a separate table may increase performance of queries but it violates normalization and then you have to write code that keeps that table in sync. If you need the queries to return the correct result rather than an approximate result, that means that your DML operations (inserts, updates, and deletes) are going to be slower in order to deal with keeping the data in sync. That may be an appropriate trade-off if this is primarily a reporting database but it's going to be much less appropriate in an OLTP environment where transaction performance is critical.

创建一个单独的表可能会提高查询的性能，但它违反了规范化，然后您必须编写代码来保持该表同步。如果您需要查询返回正确的结果而不是近似结果，这意味着您的 DML 操作（插入、更新和删除）会变慢，以便处理保持数据同步。如果这主要是一个报告数据库，这可能是一个适当的权衡，但它在事务性能至关重要的 OLTP 环境中不太合适。

If you are going to create a table, I'd generally suggest that you look at creating a materialized viewinstead. That has the performance benefits of a table but Oracle takes care of keeping it in sync so you don't have to write a lot of custom code for that.

如果您要创建一个表，我通常建议您考虑创建一个物化视图。这具有表的性能优势，但 Oracle 负责保持同步，因此您不必为此编写大量自定义代码。

But it's not at all obvious that materializing the data is the proper solution in the first place. Are you certain that you're not simply missing some indexes?

但首先，将数据具体化是正确的解决方案，这一点并不明显。您确定您不是简单地缺少一些索引吗？

回答by Amir Pashazadeh

Views are just a wrapper for queries, the performance of using a view is just the same as performance of using a query (if you ignore query parsing overhead).

视图只是查询的包装器，使用视图的性能与使用查询的性能相同（如果忽略查询解析开销）。

So using a view will not help your problem, if you are using lots of joins. But after lots of experience in query optimization in Oracle I can give you some notes:

因此，如果您使用大量连接，则使用视图对您的问题没有帮助。但是在积累了大量的 Oracle 查询优化经验后，我可以给你一些笔记：

1. Use a sub-select (inside your select clause) instead of joining when it is possible; there are some cases when this can not be done, or it is not good to do that, such as:
   • when you are using inner joins to remove some records from result set -
   • you want to have some conditions on that columns
   • you want to sort on that columns.
2. Use union allinstead of unionwhere it can be done.
3. Use coalescewhen it is proper, (even use it on with sub-select as parameters),
4. Create proper indexes (according to your execution plan)
5. Avoid using stored-functions in your join and where clauses.

1. 在可能的情况下使用子选择（在您的选择子句中）而不是加入；在某些情况下，这是无法做到的，或者这样做不好，例如：
   • 当您使用内部联接从结果集中删除一些记录时 -
   • 您想在该列上设置一些条件
   • 您想对该列进行排序。
2. 使用union all而不是union可以完成的地方。
3. coalesce在适当的时候使用，（甚至将它与子选择作为参数一起使用），
4. 创建适当的索引（根据您的执行计划）
5. 避免在 join 和 where 子句中使用存储函数。

These are the things that came to my mind now. Hope it would help.

这些是我现在想到的事情。希望它会有所帮助。

回答by GriffeyDog

A materialized viewmay be what you're looking for. A regular viewdoes not exist as a table, it just references the tables it's based on. But, like Linutis commented, you should try to optimize your query first. You may need indexes on the columns involved in the join, and statistics gathered on the tables you are using.

Amaterialized view可能就是您要查找的内容。常规view不作为表存在，它只是引用它所基于的表。但是，就像 Linutis 评论的那样，您应该首先尝试优化您的查询。您可能需要在连接中涉及的列上建立索引，并在您使用的表上收集统计信息。