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
7
votes
5 answers

Why does Redshift not need materialized views or indexes?

In the Redshift FAQ under Q: How does the performance of Amazon Redshift compare to most traditional databases for data warehousing and analytics? It says the following: Advanced Compression: Columnar data stores can be compressed much more than…
m0meni
  • 16,006
  • 16
  • 82
  • 141
7
votes
1 answer

Column names and data types for materialized views in PostgreSQL?

For general tables and views, I can see their data type by running the following query: select data_type from information_schema.columns where ..... However it does not seem that any information about materialized views appear here. I am able to…
1969877
  • 303
  • 1
  • 3
  • 12
7
votes
1 answer

Materialized view "invalidates" when adding constraint

I have a materialized view in an Oracle 10.2.0.50 database that looks like this: CREATE MATERIALIZED VIEW mv_cis NOCACHE NOLOGGING NOCOMPRESS NOPARALLEL BUILD IMMEDIATE REFRESH FORCE ON DEMAND WITH PRIMARY KEY…
Paolo Bergantino
  • 480,997
  • 81
  • 517
  • 436
7
votes
2 answers

Materialized View: How to automatically refresh it upon table data changes?

Is there a way in Oracle Materialized views so that it automatically refresh itself when there are changes on the tables used in the materialized view? What is the Refresh Mode and Refresh Method that I should use? What options should I use using…
Jemru
  • 2,091
  • 16
  • 39
  • 52
7
votes
1 answer

Oracle: insert-only materialized view

In Oracle, what is an insert-only materialized view? I have the following materialized view, which uses a MAX aggregate function: CREATE MATERIALIZED VIEW VM_FAST_MAX REFRESH FAST ON COMMIT AS SELECT d.ID_INPUT, MAX(d.ID_LOAD) AS ID_LOAD, COUNT(*)…
Danilo Piazzalunga
  • 7,590
  • 5
  • 49
  • 75
7
votes
2 answers

MySQL not using index with JOIN, WHERE and ORDER

We have two tables resembling a simple tag-record structure as follows (in reality it's much more complex but this is the essence of the problem): tag (A.a) | recordId (A.b) 1 | 1 2 | 1 2 | 2 3 | 2 .... and recordId…
Paso
  • 353
  • 2
  • 3
  • 13
6
votes
1 answer

Materialized Views: how can I find the number of updates, inserts, and deletes applied during refresh?

I have a data mart mastered from our OLTP Oracle database using basic Materialized Views with on demand fast refresh capability. Refresh is working fine. What I am interested in adding are some statistics about the refresh of each Materialized View,…
user1284595
  • 63
  • 1
  • 3
6
votes
3 answers

Why does DBMS_MVIEW.REFRESH have an implicit commit?

I noticed recently that calling dbms_mview.refresh(...), which refreshes materialized views in Oracle, has an implicit commit. Any ideas - other than "because it does" - why this action has an implicit commit?
Nick Pierpoint
  • 17,641
  • 9
  • 46
  • 74
6
votes
1 answer

How to alter materialized view with dependent views

I am currently having a series of materialized views View #1 and View #2 which I use for reporting. Using PGAdmin, I want to change the code of Materialized View #1 (just change the where clause, the structure of the view remains the same), but…
Arstotzka Hero
  • 61
  • 1
  • 4
6
votes
2 answers

Outbound integrations from Maximo: Is there a reason why materialized views are not a suitable choice?

I want to set up an outbound integration from Maximo 7.6.1.1 to an external system. Both Maximo and the external system have Oracle 12c databases. In the external system, I want to select open WOs from the Maximo WORKORDER table for the purpose of…
User1974
  • 276
  • 1
  • 17
  • 63
6
votes
1 answer

Materialized View Refresh On Commit

Suppose I have a table TABLE with two columns COL_1 and COL_2. I have a materialized view that simply reads TABLE.COL_1, and is set for: REFRESH FAST ON COMMIT. If I update TABLE.COL_2, does the materialized view refresh?
Johnny5
  • 6,664
  • 3
  • 45
  • 78
6
votes
1 answer

Partial refresh on Materialized View

We have a table TB_1 which has monthly and weekly data partitioned by monthly and weekly columns. We also have materialized view MV_1 inherited from the table TB_1. We would like to refresh the materialized view by certain weekly or monthly…
user416
  • 516
  • 3
  • 12
  • 34
6
votes
1 answer

Oracle query rewrite with virtual columns in the source table

I have a table, demo_fact in Oracle 11g and it has several virtual columns defined as such: ALTER TABLE demo_fact ADD (demo_measure_from_virtual NUMBER GENERATED ALWAYS AS (CASE WHEN demo_category_column = 20 THEN demo_numericdata_column ELSE 0…
Gudmundur Orn
  • 2,003
  • 2
  • 23
  • 31
6
votes
1 answer

Testing Materialized View in Rspec

Using the Scenic gem I've built an activerecord model backed by a materialized view class MatviewSales < ActiveRecord::Base self.table_name = 'matview_sales' self.primary_key = :id belongs_to :user belongs_to :account belongs_to…
Betjamin Richards
  • 1,071
  • 2
  • 12
  • 34
6
votes
1 answer

Oracle Disable Materialized View Refresh

Anyone have the syntax to disable the refresh of a materialized view in Oracle? I struggle with the Oracle documentation and I'm not finding an online example. I understand it starts something like: ALTER MATERIALIZED VIEW view_name ...
Jeff
  • 8,020
  • 34
  • 99
  • 157
1 2 3
…
55 56