Overview of SQL Views and Materialized Views

What is a View?

A View is virtual table and it is built by selecting data from one or more tables.A view can be accessed by using the SELECT statement as though it is like a normal table.Changes applied to the data in a relevant underlying table(s) are reflected in the data shown in subsequent queries of the view.A view can contain all columns of a table or few columns as desired.

You can even add SQL functions, WHERE and JOIN statements to a view and present the data as if the data were coming from one single table.

Note: A view always shows up to date data.Every time you select a view it fetches the data again from the underlying base table(s) which is not the case with Materialized View(we will see shortly what is Materialized View)

Why we use View?

1)We can use Views as security mechanisms in databases.We can restrict some columns or data to the users.

E.g.:Let's say you have a table called Employees, and it has columns like salary and age.You don't want everyone with access to the database to be able to see those columns, so you can create a view called Employee_Info which doesn't select those columns, and deny access to the base table to the users (either by placing them in specific roles/groups and denying access that way, or issuing explicit DENY statements to each user). This way, users don't even have to know that there is a table with more information behind the scenes.

2)Views can join multiple tables into a single virtual table.

3)Views take very little space to store.The database contains only the definition of a view, not a copy of all the data that it presents.

4)Database complexity is hidden


Lets see how to create Views

Database Views can be created using CREATE VIEW statement.

CREATE VIEW view_name AS
SELECT column1, column2, column3.....
FROM table_name
WHERE [condition];


E.g.:

CREATE VIEW EMPLOYEE_INFO AS
SELECT emp_id, emp_name,emp_age
FROM EMPLOYEES
WHERE emp_id=844;

We can query the created view like a normal select statement

SELECT * FROM EMPLOYEE_INFO;


Updating a View:

You can update a view with the following command

CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;

Dropping a View:

Once a SQL VIEW has been created, you can drop it with the SQL DROP VIEW Statement.

DROP VIEW view_name;

Let's see what is Materialized Views:

Why we need Materialized Views

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.

Materialized Views are similar to normal Views except in which the query result is cached as a concrete table.This enables much more efficient access, at the cost of some data being potentially out of date. It is most useful in data warehousing scenarios, where frequent queries of the actual base tables can be extremely expensive.

Materialized views can be set to refresh manually, on a set schedule, or based on the database detecting a change in data from one of the underlying tables.

How to create MV's

CREATE MATERIALIZED VIEW <MV_NAME>
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT * FROM <table_name>;

E.g.:

CREATE MATERIALIZED VIEW MV_EMPLOYEES_INFO
REFRESH FAST START WITH SYSDATE
NEXT SYSDATE + 1
AS SELECT * FROM EMPLOYEES;

Selecting from Materialized View

Since this  MV is similar to a physical table you can use SELECT statements normally like shown below

SELECT emp_id,emp_name from MV_EMPLOYEES_INFO;

Dropping Materialized View

DROP MATERIALIZED VIEW MV_EMPLOYEES_INFO PRESERVE TABLE;


Thanks for visiting my blog!!!!!!!!!