---

View

---

A View is object consisting of stored query, it doesn’t contain data. Views are logical tables of data extracted from existing tables. A view can be thought of as a stored query or virtual table; we can views in most places where a table can be used.

A View is a method of organizing table data a specified need and View are baesd on SELECT statements, which derive their data real tables.

Use of View in oracle

1: It is used for hiding sensitive columns.

2: It is used for hiding complex queries which is involving multiple tables.

3: Views can be created with check option and prevents the updating of other rows and columns.

4: Views can be offer an extra level of table security by limiting to a predetermined set of rows and/or columns of a table.

5: Views isolate applications from changes in definitions of base tables.

6: View provides data in a different side than of a base table by renaming columns without affecting the base table.

---

Syntax:

Create or replace view view_name as sql_query;

---

Example

Create or replace view view_1 as select e_name, e_dept from table_1;

---

Type of Views

1: Simple Views

2: Complex Views

3: Read-only Views

4: Inline Views

5: Force Views

---

What is a Materialized View?

It is a database object that stores the results of a query, and it can be stored in the same database as its base table or different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment.

Materialized view give the indirect access to table data by storing the results of a query in a distinct schema object. Not like as ordinary view, which is does not take up any storage space or contain any data.

The existence of a materialized view is transparent to SQL, but when used for query rewrites will improve the performance of SQL execution. In updatable materialized view you can insert, update, and delete the data.

---

Features of Materialized View

1: In can be partitioned and indexed

2: Can be queried directly

3: Can have DML applied against it

4: Several refresh options are available

5: Best in read-intensive environments

---