The other day we were having some slow database view at our project. It was the main view of the new module we were developing, and it was slowing down the whole application to an unacceptable point. It was a rather complex view and could not be optimized much more. So, what was the solution we adopted? We materialized the view.

Regular views store just the SQL script needed to retrieve the data, and every time they are consulted the stored SQL query is executed. However, materialized views not only store the script, but the retrieved records as well, making it much faster.

As with regular views, the SQL script defines which data is to be retrieved. However, with materialized views this script is executed when the view is first created, and the result is physically stored in a real table. From then on, when the materialized view is accessed, the DBMS transforms the SQL sentence targeting this table instead of the more expensive procedure of actually querying all elements involved in the SQL sentence.This table can be defined with the same storage parameters used for regular tables (tablespace, etc.). Indexes can be used for materialized views as well, improving the performance even more.

Obviously, the greater the number of tables used, the more benefits we’ll get by using a materialized view. In our project, we were accessing 15 tables: we cut the execution time from 4 seconds to 100 ms by implementing a materialized view… this means that execution was 40 times faster!!! Now, that’s what I call optimization!

However, when designing a materialized view you need to keep in mind that it’s actually going to become a table with static data… and you need to specify when and how that data is going to be updated. Not taking this into account might lead the application to display outdated or incorrect data. This means that we need to evaluate the benefits and drawbacks we’ll get by using a materialized view: it might as well turn out that it slows down the whole system. So I encourage you to do some design and testing before actually deploying the view into production environment.

How to build a Materialized view in Oracle

This is the command used to create materialized views:

CREATE MATERIALIZED VIEW materialized_view_name
 [TABLESPACE ts_name]
 [PARALELL (DEGREE n)]
 [BUILD {INMEDIATE|DEFERRED}]
 [REFRESH {FAST|COMPLETE|FORCE|NEVER|ON COMMIT}]
 [{ENABLE|DISABLE} QUERY REWRITE]
AS SELECT ... FROM ... WHERE ...

The Build parameter

If you choose the BUILD INMEDIATE option, the table associated with the materialized view will be populated with data at creation time. On the contrary, if you’d rather have the table populated in the moment when the first access to the view is performed, then use the BUILD DEFERRED option.

The Refresh parameter

This option indicates the mechanism Oracle will use to update the table representing the materialized view. There quite a few options here, and I would say it’s one of the most important considerations when building a materialized view. I will write a new article explaining the different updating approaches we can use to implement these views.

The Query Rewrite parameter

The ENABLE/DISABLE QUERY REWRITE option indicates whether the Oracle optimizer is allowed to rewrite SQL sentences in a way in which the materialized view will be used in the execution phase instead of the base tables included in the original SQL sentence. This is a rather complex subject, and I will dedicate a post dealing with it.

Other DBMS

This article focused on Oracle’s Materialized Views. But most DBMS implement some kind of view optimization these days. In case you’re using some other DBMS, you might be interested in searching for:

  • Microsoft SQL Server: Indexed views.
  • DB2: Materialized query table.
  • Some other DBMS (for example MySQL and PostgreSQL) don’t have such a feature, but you can implement your own manually using triggers.

Further reading

Related posts:

  1. Which database to use for a personal project?
  2. Persistance layer: object-relational mapping tools
  3. Entity Framework