Views and materialized views

A view is a saved query

Some queries are written again and again: "delivered orders with their totals" appears in the sales report, the finance export and the dashboard. A view gives that query a name. After CREATE VIEW, you can SELECT from it, join it and filter it as if it were a table.

CREATE VIEW delivered_order_totals AS
SELECT o.id AS order_id, c.name AS customer, o.order_date,
       SUM(oi.qty * oi.price) AS total
FROM orders o
JOIN customers c    ON c.id = o.customer_id
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'Delivered'
GROUP BY o.id, c.name, o.order_date;

SELECT customer, COUNT(*) AS orders, SUM(total) AS spent
FROM delivered_order_totals
GROUP BY customer
ORDER BY spent DESC;
-- Ravi Kumar | 2 | 6497
-- Asha Verma | 2 | 4028

Because a view stores only the query, its results are always current, and it uses no extra space. It is also no faster than the query inside it: the database runs that query (usually merged into yours) every time. To speed up a view, index its underlying tables.

Views for security and stable interfaces

A view can expose some columns and hide others. A support team may see customers' names and cities but not emails or phone numbers. In PostgreSQL, SQL Server and MySQL you GRANT SELECT on the view and give no access to the table itself. Views also act as a stable interface: if you later split a table, a view with the old name and columns keeps old reports working.

CREATE VIEW customers_public AS
SELECT id, name, city FROM customers;
SELECT email FROM customers_public;
-- Error: no such column: email

Can you change data through a view?

Some engines let you INSERT, UPDATE or DELETE through simple views. In PostgreSQL a view is automatically updatable when it reads from one table with no GROUP BY, DISTINCT, aggregates, UNION or LIMIT; MySQL has similar rules. WITH CHECK OPTION stops changes that would make a row vanish from the view. Views with joins and totals, like delivered_order_totals, are read-only.

In SQLite every view is read-only. UPDATE customers_public SET city = 'Nagpur' fails with "cannot modify customers_public because it is a view". An INSTEAD OF trigger can redirect such changes to the real table; module 11 covers triggers.

Materialized views: storing the result

Sometimes the query behind a view is heavy, such as a year of sales grouped by city and category, and nobody needs it to the second. A materialized view runs the query once and stores the rows. Reading it is as fast as reading a small table, and you can index it. The price: it goes stale until refreshed.

-- PostgreSQL
CREATE MATERIALIZED VIEW category_sales AS
SELECT p.category, SUM(oi.qty * oi.price) AS revenue
FROM order_items oi JOIN products p ON p.id = oi.product_id
GROUP BY p.category;

REFRESH MATERIALIZED VIEW category_sales;               -- rebuilds; readers wait
REFRESH MATERIALIZED VIEW CONCURRENTLY category_sales;  -- readers keep reading; needs a UNIQUE index on the view

• SQL Server: an indexed view. Create the view WITH SCHEMABINDING, then a UNIQUE CLUSTERED INDEX on it; SQL Server then keeps the stored result up to date on every change.
• Oracle: materialized views with several refresh options.
• MySQL and SQLite: no materialized views. Use a summary table.

Emulating one in SQLite: a summary table

CREATE TABLE category_sales (category TEXT PRIMARY KEY, revenue INTEGER NOT NULL);

-- Refresh (run on a schedule, inside one transaction):
BEGIN;
DELETE FROM category_sales;
INSERT INTO category_sales (category, revenue)
SELECT p.category, SUM(oi.qty * oi.price)
FROM order_items oi
JOIN orders o   ON o.id = oi.order_id
JOIN products p ON p.id = oi.product_id
WHERE o.status <> 'Cancelled'
GROUP BY p.category;
COMMIT;

Common mistakes

• Expecting a plain view to be faster than its query. Fix: index the base tables, or use a materialized view or summary table.
• Stacking views on views on views until nobody can read the plan. Fix: keep views one or two levels deep.
• Forgetting to refresh a materialized view or summary table. Fix: schedule the refresh and show "updated at" on the report.
• Using SELECT * inside a view. Fix: list columns, so the view does not change shape when the table does.