View and Materialized views

Index

An index is a performance-tuning method of allowing faster retrieval of records. An index creates an entry for each value that appears in the indexed columns.

Indexes are pointres that maps to the physical address of data. So by using indexes data manipulation becomes faster

View

A view is a logical entity. It is a SQL statement stored in the database in the system tablespace. Data for a view is built in a table created by the database engine in the TEMP tablespace.

View is a logical table. It is a physical object which stores data logically. View just refers to data that is stored in database tables.

Advantages of View:

• Views restrict access to the data because the view can display selective columns from the table.

• Views can be used to make simple queries to retrieve the results of complicated queries. For

Example, views can be used to query information from multiple tables without the user knowing How to write a join statement.

• Views provide data independence for ad hoc users and application programs. One view can be used to retrieve data from several tables.

• Views provide groups of user’s access to data according to their particular criteria.

Simple Views versus Complex Views

There are two classifications for views: simple and complex. The basic difference is related to the

DML (INSERT, UPDATE, and DELETE) operations.

• A simple view is one that:

– Derives data from only one table

– Contains no functions or groups of data

– Can perform DML operations through the view

• A complex view is one that:

– Derives data from many tables

– Contains functions or groups of data

– Does not always allow DML operations through the view

Performing DML Operations on a View

You can perform DML operations on data through a view if those operations follow certain rules.

You cannot remove a row from a view if it contains any of the following:

• Group functions

• A GROUP BY clause

• The DISTINCT keyword

• The pseudocolumn ROWNUM keyword and 

   NOT NULL columns in the base tables that are not selected by the view


What is a materialized view?

A materialized view is a snapshot of data which could be refreshed at defined intervals. From the name, it is unclear that a materialized view can hold data, since views don’t. Views are nothing but a query stored in a Database. Whenever we do a select * from view, it runs the saved query and fetches data.

Sometimes there would be a need for a huge query with many join operations between more than 10-20 tables containing millions of records and many where conditions. Especially in a data warehousing environment or for reporting purposes. In such conditions, running the query each time whenever required is not a feasible option. So, there should be a technique to store the data such that we should run the query only if there is a change in data in the underlying tables There comes the usage of a materialized views as they can store the data they have queried.

Ref:
http://www.w3schools.com/SQl/sql_view.asp
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_8004.htm