What is view in Oracle?

by jamuna
(hyderabad)

Question: what is a view and how many types of views are there in Oracle?


We'll answer the 2nd part first to make it easier to answer the 1st part. There are 3 types of views in Oracle:-
  1. standard (normal) views
  2. materialized views
  3. object views
Let's discuss each of these in a little more detail.

A standard view is just a pre-defined query that is stored in the Oracle database. Standard views are useful for hiding the complexity of queries and for ensuring that the same query is used throughout the application which helps with cursor re-use. Normal views are often read-only but they don't have to be.

A subset of the standard view is the modifiable join view. This is a query that joins two or more tables in such a way that one of the underlying tables can be updated through the view. This avoids the need to run a separate query on the underlying table.

A materialized view is also a pre-defined query but this time the results of the query are stored in the Oracle database (i.e. they are materialized). This type of view is useful in data warehouses where aggregates of transactional data are required. Materialized views can be set to be updated automatically by Oracle when the underlying data changes or on a fixed schedule and the Oracle optimizer can automatically (and on the fly) rewrite queries against the base tables to use the (aggregated results in the) materialized views.

Object views are a way of pretending an Oracle database that has data stored relationally (i.e. in normal tables) is storing that data in object tables. This is useful when an object-oriented application is accessing a relational database.

More information about all the types of views can be found in the Oracle Concepts manual.

You might also want to review our beginners' Oracle SQL tutorials and see our Oracle training page for information about formal training courses both on-line and in person.

Click here to post comments

Join in and write your own page! It's easy to do. How? Simply click here to return to Oracle Questions.