Fake Materialized Views

November 27, 2006 - 3 minute read -
performance database oracle

In a previous post, I discussed materialized views in Oracle. I wanted to share a relatively simple technique that can be used to create similar functionality in Oracle or another database.

Why Use Something Else?

Materialized Views are a feature available in Oracle. If you're not using Oracle, then that's reason enough on its own. Materialized Views also have limitations in terms of how they are refreshed. Refreshing is the concept of updating the contents of the view. This can be done as a fast refresh when the base tables that it relies on changes. Certain things like union or other more "complex" SQL prevents any sort of fast refreshing from going on. The other option for updating the materialized view is a periodic update. Every n minute you can have the view refreshed. This is fine in certain circumstances, but there are use cases where this will be a show stopper. So what do you do?

Yes, I ran into the refreshing problem and had to work with a DBA to figure out another way to accomplish the same thing.

Table, View, Trigger

A materialized view is an object in the database that is backed by a table behind the scenes. We often want to use them as a way to denormalize data for reporting or performance reasons. Knowing this, you can create a structure that is a directly a table instead. This table will be the structure that you query against. To keep the data up to date you can implement a trigger that responds to changes in the base table to keep the dernomalized table up-to-date.

The simplest way to do this is to create a regular database view of the data that you want. Why not use the view directly? When you query a view it re-runs the query against the base table. This can be an expensive operation and put a lot of load on your database. Once you have a view containing the data that you want, you can then "copy" the data into your table for day-to-day querying.

Example

create or replace view V$MY_VIEW AS
select table1.v1, table2.v2, table2.v3
from Table as table1
    inner join Child_Table table2
        on table1.id=table2.parent_id;

create table MY_TABLE
    value1 varchar2(32),
    value2 varchar2(128),
    value3 int;
insert into MY_TABLE select * from V$MY_VIEW;

create trigger UPDATE_MY_TABLE
after insert or update or delete on Table
begin
    delete from MY_TABLE;
    insert into MY_TABLE select * from V$MY_VIEW;
end;

Conclusion

This is a denormalization technique that you can use for some read performance benefits in certain scenarios. Of course you will likely suffer from write performance when you do this. This particular technique is likely only useful if there are relatively few rows in your dependent tables, otherwise the performance will probably degrade quickly.

Obviously this is a pretty simple example, but hopefully it would help give you some inspiration if you need a similar solution.