Oracle Materialized Views

October 29, 2006 - 4 minute read -
hibernate performance database oracle

So the existence of Materialized Views might not be news to the Oracle DBAs around the world, but I present topics from the perspective of a software developer. As software developers we often have to use databases with our applications. As a user of a database, I think it is very important that software developers know what is available to them to leverage when they build their applications. I don't want developers to be afraid of the database.

(As an aside, I have not found Materialized Views in any database other than Oracle. If you know of any others that support this, please leave a comment.)

What Are Materialized Views?

Database Views offer a way to encapsulate a query in the database and present it to a caller in a way that it looks like a regular table. Every time you query a view, join a view to another table or take similar actions, the query that makes up the view is rerun against the database.

Materialized views are a similar concept to regular views but with one very interesting difference. Materialized views are backed by a real object in the database. When a materialized view is created the query is run and a table (or a table-like structure) is created in the database. Materialized views, like regular Views, can be read-only or can be configured as read-write as well. The other thing that you can configure is who that materialized view is refreshed. The interesting thing is that these materialized views can be asynchronously refreshed in the database when dependent tables are changed. The refresh rules can be everything from never to whenever a row is updated, deleted or inserted. There are a number of rules and limitations on the various refresh schemes, rules that are too complicated to address in a short article, which means having the help of a good DBA would likely be very helpful.

This denormalization can give you really good performance gains. Of course you can do this with a View as well, but the database has to do even less work in the case of a materialized view because all of the relationships, aggregations, etc are pre-calculated and the results stored in a database object.

How Do I Use Them in my Application?

Tying this back into software development, how do we make use of them in our applications? The good news is that this is really straight forward. Using tools like Hibernate, you can map to materialized views just like you can to real tables. You can query them using JDBC (or PHP or Ruby Active Record) the same as you would a regular table. What I've been finding is that Materialized Views can be a great tool for denormalization. You can maintain a nicely normalized database schema for you application, but use the Materialized Views to offer some denormalized views of the data.

Some Example of Materialized View Use Cases:

  • Pre-calculatd Aggregate Values (sum, max, min)
  • Flatten hierarchies
  • Giving pre-filtered data selections
  • Anything that involves a complicated or slow series of calculations or joins

In an application I'm currently working with I found it very helpful to map parent-child relationships of objects using Hibernate through a materialized view to flatten a hierarchy. (Using the many-to-many mapping where the relationship table was a Materialized View.)

Example: Company -> Division -> Department -> Employee

To answer the question of who works for a given Company you would join all the way down through those tables. Now image you have just one logical Organization tables that is self-referential (i.e. the Division of a Company has a parent_id of another row in the Organization table). Those hierarchical queries can be complicated and expensive. But you don't suffer from the expense of the query (or many queries) if you flatten the hierarchy and create a "denormalized" table that maps Employees to their Company. The flattened tables or calculation tables that you choose to use are, of course, driven by your applications need for the data.

Example Query

A pre-calculated value table:

create materialized view dept_salary as
select dept.id as dept_id, sum(emp.salary) as total, avg(emp.salary) as avg,
    min(emp.salary) as min, max(emp.salary) as max
from Department dept
    inner join Employee emp
        on emp.dept_id=dept.id
group by dept.id

Some More Reading

Ask Tom "Materialized Views Secrets of Materialized Views Materialized Views for Hierarchy Expansion