View

 

View is a virtual table that is based on a SQL query.

It does not store data physically but provides a stored query that retrieves data dynamically from one or more tables.

Advantages :

✅ Simplifies Complex Queries – Instead of writing long queries repeatedly, you can create a view and query it like a table.
✅ Enhances Security – Restricts access to specific columns or rows.
✅ Provides Data Abstraction – Hides complex joins and calculations from end-users.
✅ Ensures Data Consistency – If the underlying tables change, the view reflects those changes.

CREATE VIEW emp_view AS SELECT emp_name, department FROM employees;

SELECT * FROM emp_view;

Types of Views:

1.      Simple View

              Based on a single table.

              CREATE VIEW simple_view AS SELECT emp_name, salary FROM employees;

2.      Complex View:

Based on multiple tables (uses JOIN, GROUP BY, HAVING, etc.).

Usually read-only (DML operations may not be allowed).

 

CREATE VIEW dept_salary_view AS

SELECT department, AVG(salary) AS avg_salary

FROM employees

GROUP BY department;

 

3.      Materialized View (MVIEW)

·       A physically stored copy of a query result.

·       It improves performance by avoiding repeated query execution.

·       Can be refreshed periodically (ON COMMIT, ON DEMAND, etc.).

CREATE MATERIALIZED VIEW emp_salary_mv

REFRESH FAST ON COMMIT

AS SELECT emp_id, emp_name, salary FROM employees;

Manual View Refresh:

EXEC DBMS_MVIEW.REFRESH('emp_salary_mv');

 

4.      Updating Data Using a View

You can update the underlying table through a view, but only if:

• The view is based on a single table.
• The view does not use aggregate functions, DISTINCT, GROUP BY, or JOIN.

UPDATE emp_view

SET department = 'Finance'

WHERE emp_name = 'John Doe';

 

Feature	Table	View
Data Storage	Stores data physically	No physical storage
Performance	Faster (direct access)	Slightly slower (query execution)
DML Operations	Fully supported	Limited (depends on view type)
Security	Requires column-level permissions	Can restrict access easily

 

Feature	Normal View	Materialized View (MVIEW)
Definition	A virtual table that runs a stored SQL query dynamically when accessed.	A physically stored snapshot of a query result.
Storage	No data is stored, only the query definition is saved.	Stores data physically like a table.
Data Retrieval	Retrieves data dynamically from base tables every time it is queried.	Retrieves data directly from stored results, making queries faster.
Performance	Slower because it re-executes the query each time.	Faster since data is precomputed and stored.
Refresh Mechanism	Always fetches fresh data from base tables.	Can be refreshed manually or automatically (ON COMMIT, ON DEMAND).
Data Consistency	Always up-to-date.	May not be up-to-date if not refreshed.
Use of Indexes	Cannot have indexes, but base table indexes help.	Can have indexes on stored data, improving performance.
Query Optimization	No query rewrite optimization.	Supports query rewrite to use the precomputed data for faster execution.
DML Operations	✅ Updatable if based on a single table (with some conditions).	❌ Not updatable, as it stores a static copy of data.
Refresh Options	Not needed (always fetches live data).	- FAST (only changes) - COMPLETE (full refresh) - FORCE (tries fast, then complete)
Use Case	Best for real-time data views and simplifying queries.	Best for performance optimization, reports, and reducing database load