What is view? Tell me different views? ,how to update complex view? What is materialized view and difference between view and mview? How to refresh materialized view?

View:

• View is a virtual table , every view is query attached to it.
•  It is an stored select statement.
•  It is an virtual component .
•  It allows Desc, DML, Select on it.
•  It will not hold data.
•  It is stored permanently in "User_views" system  table.
•  It can be shared with other users.
• To restrict database access.
• To make complex queries easy.
• To allow data independence.
• To present different views of the same data.
• It is used to share "Selected Rows and Columns"    with other users. 
• It is used for Reporting Purpose .
• It will Improve performance while manipulating  or retrieving data thru Views.
• Provide additional level of table security,by restricting access to a predetermined set of rows and columns of a table.
• Hide data complexity.
• Simplify Commands for the user 
• present the data in a different perspective from that of the base table.
• stored complex queries.

 DML not allowed on non key preserved table

 It is a static view.

 It holds data in it.

 It will not support DML on it. 

 DML on Table will not be reflected in view.

 To create it  " create materialized view " permission is required.

 It is used to maintain Historic data.

View with check option and read only option

A modifiable join view is a view that contains more than one table in the top-level FROM clause of the SELECT statement, and that does not contain any of the following: 

• DISTINCT operator 

• Aggregate functions: AVG, COUNT, GLB, MAX, MIN, STDDEV, SUM, or VARIANCE 

• Set operations: UNION, UNION ALL, INTERSECT, MINUS 

• GROUP BY or HAVING clauses 

• START WITH or CONNECT BY clauses 

• ROWNUM pseudocolumn 

Any UPDATE, INSERT, or DELETE statement on a join view can modify only one underlying base table. 

The WITH CHECK OPTION clause specifies the level of checking to be done when doing DML against the view. If specified, every row that is inserted, updated or deleted through the view must conform to the definition of the view.

Specify WITH READ ONLY to indicate that the table or view cannot be updated. 

Syntax: 

CREATE [OR REPLACE] VIEW <view name> AS <select statement>;

Types of Views

• Simple View
• Complex Views
• Materialized View
• Inline View

Simple View 

Creating a view by using one table is called Simple view.

create view v1 as select * from emp;

Complex View

Creating a view based on more than one table is called Complex view

create view v10 as select * from emp  where deptno = 10 with check option;

Materialized View

A materialized view is a database object that contains the results of a query.

The FROM clause of the query can name tables, views, and other materialized views. 

Collectively these objects are called master tables (a replication term) or detail tables (a data warehousing term). 

This reference uses "master tables" for consistency. The databases containing the master tables are called the master databases.

Materialized view has a physical structure it stores data in local machine or on its own.

Materialized view can be refreshed automatically or manually

But in materialized view, we can’t do any DML operations.

But a materialized view has a physical existence. Moreover a materialized view can be indexed, analysed.

Syntax (Fast Refresh)

CREATE MATERIALIZED VIEW <schema.name>

PCTFREE <integer>

PCTUSED <integer>

TABLESPACE <tablespace_name>

BUILD IMMEDIATE

REFRESH <FAST | FORCE> ON <COMMIT | DEMAND>

<USING INDEX | USING NO INDEX>

INITRANS <integer>

STORAGE CLAUSE

AS (<SQL statement>);

Syntax (Force Refresh)

CREATE MATERIALIZED VIEW <schema.name>

PCTFREE <integer>

PCTUSED <integer>

TABLESPACE <tablespace_name>

BUILD IMMEDIATE

REFRESH <FAST | FORCE> ON <COMMIT | DEMAND> 

AS (<SQL statement>);

Syntax (Complete Refresh)

CREATE MATERIALIZED VIEW <schema.name>

PCTFREE <integer>

PCTUSED <integer>

TABLESPACE <tablespace_name>

REFRESH <COMPLETE | FORCE>

START WITH <date>

NEXT <date_calculation>

[FOR UPDATE]

AS (<SQL statement>);

Syntax (Complete Refresh Using Index)

CREATE MATERIALIZED VIEW <schema.name>

[LOGGING] [CACHE]

PCTFREE <integer>

PCTUSED <integer>

USING INDEX

TABLESPACE <tablespace_name>

REFRESH <COMPLETE | FORCE>

START WITH <date>

NEXT <date_calculation>

[FOR UPDATE]

AS (<SQL statement>);

Syntax (Prebuilt Table)

CREATE MATERIALIZED VIEW <schema.name>

PCTFREE <integer>

PCTUSED <integer>

TABLESPACE <tablespace_name>

REFRESH <COMPLETE | FORCE>

START WITH <date>

NEXT <date_calculation>

[FOR UPDATE]

AS (<SQL statement>);

Syntax (Enable Query Rewrite)

CREATE MATERIALIZED VIEW <schema.name>

PCTFREE <integer>

PCTUSED <integer>

TABLESPACE <tablespace_name>

REFRESH <COMPLETE | FORCE>

START WITH <date>

NEXT <date_calculation>

[FOR UPDATE]

AS (<SQL statement>);

Types of materialized views:

Read only materialized views

Advantages: There is no possibility for conflicts as they cannot be updated. DML Can not be performed on the snapshots in this category.

Complex materialized views are supported

CREATE MATERIALIZED VIEW hr.employees AS

SELECT * FROM hr.employees@orc1.world;

Update able materialized views

Advantages: Can be updated even when disconnected from the master site or master materialized view site.

Requires fewer resources than multi master replication. Are refreshed on demand. Hence the load on the network might be reduced compared to using multi master replication because multi master replication synchronizes changes at regular intervals. Update able materialized views require the advanced replication option to be installed / You can make a materialized view update able during creation by including the FOR UPDATE clause.

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS

SELECT * FROM hr.departments@orc1.world;

Sub query materialized views

These are the MVIEW’S that are created with sub queries in the WHERE clause of a MVIEW query.

Row id Vs Primary Key materialized views

MVIEW’S that use Row id for refresh are called Row id MVIEW’s (Oracle 7).

MVIEW’S  that use primary key for refresh are called primary key MVIE’S 

Multifier materialized views 

Simple Vs Complex MVIEW’S

MVIEW’S being simple or complex determines whether it can be fast refreshed or not. 

A MVIEW is fast refresh-able if it is simple.

A MVIEW is not fast refresh-able if it is complex.

A MVIEW can be considered CONNECT BY, INTERSECT, MINUS or UNION or UNION ALL clauses in its detining query.

The following data types are not supported in MVIEW replication.

A)LONG

B) LONG RAW

C) BFILE

D) UROWID

MVIEW’S are typically used in data ware house or decision support systems.

Inline View

An inline view is a SELECT statement in the FROM-clause of another SELECT statement. 

In-line views are commonly used to simplify complex queries by removing join operations and condensing several separate queries into a single query.

Views will stores under:

SELECT * FROM USER_VIEWS

SELECT * FROM ALL_VIEWS

SELECT * FROM DBA_VIEWS

SELECT * FROM ALL_OBJECTS  WHERE OBJECT_TYPE='VIEW'

To Drop View 

DROP VIEW <VIEW_NAME>

Difference Between View and Materialized view