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
1
vote
0answers
24 views

MSSQL - Lock escalation on partitioned indexed view

I'm using partitioned tables where I set LOCK_ESCALATION to AUTO to prevent lock escalation to table level, but keep it on partition level. On the top of that table I have indexed view which is ...
0
votes
0answers
57 views

How does Optimistic concurrency interact with pessimistic concurrency in MSSQL

In MSSQL / Azure SQL, with Snapshots allowed and Read Committed Snapshot Isolation enabled, i have an application that does not use snapshot transactions explicitly (using the default RCSI everywhere)....
0
votes
1answer
235 views

Does CREATE/REFRESH MATERIALIZED VIEW in PostgreSQL use the parallel planner?

I am wondering whether CREATE (with data) or REFRESH MATERIALIZED VIEW uses the parallel planner? When I EXPLAIN ANALYZE the query by itself (any query with parallel potential works here) without ...
2
votes
2answers
79 views

How to make Indexed Views work for SQLCLR?

In SQL Server, the indexed view is a hellscape of limitations. But I need one. I have a formatter SQLCLR function that creates a prettified version of a domain key - users want to be able to search ...
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 ...
0
votes
0answers
26 views

Materialized view bigger than table it is based on?

I have a database that's ~130 GB and one table makes up for most of this (85 GB). Because this table holds a lot of duplicates, we have a materialized view which basically uses a window function to ...
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 ,...
22
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 ...
1
vote
1answer
32 views

Oracle Materialized View - Is it Safe to Use Only ROWID as Record Identifier?

this is a question related to Oracle 12c. I'm currently trying to create MVs as data sources for Golden Gate replication. We need fast refresh because We have to replicate 60,000 rows + at one time ...
0
votes
1answer
204 views

oracle-mysql fast refresh materialized view

Is is possible to Create Fast refresh enabled Materialized view from MySQL to Oracle . i know steps for oracle to oracle Fast refresh enabled Materialized view. But now the scenario is need to ...
2
votes
1answer
132 views

Implications of many materialized views in Postgres?

I'm working on a system that includes a scheduling component, which has support for recurring events. After reading many, many posts on storing recurring events, it was suggested that they be ...
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 ...
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 ...
1
vote
0answers
86 views

Do Hive Materialized Views respond to added partitions to source tables?

Hive Materialized Views support partitions, CREATE MATERIALIZED VIEW [IF NOT EXISTS] [db_name.]materialized_view_name [DISABLE REWRITE] [COMMENT materialized_view_comment] [PARTITIONED ON (...
0
votes
1answer
202 views

Create or refresh materialized view

How would you create new or otherwise refresh a materialized view in Postgres? I've tried something like this: CREATE OR REFRESH MATERIALIZED VIEW institution_mv AS SELECT entity_id, ...
1
vote
0answers
24 views

Azure SQL - Modifying Indexed Views without downtime

We have a SQL Azure database with a large indexed view that supports a website with high traffic during business hours. One of our goals from a DevOps perspective to be able to ship software (...
1
vote
1answer
34 views

Different results from select and from materialized view

select distinct on (p) coalesce(native_fcm_token, push_token) p from session where user_id = 2779 and ((push_token is not null) or (native_fcm_token is not null)) returns 27 rows refresh ...
1
vote
0answers
35 views

Cronjob to check when Oracle Materialized View was last updated?

I have two database users in my Oracle DB and want to create MV's in User B which get their data from User A through a database link. I plan to autorefresh the MV's every night but want to make sure ...
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....
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 ...
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....
0
votes
0answers
47 views

Create indexed view with fluctuating data behind

I´ve heard about indexed-views in MSSQL which can increase the performance. My problem is that I want to create a view which joins round about 5 tables. The tables are constantly updated and are ...
3
votes
2answers
4k views

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 ...
0
votes
0answers
35 views

mysql run out of memory during mview refresh

I want to create mview from mysql to Oracle using DBLink via odbc driver with Linux 64 bit os. but due to large number of Records around 2.5 cr records it raise the error ERROR at line 1: ORA-28500: ...
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 ...
0
votes
0answers
34 views

Is it possible to execute a postgresql function when any view is refreshed?

I want to alert my application that are utilizing views when the view is refreshed. Is this possible using PostgreSQL built-in functionality? If not are there any other approaches to tackle this?
3
votes
1answer
581 views

Create Nested XML in a Sql Server Indexed (Materialized) View

This page https://msdn.microsoft.com/en-us/library/ms188276.aspx indicates that if you wanted nested XML (ie an XML Tree) then you need to setup your query like this: SELECT Col1, Col2, ...
0
votes
1answer
656 views

Export Materialized View from postgresql as a table?

I have a materialized view I created in Postgresql. I was wondering is there a way to pg_dump the materialized view as if they were tables, so when I import them into another database it sees it as ...
0
votes
2answers
1k 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; ...
0
votes
0answers
15 views

Ιndexed view after replication

I am trying to find a smart (no redundunt dataflow) way to aggregate data on views/tables to be used from our reporting tools. One given is that the reporting database is different from the oltp one, ...
0
votes
1answer
71 views

How do I force a Nested Loop join on Materialized View and Table in Oracle 12c

There is a third party app which generates a high-cost query that has a join on multiple tables. I created a materialized view on some of the tables using the same join that is originated in the ...
0
votes
1answer
89 views

Getting ORA-32042 even though i can create the view in SQL Developer

I have a strange problem creating a materialized view using the code generated by the export wizard. I have created a materialized view and pasted following code into the sql query tab from the popup....
0
votes
1answer
297 views

Can there be situations where using (noexpand) on an indexed view is undesirable?

From the past experience using the schema bound and indexed view in a query with (noexpand) hint usually dramatically improves performance if the view is designed well. Apparently using the indexed ...
1
vote
0answers
33 views

How to aggregate transactions to form a stateful view which can be updated efficiently?

How to aggregate transactions to form a stateful view which can be updated efficiently? For example how to turn the following transactions: CREATE TABLE txns_combined(from_party,to_party,price,qty,...
1
vote
2answers
633 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
2answers
5k views

Trigger to update a materialized view once after relevant transaction?

I'm using PostgreSQL 9.6. I have a materialized view to support full-text search across two tables, which I'll call posts and tags. The tables are infrequently updated and frequently searched. I'm ...
1
vote
1answer
2k views

Refresh materialize View fast on commit multiple table

In a materialized view with refresh fast on commit i have the below query select pp.ROWID AS PP_ROWID,aup.rowid AS AUP_ROWID, pp.hierarchicode,pp.id, aup.userid from app_useracess aup,...
1
vote
0answers
244 views

What are the advantages of using a Materialized View directly on a table?

Recently, in enterprise's Oracle database, I have seen some materialized views that filter directly columns of table without adding aggregate fields and without calling custom functions. Example: ...
0
votes
1answer
445 views

Is using indexed view with no expand on standard edition a must?

I have recently read the text below in 70-762 Developing SQL Databases (p. 67): The first benefit of using an indexed view is that when you use it Enterprise Edition of SQL Server, it uses the ...
3
votes
1answer
384 views

Creating materialized view is very slow

I have quite a complex query in PostgreSQL 10.4 on Amazon RDS (3 joins, quite a few conditions). It takes from 1.5 to 6 seconds to perform a select. However, when I want to create a materialized view ...
1
vote
1answer
193 views

What is the proper procedure for fixing materialized view replication?

Oracle 10 Standard: I have a number of materialized views built on pre-built tables. These are fast refreshed from a remote database over a link from one master db. They've generally been working ...
0
votes
1answer
452 views

Create a materialized view on commit with PIVOT function

I'm trying to create a materialized view with on commit refresh while using a PIVOT on it's select. I have the following tables: Events: represents an event that the system process. It's like a ...
3
votes
0answers
107 views

Bloat on Materialized Views

The Postgres documentation says that materialized views with concurrent refreshes generate bloat as usual tables, because it executes deletes and inserts. For usual tables, there are some queries to ...
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 ...
0
votes
1answer
365 views

Postgres 9.5 Materialized View from a table with jsonb columns

For the purpose of improving performance of queries on a rather large table (5M+ rows) containing jsonb columns, I would like to create materialized views with data derived from those columns. ...
4
votes
1answer
307 views

Convert columns into rows for use with indexed/materalized view

I'd like to convert columns into rows for a given SQL Server table. The table effectively is an audit table; it stores a given old and new value in separate columns for a given user attribute (e.g. ...
0
votes
1answer
64 views

SQL Server - materialised (indexed) view not working as expected

I've created a materialised view in SQL Server, but when selecting from it I get the same performances and the same execution plan than when I select from the same query not "materialised". The view ...
1
vote
0answers
616 views

Autorefresh for Postgres materialised view?

Is it possible to set an automatic refresh frequency for materialized views? Here's the situation, I have an important but relatively small (20 thousand row) table that's subject to constant updates (...
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
354 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 ('...