Stack Overflow
Stack Overflow

Questions tagged [materialized-views]

A materialized view is a database object that contains the results of a query. They can be local copies of data located remotely, or can be used to create summary tables based on aggregations of a table's data. Materialized views are also known as snapshots.

A materialized view is a database object that contains the results of a query. They can be local copies of data located remotely, or can be used to create summary tables based on aggregations of a table's data. Materialized views are also known as snapshots.

When posting questions with the materialized-views tag please add another tag with the RDBMS involved(Oracle, Sql Server, etc.)

Oracle's Materialized Views documentation here

839 questions
6
votes
1 answer

Get a list of materialized view log in oracle'

I want to get a list of materialized view logs in Oracle. How can I do that? I thought that this will give me a list: select * from USER_BASE_TABLE_MVIEWS; When a materialized view log was dropped and recreated by using the script similar to this…
user1860447
  • 1,316
  • 8
  • 25
  • 46
6
votes
1 answer

Postgres 9.4 hangs during refreshing materialized view

I am considering the problems with materialized views in Postgres 9.4. Sometimes query "refresh materialized view xxx" just hangs forever. The only way I found is to completely restart postgres service. After restart execution takes seconds. My view…
andrey
  • 179
  • 1
  • 6
6
votes
1 answer

Refreshing a materialized view does not include added column

From the manual CREATE MATERIALIZED VIEW is similar to CREATE TABLE AS, except that it also remembers the query used to initialize the view, so that it can be refreshed later upon demand. As I understand refreshing a materialized view should have…
Clodoaldo Neto
  • 118,695
  • 26
  • 233
  • 260
6
votes
1 answer

Redefine materialized view with no downtime

I have a materialized view that I need to redefine the SQL for. We have an external system that hits the view over a db link, and the monster view takes 5 minutes to refresh the data in the view. The only way I know how to redefine the SQL for a…
bitwes
  • 61
  • 1
  • 2
6
votes
1 answer

Change materialized view to on commit

I have a materialized view I would like to alter to on commit (from on demand) using fast refresh. However I constantly get ora-32337 cannot alter materialized view with pending changes refresh on commit even directly after a refresh (and knowing…
beginner_
  • 7,230
  • 18
  • 70
  • 127
5
votes
1 answer

Is there a way to query the changes made by a materialized view fast refresh in Oracle?

Say that you have two Oracle databases, DB_A and DB_B. There is a table named TAB1 in DB_A with a materialized view log, and a materialized view named SNAP_TAB1 in DB_B created with CREATE SNAPSHOT SNAP_TAB1 REFRESH FAST AS SELECT * FROM…
Aurelio Martin Massoni
  • 1,706
  • 3
  • 14
  • 19
5
votes
2 answers

Oracle - FAST REFRESH Materialized views with LEFT JOINS updates very slow

I have a Materialized view in Oracle that contains a LEFT JOIN which takes a very long time to update. When I update the underlying table it takes 63914.765 s to run (yes that is almost 17 hours). I am using a LEFT JOIN on the same table, because I…
Tejo
  • 547
  • 4
  • 15
5
votes
0 answers

TimescaleDB materialized view performance

I'm facing some performance issues with TimescaleDB continuous aggregates, and I don't fully understand why it happens. I have a table for my timeseries data: CREATE TABLE IF NOT EXISTS ack_alarm_number ( time TIMESTAMP NOT NULL, …
Oldook
  • 107
  • 5
5
votes
1 answer

ERROR: must be owner of materialized view PostgreSQL

I get the following error: ERROR: must be owner of materialized view mv_sessions SQL state: 42501 When trying to refresh my materialized view: REFRESH MATERIALIZED VIEW CONCURRENTLY partners.mv_sessions; The owner of mv_sessions is my custom…
Nikk
  • 7,384
  • 8
  • 44
  • 90
5
votes
1 answer

How to refresh a materialized view incrementally in PostgreSQL i.e. only for the data that is new or has changed?

How to implement fast refresh materialized view postgresql How to update only new changes to materialized view from base tables
Deepak
  • 51
  • 5
5
votes
0 answers

How to get the progress of the materialized view creation in PG

I have a create materialized view running for days now, is there a way to know the progress of the query? Any metric would be OK, e.g. percentage finished, time left, disk space to be consumed by the left work. I'm mainly concerned about the disk…
Incömplete
  • 853
  • 8
  • 20
5
votes
1 answer

Materialized View Performance of Exists vs In

I did some googling and couldn't find a clear answer to an oracle performance question. Maybe we can document it here. I am building an MV that is pretty simple but on fairly large tables. The query like many things can be written more than one…
Greg
  • 65
  • 4
5
votes
1 answer

Oracle - Materialized View confusion (is Toad IDE wrong in displaying MV in Tables section?)

I'm confused about Materialized views. Either it is the Toad IDE that I am using that is confusing me, or its that I don't understand MVs enough. I created a materialized view in Oracle by something like this.... CREATE MATERIALIZED VIEW…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
5
votes
2 answers

Snowflake invalid materialized view definition

When running in Snowflake the following command: CREATE MATERIALIZED VIEW MV_CUSTOMER_PREFERENCE as select * from V_CUSTOMER_PREFERENCE; I get the following error: SQL compilation error: error line {0} at position {1} Invalid materialized view…
user2129817
  • 85
  • 1
  • 7
5
votes
1 answer

Best caching strategy data that is updated frequently (Redis/Memcached vs Nginx/Varnish vs Materialized view)

I am currently running an AWS EC2 Ubuntu server that fetches data from a Postgres RDS database instance. One of the SQL queries used in a view function for a particular page has a lot of joins in it and runs quite slowly. I've tried to trim down the…
HaDoMin
  • 171
  • 4
  • 12
1 2 3
…
55 56