Materialized View

Materialized Views:

A materialized view in a relational database management system (RDBMS) is a database object that stores the results of a query as a precomputed table. Unlike regular views, which are virtual and dynamically computed at query time, materialized views physically store the results, allowing for faster query performance by eliminating the need to recompute the result set every time the view is accessed.

Purpose and Use:

Materialized views are used to improve query performance, especially in scenarios where complex and resource-intensive queries need to be executed frequently. By precomputing and storing the results, materialized views reduce the overhead of executing the underlying query repeatedly, thus enhancing overall system performance. They are particularly beneficial in data warehousing environments, where large volumes of data are involved, and query performance is critical.

When to Use:

Materialized views are useful in situations where:

• Query performance is a priority, and there are frequently accessed or resource-intensive queries.
• The underlying data doesn't change frequently, or the cost of refreshing the materialized view is acceptable compared to the performance gain.
• Aggregation, summarization, or joins are involved in the queries, as materialized views can store the precomputed results of these operations.

When Not to Use:

Materialized views may not be suitable in scenarios where:

• The underlying data changes frequently, leading to the need for frequent refreshes of the materialized view, which can impact system performance.
• The storage requirements for maintaining materialized views become prohibitive, especially for large datasets.
• Real-time or near real-time data access is required, as materialized views store static snapshots of data rather than dynamically computed results.

Behavior in Different RDBMS:

Behavior of materialized views can vary across different RDBMS. Here's a brief overview:

• Oracle: Oracle supports materialized views and provides a rich set of features for their creation, management, and refresh. Syntax for creating materialized views involves the CREATE MATERIALIZED VIEW statement.
• PostgreSQL: PostgreSQL also supports materialized views with similar syntax to Oracle. Materialized views are created using the CREATE MATERIALIZED VIEW statement.
• MySQL: MySQL does not have built-in support for materialized views. However, similar functionality can be achieved using scheduled jobs or triggers to periodically refresh pre-computed query results.

Example in Oracle DBMS:

Let's consider an example scenario in Oracle DBMS:

1. Create Sample Table:

CREATE TABLE sales (
    product_id NUMBER,
    product_name VARCHAR2(50),
    quantity_sold NUMBER,
    sale_date DATE
);

2. Insert Sample Data:

INSERT INTO sales VALUES (1, 'Product A', 100, TO_DATE('2024-01-01', 'YYYY-MM-DD'));
INSERT INTO sales VALUES (2, 'Product B', 150, TO_DATE('2024-01-02', 'YYYY-MM-DD'));
INSERT INTO sales VALUES (3, 'Product C', 200, TO_DATE('2024-01-03', 'YYYY-MM-DD'));

3. Create Materialized View:

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD IMMEDIATE
REFRESH FAST ON COMMIT
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity
FROM sales
GROUP BY product_id;

In this example, we create a materialized view called mv_sales_summary that summarizes the total quantity sold for each product from the sales table. The materialized view is set to refresh immediately (ON COMMIT) after any transaction that changes the base table data. This ensures that the materialized view always reflects the latest data from the sales table, providing efficient querying capabilities.

You can create a materialized view without specifying the refresh method, and then later manually refresh it using the DBMS_MVIEW package. Here's how you can create the materialized view without specifying the refresh method and then manually refresh it.

CREATE MATERIALIZED VIEW mv_sales_summary
BUILD DEFERRED
AS
SELECT product_id, SUM(quantity_sold) AS total_quantity
FROM sales
GROUP BY product_id;

In this example, the materialized view mv_sales_summary is created without specifying the refresh method, meaning it won't be refreshed automatically.

To manually refresh this materialized view, you can use the DBMS_MVIEW.REFRESH procedure. Here's how you can do it:

BEGIN
  DBMS_MVIEW.REFRESH('mv_sales_summary');
END;

This PL/SQL block calls the DBMS_MVIEW.REFRESH procedure, specifying the name of the materialized view (mv_sales_summary) as the parameter. When executed, this procedure refreshes the materialized view, updating it with the latest data from the base tables.

You can schedule this manual refresh operation to occur at specific intervals using a job scheduler or execute it whenever necessary based on your application requirements.

View in SQL

In SQL, a view is a virtual table generated from a SQL SELECT query's result set. Unlike physical tables, views do not store data themselves but provide a dynamic, up-to-date snapshot of the underlying data. They act as predefined SQL queries that you can reference as if they were tables.

Here's a breakdown of the key details about views in SQL:

1. Definition:

• A view is defined using the CREATE VIEW statement in SQL.
• It consists of a SELECT statement that defines the columns and rows of the virtual table.

1. Structure:

• Views have a structure similar to a table, with columns and rows.
• The columns and data types of a view are derived from the SELECT statement used to create it.

1. Usage:

• Once created, views can be queried, updated, inserted into, or deleted from, just like regular tables.
• Views can be joined with other tables or views in SQL queries.
• They can also be used as the source for creating other views, allowing for a layered approach to data access.

1. Advantages:

• Simplification: Views can hide complexity by encapsulating complex queries, making them easier to understand and maintain.
• Security: Views can restrict access to specific columns or rows of underlying tables, providing a layer of security.
• Performance: Views can improve performance by storing frequently used or resource-intensive queries, reducing the need to rewrite the same logic multiple times.

1. 
   

2. Types of Views:

• Simple Views: Derived from a single base table.
• Complex Views: Derived from multiple tables through joins or subqueries.
• Materialized Views: Stores the result set of the query physically, which can improve performance by precomputing the results.

1. Modifiability:

• Views are generally updatable if they meet certain criteria, such as having a single table in the SELECT statement and not containing certain constructs like DISTINCT, GROUP BY, or aggregate functions.
• Complex views or views involving multiple tables may not be directly updatable.

1. Creating and Managing Views:

• Views are created using the CREATE VIEW statement.
• They can be altered using the ALTER VIEW statement to modify the underlying SELECT query.
• Views can be dropped using the DROP VIEW statement.

How to Use Views?

Creating a View:

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

Querying a View:

SELECT * FROM view_name;

Updating a View:

Views can be updatable depending on their complexity and the underlying tables.

Dropping a View:

DROP VIEW view_name;

Benefits of Views:

• Simplicity: Views encapsulate complex logic, making queries more straightforward and easier to understand.
• Security: Views allow fine-grained control over data access by restricting the visibility of sensitive information.
• Reusability: Views promote code reuse by encapsulating frequently used SQL queries.
• Performance: Materialized views can enhance performance by caching query results, reducing the need for repeated expensive computations.

Limitations of Views:

• Complexity Restrictions: Views may not support all SQL constructs, especially those involving aggregations or complex joins.
• Performance Overhead: Views can introduce performance overhead, particularly if they involve complex computations or are based on large underlying datasets.
• Updatability Constraints: Views may not be directly updatable if they involve multiple tables, aggregate functions, or certain SQL constructs.

Here's an example scenario where we have a simple database schema with two tables: students and courses. We'll also create a view called student_course_details that combines information from both tables.

-- Creating the students table
CREATE TABLE students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    age INT,
    gender VARCHAR(10)
);

1. 
   

-- Inserting some sample data into the students table
INSERT INTO students (student_id, student_name, age, gender)
VALUES
    (1, 'Alice', 20, 'Female'),
    (2, 'Bob', 22, 'Male'),
    (3, 'Charlie', 21, 'Male'),
    (4, 'Diana', 23, 'Female');

-- Creating the courses table
CREATE TABLE courses (
    course_id INT PRIMARY KEY,
    course_name VARCHAR(50),
    credit_hours INT
);

-- Inserting some sample data into the courses table
INSERT INTO courses (course_id, course_name, credit_hours)
VALUES
    (101, 'Mathematics', 3),
    (102, 'Physics', 4),
    (103, 'Literature', 3),
    (104, 'History', 3);

-- Creating the student_course_details view
CREATE VIEW student_course_details AS
SELECT s.student_id, s.student_name, c.course_id, c.course_name
FROM students s
CROSS JOIN courses c;

Now, let's see how we can interact with this setup:

1. Query the view: You can query the student_course_details view just like you would query a regular table.

SELECT * FROM student_course_details;

2. Update the view: Views can't be directly updated, but you can update the underlying tables. For example, let's say we want to update the name of the course with course_id 101 from "Mathematics" to "Advanced Mathematics".

UPDATE courses
SET course_name = 'Advanced Mathematics'
WHERE course_id = 101;

3. Insert into the view: You can't directly insert into a view, but you can insert into the underlying tables. For example, let's say we want to enroll a new student, Emma, into the Mathematics course.

INSERT INTO students (student_id, student_name, age, gender)
VALUES (5, 'Emma', 19, 'Female');
INSERT INTO student_course_details (student_id, course_id)
VALUES (5, 101);

4. Delete from the view: Similar to insertion, you can't directly delete from a view, but you can delete from the underlying tables. For example, let's say we want to remove Bob from the Physics course.

DELETE FROM student_course_details
WHERE student_id = 2 AND course_id = 102;

These are basic examples, and real-world scenarios might involve more complex queries, updates, inserts, or deletes based on the specific requirements of your application.

Combine View with other view/ tables

Let's suppose we have two tables: employees and departments, and we want to create views from these tables, and then combine those views to fetch some information. Here's how you can do it:

1. Creating Tables and Sample Data:

CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    employee_name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2),
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'Finance');

INSERT INTO employees (employee_id, employee_name, department_id, salary) VALUES
(1, 'John Doe', 1, 50000),
(2, 'Jane Smith', 1, 60000),
(3, 'Michael Johnson', 2, 70000),
(4, 'Emily Davis', 2, 55000);

1. 
   
2. Creating Two Diffwerent Views:

-- View 1: Employees in HR Department
CREATE VIEW hr_employees AS
SELECT * FROM employees WHERE department_id = 1;

-- View 2: Employees in Finance Department
CREATE VIEW finance_employees AS
SELECT * FROM employees WHERE department_id = 2;

Combining Views with a JOIN:

-- Combine HR and Finance views to get the names of employees and their departments
select * from finance_employees
cross join hr_employees; partment_id;

Combining Views with Tables:

1. Creating Additional Table and Sample Data:

CREATE TABLE projects (
    project_id INT PRIMARY KEY,
    project_name VARCHAR(50),
    department_id INT,
    FOREIGN KEY (department_id) REFERENCES departments(department_id)
);

INSERT INTO projects (project_id, project_name, department_id) VALUES
(101, 'HR Software Upgrade', 1),
(102, 'Finance Report Automation', 2),
(103, 'Employee Training Program', 1);

2. Combining Views with Tables:

-- Combine HR employees with projects
SELECT e.employee_name, p.project_name
FROM hr_employees e
JOIN projects p ON e.department_id = p.department_id;

-- Output will be:
-- +------------------+-------------------------+
-- | employee_name    | project_name            |
-- +------------------+-------------------------+
-- | John Doe         | HR Software Upgrade     |
-- | Jane Smith       | HR Software Upgrade     |
-- | John Doe         | Employee Training Program|
-- | Jane Smith       | Employee Training Program|
-- +------------------+-------------------------+

In this example, we combine the hr_employees view with the projects table using a join operation. We fetch the names of employees working in the HR department along with the projects they are involved in.

Combining Views with Subqueries:

Suppose we want to find the average salary of employees in the HR department. We can achieve this by using a view within a subquery:

1. Using View in a Subquery:

-- Subquery using the hr_employees view to find the average salary of employees in the HR department
SELECT AVG(salary) AS avg_salary_hr
FROM (
    SELECT salary
    FROM hr_employees
) AS hr_employee_salaries;

In this example, we are using the hr_employees view within a subquery to fetch the salaries of employees in the HR department. Then, we calculate the average salary of these employees in the outer query.

The output will be the average salary of employees in the HR department.

Combining Multiple Views and Tables:

Suppose we want to fetch the names of employees along with their department names and the projects they are working on. We can achieve this by combining the hr_employees and finance_employees views with the departments table and the projects table:

1. Combining Multiple Views and Tables:

-- Combine HR and Finance employees with departments and projects
SELECT e.employee_name, d.department_name, p.project_name
FROM (
    SELECT * FROM hr_employees
    UNION ALL
    SELECT * FROM finance_employees
) AS e
JOIN departments d ON e.department_id = d.department_id
JOIN projects p ON e.department_id = p.department_id;

Certainly! Here's an example of combining multiple views and tables using multiple join operations:

Suppose we want to fetch the names of employees along with their department names and the projects they are working on. We can achieve this by combining the hr_employees and finance_employees views with the departments table and the projects table:

1. Combining Multiple Views and Tables:

sql
-- Combine HR and Finance employees with departments and projects
SELECT e.employee_name, d.department_name, p.project_name
FROM (
    SELECT * FROM hr_employees
    UNION ALL
    SELECT * FROM finance_employees
) AS e
JOIN departments d ON e.department_id = d.department_id
JOIN projects p ON e.department_id = p.department_id;

In this example, we first combine the hr_employees and finance_employees views using a UNION ALL operation to get a unified list of employees. Then, we join this unified list with the departments table to fetch the department names and with the projects table to fetch the project names.

The output will be the names of employees along with their department names and the projects they are working on across both HR and Finance departments.

In SQL, you can use system tables or views to retrieve the definition of a view. The exact method may vary slightly depending on the database management system (DBMS) you're using. Here's how you can generally achieve this:

1. Using INFORMATION_SCHEMA.VIEWS (Standard SQL):

   This method is supported by most relational database systems that comply with SQL standards, including MySQL, PostgreSQL, SQL Server, etc.

   SELECT VIEW_DEFINITION
   FROM INFORMATION_SCHEMA.VIEWS
   WHERE TABLE_NAME = 'your_view_name';

   Replace 'your_view_name' with the name of your view.

2. 
   

3. Database-specific methods:

   Some database systems have their own system tables or views to store view definitions.

   • For MySQL and MariaDB, you can use SHOW CREATE VIEW:

     SHOW CREATE VIEW your_view_name;

   • 
     

   • For PostgreSQL, you can query the pg_views system catalog:

     SELECT definition
     FROM pg_views
     WHERE viewname = 'your_view_name';

   • 
     

   • For SQL Server, you can use the sp_helptext system stored procedure:

     EXEC sp_helptext 'your_view_name';

   • 
     

   • For Oracle, you can query the ALL_VIEWS data dictionary view:

     SELECT TEXT
     FROM ALL_VIEWS
     WHERE VIEW_NAME = 'your_view_name';

   Replace 'your_view_name' with the name of your view in all the above examples.

Using one of these methods, you can retrieve the SQL query that defines your view.

Q. Where View store the data?

In SQL, a view does not actually store data itself; instead, it's a virtual table generated dynamically from a SELECT query. When you create a view, you're essentially saving a query as an object in the database, and whenever you query that view, the underlying SELECT statement is executed to fetch the data.

The data that you see when querying a view is fetched directly from the tables or other views referenced in the view's definition. Therefore, views themselves do not store any data. They provide a convenient way to encapsulate complex queries, simplify data access, and provide a consistent interface to users, while the actual data resides in the underlying tables.

However, it's important to note that materialized views are an exception to this rule. Unlike regular views, materialized views do store data. They are precomputed and stored as a table-like structure, which can improve query performance by caching the results of expensive queries. Materialized views need to be refreshed periodically to ensure the data remains up-to-date with the underlying tables.