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
3
votes
2 answers

Why are ScyllaDB MVs production ready and Cassandra MVs are still experimental and are not recommended to use?

Cassandra's MV are not production ready: Cassandra Materialized views impact Limitations:…
J. Doe
  • 12,159
  • 9
  • 60
  • 114
3
votes
3 answers

What is the advantage of using a Materialized View over a base table in Amazon Redshift?

Conceptually, I understand that materialized views are static representations of computed values, but I don't understand how that is functionally different from creating a table that contains the same pre-computed data. I would think a table could…
XaxD
  • 1,429
  • 14
  • 27
3
votes
1 answer

How to refresh Materialized View using DB link in Oracle

I have 3 schemas in Oracle. There's a Materialized View in the 3rd schema which I need to refresh from the 1st schema. Below is the elaboration of the requirement: uv1 (1st schema) --> db link to nwdb2 (2nd schema) --> nwdb3 (3rd schema) -->…
RAS
  • 8,100
  • 16
  • 64
  • 86
3
votes
2 answers

Laravel/Eloquent pagination and groupBy for a postgres materialized view model

I've created a materialized view: accounts_index that has a couple left joins, so I need to do a group by with the eloquent model. I also need to paginate the collection and am having trouble nailing the order of things. The materialized view:…
Matt Larsuma
  • 1,456
  • 4
  • 20
  • 52
3
votes
1 answer

Oracle: how to create a fast refresh materialized view that extracts data from XMLType?

I have a table xml_documents with two columns: a document_id column (primary key) and an xml column with some XML data, which is a schema-less XMLType. I can create a materialized view with just the document_id with: create materialized view mv…
avernet
  • 30,895
  • 44
  • 126
  • 163
3
votes
1 answer

Postgres: Materialized Views query taking longer to execute

I am new to using Postgres and I have a question concerning Materialized views. I am currently using Postgres 9.6.0 and PGAdmin 4. I have a query that is composed of a number of joins and when it's executed in PGAdmin, it takes approximately 13…
rm12345
  • 1,089
  • 3
  • 18
  • 32
3
votes
1 answer

Postgres 10.5: how to link an array of integers to primary keys of separate table?

I have two Postgres tables, TABLE_A and TABLE_B. TABLE_A contains column table_b_fks, whose values are an array of integers (e.g. "{1,2,4}"), which are of the set of primary keys for TABLE_B. TABLE_B has two columns, the primary keys and text.…
Kwhitejr
  • 2,206
  • 5
  • 29
  • 49
3
votes
4 answers

ScyllaDB 2.1 - Inconsistency with Materialized View

While deciding on the technology stack for my own product, I decided to go with scyllaDB for database due to it's impressive performance. For local development, I setup Cassandra on my Macbook. Considering ScyllaDB now supports (experimental) MV…
DroidBoyJr
  • 89
  • 1
  • 6
3
votes
1 answer

SQL Error: ORA-32361

When I create real-Time Materialized Views in Oracle Database 12c Release 2 I get SQL Error: ORA-32361. What does it mean? My view is simple, without any aggregation. CREATE MATERIALIZED VIEW example_t_mv REFRESH FAST ON DEMAND ENABLE QUERY…
Marek A
  • 31
  • 4
3
votes
1 answer

Oracle Materialized Views with primary key

I created the Oracle Materialized View below: CREATE MATERIALIZED VIEW MyMV REFRESH COMPLETE ON DEMAND AS SELECT t1.* FROM table1 t1, table2 t2 where t1.id=t2.id; The table1 has a primary key and the MV was created succesfully but the primary…
Ernesto Rodriguez
  • 257
  • 2
  • 9
  • 26
3
votes
1 answer

Change column type in a materialized view

I have a materialized view based on a table that on which a column type changed. and I'd like to change the column on the materialized view. I'm aware that a view is based on it's definition but I couldn't find how to updated the select in the…
bl0b
  • 926
  • 3
  • 13
  • 30
3
votes
1 answer

Subquery's rand() column re-evaluated for every repeated selection in MySQL 5.7/8.0 vs MySQL 5.6

I am doing a subquery in which I have a calculated column involving random number generation. In the base query I select this column twice. MySQL 5.6 works as I expect, the calculated value being called once and fixed. The 5.7+/8.0+ execution seems…
Pavel S.
  • 1,202
  • 1
  • 13
  • 29
3
votes
1 answer

PostgreSQL - REFRESH MATERIALIZED VIEW while pg_dump

(Postgres version: 9.3) Asking this after searching around without any solution: I create a simple materialized view (in practice, I have a few of them). psql my_db -c "CREATE MATERIALIZED VIEW my_view AS (SELECT * FROM my_table);" I call pg_dump…
gilad905
  • 2,842
  • 2
  • 16
  • 23
3
votes
1 answer

Can Materialized Views in PostgreSQL inherit?

I use PostgreSQL for time series data. There is an Event table and partitioned tables like Event_2016, Event_2017 which inherit from Event with a CONSTRAINT CHECK for the date range. So when querying the Event table, PostgreSQL uses only the child…
KingOfCoders
  • 2,253
  • 2
  • 23
  • 34
3
votes
1 answer

How to slow down cassandra to expose eventual consistency issues

I have cassandra with table and materialized views. The materialized views are eventually consistent with the table and dev has to be extra careful implementing algorithms against cassandra of not hitting issues like just added item seems to not be…
gsf
  • 6,612
  • 7
  • 35
  • 64
1 2 3
…
55 56