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.

43 questions with no upvoted or accepted answers
Filter by
Sorted by
Tagged with
5
votes
0answers
446 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 ...
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 ...
3
votes
0answers
468 views

transaction replication causing too many deadlocks after adding a indexed view - what to monitor in order to avoid this?

I have created the following indexed view and its clustered index: This view was created on a subscriber database and all the tables used in the view are constantly being written to through the ...
2
votes
0answers
431 views

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Version 9.3) Asking this after searching around without any solution: I create a simple materialized view (in practice, I have a few of them). I call pg_dump to backup the database. While pg_dump ...
2
votes
0answers
207 views

Shouldn't DISCARD PLANS re-evaluate every function definition in postgres?

Hi have a set of functions that take a row of a table (let's call it bookings) as input parameters. Some of these functions call other functions, and they are used to calculate a materialized view. ...
2
votes
0answers
2k views

Postgres: How to log materialized view refresh

I would like to keep a record of when various Postgres materialised views are refreshed. As far as I can see there are no built in logs. The next obvious choice is to create a trigger, however it ...
2
votes
0answers
513 views

parallel pg_restore with materialized views

I am trying to restore a database that includes some materialized views. I am migrating from a standalone postgres installation to RDS. To troubleshoot the restore process, I have split the restore ...
2
votes
0answers
157 views

Promote Postgres standby while materialized view updates in primary

I have one primary Postgres server (serverA) and one standby Postgres server (serverB), both running Postgres 9.4 with Streaming Replication enabled. Is it possible to update a materialized view on ...
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 ...
2
votes
0answers
406 views

Refreshing Materialized view creates many child cursors

Doing some tests, I noticed that having REFRESH FAST ON COMMIT materialized view and flashbacks enabled cause Oracle to create many child cursors in some cases. For example, CREATE TABLE table1 (...
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 ...
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 ...
1
vote
0answers
82 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 (...
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
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 ...
1
vote
0answers
241 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: ...
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
0answers
611 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 (...
1
vote
0answers
54 views

Combining two event tables into one postgres view

I have two tables, events and event_logs. The events table stores the theoretical data on a recurring event whereas the event_log stores specific information on a particular datetime. Event +-----...
1
vote
0answers
118 views

Error creating aggregate materialized view

Trying to create an aggregate materialized view: CREATE MATERIALIZED VIEW MV_LLATTRDATA_MAX_VERSIONS NOLOGGING BUILD IMMEDIATE REFRESH FAST ON COMMIT ENABLE QUERY REWRITE AS ...
1
vote
0answers
314 views

An Indexed View slows updates to a crawl. Is there a hint to work around it?

I experimented with creating an Indexed View. I finally had a case which (almost) met the criteria for one. All inner joins, very few non-deterministic functions (which were moved to an outer view) ...
1
vote
0answers
407 views

Multiple UNIONs get slow. Indexing or materialized views?

Let me start by stating that my DBMS is PostgreSQL 9.4. I have a database with, say 30 tables. Each table has a mirror table with historical data, that is the same columns and some timestamps. To ...
1
vote
0answers
929 views

Oracle 12c fast refreshing nested materialized view problems

I have a couple of nested materialized views that fast refresh. They worked fine in Oracle 11g but since upgrading to Oracle 12c (12.1.0.2.0 on Solaris) I am occasionally getting duplicate rows (the ...
1
vote
0answers
485 views

Trying to find a MIN() workaround for an indexed view

I'm trying to speed up a Azure V12 SQL Server query using indexed views. I have one slow element, which I think is due to the use of GUIDs for the particular comparison column. The GUID can be mapped ...
1
vote
0answers
120 views

Query with join to a table on a different database need an index

I'm creating a query that one of its joins is made to a table from a different database. The query analyzer is suggesting the creation of an index, however I don't have permission to add objects to ...
1
vote
0answers
858 views

Update column type when materialized views depend on the column?

I'm working in Postgres 9.4. I want to alter the type of a column in a table: ALTER TABLE mytable ALTER COLUMN quantity TYPE integer; But I get this: ERROR: cannot alter type of a column used by a ...
1
vote
0answers
205 views

MQT automatic `set integrity off` when underlying table updated

Have a slow query. Need an MQT so the optimizer will rewrite the query and use my MQT instead. Problem: a manual full refresh is much much faster than using refresh immediate type or a staging table: ...
1
vote
0answers
108 views

When will SQL Server automatically use a supporting indexed view?

I have the following expensive query that runs against our data warehouse: SELECT d.DateYear, COUNT_BIG(*) AS WidgetCount FROM Widgets AS w JOIN Dates AS d ON d.DateId = w.DateId WHERE w....
1
vote
0answers
219 views

Materialized view for a yet-to-be-defined DBlink

In our product we have a main application with an oracle 11g DB and an optional module with another DB. We want the two to communicate through a dblink and I was told to put some code for the ...
0
votes
0answers
56 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
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 ...
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 ...
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: ...
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?
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
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 ...
0
votes
1answer
203 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 ...
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, ...
0
votes
0answers
46 views

In-line TVF is using indexed views in a UNION - parameter should eliminate most of them, except it doesn't

I am using SQL Server 2016-SP1-CU8 Here is the code to setup my scenario: --1. Drop indexed views (in case of re-running IF OBJECT_ID('Widgets_1', 'V') IS NOT NULL DROP VIEW Widgets_1 IF ...
0
votes
0answers
408 views

index of aggregate value with fast update?

Is it possible for me to index the sum of a particular table column, such that future lookups will not have to scan the entire table? This table will grow to be very large and a sum of the entire ...
0
votes
0answers
402 views

Is it possible to create materialized view based on another materialized view in postgres?

I have a very large table and I summarize it in a materialized view. I then create an even smaller materialized view based on the earlier mview. The second mv is always showing zero rows. Is there a ...
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 ...