Understanding Views and Materialized Views in Oracle with Examples

Views

• Definition: Views are virtual tables created using SQL queries. They don't store actual data but rather provide a dynamic view of underlying data in other tables.
• Functionality: Views offer a way to:
  • Simplify complex queries by combining multiple tables into a single view.
  • Restrict access to specific columns or rows of underlying data.
  • Provide a layer of abstraction, making the database structure easier to understand.
• Behavior: When a query is executed against a view, Oracle dynamically re-evaluates the view's definition and joins the underlying tables to produce the result.
• Performance: Views can be slower than direct table access, especially for complex views or when frequently updated underlying data is involved.

Materialized Views

• Definition: Materialized Views are pre-computed copies of views stored as physical tables. They contain actual data and are updated periodically.
• Functionality: Materialized Views are used to:
  • Improve query performance for frequently executed queries against complex views.
  • Reduce load on the database server by caching query results.
  • Provide a snapshot of data at a specific point in time.
• Behavior: When a query is executed against a Materialized View, Oracle directly accesses the stored data, eliminating the need to re-evaluate the view's definition and join underlying tables.
• Maintenance: Materialized Views require periodic refreshing to keep them synchronized with the underlying data. This can be done manually or automatically using refresh mechanisms like Fast Refresh, Incremental Refresh, or Complete Refresh.

Key Differences:

SQL Programming Considerations:

• View Creation: Use the CREATE VIEW statement to define views.
• Materialized View Creation: Use the CREATE MATERIALIZED VIEW statement to define materialized views.
• Querying: Both views and materialized views can be queried using standard SQL syntax.
• Refreshing: For materialized views, use the REFRESH MATERIALIZED VIEW statement to manually refresh them. You can also configure automatic refreshing using database scheduling mechanisms.

Understanding Views and Materialized Views in Oracle with Examples

Creating a View

A view is a virtual table defined by a SELECT statement. It doesn't store actual data, but provides a dynamic view based on the underlying tables.

Example:

CREATE VIEW employee_view AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;

SELECT * FROM employee_view;

Creating a Materialized View

A materialized view is a pre-computed copy of a view stored as a physical table. It can improve query performance for frequently executed queries.

CREATE MATERIALIZED VIEW employee_mv
REFRESH ON DEMAND
AS
SELECT employee_id, first_name, last_name, department_id
FROM employees;

SELECT * FROM employee_mv;

Key Differences Illustrated

• Data Storage:
  • View: Doesn't store data.
  • Materialized View: Stores pre-computed data.
• Query Performance:
  • View: Queries can be slower due to dynamic evaluation.
  • Materialized View: Queries can be faster due to pre-computed data.
• Maintenance:
  • View: No specific maintenance required.
  • Materialized View: Requires periodic refreshing to keep it synchronized with the underlying data.

Example: Using a Materialized View for Performance Improvement

Consider a frequently executed query to get the total salary for each department:

SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

If this query is slow due to the volume of data, creating a materialized view can improve performance:

CREATE MATERIALIZED VIEW department_totals_mv
REFRESH ON COMMIT
AS
SELECT department_id, SUM(salary) AS total_salary
FROM employees
GROUP BY department_id;

Then, the query can be rewritten to use the materialized view:

SELECT * FROM department_totals_mv;

This will directly access the pre-computed data in the materialized view, potentially resulting in significant performance gains.

Alternative Methods for Views and Materialized Views in Oracle

While views and materialized views are the primary mechanisms for creating virtual tables and pre-computed data in Oracle, there are some alternative approaches that might be suitable in certain scenarios:

Database Triggers:

• Functionality: Triggers can be used to automatically update a table based on changes in another table.
• Similar to Views: Triggers can provide a view-like functionality by maintaining a derived table.
• Limitations: Triggers can be complex to manage and can impact database performance if not used carefully.

CREATE TRIGGER update_department_totals
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
  UPDATE department_totals
  SET total_employees = total_employees + CASE WHEN INSERTING OR UPDATING THEN 1 ELSE -1 END,
      total_salary = total_salary + CASE WHEN INSERTING OR UPDATING THEN :new.salary ELSE -1 * :old.salary END
  WHERE department_id = :new.department_id;
END;

Stored Procedures:

• Functionality: Stored procedures can be used to encapsulate complex logic and return result sets.
• Similar to Views: Stored procedures can be used to create virtual tables by returning query results.
• Limitations: Stored procedures might require more development effort compared to views or materialized views.

CREATE PROCEDURE get_employee_data (p_department_id IN NUMBER)
AS
BEGIN
  SELECT employee_id, first_name, last_name
  FROM employees
  WHERE department_id = p_department_id;
END;

External Tables:

• Functionality: External tables allow you to access data from external sources (e.g., files, databases) as if it were stored in Oracle tables.
• Similar to Views: External tables can provide a view-like interface to external data.
• Limitations: External tables might have performance limitations depending on the external data source and access methods.

CREATE TABLE employee_external_table
EXTERNAL TABLE
(
  employee_id NUMBER,
  first_name VARCHAR2,
  last_name VARCHAR2
)
LOCATION ('/path/to/employee_data.csv')
FORMAT 'CSV'
REJECT LIMIT 5;

Choosing the Right Approach: The best approach depends on your specific requirements, including:

• Performance: Materialized views are generally the most performant option for frequently executed queries.
• Complexity: Views are simpler to create and maintain than triggers or stored procedures.
• Data Source: External tables are useful for accessing data from external sources.
• Maintenance: Triggers and stored procedures can be more complex to manage than views or materialized views.