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

How to indicate that a column is not nullable when creating a materialized view?

I am creating a materialized view using the following query: CREATE MATERIALIZED VIEW article_view AS SELECT id, alternative_headline, article_author_id, created_at, description, headline, preview_paragraph_image_id, published_at, …
Gajus
  • 69,002
  • 70
  • 275
  • 438
5
votes
1 answer

Refresh a materialized view in database transaction

Is it possible to refresh a materialized view in a database transaction? I'm writing test cases for complex queries in laravel which uses transactions to roll back after the test has been run. When i add data and refresh the view- no records appear…
Chris Mccabe
  • 1,902
  • 6
  • 30
  • 61
5
votes
2 answers

Use a Materialized View to track latest versions of records

We've got a highly (perhaps over?) normalized table that keeps track of versioned values. It's insert only, no updates. Example Data: "ID" "Version" "Value" 1 0 "A_1" 2 0 "B_1" 1 1 "A_2" 3 …
Tom Halladay
  • 5,651
  • 6
  • 46
  • 65
5
votes
1 answer

Can materialized views be used as a fast denomalized big table?

Can Oracle Materialized views be used to join multiple related tables having foreign keys to create a larger denormalized big table which is refreshed instantaneously? On some investigations, it says that joins are not allowed while using fast…
JavaRocky
  • 19,203
  • 31
  • 89
  • 110
5
votes
4 answers

oracle materialized view refresh time

anyone able to tell me how often a materialized view is set to refresh with the following setting plz? REFRESH FORCE ON DEMAND START WITH sysdate+0 NEXT (round(sysdate) + 1/24) + 1 i think i read it as every hour but i'm not sure
shaunf
  • 1,063
  • 4
  • 10
  • 11
5
votes
1 answer

Is it possible to set a primary key to a view?

Is it possible to set a primary key to a view in SQLServer 2008?
Andre Nascentes
  • 391
  • 4
  • 14
5
votes
2 answers

HibernateException when validating mapping to MATERIALIZED VIEW in postgresql 9.3

How can I map object to materialized view and keep validate? When launch webapp, I get this exception on startup: Caused by: org.hibernate.HibernateException: Missing table: subjects_lp at…
astafev.evgeny
  • 466
  • 4
  • 21
5
votes
3 answers

When refreshing the result of a view?

I materialize the result of a view in a table. I want to know if their is a way to know when the result of this view change to refresh it. Materialized view and result cache can do that but I don't want to use both techniques. Is their another way…
eliatou
  • 744
  • 5
  • 12
5
votes
2 answers

"too many declarations of 'SUBSTR' match this call" while refreshing Materialized view

I have a Materialized View set to REFRESH FAST ON COMMIT. There are 4 BLOB columns from the source table that I'm converting to VARCHAR2 as part of the MV: CREATE MATERIALIZED VIEW Employee_MV REFRESH FAST ON COMMIT WITH PRIMARY KEY AS SELECT…
Karun
  • 853
  • 1
  • 7
  • 15
5
votes
3 answers

Why does SQL Developer think there's an error in my materialized views?

I created some materialized views and Oracle SQL Developer puts a little red 'x' next to each of them. At the moment they are returning the correct information when I query them and running the following query in SQL Plus suggests that there are no…
user1578653
  • 4,888
  • 16
  • 46
  • 74
5
votes
2 answers

Drop normal or materialized view in Postgresql 9.3 in one query

I’ve got a view in my PostgreSQL, which can be both normal or materialized, depending on some circumstances. I'm trying to write a query that would drop the view for sure and with no errors no matter what type it has got at the moment. However, this…
Alexander Kachkaev
  • 842
  • 15
  • 29
5
votes
4 answers

materialized view creation is fast but refresh takes hours

I am using a materialized view, and I cant set it to fast refresh because some of the tables are from remote database which does not have materialized view log. When I create the materialized view, it took like 20 30 seconds. however when I was…
Yili Li
  • 131
  • 1
  • 1
  • 4
5
votes
2 answers

PostgreSQL script execution every night

I am trying to mimic snapshot materialized view based on this article on valena.com and have created the materialized views that I need. My next task is to execute the refresh materialized view scripts on a nightly basis in PostgreSQL. I am using…
cableload
  • 4,215
  • 5
  • 36
  • 62
4
votes
2 answers

Oracle refresh throws a table not found exception

Having simple refresh script: BEGIN DBMS_SNAPSHOT.REFRESH( LIST => 'SCHEMA_NAME.TABLE_NAME', PUSH_DEFERRED_RPC => TRUE, REFRESH_AFTER_ERRORS => FALSE, PURGE_OPTION => 1, PARALLELISM => 0, ATOMIC_REFRESH => TRUE, NESTED =>…
Denys S.
  • 6,358
  • 12
  • 43
  • 65
4
votes
2 answers

PostgresSQL 14 materialized views not updating

I have 20+ materialized views that I use as some sort of pre-calculated cache for a number of operations. Each night at 3 AM I do automated sync with an external source, followed by a series of materialized views refreshing. This is where it gets…
Jovan Perovic
  • 19,846
  • 5
  • 44
  • 85
1 2 3
…
55 56