Learn something new every day
More Info... by email
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.