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
20
votes
4 answers

How to implement Materialized View with MySQL?

How to implement Materialized Views? If not, how can I implement Materialized View with MySQL? Update: Would the following work? This doesn't occur in a transaction, is that a problem? DROP TABLE IF EXISTS `myDatabase`.`myMaterializedView`; CREATE…
GregJohn
  • 251
  • 1
  • 3
  • 5
20
votes
2 answers

When refreshing a materialized view, are indexes also refreshed automatically?

I'm currently using Postgres 9.3.3. If you refresh a materialized view, are the indexes on that materialized view also reindexed? Or do the indexes need to be manually reindexed? In looking at the following question, it looks like it but there is no…
thames
  • 5,833
  • 6
  • 38
  • 45
20
votes
5 answers

How to refresh all materialized views in Postgresql 9.3 at once?

I am loading a bunch of data into a PostgresQL 9.3 database and then I want to refresh all materialized views that depend on the updated tables. Is there a way to do it automatically instead of going through each view and refreshing them one by…
srk
  • 725
  • 1
  • 9
  • 22
18
votes
2 answers

List grants and privileges for a materialized view in PostgreSQL

I need to determine what privileges are currently granted for some materialized views in my database. The query to do this for a table or standard view is pretty straight forward: SELECT grantee, string_agg(privilege_type, ', ') AS privileges FROM…
Leah Sapan
  • 3,621
  • 7
  • 33
  • 57
16
votes
4 answers

Create Materialized view which refresh records on daily

Currently the Materialized view which I had created using REFRESH ON DEMAND so in this case I need to refresh MV explicitly using below command: BEGIN DBMS_MVIEW.REFRESH('MV_DATA'); END; But now I need to refresh this MV on daily basis so could…
cool_taps
  • 340
  • 1
  • 4
  • 16
13
votes
3 answers

Oracle materialized view error: code included

When I run the following code on Oracle 10g: drop materialized view test4; drop materialized view test3; drop table test2; drop table test1; create table test1 ( x1 varchar2(1000), constraint test1_pk primary key (x1) ); create materialized…
Clinton
  • 22,361
  • 15
  • 67
  • 163
13
votes
2 answers

Pros and cons of database triggers vs Rails ActiveRecord callbacks?

I am writing a program using Ruby on Rails and PostgreSQL. The system generates alot of reports which are frequently updated and frequently accessed by users. I am torn between whether I should use Postgres triggers to create the report tables (like…
yazz.com
  • 57,320
  • 66
  • 234
  • 385
12
votes
1 answer

Oracle - Materialized View still accessible during complete refresh. How does this work?

In one of our applications, we have a massive Materialized View that refreshes three times a day, and takes seven hours to refresh. (Not ideal, I know). This perplexed me, because I surely thought that users and sessions could not access this…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
12
votes
2 answers

ActiveRecord migration not populating a Postgres materialized view

I have a MATERIALIZED VIEW that is created via a migration. class MyView < ActiveRecord::Migration def up ActiveRecord::Base.connection.execute <<-SQL CREATE MATERIALIZED VIEW my_view AS ( SELECT DISTINCT something, something_else,…
Tyler
  • 19,113
  • 19
  • 94
  • 151
12
votes
3 answers

Why use NoSQL over Materialized Views?

There has been a lot of talk recently about NoSQL. The #1 reason why I hear people use NoSQL is because they start to de-normalize their DBMS data so much so, to increase performance, that they end up with just one table with all of their data…
JustinT
  • 2,481
  • 3
  • 18
  • 11
12
votes
2 answers

How do I discover the underlying query of a materialized view I created?

I created a materialized view in Postgres 9.3 but I have since lost the underlying SELECT query that created it. I would like to DROP the materialized view, rewrite the query to include more data, and then CREATE a materialized view of the same name…
stevevance
  • 381
  • 4
  • 10
12
votes
2 answers

Update materialized view when urderlying tables change

I have a materialized view defined this way: CREATE MATERIALIZED VIEW M_FOO REFRESH COMPLETE ON COMMIT AS SELECT FOO_ID, BAR FROM FOO WHERE BAR IS NOT NULL GROUP BY FOO_ID, BAR / COMMENT ON MATERIALIZED VIEW M_FOO IS 'Foo-Bar…
Álvaro González
  • 142,137
  • 41
  • 261
  • 360
11
votes
1 answer

Postgres Materialize causes poor performance in delete query

I have a DELETE query that I need to run on PostgreSQL 9.0.4. I am finding that it is performant until it hits 524,289 rows in a subselect query. For instance, at 524,288 there is no materialized view used and the cost looks pretty good: explain…
cizer
  • 213
  • 2
  • 7
11
votes
2 answers

Fast Refresh on commit of materialized view

I just created tables DEPT and EMP like follow : create table DEPT ( dept_no number , dept_name varchar(32) , dept_desc varchar(32), CONSTRAINT dept_pk Primary Key (dept_no) ); create table EMP ( emp_no number, dept_no number, CONSTRAINT emp_pk…
Deepak Venga
  • 127
  • 2
  • 2
  • 9
11
votes
2 answers

Refresh materialized views: Concurrency, transactional behaviour

The official PostgreSQL 9.3 documentation on REFRESH MATERIALIZED VIEW does not yet describe it in detail. A quote from this blog: materialized views in Postgres 9.3 have a severe limitation consisting in using an exclusive lock when refreshing it.…
Beryllium
  • 12,808
  • 10
  • 56
  • 86
1
2
3
…
55 56