The Stack Overflow podcast is back! Listen to an interview with our new CEO.

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
57
votes
7answers
53k 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 ...
36
votes
1answer
4k views

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 ...
33
votes
2answers
20k 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 ( ...
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....
23
votes
3answers
14k views

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 ...
19
votes
2answers
19k 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....
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
33k 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 ...
16
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 ...
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::...
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 ...
12
votes
2answers
5k views

Risks of changing to ARITHABORT ON

I'm working with a vendor with the arrangement that they provide the core application, and I can build my own extensions as long as I don't modify the core application. It's built in ColdFusion ...
11
votes
2answers
9k views

Why do Indexed Views not permit non-unique clustered indexes?

I've been looking into using Indexed Views to increase performance on a few of our most commonly used views. However Indexed Views do not support non-unique clustered indexes which goes a little ...
11
votes
2answers
1k views

Indexed view in SQL Server

I have a table and an indexed view on it like Create table mytable1 (ID int identity(1,1), Name nvarchar(100)) Create table mytable2 (ID int identity(1,1), Name nvarchar(100)) Create view myview ...
11
votes
1answer
930 views

How to set up indexed view when SELECTing TOP 1 with ORDER BY from different tables

I am struggling to setup an indexed view in the following scenario so that the following query performs without two clustered index scans. Whenever I create an index view for this query and then use ...
11
votes
2answers
21k 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 ...
10
votes
1answer
3k views

Indexed view not being used - SQL Server [duplicate]

I've a query with multiple joins which takes long time to return data for one date. I've created an indexed view on the same. I set all the required options appropriately, while creating view and ...
10
votes
3answers
3k views

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

Limit redo for materialized view complete refresh or manual equivalent

A materialized view(MV) log can be used to allow a MV to do a fast refresh which only modifies the data that has changed. However, various conditions prevent the MV from using the log and therefore ...
10
votes
1answer
8k views

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 ...
10
votes
1answer
483 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 ...
10
votes
1answer
4k views

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

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

Update an Indexed View “Online”

Say I have an indexed view that looks like this: ALTER VIEW dbo.MyIndexedView WITH SCHEMABINDING AS SELECT ord.SomeColumn, COUNT_BIG(*) AS Count FROM dbo.Ordered ord WHERE ord....
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) , ...
7
votes
2answers
149 views

Need to include FKs in SQL Indexed View clustered index?

I'm using a SQL Server Indexed view to improve performance of a join that filters a (very) large table by a category on a small table. eg: CREATE VIEW BigTableSubset WITH SCHEMABINDING AS SELECT b....
6
votes
1answer
3k views

How much of a view is persisted when you create an index?

Say I have a view defined by the following SQL: SELECT t1.id, t2.name, t3.address FROM Table1 as t1 INNER JOIN Table2 t2 ON t1.id = t2.tID INNER JOIN Table3 t3 ON t1.id = t3.tID From what I ...
6
votes
1answer
486 views

Why SQL does not use the indexed view?

I have the following version of SQL Server: Microsoft SQL Server 2014 (SP3) (KB4022619) - 12.0.6024.0 (X64) Enterprise Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) I created ...
6
votes
1answer
4k views

How to do an incremental refresh of a PostgreSQL Materialized View?

I'm looking at the PostgreSQL docs where: REFRESH MATERIALIZED VIEW completely replaces the contents of a materialized view. The old contents are discarded. However, since 9.5 I believe, there is ...
6
votes
1answer
89 views

Indexed (Materialized) View for Parent and Newest Child

Say I have parent->child tables setup. Call them Shipment and Widget. Is there anyway to create an Indexed view that will have the PK of the parent (ShipmentId) and the created when of the newest ...
6
votes
2answers
3k views

Create index view with self join

I use SQL Server 2008 R2 and know that in Indexed View cant have self join. I have a tree table that have ID and ParentID Column and I need create indexed view on this table with self join between ...
5
votes
1answer
8k views

Does view take advantage of table indexes

I have a table with a clustered index and 2 non clustered index. Now, in my view I am selecting everything from my table create view dbo.MyView as Select * from MyTable Now, when I am using this ...
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'), ('...
5
votes
1answer
446 views

Materialized View PostgreSQL

Is there any possibility of having built-in materialized views in PostgreSQL ? I don't want Gardner version or somehthing like physical table populated by triggers. I've read that was/is something ...
5
votes
3answers
2k views

Recreating mat. view in Oracle failing due to dba_summaries entry, how to prevent?

I have run into the issue described here several times in our current project and I would like to understand how it happens and how to prevent it in the future. What is the meaning of DBA_SUMMARIES ...
5
votes
1answer
479 views

Why does a WHERE clause with an indexed, computed datetime column on an indexed view seek the clustered index on the original table

First, sorry for the long title. The setup I reduced the example to the minimum, to make it clearer, so there is no meaningful semantic anymore. DBMS: Azure SQL Database V12. Assume there is ...
5
votes
1answer
355 views

What is a good way to parameterize a materialized view on Postgres?

Given for example a materialized view like this (Postgres 10.3): create materialized view my_view as select * from my_table where sell_date < '2018-03-01'; The sell_date comparison value ('...
5
votes
1answer
1k views

Refresh Materialized View impact on the DB

Hi we are running an PostgreSQL 9.6 database in Amazon RDS under a m4.large(2cpu 8gb) and a provisioned IOPS of 1000. The use case is the following: We have a table with several million of registries (...
5
votes
1answer
467 views

Need ODBC connection to postgresql to include materialized views

I have an Access front end user interface that links to tables in a postgreSQL database using the most recent ODBC driver. I can link to the postgreSQL tables and views but can't get to the ...
5
votes
1answer
708 views

Why does dropping a MVIEW trigger a full refresh?

I just created a materialized view in Oracle but realized I used the wrong script, so I wanted to drop the MVIEW again. The MVIEW was created with a statement like this: create materialized view ...
5
votes
0answers
450 views

How do transactions affect PostgreSQL's REFRESH MATERIALIZED VIEW CONCURRENTLY command?

PostgreSQL 9.4 supports REFRESH MATERIALIZED VIEW CONCURRENTLY. I'm not sure, but I think the REFRESH command is viewed as DDL by PostgreSQL, and PostgreSQL wraps all DDL within transactions so that ...
4
votes
2answers
2k views

Materialized view for the last 24 hour period

I am collecting spatial point data that has an associated timestamp with it. I want to create a view of this data for the last 24 hour period. Because I am piping this spatial data through ArcGIS ...
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 ...
4
votes
1answer
3k views

postgres materialized views and indexes to improve aggregate performance

I have a large table with a number of materialized views used for pre-calculating aggregates. New data is inserted only once a day so think of this as a data warehouse type system. Obviously when I ...
4
votes
1answer
571 views

Indexed View over multiple partitioned tables and ALTER TABLE SWITCH

Is it possible to have an indexed view that combines 2 partitioned tables and still use ALTER TABLE SWITCH to move data out of both base tables? I'm receiving the following error when trying this and ...
4
votes
1answer
2k views

Deterministic function for getting today's date

I am trying to create an indexed view using the following code (so that I can publish it to replication it as a table): CREATE VIEW lc.vw_dates WITH SCHEMABINDING AS SELECT DATEADD(day, DATEDIFF(day,...
4
votes
1answer
3k views

Strange behaviours with SET options in SSMS

I am investigating the effect of various SET options on the creation of indexed views on SQL Server 2014 CU1. The following code: ensures that the SET options are as they should be creates a table ...
4
votes
1answer
839 views

Adding indices to views

I have a table that contains XML data in a varchar type field. This field is used to store data from a variety of different XML schemas some of which are related via a record stored in another table. ...
4
votes
1answer
91 views

MSSQL - How to support aggregate pushdown on indexed view with SUM(decimal)

I have an indexed view with non-clustered columnstore index, e.g. CREATE VIEW [dbo].[SalesAggregated] with schemabinding AS SELECT BusinessDate, ItemId ,...