Questions tagged [materialized-view]

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.

Filter by
Sorted by
Tagged with
56
votes
7answers
52k views

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 ...
19
votes
1answer
2k views

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 ...
16
votes
2answers
31k views

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 ...
28
votes
2answers
10k views

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....
18
votes
2answers
18k views

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....
15
votes
1answer
3k views

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 ...
7
votes
1answer
5k views

What's difference between standard view and indexed view in SQL Server [closed]

I learn that the indexed view holds the data from base tables while standard view does not holds data.Is it means that the indexed view need memory and standard view don't? Indexed view needs to be ...
32
votes
2answers
19k views

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 ( ...
14
votes
1answer
5k views

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::...
11
votes
2answers
20k views

Why in Oracle 11gR2 I can't drop the materialized view with the same user that created it?

I created a materialized view with the DI_TEST_AL user, let's name it MY_MVIEW. It appears in the USER_OBJECTS table as MATERIALIZED VIEW, I try to drop it, I get a success message, but the object is ...
14
votes
2answers
2k views

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 ...
5
votes
1answer
3k views

Postgres Refresh Materialized View Locks

I have a materialized view in Postgres, and want to know what locks (if any) are taken out when refreshing that view. CREATE TABLE people ( name VARCHAR(30) ); INSERT INTO people VALUES ('Alice'), ('...
10
votes
1answer
460 views

Why does the READPAST hint cause indexed views to be ignored?

I am investigating using the READPAST hint to reduce resource locking in our application's financial subsystem. It seemed like a good way to go because financial transaction records are only ever ...
7
votes
2answers
2k views

Using totals on aggregates to improve performance

I have two tables: details and totals of these details. Details (Slow solution): select OrderId = r.OrderId , TotalQty = SUM(r.Quantity) , ...
4
votes
1answer
1k views

Analyze needed after a refresh materialized view?

Are we supposed to run ANALYZE myview; on a PostgreSQL 9.6+ materialized view after running a REFRESH MATERIALIZED VIEW CONCURRENTLY myview;? Or is it useless (maybe index statistics are already ...
2
votes
1answer
2k views

List MATERIALIZED VIEWs in PostgreSQL that do not have a UNIQUE index

Postgresql 9.4 allows to REFRESH materialized views CONCURRENTLY, if they have a UNIQUE index. I am looking fo a SQL-query that lists all materialized views that DO NOT have such a UNIQUE or PRIMARY ...
2
votes
1answer
141 views

what would be a good choice of clustered index (and other indexes) for this situation involving a indexed view?

To help me improve the performance of some queries I have created the following indexed view: CREATE VIEW DBO.vwOrders WITH SCHEMABINDING AS /*-------------------------------------------------------...
1
vote
2answers
515 views

Why are some views listed as Materialized in MySQL explain statements if Materialized views don't exist in MySQL?

Hopefully the question is self explanatory. I've read that Materialized views do not exist in MySQL, yet sometimes I see something like this in EXPLAIN Can somebody clear this up for me? What's ...
1
vote
1answer
297 views

Group by's in materialized views and query planning [duplicate]

In part out of curiosity, I wondered if I could use an indexed (materialized) view to speed up a count query on some base table. The query is something like SELECT COUNT(*) FROM BaseTable WHERE Slot ...
1
vote
1answer
106 views

Why is an index I created not being used in a query execution?

Background: this question stemmed from another question I asked, so you can get additional DDL/indexes from there, if need be. I have a query inside an indexed view, like this: SELECT [a].[...
0
votes
2answers
662 views

Materialized views Performance

Postgres’ docs note: While access to the data stored in a materialized view is often much faster than accessing the underlying tables directly or through a view, the data is not always current; ...
-3
votes
1answer
1k views

Why does a VIEW with a CLUSTERED index not use the index?

I have a view that has a clustered index along with other non-clustered indexes. A simple SELECT query does not use any of the indexes even when I select an indexed column. What could be the ...
-3
votes
1answer
187 views

Optimizing an indexed view used for selects to also do updates

I have a materialized indexed view that returns ~2.6 million of the ~2.7 million rows in the table, causing the optimizer to mostly use clustered index scans. If I do an update on this view will it ...