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]
253 questions
74
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,170
- 17
- 73
- 119
45
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,009
- 3
- 12
- 20
37
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
- 41,112
- 30
- 166
- 313
36
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
- 647
- 1
- 5
- 12
30
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
- 910
- 1
- 7
- 11
30
votes
3 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
- 541
- 2
- 7
- 16
27
votes
3 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
- 510
- 1
- 4
- 11
19
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
- 660
- 6
- 13
18
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
- 323
- 4
- 10
17
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
- 231
- 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,100
- 5
- 29
- 58
15
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
- 305
- 3
- 10
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
13
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
- 309
- 1
- 3
- 9
12
votes
1 answer
Is it possible to backup and restore a materialized view with it's data?
I have some hard to compute materialized views, which I want to be able to backup and restore using the actual stored data, and not by recomputing.
Is it possible in PostgreSQL 9.4?
(the alternative is to create actual tables, but they are harder…
Ophir Yoktan
- 223
- 2
- 6