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

Oracle materialized view question

I have a table that holds information about different events, for example CREATE TABLE events ( id int not null primary key, event_date date, ... ) I realized that 90% of all queries access only today events; the older rows are stored…
a1ex07
  • 36,826
  • 12
  • 90
  • 103
4
votes
1 answer

Does timescaledb support window functions?

I am trying to use the TimescaleDB extension to compute some continuous aggregates. I have this query which works fine: SELECT distinct time_bucket('1 hour', entry_ts) as date_hour, type_id, entry_id, …
jbx
  • 21,365
  • 18
  • 90
  • 144
4
votes
0 answers

Materialized Views on AWS Redshift

I am trying to create a materialized view using different schemas in the same database. But it is not allowing me to create with the error being: "ERROR: Materialized views cannot be defined on regular or late binding views." Below is the sql query…
Shyam V
  • 444
  • 4
  • 22
4
votes
2 answers

Add a new column to a Postgres materialized view

I am needing to add a new column to an existing materialized view in Postgres. According to this documentation: https://www.postgresql.org/docs/9.3/sql-altermaterializedview.html It says these are the options for columns: ALTER [ COLUMN ]…
DShultz
  • 4,381
  • 3
  • 30
  • 46
4
votes
1 answer

Select last record of each group as a materialised view bq

Is it possible to select the record with highest version of each group and store it as a materialised view in bigquery? Source table version name value 1 a 100 1 b 200 1 c 300 2 c 400 I only want to get the last…
Yang
  • 177
  • 4
  • 20
4
votes
1 answer

Materialized views with partitions in PostgreSQL

Can we create materialized views with partitions in PostgreSQL (version 10.7) ? Thanks
Babar
  • 65
  • 1
  • 7
4
votes
1 answer

Oracle - What happens when refreshing a 'REFRESH FORCE ON DEMAND' view with DBMS_MVIEW.REFRESH

I have the following materialized view - CREATE MATERIALIZED VIEW TESTRESULT ON PREBUILT TABLE WITH REDUCED PRECISION REFRESH FORCE ON DEMAND WITH PRIMARY KEY AS SELECT... FROM... WHERE... This materialized view has no backing MATERIALIZED VIEW…
contactmatt
  • 18,116
  • 40
  • 128
  • 186
4
votes
2 answers

Oracle - Materialized View alter structure so slow

I have a huge materailized view that I have to adjust. It's a simple adjustment as I'm just adding an NVL function to the select statement. I.e. Original... Select this, that..... I.e. Modified Select NVL(this, orThat) as this, …
contactmatt
  • 18,116
  • 40
  • 128
  • 186
4
votes
1 answer

Views VS Materialized Views

What's the actual difference between View and Materialized View? I have gone through lot of links where they said normal View is a like a virtual table and data cannot be stored on a disk where in case materialized data will get stores on the disk.…
Jaspreet Singh
  • 155
  • 1
  • 10
4
votes
1 answer

Business Reporting on an OLTP Application

We have an OLTP application using Oracle Database 10g Enterprise Edition, and plan to build a business reporting layer to meet the following needs. Sheilding complexity of the current OLTP database design Improving query performance of the current…
Sherry
  • 43
  • 6
4
votes
0 answers

TRIGGER or REFRESH MATERIALIZED VIEW in the background

Is it possible to execute a trigger (right now it will contain a refresh materialized view, but if there is a general solution, that would be better) in the background? So that the query returns before the trigger finishes? We have quite complicated…
Ákos Vandra-Meyer
  • 1,890
  • 1
  • 23
  • 40
4
votes
1 answer

ORACLE: Fast Refresh on Materialized View not working with OUTER JOIN in some conditions

problem is simple. I create TableParent and TableChild linked by foreign key. I create Materialized View Log for both. I create Materialized View with fast refresh as left join from TableParent to TableChild. Result is Adding a child record,…
kinghomer
  • 3,021
  • 2
  • 33
  • 56
4
votes
2 answers

MView "enable query rewrite" usage

CREATE TABLE TEST_DATE(COL1 VARCHAR2(20),COL2 NUMBER,COL3_DATE DATE,COL4_DATE DATE) / create materialized view TEST_SYS REFRESH FORCE ON DEMAND ENABLE QUERY REWRITE --- ???? AS SELECT COL1,COL2 FROM TEST_date WHERE TRUNC(SYSDATE) BETWEEN…
Chandra Bhushan
  • 375
  • 2
  • 5
  • 15
4
votes
1 answer

How to Fast Refresh on a Materialized View with Joins and Aggregates?

Suppose I have two tables job and batch: CREATE TABLE batch ( batch_id NUMBER(20) PRIMARY KEY, batch_type NUMBER(20), [some other values] ... ); CREATE TABLE job ( job_id NUMBER(20) PRIMARY KEY, job_batch_id NUMBER(20), job_usr_id…
Mike Carpenter
  • 197
  • 1
  • 2
  • 9
4
votes
2 answers

Materialized View vs SSAS Cube

Here is current scenario - We have 3 tables in Oracle DB (with millions of records) which are being used to generate SSRS reports. These reports are displaying complex data calculation such as deviations, median etc. SSRS fetch data using stored…
Prateek Singh
  • 863
  • 1
  • 8
  • 28
1 2 3
…
55 56