We are independent & ad-supported. We may earn a commission for purchases made through our links.
Advertiser Disclosure
Our website is an independent, advertising-supported platform. We provide our content free of charge to our readers, and to keep it that way, we rely on revenue generated through advertisements and affiliate partnerships. This means that when you click on certain links on our site and make a purchase, we may earn a commission. Learn more.
How We Make Money
We sustain our operations through affiliate commissions and advertising. If you click on an affiliate link and make a purchase, we may receive a commission from the merchant at no additional cost to you. We also display advertisements on our website, which help generate revenue to support our work and keep our content free for readers. Our editorial team operates independently of our advertising and affiliate partnerships to ensure that our content remains unbiased and focused on providing you with the best information and recommendations based on thorough research and honest evaluations. To remain transparent, we’ve provided a list of our current affiliate partners here.
Technology

Our Promise to you

Founded in 2002, our company has been a trusted resource for readers seeking informative and engaging content. Our dedication to quality remains unwavering—and will never change. We follow a strict editorial policy, ensuring that our content is authored by highly qualified professionals and edited by subject matter experts. This guarantees that everything we publish is objective, accurate, and trustworthy.

Over the years, we've refined our approach to cover a wide range of topics, providing readers with reliable and practical advice to enhance their knowledge and skills. That's why millions of readers turn to us each year. Join us in celebrating the joy of learning, guided by standards you can trust.

What Is a Materialized View?

By T.S. Adams
Updated: Jan 29, 2024

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.

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.

WiseGeek is dedicated to providing accurate and trustworthy information. We carefully select reputable sources and employ a rigorous fact-checking process to maintain the highest standards. To learn more about our commitment to accuracy, read our editorial process.
Discussion Comments
Share
WiseGeek, in your inbox

Our latest articles, guides, and more, delivered daily.

WiseGeek, in your inbox

Our latest articles, guides, and more, delivered daily.