question

BI DWH BALA avatar image
BI DWH BALA asked

What is the advantage of a view over a normal query?

What is the advantage of a view over a normal query?

oracle
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
KillerDBA avatar image
KillerDBA answered

People cite several advantages:

  1. Encapsulation of logic. If you have a particular query that is used by multiple systems, using a view makes it easy to share the logic and it also allows for any necessary maintenance to occur in one place (if you change the underlying tables, you may be able to simply change the query in the view to suit the new tables, transparently to the client applications). This can reduce effort of reinventing the whee

  2. Consistency. If we're both looking at a customer ranking via a view, we're looking at the same thing.

  3. Security. You can deny access to the base tables but grant access to restrictive views to some users to to prevent them reading or updating data outside their area of responsibility.

  4. On some systems (don't know about Oracle), views can be given certain performance advantages (indexed views on SQL Server, fro example).

  5. Change management. In line with #1, views can be used to shield users from changes.

  6. User Friendliness Views can be used to make the data in a new database present like the data from an old database with which the users are already familiar.

  7. (Added later) Simplification. I can't think of any good example right now but we've used views in the past to break down logic into workable parts and simplify query problems.

There are probably other reasons, too.

2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Hi Killer DBA. Its a very nice explanation. I never thought that, views have these many advantages over normal queries. Thanks for sharing this.
0 Likes 0 ·
I'm hoping we'll hear from more people with more reasons.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.