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中的View是什么？
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.
不同之处在于数据刷新对于物化视图还是新表更有效。对于新表，基本上我将在旧表更新的地方添加更新语句。因此，当用户查询新表时，数据已经存在（不需要进一步处理）。但是对于物化视图，如果视图仅在用户查询视图时刷新自身，那么这可能会更慢。
3 个解决方案
#1
7
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 view instead. 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?
但是，首先实现数据的具体化解决方案并不是很明显。你确定你不是只是错过了一些索引吗？
#2
5
A materialized view may be what you're looking for. A regular view does 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.
物化视图可能是您正在寻找的。常规视图不作为表存在，它只引用它所基于的表。但是，就像Linutis评论的那样，您应该首先尝试优化查询。您可能需要连接中涉及的列的索引，以及您正在使用的表上收集的统计信息。
#3
5
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的查询优化方面有很多经验之后我可以给你一些笔记：
- 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.
- 你想对那些列进行排序。
- 使用子选择（在select子句中）而不是在可能时加入;在某些情况下，如果无法完成此操作，或者这样做不好，例如：当您使用内部联接从结果集中删除某些记录时 - 您希望在要排序的列上有一些条件在那些专栏上。
- Use
union allinstead ofunionwhere it can be done. - 使用union all而不是union可以完成。
- Use
coalescewhen it is proper, (even use it on with sub-select as parameters), - 在合适的情况下使用合并，（甚至使用sub-select作为参数），
- Create proper indexes (according to your execution plan)
- 创建适当的索引（根据您的执行计划）
- Avoid using stored-functions in your join and where clauses.
- 避免在join和where子句中使用stored-functions。
These are the things that came to my mind now. Hope it would help.
这些是我现在想到的事情。希望它会有所帮助。
#1
7
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 view instead. 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?
但是，首先实现数据的具体化解决方案并不是很明显。你确定你不是只是错过了一些索引吗？
#2
5
A materialized view may be what you're looking for. A regular view does 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.
物化视图可能是您正在寻找的。常规视图不作为表存在，它只引用它所基于的表。但是，就像Linutis评论的那样，您应该首先尝试优化查询。您可能需要连接中涉及的列的索引，以及您正在使用的表上收集的统计信息。
#3
5
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的查询优化方面有很多经验之后我可以给你一些笔记：
- 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.
- 你想对那些列进行排序。
- 使用子选择（在select子句中）而不是在可能时加入;在某些情况下，如果无法完成此操作，或者这样做不好，例如：当您使用内部联接从结果集中删除某些记录时 - 您希望在要排序的列上有一些条件在那些专栏上。
- Use
union allinstead ofunionwhere it can be done. - 使用union all而不是union可以完成。
- Use
coalescewhen it is proper, (even use it on with sub-select as parameters), - 在合适的情况下使用合并，（甚至使用sub-select作为参数），
- Create proper indexes (according to your execution plan)
- 创建适当的索引（根据您的执行计划）
- Avoid using stored-functions in your join and where clauses.
- 避免在join和where子句中使用stored-functions。
These are the things that came to my mind now. Hope it would help.
这些是我现在想到的事情。希望它会有所帮助。