Will View Increase The Performance?

Question

I have Six tables which will joined in many queries.If a View created by joining all the tables will increase the performance?.Is there are any alternatives to increase the performance?. I am using oracle as the database and all the joined columns are indexed.

Answer 1

A view in and of itself will not increase performance. With that said depending on the database engine you are using there are things you can do with a view.

In SQL Server you can put an index on the view (Assuming the view fits a variety of requirements). This can greatly improve the performance.

What DBMS are you using?

Edit

In oracle you can create a Materialized View. Here's a link which should get you started. I don't know Oracle so I really can only provide links.

http://searchoracle.techtarget.com/expert/KnowledgebaseAnswer/0,289625,sid41_gci909605,00.html

Answer 2

Correct indexing is the key to improving performance when joining to many tables.

Add a view may improve performance if it is indexed or may decrease performance if it is not. A lot depends on your database design and the actual query the view is based on. Once thing a view will do is ensure that everyone who uses is is looking at the same six tables. This is a plus or a minus depending on the query (not all queries based on the view may actually need all six tables).

The very first thing I would look at is whether your foreign key fields are currently indexed. Primary keys are given an index when created, but foreign keys are not. Since they are used in the joins, they should be indexed for performance.

Answer 3

It should be noted how views work: They run the view query at run time. They are not pre-cached, so without indexes, they are essentially function calls.

Make heavy uses of indexes on your tables and views to improve performance. Don't shotgun them, but well designed indexes can be day and night for your performance.

Answer 4

It also depends on the Database. If the RDMS does not analyze the underlying query based on your criteria, it may not perform well at all.

Most current RDMS's will do a query rewrite (I think some earlier versions of SQL Server, like 6 or 7, did not do this), it will pull apart the view in light of your query, then rewrite the whole thing based on its optimization techniques.

The various iterations of queries using the view could have their plans cached and gain some performance in that way.

So, from my understanding, at least in theory (depending on the database optimizer), using views is not different then writing the whole query.