Will it increase productivity?

Question

I have six tables that will be combined into many queries. If the view created by joining all the tables will increase productivity. Are there any alternatives to improve performance ?. I use oracle as a database and all merged columns are indexed.

Answer 1

Glance alone will not increase productivity. With that said, depending on the database engine you use, there are things you can do with the view.

In SQL Server, you can put an index in a view (Assuming the view meets many requirements). This can significantly improve performance.

What DBMS do you use?

Edit

In the oracle, you can create a materialized view. Here is the link you should start with. I do not know Oracle, so I really can 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 connecting to many tables.

Adding a view can improve performance if it is indexed or it can decrease performance if it is not. Much depends on your database design and the actual query on which the view is created. After everything looks, make sure that everyone who uses it looks at the same six tables. This is a plus or minus depending on the query (not all view-based queries may really need all six tables).

The very first thing I would like to pay attention to is whether your foreign key fields are indexed. Primary keys are assigned an index at creation, but foreign keys are not. Because they are used in joins, they must be indexed for performance.

Answer 3

It should be noted how views work: they run a view query at runtime. They are not pre-cached, so without indices they are essentially functional calls.

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

Answer 4

It also depends on the database. If RDMS does not analyze the basic query based on your criteria, it may not work at all.

Most existing RDMSs will rewrite the query (I think some earlier versions of SQL Server, such as 6 or 7, did not), it will separate the view in the light of your query, and then rewrite it all based on its optimization methods .

Different query iterations using a view can have their own caching plans and get some performance this way.

So, from my understanding, at least theoretically (depending on the database optimizer), using views is no different from writing the entire query.