Virtual tables in SQL - Views, Materialized View, Global Temporary Table and Inline View

Virtual tables in SQL - Views, Materialized View, Global Temporary Table and Inline View

In Oracle Database, virtual tables refer to tables that do not physically store data but provide a logical view or representation of data. These tables are not actual, permanent tables in the database, but instead, their content is derived dynamically at query time. There are a couple of concepts that fall under this category:

---

1. Views:

A view is a virtual table that is defined by a SELECT query. The view doesn't store data itself but pulls data from one or more tables when accessed.

Views simplify complex queries, abstract the database structure, and enhance security by exposing only specific data to the user.

Example:

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

Here, employee_view is a virtual table showing data dynamically from the employees table based on the query.

---

2. Materialized Views:

Unlike standard views, materialized views store a snapshot of the data physically. However, they still act as virtual tables when queried, providing precomputed data, often used for performance improvement in complex queries.

The data in a materialized view can be refreshed periodically.

Example:

CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(sales_amount)
FROM sales
GROUP BY region;

---

3. Global Temporary Tables:

A Global Temporary Table (GTT) is a table where data is stored temporarily and is session-specific. Data in GTTs is not permanently stored in the database.

The structure is defined like a regular table, but the data is private for each session and can be discarded when the session ends or when the transaction is complete.

Example:

CREATE GLOBAL TEMPORARY TABLE temp_sales_data (
   sales_id NUMBER,
   product_id NUMBER,
   sales_amount NUMBER
) ON COMMIT DELETE ROWS;

---

4. Inline Views:

An inline view is a subquery used within the FROM clause of a SELECT query, essentially acting as a virtual table for that specific query.

Example:

SELECT *
FROM (SELECT employee_id, first_name FROM employees WHERE department_id = 10) e
WHERE e.first_name LIKE 'J%';

In summary, virtual tables like views and temporary tables in Oracle provide logical abstractions, allow for data manipulation, simplify complex queries, and offer flexibility without requiring the physical storage of data.