Composite Views

In Oracle, a composite view is a view that is built by combining data from multiple tables, usually through joins, subqueries, or complex queries. Unlike simple views, which are based on a single table, composite views allow for more complex data retrieval by pulling together data from different sources, and this can include aggregation, filtering, and transformations.

Key Points About Composite Views:

1. Definition of a Composite View A composite view:
• Is built from multiple tables.
• May contain joins (INNER, LEFT, RIGHT, etc.), subqueries, or other complex operations.
• Provides a way to present data from different tables as if it were a single virtual table.
• Can contain aggregation (e.g., SUM(), COUNT(), AVG()) and grouping (e.g., GROUP BY).
• Often used for reporting, data analysis, and complex queries.

2. Creating a Composite View

A composite view is created using the CREATE VIEW statement, where you can specify the tables to join or combine, as well as any transformations, filters, or groupings.

Basic Syntax:

CREATE VIEW view_name AS

SELECT columns

FROM table1

JOIN table2 ON table1.column = table2.column

WHERE condition;

Example: Let's say you have two tables:

• employees (contains employee details)
• departments (contains department information)

You can create a composite view that joins these two tables:

CREATE VIEW employee_department_view AS

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

FROM employees e

JOIN departments d ON e.department_id = d.department_id;

This view employee_department_view combines data from both the employees and departments tables into a single virtual table.

3. Types of Joins in Composite Views

When creating a composite view, you can join multiple tables using different types of joins:

• INNER JOIN: Returns only the rows where there is a match in both tables.
• LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, along with matching rows from the right table. If there’s no match, NULL values are returned for the right table.
• RIGHT JOIN (or RIGHT OUTER JOIN): Similar to LEFT JOIN, but it returns all rows from the right table.
• FULL JOIN (or FULL OUTER JOIN): Returns rows when there is a match in one of the tables.
• CROSS JOIN: Returns the Cartesian product of both tables, meaning all possible combinations of rows.

Example with an INNER JOIN:

CREATE VIEW employee_salary_view AS

SELECT e.employee_id, e.first_name, e.last_name, s.salary

FROM employees e

JOIN salaries s ON e.employee_id = s.employee_id;

4. Aggregations and Grouping in Composite Views

Composite views can include aggregation functions (e.g., SUM(), AVG(), COUNT()) to summarize data, and grouping data using GROUP BY.

Example: Suppose you want to create a view that shows the total salary expenditure per department:

CREATE VIEW department_salary_view AS

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

FROM employees e

JOIN departments d ON e.department_id = d.department_id

JOIN salaries s ON e.employee_id = s.employee_id

GROUP BY d.department_name;

In this case, the department_salary_view shows the total salary for each department.

5. Subqueries in Composite Views

Subqueries can be used in composite views to perform calculations, filters, or even select data from another view.

Example: You can use a subquery in the SELECT clause to calculate the average salary for employees in a department:

CREATE VIEW department_avg_salary_view AS

SELECT department_name,

       (SELECT AVG(salary)

        FROM employees e

        JOIN salaries s ON e.employee_id = s.employee_id

        WHERE e.department_id = d.department_id) AS avg_salary

FROM departments d;

This view calculates the average salary for each department by using a subquery within the SELECT clause.

6. Advantages of Using Composite Views

• Data Abstraction: Composite views can hide the complexity of multiple tables, joins, and subqueries, making data access easier for users.
• Simplify Complex Queries: A complex query that involves multiple tables can be abstracted into a composite view, which can be queried like a simple table.
• Data Security: You can limit the columns and rows exposed to users by creating a composite view that filters or aggregates the data before presenting it.
• Centralized Reporting: Composite views are commonly used to provide a unified view of data for reporting and analytics, simplifying queries for business users.

7. Restrictions of Composite Views

• Non-Updatable Views: Composite views that include joins, subqueries, or aggregation operations are often non-updatable. This means you cannot directly INSERT, UPDATE, or DELETE data via the view.
• Complexity: A composite view that involves multiple joins or aggregations may have performance implications, particularly with large tables, as the query behind the view must be executed each time the view is queried.
• Maintenance: If the underlying tables change (e.g., columns are added or removed), the composite view may require modification. Additionally, if the view is used in other objects or queries, those may break if the view structure changes.

8. Updating Data Through a Composite View

In most cases, data cannot be directly updated through a composite view because the view involves multiple tables and possibly aggregations or complex transformations. Oracle typically allows data updates only through simple views or views that directly correspond to a single table.

However, if the composite view is designed in a way that the base tables can be uniquely identified and all the necessary fields are available, you might be able to use INSTEAD OF TRIGGERS to enable updates.

Example of an INSTEAD OF trigger for an update on a composite view:

CREATE OR REPLACE TRIGGER update_salary

INSTEAD OF UPDATE ON employee_salary_view

FOR EACH ROW

BEGIN

   UPDATE employees

   SET salary = :NEW.salary

   WHERE employee_id = :OLD.employee_id;

END;

This trigger allows updates on the employee_salary_view to be passed to the employees table.

9. Dropping a Composite View

You can drop a composite view using the DROP VIEW command:

DROP VIEW employee_department_view;

This will remove the composite view from the database, but the underlying tables and data are unaffected.

10. Performance Considerations

• Execution Plan: Oracle’s query optimizer will create an execution plan for the composite view when it's queried, which can sometimes lead to slower performance if the view contains complex joins or subqueries.
• Indexes: To improve the performance of composite views, especially those involving joins, make sure that the relevant columns in the underlying tables are indexed.
• Materialized Views: If performance is a concern, consider using materialized views. A materialized view stores the result of the query physically, which can speed up retrieval time, especially for complex queries.

11. Composite Views vs. Materialized Views

• A composite view is a virtual object that executes a query when queried, meaning it doesn’t store data.
• A materialized view, on the other hand, stores the results of the query and periodically refreshes the data, which improves query performance by avoiding repeated computation of the query.

Conclusion:

A composite view in Oracle is an essential tool for creating complex, abstracted, and reusable queries that combine data from multiple tables. It simplifies complex data retrieval, but it also comes with challenges, especially around performance and data updates. By using composite views, you can provide a centralized, simplified interface to complex database schemas.