Database performance of view vs new table

Question

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.

Answer 1

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?

Answer 2

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.