8

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.

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?

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.

Community
  • 1
  • 1
john
  • 721
  • 3
  • 9
  • 13
  • View displays data based on a query. This avoids data replication and saves your storage space instead of creating a new table. I think for the performance issue look at query optimisation. – fn27 Mar 12 '12 at 19:09
  • I think u mean materialized view, no? – tbone Mar 12 '12 at 19:10
  • Oh, need to look at the difference, I have just started using oracle. I use only simple view, like `CREATE VIEW` – fn27 Mar 12 '12 at 19:14
  • Is your problem that you have a lot of complicated queries that all work OK separately, but perform horribly when you combine them? – Jon Heller Mar 13 '12 at 03:44
  • 1
    Your argument: "slow performance because of joins" is not convincing. Usually, with properly introduced indexes, joins are executed as indexed nested loops. Therefore, there is no fundamental reason why your queries with joins should be significantly slower than simplistic queries (select from one table with single table predicates). – Tegiri Nenashi Mar 13 '12 at 20:11

3 Answers3

8

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.

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.

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?

Justin Cave
  • 227,342
  • 24
  • 367
  • 384
  • But materialized views can not be fast refreshed on complex queries, and that would be a problem. If he wants to full-refresh a MV on every DML, then what's the gain of using MV? – Amir Pashazadeh Mar 12 '12 at 19:27
  • @AmirPashazadeh - The gain is that you don't have to write, maintain, and debug a bunch of code to try to keep the materialized data in sync with the underlying data. If you have a complex query that Oracle cannot create a fast refreshable materialized view for, you're going to have difficulty developing the code to keep the materialized data transactionally consistent. – Justin Cave Mar 12 '12 at 19:33
  • 1
    Agreed, but I believe optimizing the query is first thing ha shall done, most of the time query optimization is the answer. – Amir Pashazadeh Mar 12 '12 at 19:57
  • @AmirPashazadeh - I agree that optimizing the query is likely the better answer. That's why I included in my answer the question about whether materializing the data is the proper solution in the first place. – Justin Cave Mar 12 '12 at 20:03
  • Clarification. This is a primarily a reporting database (it is used to do some history comparisons for multiple pieces of data) so not an OLTP environment. 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. Thanks. – john Mar 12 '12 at 20:37
  • If this is your case, using a materialized view is much better, because you write the query and not the code to keep it synch, and the Oracle do the rest. – Amir Pashazadeh Mar 12 '12 at 21:09
  • I am interested in performance. Writing code to keep it in sync is not a problem. I do not want to use a materialized view if it is slower than using a new table. – john Mar 12 '12 at 22:44
  • @john2011 - Queries against a table and a materialized view will have identical performance. A materialized view is just an aggregate table that knows how to refresh itself. Generally, Oracle's code for refreshing the materialized view will be more efficient than the code the average developer will write to refresh a table. On the other hand, if there are optimizations you can implement that would not be generally possible because of some knowledge of the underlying data, it may be possible that you can write manual more efficient refresh code. – Justin Cave Mar 12 '12 at 22:47
  • @Justin - The difference is whether the refresh of the table 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. This update will be done during table updates, so when the user queries the new table, the data is already there (no further processing needed). But for a materialized view, how does the view know when to refresh itself? If it refreshes itself only when the user queries the view, then this might be slower. – john Mar 12 '12 at 23:06
  • @john2011 - You would refresh the materialized view as part of your ETL process. Assuming you create the materialized view as fast refreshable, you would create materialized view logs on the base tables and then refresh the materialized view once the base tables are loaded. – Justin Cave Mar 13 '12 at 01:14
  • @Justin - OK, I see your point. As you stated, it might depend on the individual case of whether optimizations are available for updating the new table. Have there been any studies done for the benefits of using a materialized view versus a new table with same data as the view? Maybe Oracle itself would have looked into this when they designed materialized views, but I have not found anything online. It seems like any automated code for updates would require more cumbersome generic algorithms than if updates were done manually to a new table. – john Mar 13 '12 at 19:31
  • @john2011 - I don't know of any studies off the top of my head. The major value propositions of the materialized view are that Oracle will automatically keep it in sync so you never have to write, maintain, debug, and support code that refreshes the summary table and that Oracle can potentially rewrite queries against the base tables to use the materialized view (depending on various environment settings). If you're not using query rewrite, the question is whether the code you're going to write is better than the code Oracle has developed and that is going to be very site-specific. – Justin Cave Mar 13 '12 at 19:49
  • @john2011 - Oracle's code is generic so if you have some knowledge of the data that you can apply, it may be possible for custom code to refresh a custom aggregate table more efficiently. On the other hand, lots of folks have written mostly correct code to maintain summary tables that becomes a pain to support when the summary table no longer gives the correct answer. – Justin Cave Mar 13 '12 at 19:51
6

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:

  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 all instead of union where it can be done.
  3. Use coalesce when 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.

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

Amir Pashazadeh
  • 7,170
  • 3
  • 39
  • 69
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.

GriffeyDog
  • 8,186
  • 3
  • 22
  • 34