Defined like a view but holding persistent data like a table, Materialized Views are a feature of a number of RDBMSs including Oracle, DB2 and postgres. SQL Server has a similar feature called an Indexed View that is considered similar enough to come under this tag.
Questions tagged [materialized-view]
290 questions
81
votes
7 answers
Writing a simple bank schema: How should I keep my balances in sync with their transaction history?
I am writing the schema for a simple bank database. Here are the basic specifications:
The database will store transactions against a user and currency.
Every user has one balance per currency, so each balance is simply the sum of all…
Nick Chammas
- 14,810
- 17
- 76
- 124
52
votes
2 answers
Refresh materalized view incrementally in PostgreSQL
Is it possible to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?
Consider this table & materialized view:
CREATE TABLE graph (
xaxis integer NOT NULL,
value integer NOT…
user4150760
- 1,129
- 3
- 14
- 20
47
votes
3 answers
Replace a materialized view in Postgres
I have a materialized view in Postgres 9.3 that I'd like to update with new columns. However, other materialized views also depend upon this view, and the error message indicates that dropping a view isn't possible when other objects depend on it. …
John
- 777
- 1
- 7
- 12
39
votes
1 answer
How do you create a view with SNAPSHOT_MATERIALIZATION in SQL Server 2017?
SQL Server 2017 has a couple new stored procedures:
sp_refresh_single_snapshot_view – input param for @view_name nvarchar(261), @rgCode int
sp_refresh_snapshot_views – input param for @rgCode int
And new entries in sys.messages:
10149 – Index…
Brent Ozar
- 43,325
- 51
- 233
- 390
34
votes
4 answers
Query the definition of a materialized view in Postgres
I'm wondering how to query the definition of a materialized view in Postgres. For reference, what I hoped to do is very similar to what you can do for a regular view:
SELECT * FROM information_schema.views WHERE table_name = 'some_view';
which…
Sean the Bean
- 600
- 1
- 4
- 11
33
votes
4 answers
Best way to create a materialized view in MySQL
I am using MySQL 5.6. I am not being able to a create materialized view like I can in Oracle. I have seen one or two solutions like Flexview.
Can anybody tell me best way to create a materialized view in MySQL (auto refresh like in Oracle ) with the…
Bhupendra Pandey
- 579
- 3
- 8
- 16
31
votes
2 answers
Using indexed views for aggregates - too good to be true?
We have a data warehouse with a fairly large record count (10-20 million rows) and often run queries that count records between certain dates, or count records with certain flags, e.g.
SELECT
f.IsFoo,
COUNT(*) AS WidgetCount
FROM Widgets AS…
Justin
- 930
- 1
- 7
- 11
20
votes
1 answer
Postgres: check disk space taken by materialized view?
I know how to check the size of indexes and tables in Postgres (I'm using version 9.4):
SELECT
relname AS objectname,
relkind AS objecttype,
reltuples AS "#entries", pg_size_pretty(relpages::bigint*8*1024) AS size
FROM pg_class
WHERE…
Richard
- 343
- 1
- 5
- 11
20
votes
1 answer
What factors go into an Indexed View's Clustered Index being selected?
Briefly
What factors go into they query optimizer's selection of an indexed view's index?
For me, indexed views seem to defy what I understand about how the Optimizer picks indexes. I've seen this asked before, but the OP wasn't too well received.…
EBarr
- 690
- 6
- 13
19
votes
3 answers
What happens if two process try to REFRESH MATERIALIZED VIEW CONCURRENTLY at the same time?
According to the docs:
CONCURRENTLY
Refresh the materialized view without locking out
concurrent selects on the materialized view. (...)
... OTHER CONTENTS ...
Even with this option only one REFRESH at a time may run against any
one…
ffflabs
- 345
- 1
- 3
- 11
19
votes
1 answer
How to check/know the highest run queries
I would like to gather statistics on the highest run queries in my database.
For that I need to know how I can track queries so I can create views or materialized views to cache the results of the highest run queries since I have a big DB.
mamesaye
- 395
- 2
- 3
- 9
18
votes
1 answer
Resolving deadlock from 2 tables only related through indexed view
I have a situation where I'm getting deadlocks, and I think I've narrowed down the culprits, but I'm not quite sure what I can do to fix it.
This is on a production environment running SQL Server 2008 R2.
To give you a slightly simplified view of…
Leland Richardson
- 241
- 2
- 9
17
votes
2 answers
DBCC CHECKDB unfixable corruption: Indexed view contains rows that were not produced by the view definition
TL;DR: I've got an unfixable corruption in an indexed view. Here are the details:
Running
DBCC CHECKDB([DbName]) WITH EXTENDED_LOGICAL_CHECKS, DATA_PURITY, NO_INFOMSGS, ALL_ERRORMSGS
on one of my databases produces the following error:
Msg 8907,…
usr
- 7,390
- 5
- 33
- 58
16
votes
3 answers
PostgreSQL 9.3.13, How do I refresh Materialised Views with different users?
[I think the root cause of this problem is me not understanding permissions and privileges...]
So, to set the stage, the set up I have is a DB, call it MyDb.
I have two users, spu1 and u1, spu1 is a superuser and u1 a 'regular' user. The owner of…
Alex
- 339
- 1
- 4
- 9
14
votes
1 answer
Refresh a PostgreSQL materialized view automatically without using triggers
I created a materialized view named view_table_A on a foreign data wrapper table table_A. What I want is for the view to be automatically updated after every new insert in table_A. I tried to do this with triggers but it didn't work.
Is it possible…
James
- 141
- 1
- 1
- 3