DevOps.com

  • Latest
    • Articles
    • Features
    • Most Read
    • News
    • News Releases
  • Topics
    • AI
    • Continuous Delivery
    • Continuous Testing
    • Cloud
    • Culture
    • DataOps
    • DevSecOps
    • Enterprise DevOps
    • Leadership Suite
    • DevOps Practice
    • ROELBOB
    • DevOps Toolbox
    • IT as Code
  • Videos/Podcasts
    • Techstrong.tv Podcast
    • Techstrong.tv Video Podcast
    • Techstrong.tv - Twitch
    • DevOps Unbound
  • Webinars
    • Upcoming
    • On-Demand Webinars
  • Library
  • Events
    • Upcoming Events
    • On-Demand Events
  • Sponsored Content
  • Related Sites
    • Techstrong Group
    • Container Journal
    • Security Boulevard
    • Techstrong Research
    • DevOps Chat
    • DevOps Dozen
    • DevOps TV
    • Techstrong TV
    • Techstrong.tv Podcast
    • Techstrong.tv Video Podcast
    • Techstrong.tv - Twitch
  • Media Kit
  • About
  • Sponsor
  • AI
  • Cloud
  • Continuous Delivery
  • Continuous Testing
  • DataOps
  • DevSecOps
  • DevOps Onramp
  • Platform Engineering
  • Low-Code/No-Code
  • IT as Code
  • More
    • Application Performance Management/Monitoring
    • Culture
    • Enterprise DevOps
    • ROELBOB

Home » Blogs » Introduction to Materialized Views

Introduction to Materialized Views

Avatar photoBy: Mia Lang on March 8, 2021 Leave a Comment

More often than not, the types of queries that use materialized views are both faster, and use fewer resources than those that are still processing queries directly to base tables. But the key to understanding the potential that materialized views bring with them involves first understanding as much about them as possible.

Recent Posts By Mia Lang
  • The Value of OpenTelemetry in Observability
Avatar photo More from Mia Lang
Related Posts
  • Introduction to Materialized Views
  • Walgreens reminds us to keep our database healthy
  • Webinar: How major enterprises are bringing their database Continuous Delivery up-to-date
    Related Categories
  • Application Performance Management/Monitoring
  • Blogs
  • Business of DevOps
  • DevOps Practice
    Related Topics
  • database
  • materialized view
  • Oracle database
  • PostgreSQL
Show more
Show less

What is a Materialized View?

In the world of computing, a materialized view is a database object made up of any results that were obtained from a query. Note that this could be a local copy of data that is also stored in a separate remote location, or it could have been derived from a join result. Sometimes, a materialized view could even be created after a user performed an aggregate function, thus allowing it to act as a summary of that database information.

TechStrong Con 2023Sponsorships Available

Oracle Database first made materialized views available in version 8i of their software, and it has been a part of every release since. Just a few of the environments that support materialized views include, but are not limited to, others like SQL Server, BigQuery, PostgreSQL and Sybase SQL Anywhere.

View vs. Materialized View

What’s the difference between a regular view versus a materialized view? There are a number of important things you’ll need to keep in mind.

Chief among them, the fact that a view itself is a precise type of virtual table created via the “Create View” command. As the name suggests, it collects all data obtained from any relevant query that you’ve executed. A view will always be compiled every time it is either used or accessed in any way. This is how a view is able to make sure that you’re always getting the most updated data, no matter what – it’s literally designed to do that.

If you make any changes to the data contained inside that view, it will get pushed out to the table in which it originated. Along the same lines, any changes made to that base table are automatically pushed to the view so that the same information is in both places at once.

Because of this extra step, a view will always be a bit slower than a materialized view when it comes to performance. The upside is that this really doesn’t require much in the way of storage space. But it does mean that you’ll be sacrificing a bit in terms of performance.

With a materialized view, you’re talking about a physical copy of the information contained in that original table. This means that it isn’t updated every time you interact with it – you have to go out of your way to update it manually. Or, if you’d prefer, you can have it update using pre-defined triggers.

Because of this, a materialized view is always going to respond much faster than its traditional counterpart. However, if you don’t make an effort to confirm that it has been updated, you also run the risk of retrieving stale data.

Why Use a Materialized View?

So at this point, you’re likely asking yourself, “Why use a materialized view in the first place? What can this do for me that a simple view cannot?” The key thing to understand has to do with the costs that you accrue whenever a database is queried.

Even a simple query still needs to be parsed, validated, planned, optimized and executed. In an operational sense, this equates to CPU time. This means memory usage. This even directly impacts your opportunity costs as time goes on. All of this adds up – especially as the application you’re working with continues to evolve and become more resource-intensive.

Savvy developers are always looking for a way to cut costs, and materialized views are a big part of how they’re able to do it. Remember that the results obtained are always kept in memory – meaning that they are only updated when absolutely needed. This is the opposite of what happens when you query tables or use logical views; those results are updated constantly. Therefore, using materialized views is a great way to significantly reduce your costs without impacting performance in a way that will also affect your end users.

Materialized Views Best Practices

For best results, you should always make sure that your materialized view reflects query patterns against the original table you’re working with. Don’t just create a materialized view for every iteration of a query. Instead, create one to help focus on a broader set of queries.

Likewise, if the base table you’re working with is partitioned, your materialized view may grow to a size larger than you’re comfortable with. Therefore, it should be partitioned, as well, to preserve the performance benefits it is supposed to bring with it.

Filed Under: Application Performance Management/Monitoring, Blogs, Business of DevOps, DevOps Practice Tagged With: database, materialized view, Oracle database, PostgreSQL

« How Open Source Software Powers Digital Innovation
Despite the Pandemic, DevOps Salaries Rose in 2020 »

Techstrong TV – Live

Click full-screen to enable volume control
Watch latest episodes and shows

Upcoming Webinars

Five Best Practices for Safeguarding Salesforce Data
Thursday, February 2, 2023 - 1:00 pm EST
Modernizing Software Delivery for Regulated Industries With Harness and AWS
Thursday, February 2, 2023 - 3:00 pm EST
Automating Day 2 Operations: Best Practices and Outcomes
Tuesday, February 7, 2023 - 3:00 pm EST

Sponsored Content

The Google Cloud DevOps Awards: Apply Now!

January 10, 2023 | Brenna Washington

Codenotary Extends Dynamic SBOM Reach to Serverless Computing Platforms

December 9, 2022 | Mike Vizard

Why a Low-Code Platform Should Have Pro-Code Capabilities

March 24, 2021 | Andrew Manby

AWS Well-Architected Framework Elevates Agility

December 17, 2020 | JT Giri

Practical Approaches to Long-Term Cloud-Native Security

December 5, 2019 | Chris Tozzi

Latest from DevOps.com

Automation Challenges Holding DevOps Back
February 1, 2023 | Mike Vizard
5 Unique Challenges of Mobile App Testing
February 1, 2023 | Frank Moyer
Cisco AppDynamics Survey Surfaces DevSecOps Challenges
January 31, 2023 | Mike Vizard
Jellyfish Adds Tool to Visualize Software Development Workflows
January 31, 2023 | Mike Vizard
3 Performance Challenges as Chatbot Adoption Grows
January 31, 2023 | Christoph Börner

TSTV Podcast

On-Demand Webinars

DevOps.com Webinar ReplaysDevOps.com Webinar Replays

GET THE TOP STORIES OF THE WEEK

Most Read on DevOps.com

Atlassian Extends Automation Framework’s Reach
January 26, 2023 | Mike Vizard
Software Supply Chain Security Debt is Increasing: Here̵...
January 26, 2023 | Bill Doerrfeld
The Strategic Product Backlog: Lead, Follow, Watch and Explo...
January 26, 2023 | Chad Sands
Stream Big, Think Bigger: Analyze Streaming Data at Scale
January 27, 2023 | Julia Brouillette
What’s Ahead for the Future of Data Streaming?
January 27, 2023 | Danica Fine
  • Home
  • About DevOps.com
  • Meet our Authors
  • Write for DevOps.com
  • Media Kit
  • Sponsor Info
  • Copyright
  • TOS
  • Privacy Policy

Powered by Techstrong Group, Inc.

© 2023 ·Techstrong Group, Inc.All rights reserved.