what are benefits of using view in database? [duplicate]

This question already has answers here: Closed 11 years ago. 开发者_JS百科

Possible Duplicate:

Why is database view used?

Since I always can use Select statement from original tables instead of creating a view and using select from it, I wonder what are benefits of using view in database?

---

It simplifies calls and provides a layer of indirection.

So, if you have a complex select with lots of joins, you can implement it in a view and simply call the view without need to consider all these joins. You can then reuse this view.

Additionally, if you use a view instead of a table in this manner, in the future if you need to migrate a column, you can easily do that and only require changes to the view.

---

There are several, but I think the main benefit is that views are the SQL implementation of logical data independence.

Build an updatable view, and applications that use the view are relatively immune to changes in the underlying tables. Change the structure of the underlying tables, update the view definition, and all applications work as if nothing had happened. (On legacy databases, there might be hundreds of applications written in dozens of languages. This is the big win.)

Other benefits (paraphrasing Chris Date)

"Automatic" security for hidden data. Restrict access to views, and you have fine-grained control over who sees what.

"DRY" capability for applications. A view can provide a simple, public interface to a complex SELECT statement, so applications can just SELECT column-list FROM my-easy-view.

Different users can see the same data in different ways.

---

Besides the obvious benefits that Oded mentioned, you can sometimes drastically improve speed by using materialized views. From wikipedia:

In a database management system following the relational model, a view is a virtual table representing the result of a database query. Whenever an ordinary view's table is queried or updated, the DBMS converts these into queries or updates against the underlying base tables. A materialized view takes a different approach in which the query result is cached as a concrete table that may be updated from the original base tables from time to time.