Introduction to Database Views
To understand materialised views, we first need to understand what regular views are in a database.
In a relational database, you’re familiar with tables - structures that store data in rows and columns. A view is essentially a virtual table created from one or more existing tables. Unlike physical tables that store data, a view doesn’t store any data itself; it’s just a saved SQL query that creates a virtual representation of data from other tables.
Think of a view as a window that shows specific data from the underlying tables in a format you define. When you query a view, the database executes the stored query in real-time to fetch the data.
What is a Materialised View?
A materialised view takes the concept of a view one step further. Unlike a regular view which runs its query each time it’s accessed, a materialised view actually stores the result data physically, like a regular table.
When you create a materialised view, the database:
- Executes the defining query
- Stores the results as actual data on disk
- Makes this stored data available for querying
Key Differences Between Regular Views and Materialised Views
| Regular Views | Materialised Views |
|---|---|
| Don’t store data | Store physical data |
| Query executes every time the view is accessed | Query executes only when the view is refreshed |
| Always show the most current data | Show data as of the last refresh |
| Use more CPU resources when queried | Use more disk space but less CPU when queried |
| No additional storage required | Require storage space for the result set |
Advantages of Materialised Views
- Performance: Queries run faster since the data is pre-computed and stored
- Reduced load: Complex calculations are performed once rather than every time the view is queried
- Consistency: All users see the same snapshot of data until the next refresh
- Offline availability: Data can be available even if source tables are temporarily inaccessible
When to Use Materialised Views
Materialised views are particularly useful when:
- You frequently run complex queries or aggregations
- The source data doesn’t change very often
- Query performance is more important than having the most up-to-date data
- You need to create local copies of data from remote databases
Refreshing Materialised Views
Since materialised views store data at a point in time, they need to be refreshed to show current data. Refreshing options typically include:
- Complete refresh (recreate the entire view)
- Incremental refresh (update only changed data)
- Scheduled refresh (automatically update at defined intervals)
- Manual refresh (update on demand)
Example Scenario
Imagine you have a sales database with millions of transactions. Every day, executives need to see regional sales totals:
- With a regular view, each executive’s query would recalculate totals from millions of rows
- With a materialised view, the totals are calculated once and stored, making reports faster to generate
Materialised views are powerful tools for optimizing database performance, especially for read-heavy workloads with data that doesn’t change frequently.