Views - Simple | Complex | Materialized

 In Oracle, there are several types of views. A view is a virtual table that does not store data but retrieves data from one or more tables. Views provide a way to simplify complex queries by presenting data in a more manageable format. The following are the most common types of views in Oracle:

Simple View:

A simple view is a view that is based on a single table. A simple view can be created using the following 

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, column3, ...

FROM table_name

WHERE condition;

Example:

if we want to create a view that displays the names and salaries of employees whose salaries are greater than 5000 from the employees table, we can use the following syntax:

CREATE VIEW high_salary_employees AS

SELECT first_name, last_name, salary

FROM employees

WHERE salary > 5000;

Complex View:

A complex view is a view that is based on multiple tables or subqueries. A complex view can be created using the following 

Syntax:

CREATE VIEW view_name AS

SELECT column1, column2, column3, ...

FROM table1

JOIN table2 ON condition

WHERE condition;

Example:

if we want to create a view that displays the names and salaries of employees who work in the IT department from the employees and departments tables, we can use the following syntax:

CREATE VIEW it_department_employees AS

SELECT e.first_name, e.last_name, e.salary, d.department_name

FROM employees e

JOIN departments d ON e.department_id = d.department_id

WHERE d.department_name = 'IT';

Materialized View:

A materialized view is a view that stores data physically in the database. Unlike simple and complex views, materialized views can improve query performance by precomputing and caching the results of the view. A materialized view can be created using the following 

Syntax:

CREATE MATERIALIZED VIEW [schema.]view_name

REFRESH [FAST | COMPLETE | FORCE]

ON [COMMIT | DEMAND | SYSTEM]

[ENABLE | DISABLE] QUERY REWRITE

AS select_statement;

Explanation of the syntax elements:

• [schema.]view_name - the name of the materialized view, optionally prefixed with the schema name
• REFRESH - specifies the refresh method of the materialized view
• FAST | COMPLETE | FORCE - specifies the refresh method options
• ON - specifies when the materialized view is refreshed
• COMMIT | DEMAND | SYSTEM - specifies the refresh method options
• [ENABLE | DISABLE] QUERY REWRITE - specifies whether query rewrite is enabled or disabled for the materialized view
• AS select_statement - the SQL query that defines the materialized view

Example: 01

If we want to create a materialized view that displays the number of employees in each department from the employees and departments tables, we can use the following syntax:

CREATE MATERIALIZED VIEW employee_count_by_department AS

SELECT d.department_name, COUNT(e.employee_id) AS employee_count

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name

ORDER BY d.department_name;

Example: 02

Suppose we have two tables in the database - employees and departments. We want to create a materialized view that shows the total salary of each department. Here is the example SQL code to create such a materialized view:

CREATE MATERIALIZED VIEW emp_salary_by_dept

REFRESH FAST ON DEMAND

ENABLE QUERY REWRITE

AS

SELECT d.department_name, SUM(e.salary) as total_salary

FROM employees e, departments d

WHERE e.department_id = d.department_id

GROUP BY d.department_name;

In this example, we've created a materialized view named emp_salary_by_dept. The REFRESH clause specifies that the materialized view will be refreshed FAST and ON DEMAND. The ENABLE QUERY REWRITE clause enables query rewrite for the materialized view, which means that Oracle can use the materialized view to rewrite queries for improved performance.

The AS clause defines the SQL query that defines the materialized view. In this case, we're selecting the department_name column from the departments table and the sum of the salary column from the employees table, grouping the results.

Example:03

Let's say we have a table called sales that contains information about sales made by different employees in different regions. We can create a materialized view that calculates the total sales made by each employee by region as follows:

CREATE MATERIALIZED VIEW mv_sales_by_employee_region

REFRESH FAST ON COMMIT

AS

SELECT employee_id, region, SUM(sales_amount) AS total_sales

FROM sales

GROUP BY employee_id, region;

In the above example, the materialized view mv_sales_by_employee_region is created with the REFRESH FAST ON COMMIT option, which means that the materialized view is refreshed automatically whenever there is a commit on the base table sales. The SELECT statement in the AS clause defines the columns and the data that are included in the materialized view. The GROUP BY clause groups the sales data by employee and region, and the SUM function calculates the total sales for each employee by region.

Example:04

Let's say we have two tables, employees and departments, with the following data:

Employees Table:

employee_id first_name last_name salary department_id

        1                 John         Smith         5000     1

        2                 Jane         Doe                6000     1

        3                 Bob                 Johnson 7000     2

        4                 Sally         Smith         8000     2

Departments Table:

department_id department_name

        1                     Sales

        2                     Marketing

We can create a materialized view that shows the total salary of each department using the following SQL statement:

CREATE MATERIALIZED VIEW department_salary_summary

REFRESH COMPLETE ON DEMAND

AS

SELECT d.department_name, SUM(e.salary) AS total_salary

FROM employees e

JOIN departments d ON e.department_id = d.department_id

GROUP BY d.department_name;

In this example, the material    zed view is called department_salary_summary, and it will be refreshed completely on demand. The query in the materialized view calculates the total salary of each department by joining the employees and departments tables and grouping the data by department.

Once the materialized view is created, we can query it just like a regular table:

SELECT * FROM department_salary_summary;

Note - that the first time we query the materialized view, it will be populated with data from the underlying tables. Subsequent queries will use the cached data until the materialized view is refreshed.