Internet
Fact-checked

At WiseGEEK, we're committed to delivering accurate, trustworthy information. Our expert-authored content is rigorously fact-checked and sourced from credible authorities. Discover how we uphold the highest standards in providing you with reliable knowledge.

Learn more...

What Is a Materialized View?

T.S. Adams
T.S. Adams

The materialized view is a database object in which information requested from a database is displayed. A database is a collection of information arranged in table format. It allows the end-user to retrieve data through queries posed in the Structured Query Language (SQL) format. This enables the end-user to make requests from the database for specific types of information, such as a list of employees under age 30. This information would be accessibly displayed in the materialized view, providing is a concrete, virtual table that contains the complete list of results.

When the end-user enters a query into the database, the program immediately begins sifting through its records. It compares the information sought against each individual record in the tables selected. So, continuing the previous example, when looking for employees under 30, the database goes to the "Employees" table, and starting at the very beginning, it examines the birthdate of each, comparing it against the current date to come up with an age. All employees under age 30 are moved to the materialized view table. Only then is the final materialized view list "presented" to the end-user, providing a complete answer to the requested query.

The materialized view is a database object in which information requested from a database is displayed.
The materialized view is a database object in which information requested from a database is displayed.

Unlike a traditional view for a relational database, which stores queries information in a temporary table, the materialized view stores the queried information into an actual, permanent, concrete table. The information stored in the materialized view is cached in system memory, allowing the end-user to either make changes or update the data from time to time. For example, if the same example query — all employees under 30 — is made on day one, it remains in the system as a materialized view from that point forward.

The advantage to this is that re-accessing the information is much quicker; the information is already stored in the system. The primary disadvantage is that the information can become obsolete or expired between access times. For example, if the end-user re-accesses the same materialized view on day ten, things might have changed: someone might have just turned 30. The materialized view version will not take these changes into account until the entire query has been run again.

Discuss this Article

Post your comments
Login:
Forgot password?
Register:
    • The materialized view is a database object in which information requested from a database is displayed.
      By: Nmedia
      The materialized view is a database object in which information requested from a database is displayed.