Stack Overflow
Stack Overflow

Questions tagged [materialized-views]

A materialized view is a database object that contains the results of a query. They can be local copies of data located remotely, or can be used to create summary tables based on aggregations of a table's data. Materialized views are also known as snapshots.

A materialized view is a database object that contains the results of a query. They can be local copies of data located remotely, or can be used to create summary tables based on aggregations of a table's data. Materialized views are also known as snapshots.

When posting questions with the materialized-views tag please add another tag with the RDBMS involved(Oracle, Sql Server, etc.)

Oracle's Materialized Views documentation here

839 questions
424
votes
8 answers

What is the difference between Views and Materialized Views in Oracle?

What is the difference between Views and Materialized Views in Oracle?
juan
  • 80,295
  • 52
  • 162
  • 195
119
votes
3 answers

How can I ensure that a materialized view is always up to date?

I'll need to invoke REFRESH MATERIALIZED VIEW on each change to the tables involved, right? I'm surprised to not find much discussion of this on the web. How should I go about doing this? I think the top half of the answer here is what I'm looking…
John Bachir
  • 22,495
  • 29
  • 154
  • 227
94
votes
2 answers

Refresh a materialized view automatically using a rule or notify

I have a materialized view on a PostgreSQL 9.3 database which seldom changes (about twice a day). But when it does, I'd like to update its data promptly. Here is what I was thinking about so far: There is a materialized view mat_view which gets its…
mawimawi
  • 4,222
  • 3
  • 33
  • 52
79
votes
12 answers

Materialized View vs. Tables: What are the advantages?

It's clear to me why a materialized view is preferable over just querying a base table. What is not so clear is the advantage over just creating another table with the same data as the MV. Is the only advantage to the MV really just the ease of…
seth
  • 2,741
  • 3
  • 20
  • 15
68
votes
9 answers

How to refresh materialized view in oracle

Iam trying to refresh the materialized view by using: DBMS_MVIEW.REFRESH('v_materialized_foo_tbl') But it's throwing invalid sql statement. Then I have created a stored procedure like this: CREATE OR REPLACE PROCEDURE MAT_VIEW_FOO_TBL IS BEGIN …
Srinivas
  • 1,516
  • 3
  • 17
  • 27
35
votes
4 answers

Is there a postgres command to list/drop all materialized views?

I am creating multiple views in my code and each time the code is run, I would like to drop all the materialized views generated thus far. Is there any command that will list all the materialized views for Postgres or drop all of them?
user1150989
  • 493
  • 1
  • 4
  • 9
32
votes
4 answers

Is it possible to have an indexed view in MySQL?

I found a posting on the MySQL forums from 2005, but nothing more recent than that. Based on that, it's not possible. But a lot can change in 3-4 years. What I'm looking for is a way to have an index over a view but have the table that is viewed…
Thomas Owens
  • 114,398
  • 98
  • 311
  • 431
31
votes
6 answers

Check last refreshed time for materialized view

I've got a materialized view called price_changes used for some reporting. I've also got a cron job refreshing the materialized view with refresh materialized view price_changes. Everything is working great. I'd like to give users looking at the…
spike
  • 9,794
  • 9
  • 54
  • 85
29
votes
3 answers

Materialized Views - Identifying the last refresh

I presently access a series of views and materialized views. The materialized are maintained by a third party who offers little information regarding the frequency and success of the materialization. Of late the materialized views have failed to…
Scott
  • 293
  • 1
  • 3
  • 4
28
votes
3 answers

How can I prevent materialized views from refreshing during pg_restore?

I have created a dump of the database using pg_dump in "custom" format (-Fc). This format allows for pg_restore to be invoked with the "jobs" option (-j8). The jobs options starts 8 processes, and restores the vast majority of relations in my…
Kirk Roybal
  • 17,273
  • 1
  • 29
  • 38
27
votes
1 answer

Refresh materialized views with concurrency

I have a PostgreSQL DB, where I use materialized views. The problem occurs when I try to refresh these materialized views. REFRESH MATERIALIZED VIEW product_cat_mview; REFRESH MATERIALIZED VIEW productsforproject; My solution is, when the user…
Volodymyr Zavada
  • 579
  • 2
  • 8
  • 18
27
votes
2 answers

Is it possible to partially refresh a materialized view in PostgreSQL?

In Oracle, it is possible to refresh just part of the data. But in PostgreSQL, materialized views are supported since 9.3 (the current version now), which is not so long. So I wonder: is it possible to refresh just part of the data in the…
Pavel V.
  • 2,653
  • 10
  • 43
  • 74
24
votes
2 answers

Refreshing materialized view CONCURRENTLY causes table bloat

In PostgreSQL 9.5 I've decided to create a materialized view "effects" and scheduled an hourly concurrent refresh, since I wanted it to be always available: REFRESH MATERIALIZED VIEW CONCURRENTLY effects; In the beginning everything worked well, my…
Alechko
  • 1,406
  • 1
  • 13
  • 27
21
votes
1 answer

Create primary key on materialized view in Postgres

How can I create a primary key on a materialized view in Postgres? ALTER MATERIALIZED VIEW my_mat_view ADD PRIMARY KEY (id) returns error: Error in query: ERROR: "my_mat_view" is not a table
111
  • 1,788
  • 1
  • 23
  • 38
21
votes
1 answer

How can a materialized view be created in sqlite?

I've done countless searches on materialized views and SQLite. Of what I can find there seems to be mentions in 2004 and 2006 that SQLite DOES NOT have materialized views. Followed immediately by SQLite's changelog from March 2008 where it…
McAden
  • 13,714
  • 5
  • 37
  • 63
1
2 3
…
55 56